Table Variables
Table variables are kinds of variables that allow you to hold rows of data, which are similar to temporary tables.
Declare table variables
The name of the table variables must start with the @ symbol.
The scope of table variables
Similar to local variables, table variables are out of scope at the end of the batch.
If you define a table variable in a stored procedure or user-defined function, the table variable will no longer exist after the stored procedure or user-defined function exits.
Inserting data into the table variables
Once declared, the table variable is empty. You can insert rows into the table variables using the INSERT statement:
Querying data from the table variables
You can query data from the table variables using the SELECT statement:
Note that you need to execute the whole batch or you will get an error
Restrictions on table variables
1. You have to define the structure of the table variable during the declaration.
2. Unlike a regular or temporary table, you cannot alter the structure of the table variables after they are declared.
3. Unfortunately, table variables do not contain statistics. Therefore, you should use table variables to hold a small number of rows.
4. You cannot use the table variable as an input or output parameter like other data types. However, you can return a table variable from a user-defined function
5. 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.
6. If you are using a table variable with a join, you need to alias the table in order to execute the query
Performance of table variables
Using table variables in a stored procedure results in fewer recompilations than using a temporary table.
A table variable use fewer resources than a temporary table with less locking and logging overhead.
Similar to the temporary table, the table variables do live in the tempdb database, not in the memory.
When To Use? Use Table variable, if you have less than 1000 rows otherwise prefer Temporary tables.
Where Store:- A
Table variable will store in the physical memory for some of the data,
then later when the size increases it will be moved to the tempdb.
Using table variables in user-defined functions
