Michael H Lutz (4/17/2010)
Great article. There is one thing I see though that is incorrect, the statement " This means that the execution plan for table variables will always treat the table as if it has 1 row. " is not true. I realize that SQL Server does not keep stats on indexes on table variables. But I have a counter-example to this claim.
If you create a table and load it with let's say a million rows. Then create a table variable (doesn't matter if it has a primary key or unique constraint the result is the same) and load the million rows into the table variable. Let's say the permanent table is TableA and the table variable is @TableA. Then run the following query:
SELECT COUNT(*) FROM (
SELECT @TableA.* FROM @TableA INNER JOIN TableA on TableA.SomeCol = @TableA.SomeCol) allrows
You will see in the actual execution plan that SQL Server knows the exact number of rows in the table variable. I found this to be true on SQL 2005 and 2008, and regardless of whether or not auto_create_statistics is on or off and auto_update_statistics is on or off. In some magical way SQL knows the number of rows at least in this case of the temp tables. It doesn't have stats I'm assuming, but it does know the # of rows. The estimated execution plan estimates 1 row for the table variable but the actual plan has the exact number of rows.
No, that just means that when running the query, it is able to get the proper count of records. The optimizer still treats it as only having one record.
Let me demonstrate that. This code builds a one-million row tally table into a table variable:
declare @test-2 table (N int PRIMARY KEY CLUSTERED);
TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),
MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),
TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)
INSERT INTO @test-2 (N)
SELECT N from TALLY ORDER BY N
select count(*) from @test-2
This produces this execution plan:
I've circled the two parts of interest... see how the "Estimated Number of Rows" is one? That's the optimizer treating the table variable as one row. Since the optimizer thinks that the table variable only has one row, the plan might not be the best for the query.