Updating table with table variable
Table variables can often avoid this recompilation hit.
For more information on why stored procedures may recompile, look at Microsoft knowledge base article 243586 (INF: Troubleshooting Stored Procedure Recompilation).
statements, except that they do not replace populated values with missing values unless you specify.
In addition, non-unique keys generate a warning, which is helpful for debugging.
Also, SQL Server does not maintain statistics on a table variable, and statistics are used heavily by the query optimizer to determine the best method to execute a query.
Neither of these restrictions should be a problem, however, as table variables generally exist for a specific purpose and aren’t used for a wide range of ad-hoc queries.
Constraints are an excellent way to ensure the data in a table meets specific requirements, and you can use constraints with table variables.
The following example ensures Product ID values in the table will be unique, and all prices are less then 10.0.
Secondly, the size of the resultset will determine which solution to choose.
Transactions touching table variables only last for the duration of the update on the table variable, so there is less locking and logging overhead.
Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure.
In general, I just add the option at the final steps that I need to do to create the master or transactional datasets.
It tends to save some time because an additional sorting step is prevented.
It’s also important to note how table variables do not participate in transaction rollbacks.