For The Last Time, It Doesn’t Matter How Many Rows You’re Putting In A Table Variable

Posted on by Erik Darling

For The Last Time, It Doesn’t Matter How Many Rows You’re Putting In A Table Variable


Thanks for watching!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Related Posts

8 thoughts on “For The Last Time, It Doesn’t Matter How Many Rows You’re Putting In A Table Variable”

  1. Hi Eric.
    I tested this on my (Microsoft SQL Server 2022 (RTM) – 16.0.1000.6) and the StackOverFlow2010 Db.
    I dont get that same queryplan when using a table variable.
    It uses “Parallelism” and is pretty fast even if estimates are off.
    I changed compatibility_level to 150

    I dislike table variables as much as you but I need to convince my devs to not use them by showing them when it´s bad using our servers and Db´s.
    In order to do that I have to try your stuff and see the same results as you.

    Could you send me the queries for Table variable and Temptable, so I am sure that the queries are correct or hint me to what could be going on?

    1. What you’re describing is impossible. Table variables do not under any circumstances allow for parallel inserts, unless the query is executed in a different context, like from dynamic SQL.

      Are you sure you’re using a @table_variable and not a #temp_table? If so, please show me what you’re doing. I’ve already shown what I’m doing 😃

      1. I was sure as hell I would get the same results as you so I guess i´m doing it wrong 🙂

        The part in queryplan that shows parallelism(Gather Streams):

        Query:
        DECLARE @tblvar TABLE
        (
        Id INT
        , INDEX ix CLUSTERED (Id)
        );

        INSERT INTO @tblvar
        (
        Id
        )
        SELECT dta.Id
        FROM
        (
        SELECT Id
        FROM
        Users
        WHERE
        Id = 22656
        UNION ALL
        SELECT TOP (99) Id
        FROM
        Users
        WHERE
        Reputation = 1
        ORDER BY CreationDate
        ) dta (Id);

        SELECT COUNT_BIG(1)
        FROM
        @tblvar t
        INNER JOIN
        Posts p
        ON p.OwnerUserId = t.Id
        INNER JOIN
        Comments c
        ON c.UserId = t.Id;

        1. Try it with these indexes:

          CREATE INDEX
          dog
          ON dbo.Posts
          (OwnerUserId)
          WITH
          (MAXDOP = 8, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

          CREATE INDEX
          cat
          ON dbo.Comments
          (UserId)
          WITH
          (MAXDOP = 8, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

  2. Gahh, xml wasn´t the best to send in a Reply obviously.
    Lets try it without the xml stuff.
    RelOp AvgRowSize=”15″ EstimateCPU=”0.00181815″ EstimateIO=”0″ EstimateRebinds=”0″ EstimateRewinds=”0″ EstimatedExecutionMode=”Batch” EstimateRows=”171.014″ LogicalOp=”Inner Join” NodeId=”2″ Parallel=”true” PhysicalOp=”Hash Match” EstimatedTotalSubtreeCost=”125.696″

      1. Yep, just like I said in the video, table variable modifications can’t use parallel plans. At no point did I say that select queries can’t.

        1. Of course. I was assuming that my plan would be more or less exactly like yours and didn´t listen very well to what you didn´t say 🙂
          I´ll pay better attention going forward.

          Thanks for your time and have a great weekend.

Comments are closed.