July 11, 2014

Difference between Temp Table, Table Variable and Common Table Expression (CTE)

Temp Table in SQL Server are temporarily created table in TempDB database. TempDB can be found on System Databases. It is stored as an object and created with the help of single pound sign (#).


Temp table acts just like normal table, it can have primary keys, constraints, indexes and statistics. It can be referenced in different places within the procedures but are limited to the SQL Server session or connection. Its better to use Temp table when there is lot more data to be handled with the help of indexes and statistics.

Example:

CREATE TABLE #TEMP
(
        ID INT,
        FIRSTNAME VARCHAR(50),
        LASTNAME VARCHAR(50)
)

GO

INSERT INTO #TEMP VALUESA (1, 'Avesh', 'Dhakal')

GO
SELECT * FROM #TEMP

Table Variable is like a temp table and are created in TempDB database. The main difference is primary key can be created in Table Variable but there cannot be indexes and statistics.  Primary key and unique constraint in the table declaration will automatically create an index. It acts much like variables in their scoping variables. They are created when they are declared and gets dropped once it comes out of the batch. If there isn't much more data to be handled and does not require indexes and statistics then Table variable can be used.

DECLARE @TABLE TABLE
(
        ID INT,
        FIRSTNAME VARCHAR(50),
        LASTNAME VARCHAR(50)
)

GO

INSERT INTO @TABLE VALUESA (1, 'Avesh', 'Dhakal')

GO
SELECT * FROM @TABLE

GO  -- table variable is not valid after this GO ends the batch
SELECT * FROM @TABLE -- This will throw an error because

Common Table Expression (CTE) also act like a temp table/view, but are not created in TempDB. It is created in memory. 

No comments: