I would very much like the experts advice on the subject of Table Variables and Temporary tables. What should be the recommendation on their use?
I wrote an article on Simple-Talk recently called Temporary Tables in SQL Server in which I said in the conclusion *"I'm always wary of providing over-generalized advice, but I always prefer my databases to use Table Variables, and TVPs wherever possible, They require less resource, and you're less likely to hold onto them when you're finished with them. I like to use them to the max, with column and table checks and constraints. You may find times when they run out of steam, especially when table sizes get larger. In cases like this, or where it isn't practical to use table variables because of their restricted scope, then I'll use local temporary tables.*"
In answer to a comment, I said "*I usually do the development work on a local table (#) and get a set of timings using a good hunk of data. I then change to table variables (@), being careful to put in constraints on the rows I use to join.I then run the tests again to make sure performance is the same. It usually is. If I hit a performance problem, I'll try option (recompile), on the assumption that it can't generate a good query plan. If I still can't get equivalent performance then I'll go back to using a local temporary table (#)"*
Kevin Boles, who knows a great deal on the subject, has commented at the end of the article *"Sorry Phil, but your advice to use table vars over real temp tables is completely contrary to proper guidance. There are optimization limitations (especially the lack of statistics) that very frequently lead to horrible query plans. A SINGLE ROW table variable can get you a suboptimal plan compared to the same query using a temp table, so the very common "use table vars if you have less than NNN rows" advice is flawed. I also don't understand your statement about resource differences between the two.*
*There are 2 reasons where table vars are appropriate and they are VERY infrequently found in the wild: 1) extremely high-call-frequency code where recompilations from temp table activity are a problem a 2) audit scenarios where you need to keep information after a transaction rollback has occurred.*
*I will also take exception to the advice to use constraints, checks, indexes, etc. In 15+ years of relational engine consulting I have eliminated those things (especially indexes) from temp tables to improve performance at least an order of magnitude more frequently than I have added them.'*
I'm puzzled that I'm getting strong advice from more than one MVPs whose advice I respect, that one should not use such an important part of SQL Server as table variables and TVPs. I'm aware of their limitations (I covered them in the article) but my feeling is that the compromises made by Microsoft SQL Server Database Engine team in designing TVs and TVPs have been sensible for the general usage cases of table variables. Lack of Parallelism, for example, isn't necessarily a disaster for performance, for smallish tables surely? I'd always felt that if the database developer was aware of the risks and issues, then it was reasonable for them to judge when and where in their work it is better to use a Table Variable rather than a temporary table.
So. What do you think? Obviously, if I've got it wrong, I'll get the article amended, as I'd hate to think of anyone being misled by my advice!
Answer by Grant Fritchey ·
Answer by WayneS ·
Answer by Fatherjack ·
Answer by KGB ·
Answer by Phil Factor ·
Here is a simple test to explore the differences between Table Variables and temporary tables in handling a simple join. This is a variation of the test that Kevin Boles used to show '6668 IO BAD PLAN'. I've compared the four possible combinations of using a small table joining to a much larger. When I ran it, (and you might find different results) there was a difference in the parallelism but no significant difference in the four query plans. All ran in under 3 Ms. SQL Server 2008 r2.
dbcc freeproccache --SET STATISTICS IO on DECLARE @Words TABLE (Word varchar(40) PRIMARY KEY) CREATE table #Words (Word varchar(40) PRIMARY KEY) SET STATISTICS PROFILE ON INSERT INTO @Words (word) --no parallelism inserted 60387 words SELECT CommonWords.string FROM CommonWords INSERT INTO #Words (word) --yes parallelism inserted 60387 words SELECT CommonWords.string FROM CommonWords SET STATISTICS PROFILE off /* so now we have a table variable of around 60,000 words and a temporary table with 60,000 words*/ DECLARE @WordsToMatch TABLE (Word varchar(40)) --create a sample of words to join with the list of 60,000 common words --and put them in a table variable inSERT INTO @WordsToMatch (word) values ('Temporary'),('tables'),('are'),('used'),('by'),('every'),('DB'),('developer,'),('but'),('they''re'),('not'),('likely'),('to'),('be'),('to'),('adventurous'),('with'),('their'),('use'),('or'),('exploit'),('all'),('their'),('advantages'),('They'),('can'),('improve'),('your'),('code'),('performance'),('and'),('maintainability'),('but'),('can'),('be'),('the'),('source'),('of'),('grief'),('to'),('both'),('developer'),('and'),('DBA'),('if'),('things'),('go'),('wrong'),('and'),('a'),('process'),('grind'),('away'),('inexorably'),('slowly'),('We'),('asked'),('Phil'),('for'),('advice'),('thinking'),('that'),('it'),('would'),('be'),('a'),('simple'),('explanation') CREATE table #WordsToMatch (Word varchar(40)) INSERT INTO #WordsToMatch SELECT word FROM @wordstomatch --and also put them in a table variable /* now we'll run all four possible combinations of temporary table and table variable and see what sort of plan is being generated in each case */ SET STATISTICS PROFILE ON SELECT COUNT(*) FROM @words commonwords INNER JOIN @wordsToMatch intro ON commonWords.word=intro.word SELECT COUNT(*) FROM #words commonwords INNER JOIN @wordsToMatch intro ON commonWords.word=intro.word SELECT COUNT(*) FROM @words commonwords INNER JOIN #wordsToMatch intro ON commonWords.word=intro.word SELECT COUNT(*) FROM #words commonwords INNER JOIN #wordsToMatch intro ON commonWords.word=intro.word SET STATISTICS PROFILE OFF /* now we'll run a simple set of timings just to see how long each join took (in Milliseconds) */ dbcc freeproccache DECLARE @log TABLE ( Log_Id INT IDENTITY(1, 1),TheeVent VARCHAR( 2000 ), DateAndTime DATETIME DEFAULT GetDate()) ; INSERT INTO @log (TheEvent) SELECT 'both table variables'; SELECT COUNT(*) FROM @words commonwords INNER JOIN @wordsToMatch intro ON commonWords.word=intro.word INSERT INTO @log (TheEvent) SELECT 'large table variable joined to small temp table'; SELECT COUNT(*) FROM #words commonwords INNER JOIN @wordsToMatch intro ON commonWords.word=intro.word INSERT INTO @log (TheEvent) SELECT 'Large temp table small table variable'; SELECT COUNT(*) FROM @words commonwords INNER JOIN #wordsToMatch intro ON commonWords.word=intro.word INSERT INTO @log (TheEvent) SELECT 'Both temporary tables'; SELECT COUNT(*) FROM #words commonwords INNER JOIN #wordsToMatch intro ON commonWords.word=intro.word INSERT INTO @log (TheEvent) SELECT 'end'; SELECT TheStart.TheeVent + ' took ' + CAST( DatedIff( ms, TheStart.DateAndTime, Theend.DateAndTime ) AS VARCHAR( 10 )) + ' Ms.' FROM @log TheStart INNER JOIN @log Theend ON Theend.Log_Id = TheStart.Log_Id + 1; DROP table #WordsToMatch DROP table #Words
Answer by NickDech ·
Answer by drueter ·
Answer by licentiat ·
Answer by drueter ·
Answer by TimothyAWiseman ·