skip to content »

Updating table variables

We can insert values using INSERT INTO clause in table variable.

We can return a table variable from user-defined function.Therefore, cardinality estimate for the table variable is always 1.If you always insert small number of rows into the table variable, it may not matter.But if you insert large number of rows into the table variable, the query plan generated (based on 1 row assumption) may not be that efficient.As part of supportability improvement, SQL Server 2012 Service Pack 2 made an improvement.This improvement will help in situations where you have large number of rows inserted into a table variable which joins with other tables.

A new trace flag 2453 is introduced to activate this improvement.

When SQL Server detects enough rows inserted into the table variable, it will then recompile the subsequent statements referencing the table variable.

Math Works Machine Translation The automated translation of this page is provided by a general purpose third party translator tool.

Math Works does not warrant, and disclaims all liability for, the accuracy, suitability, or fitness for purpose of the translation.

In a previous blog, I talked about how table variable can impact performance of your query.

The reason is that the statements referencing the table variable are compiled when the table variable has no data in it.