Grant Fritchey (10/8/2008)
Except for a primary key, table variables have not statistics where as temp tables do. This means that they are treated as one row tables for all execution plans, which can lead to some seriously bad performance.
Even the PK of a table var doesn't keep stats. Run the below code with the exec plan on and look at the estimated and actual rows on the index seek. When run in master on my server, I get Estimated rows 1, actual rows 191
DECLARE @test-2 TABLE (
id INT IDENTITY PRIMARY KEY,
name VARCHAR(50)
)
INSERT INTO @test-2 (name)
SELECT name FROM sys.columns
SELECT * from @test-2 WHERE id between 10 and 200
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability