{"id":853,"date":"2018-10-06T17:54:18","date_gmt":"2018-10-06T10:54:18","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=853"},"modified":"2020-04-11T20:13:09","modified_gmt":"2020-04-11T13:13:09","slug":"sql-server-table-variables","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-user-defined-functions\/sql-server-table-variables\/","title":{"rendered":"SQL Server Table Variables"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the SQL Server table variables that hold rows of data.<\/p>\n\n\n\n

What are table variables #<\/a><\/h2>\n\n\n\n

Table variables are kinds of variables that allow you to hold rows of data, which are similar to temporary tables<\/a>.<\/p>\n\n\n\n

How to declare table variables #<\/a><\/h2>\n\n\n\n

To declare a table variable, you use the DECLARE<\/code> statement as follows:<\/p>\n\n\n

DECLARE<\/span> @table_variable_name TABLE<\/span> (\n    column_list\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify the name of the table variable between the DECLARE<\/code> and TABLE<\/code> keywords. The name of the table variables must start with the @<\/code> symbol.<\/p>\n\n\n\n
Following the TABLE<\/code> keyword, you define the structure of the table variable which is similar to the structure of a regular table that includes column definitions, data type, size, optional constraint, etc.<\/p>\n\n\n\n
The scope of table variables #<\/a><\/h2>\n\n\n\n
Similar to local variables<\/a>, table variables are out of scope at the end of the batch.<\/p>\n\n\n\n
If you define a table variable in a stored procedure<\/a> or user-defined function<\/a>, the table variable will no longer exist after the stored procedure or user-defined function exits.<\/p>\n\n\n\n
Table variable example #<\/a><\/h2>\n\n\n\n
For example, the following statement declares a table variable named @product_table<\/code> which consists of three columns: product_name<\/code>, brand_id<\/code>, and list_price<\/code>:<\/p>\n\n\n
DECLARE<\/span> @product_table TABLE<\/span> (\n    product_name VARCHAR<\/span>(MAX<\/span>) NOT<\/span> NULL<\/span>,\n    brand_id INT<\/span> NOT<\/span> NULL<\/span>,\n    list_price DEC<\/span>(11<\/span>,2<\/span>) NOT<\/span> NULL<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Inserting data into the table variables #<\/a><\/h3>\n\n\n\n
Once declared, the table variable is empty. You can insert rows into the table variables using the INSERT<\/a><\/code> statement:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> @product_table\nSELECT<\/span>\n    product_name,\n    brand_id,\n    list_price\nFROM<\/span>\n    production.products\nWHERE<\/span>\n    category_id = 1<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Querying data from the table variables #<\/a><\/h3>\n\n\n\n
Similar to a temporary table, you can query data from the table variables using the SELECT<\/code> statement:<\/p>\n\n\n
SELECT<\/span>\n    *\nFROM<\/span>\n    @product_table;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that you need to execute the whole batch or you will get an error:<\/p>\n\n\n
DECLARE<\/span> @product_table TABLE<\/span> (\n    product_name VARCHAR<\/span>(MAX<\/span>) NOT<\/span> NULL<\/span>,\n    brand_id INT<\/span> NOT<\/span> NULL<\/span>,\n    list_price DEC<\/span>(11<\/span>,2<\/span>) NOT<\/span> NULL<\/span>\n);\n\nINSERT<\/span> INTO<\/span> @product_table\nSELECT<\/span>\n    product_name,\n    brand_id,\n    list_price\nFROM<\/span>\n    production.products\nWHERE<\/span>\n    category_id = 1<\/span>;\n\nSELECT<\/span>\n    *\nFROM<\/span>\n    @product_table;\nGO<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the partial output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
Restrictions on table variables #<\/a><\/h2>\n\n\n\n
First, you have to define the structure of the table variable during the declaration. Unlike a regular or temporary table, you cannot alter<\/a> the structure of the table variables after they are declared.<\/p>\n\n\n\n
Second, statistics help the query optimizer to come up with a good query’s execution plan. Unfortunately, table variables do not contain statistics. Therefore, you should use table variables to hold a small number of rows.<\/p>\n\n\n\n
Third, you cannot use the table variable as an input<\/a> or output parameter<\/a> like other data types<\/a>. However, you can return a table variable from a user-defined function<\/p>\n\n\n\n
Fourth, you cannot create non-clustered indexes for table variables. However, starting with SQL Server 2014, memory-optimized table variables are available with the introduction of the new In-Memory OLTP that allows you to add non-clustered indexes as part of table variable’s declaration.<\/p>\n\n\n\n
Fifth, if you are using a table variable with a join<\/a>, you need to alias<\/a> the table in order to execute the query. For example:<\/p>\n\n\n
SELECT<\/span>\n    brand_name,\n    product_name,\n    list_price\nFROM<\/span>\n    brands b\nINNER<\/span> JOIN<\/span> @product_table pt \n    ON<\/span> p.brand_id = pt.brand_id;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Using table variables in user-defined functions #<\/a><\/h2>\n\n\n\n
The following user-defined function named ufnSplit()<\/code> that returns a table variable.<\/p>\n\n\n
CREATE<\/span> OR<\/span> ALTER<\/span> FUNCTION<\/span> udfSplit(\n    @string<\/span> VARCHAR<\/span>(MAX<\/span>), \n    @delimiter VARCHAR<\/span>(50<\/span>) = ' '<\/span>)\nRETURNS<\/span> @parts TABLE<\/span>\n(    \nidx INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\nval VARCHAR<\/span>(MAX<\/span>)   \n)\nAS<\/span>\nBEGIN<\/span>\n\nDECLARE<\/span> @index<\/span> INT<\/span> = -1<\/span>;\n\nWHILE (LEN(@string) > 0) \nBEGIN<\/span> \n    SET<\/span> @index<\/span> = CHARINDEX<\/span>(@delimiter , @string<\/span>)  ;\n    \n    IF (@index = 0) AND (LEN(@string) > 0)  \n    BEGIN<\/span>  \n        INSERT<\/span> INTO<\/span> @parts \n        VALUES<\/span> (@string<\/span>);\n        BREAK  \n    END<\/span> \n\n    IF<\/span> (@index<\/span> > 1<\/span>)  \n    BEGIN<\/span>  \n        INSERT<\/span> INTO<\/span> @parts \n        VALUES<\/span> (LEFT<\/span>(@string<\/span>, @index<\/span> - 1<\/span>));\n        \n        SET<\/span> @string<\/span> = RIGHT<\/span>(@string<\/span>, (LEN<\/span>(@string<\/span>) - @index<\/span>));  \n    END<\/span> \n    ELSE<\/span>\n    SET<\/span> @string<\/span> = RIGHT<\/span>(@string<\/span>, (LEN<\/span>(@string<\/span>) - @index<\/span>)); \n    END<\/span>\nRETURN<\/span>\nEND<\/span>\nGO<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement calls the udfSplit()<\/code> function:<\/p>\n\n\n
SELECT<\/span> \n    * \nFROM<\/span> \n    udfSplit('foo,bar,baz'<\/span>,','<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this tutorial, you will learn how to use the SQL Server table variables which offer some performance benefits and flexibility in comparison with temporary tables.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Performance of table variables #<\/a><\/h2>\n\n\n\n
Using table variables in a stored procedure<\/a> results in fewer recompilations than using a temporary table<\/a>.<\/p>\n\n\n\n
In addition, a table variable use fewer resources than a temporary table with less locking and logging overhead.<\/p>\n\n\n\n
Similar to the temporary table, the table variables do live in the tempdb<\/code> database, not in the memory.<\/p>\n\n\n\n