How Many Types of Table in Transact-SQL

In this article I am going to explain about how how many types of table in Transact-SQL.
  • 1412

Introduction

Transact-SQL (T-SQL) is Structured Query Language and it is Microsoft's and Sybase's proprietary. Transact-SQL is originally developed by IBM for querying, altering and defining relational database. Transact SQL is also known as T-SQL. T-SQL supports many feature like variables, loops, conditions  and exceptions.  

Types of Table

 There are five type of tables in Transact SQL

  • Standard table
  • Temporary table
  • Table Variable
  • Derived table
  • View

Standard Table

Standard tables are available within the system until explicitly deleted.

Example

create table mcnemp

(

id int,

name varchar(15),

age int,

city varchar(15),

salary money

)


Temporary
Table

Temporary tables are available within the system while current database session is open.

Example

SELECT TOP 1 vendorid,SUM(invoicetotal)

AS inv_sum

INTO #ven_info

FROM dbo.mcninvoices

GROUP BY vendorid

ORDER BY inv_sum DESC

Table variable

Tables variable are available within a script while current batch is executing.

Example

USE vipendra

DECLARE @vendorinfo TABLE

(vendorid INT, vendorname VARCHAR(40))

 

INSERT @vendorinfo

SELECT vendorid, vendorname

FROM dbo.mcnvendors

WHERE vendorid IN

(

SELECT vendorid

FROM dbo.mcninvoices

WHERE invoicetotal>500

Derived Table

Derived tables are available within a statement while current batch is executing.

View

View are available within a system until explicitly deleted.


© 2013 dotNetheaven. All rights reserved.