Table Variable in SQL Server

Alternative of Temporary table is the Table variable which can do all kinds of operations that we can perform in Temp table.

In SQL Server we have a Data Type Table. We can make use of this data type to create temporary tables in database. Table variables are partially stored on disk and partially stored in memory. It's a common misconception that table variables are stored only in memory. Because they are partially stored in memory, the access time for a table variable can be faster than the time it takes to access a temporary table.Table variable is always useful for less data. If the result set returns a large number of records, we need to go for temp table. We don’t use CREATE command to Create Temporary Table Variable, we use DECLARE keyword to create Temporary Table Variable. As I have already mentioned that Table is a data type in SQL Server that is why we use declare keyword just like we use DECLARE keyword for any data type. Functions and variables can be declared to be of type Table.

A table variable behaves like a local variable. It has a well-defined scope. This is the function, stored procedure, or batch that it is declared in.

Within its scope, a table variable can be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements.

However, table variable cannot be used in the following statement:

SELECT select_list INTO table_variable;

Table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they are defined.

Here is the syntax for temporary table variable.

DECLARE @TempTableVariable (Column Data_Type Width, … n)

Let’s declare a Table variable and perform Insert and Select operation on it.

USE TEACHMESQLSERVER

GO

DECLARE @EMPTABLEVARIABLE TABLE(EMP INT, EMPNAME VARCHAR(MAX))

INSERT INTO @EMPTABLEVARIABLE VALUES(1,'SUMIT')

SELECT * FROM @EMPTABLEVARIABLE

GO

We can’t run INSERT and SELECT commands separately, we have to include DECLARE Table variable command otherwise it will give below error.

Msg 1087, Level 15, State 2, Line 1

Must declare the table variable "@EMPTABLEVARIABLE".

If you have less than 100 rows generally use a table variable.  Otherwise use a temporary table.  This is because SQL Server won't create statistics on table variables.

Click here to know about Differences Between Temporary Tables and Table Variable. Don’t forget to share on Facebook if you like the article.