Updating table with table variable

Rated 4.46/5 based on 958 customer reviews

The table definition of a table variable cannot change after the DECLARE statement.

Any ALTER TABLE query attempting to alter a table variable will fail with a syntax error.

Knowing what we have learned so far, how do you decide on using a table variable or a temporary table?

First, we know there are situations that which demand the use of a temporary table.

It’s also important to note how table variables do not participate in transaction rollbacks.

The variable will no longer exist after the procedure exits - there will be no table to clean up with a DROP statement.To demonstrate, the following query batch will return a count of 77 records even though the INSERT took place inside a transaction with ROLLBACK.DECLARE @Product Totals TABLE ( Product ID int , Revenue money ) BEGIN TRANSACTION INSERT INTO @Product Totals (Product ID, Revenue) SELECT Product ID, SUM(Unit Price * Quantity) FROM [Order Details] GROUP BY Product ID ROLLBACK TRANSACTION SELECT COUNT(*) FROM @Product Totals Now you’ve come to a stored procedure that needs temporary resultset storage.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).

Leave a Reply