SQL SERVER – How to Create Table Variable and Temporary Table?

This is a very simple question and indeed many of us know about this one. However, if you are absolutely beginner, you will for sure need help with this one. Let us understand how we can create a table variable and temporary table in this simple tutorial.

Temporary Table

Let us first learn how to create a temporary table.

CREATE TABLE #TempTable (ID INT IDENTITY(1,1))
GO

Now you can query the table just like a regular table by writing select statement.

SELECT * FROM #TempTable

As long as the session is active you can query the same table multiple times.

The table will be automatically dropped when you close the connection. If you want to explicitly drop the table you can execute the following command.

DROP TABLE #TempTable
GO

Just remember, any table which is created with # in the beginning is a temporary table and it is created in the temp database.

Table Variable

Table variables are also temporary objects and they are created as with DECLARE keywords.

Once you have successfully created a table variable, you can query the same table variable via following select statement.

DECLARE @TableVariable TABLE (ID INT)
SELECT ID FROM @TableVariable ;

You do not have to drop the table variable because as soon as the statement execution is completed, the table variable does not exist. In this example also you have to declare and select both of the statement together otherwise you will face an error where it says that table variable does not exist.

Additionally, please do not use keyword GO between DECLARE and SELECT statement when you are using table variable as it will reset the declaration of the table variable.

Is Table Variable Memory Only Object?

Here is the article where I have explained previously that table variable are created in a temporary database just like temporary table. Both of them exist on the disk. Here is the article you must read today – SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth

Reference: Pinal Dave (https://blog.sqlauthority.com)