Updating a table variable

If you've been a DBA for any amount of time I'm sure you've been asked the question: Which is better to use, a temp table or a table variable?There are technical reasons why to use one over the other, but being that I am usually just interested in performance I am more concerned with which one will be faster and use fewer resources.

updating a table variable-25updating a table variable-41

As we can see from the results above a temporary table generally provides better performance than a table variable.-- Create index logic CREATE NONCLUSTERED INDEX [IX_temptable] ON #temptable ([col2] ASC) CREATE NONCLUSTERED INDEX [IX_bigtemptable] ON #bigtemptable ([col2] ASC) -- Populate tables DECLARE @val INT SELECT @val=1 WHILE @val To test the performance of these objects we are going to run statements for each of the 4 basic DML operations, SELECT/INSERT/UPDATE/DELETE.For each operation we are going run statements that effect both single and multiple rows.The only time this is not the case is when doing an INSERT and a few types of DELETE conditions.This increase in performance is especially evident when dealing with larger data sets as the ability to create indexes on the temporary table speeds up query execution significantly.

Leave a Reply