Are Table Variables a bad thing?

I too have found them to be extremely problematic. The issue comes down to the optimizer and how it treats them and the results of that treatment. The optimizer always assumes a single row. That's fairly well known and I think you mentioned it in the article. But, the question is, what happens when there are two or more rows? Generally, my advice has always been, if it's less than 100 rows (about, give or take, which direction is the window blowing), then you're probably OK to use TV. But, it's that word, "use" that digs the hole. Generally, I use them as temporary place holder and I avoid any actions on them other than insert & select if I can at all help it. As soon as you JOIN on them... things break down very quickly. I've recently seen execution plans that had 3-5 rows in a TV in a LEFT JOIN that produced horror show execution plans and substituting temp tables fixed the issue.

It's nuts, but I actively attempt to avoid their use unless I'm forced into it or I can limit it so that their advantages (of which there are a couple) come to the fore without their disadvantages (of which there are many) overwhelm them.

Similar to Kevin and Grant, I also have seen very poor performance from table vars, and just simply changing to a temp table usually fixes the issue. In fact, in was when I was investigating the reasons behind this that gave me the question "What is so different between a temp table and a table var to cause this kind of performance issue?", which led me to creating my Comparing Table Vars and Temp Tables article and subsequently create a presentation off of that which I now present at a lot of SQLSaturday events.

While I haven't seen an issue at one row like Kevin has, I have seen a query that joined to a table var with only 48 rows (and appropriately indexed) return the results in over 3 minutes... just changing to a temp table returns results in under 10ms.

It's been my experience that as soon as you either join a table var to any other table, or apply a where clause to a column in a table var, that performance takes a deep dive. Subsequently, if I'm doing a join or a where clause, I now only use temp tables. Since this is 99% of stuff I do, I essentially don't use table vars except in those places where SQL forces me to (table-valued functions or parameters). The only time I would choose to use them now is if I wanted to leverage their ability to be immune to explicit transaction rollbacks.

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. The data I used for CommonWords (useful test data) can be downloaded from here

       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

1) guidance from Microsoft is varied and problematic. All one need do is examine the database structures and code of Sharepoint to understand that there are lots of substandard things going on SQL Server wise within Microsoft. Oh, take a gander at the myriad of issues that come along with TSQL UDF usage too. :-) And how about the hugely outdated 1MB growth fragment size default for database files (and more importantly lack if exceptionally stressed guidance to NOT keep that for any database), 5 for cost threshold for parallelism, 1 file per cpu core for tempdb guidance, etc, etc.

2) Voiding parallelism for DML activity involving table vars has essentially nothing to do with the TVP itself. It is with that massive 6 table 50M row hitting SELECT statement that is populating it. THAT is where the perf problem comes into play.

3) But the simplest thing to keep in mind is that you are GUARANTEED to get SEVERAL bad things happening some/most of the time when you use table vars over temp tables as I have said and demonstrated. So the guidance should always be if you are not seeing the two reasons I said they should be used for (recompilation perf issues or the very esoteric auditing need for rolled-back transactions) then don't use them. You just have the guidance backwards is all.

4) BTW, I am surprised and disappointed that a tech reviewer for the article didn't call you out on some of the issues I brought up.

I have come across a few instances where TVs have been employed in code and have cringed when I see the est row count going into them. Usually re-writing the code to use a #table has brought significant improvements. I cant say every time as i havent recorded the occurrences.

I now recommend in the office to write options using both and compare but to expect anything with more than a handful of records in a TV to be the slower/worse option.

Christian Bolton was speaking at our user group a few weeks ago and in his parallelism and CXPacket wait session he suggested that seeing performance gains with TVs comes when they are used for few records - OK for months of the year and so on but when the record count goes up the performance drops of and the #table is favoured.