June 11, 2008 at 11:13 pm
please correct me if i am wrong. i'm pretty sure that statistics will not be maintained for table variable. but i read i none of our forums that if table variable has a pkey column, then statistics will be maintained.. is this true?? can someone please enlighten me on this???
thanks
June 12, 2008 at 12:23 am
It still doesn't have row statistics. Run the following with the execution plan enabled
DECLARE @Testing TABLE (
id INT PRIMARY KEY,
somecolumn uniqueidentifier
)
INSERT INTO @Testing
SELECT number, NEWID()
FROM master..spt_values WHERE NAME IS NULL
SELECT * FROM @Testing WHERE id > 75
If you look at the clustered index seek in the 2nd query you'll see estimated rows is 1, but the actual rows affected is a lot higher (1972 on my system)
If I compare this with a temp table (which does maintain statistics), the differences are quite obvious. Run this one with the exec plan enables and look at the clustered index seek in the 2nd query.
CREATE TABLE #Testing (
id INT PRIMARY KEY,
somecolumn uniqueidentifier
)
INSERT INTO #Testing
SELECT number, NEWID()
FROM master..spt_values WHERE NAME IS NULL
SELECT * FROM #Testing WHERE id > 75
DROP TABLE #Testing
On my machine, estimated rows 1972.7, actual rows 1972.
If you run both queries together, the other thing that's noticable is that the optimiser has costed the select from the table variable much lower than the select from the temp table (4% vs 12%), even thought they're doing the same thing. It's another effect of the lack of stats. Without accurate stats, the optimiser gets the cost wrong and the query appears better than it is.
Does that help?
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
June 12, 2008 at 12:29 am
so from this can i infer than even if we have a pkey col in a table variable defined, the statistics wont be there or rather it wont be accurate enough? thereby fooling the query optimizer..
June 12, 2008 at 12:32 am
The statistics won't be there. They are not kept on table variables.
For this reason mainly, table variables are best used for small numbers of rows. I prefer < 100.
It's not fooling the optimiser. It's that the optimiser doesn't have sufficient information to make an accurate estimate.
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
June 12, 2008 at 12:34 am
great. thanks for ur time..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply