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.
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.