Statistics on temp tablesPosted: - Source : stackoverflow
Quick and simple... why is this sp failing when I attempt to retrieve statistics for one of the columns on the temp table?
CREATE PROCEDURE dbo.Demo AS BEGIN SET NOCOUNT ON -- Declare table variable CREATE TABLE #temp_table (ID INT) DECLARE @I INT = 0 -- Insert 10K rows WHILE @I < 100 BEGIN INSERT INTO #temp_table VALUES (@I) SET @[email protected]+1 END -- Display all rows and output execution plan (now the EstimateRow is just fine!) SELECT * FROM #temp_table -- Is the object there SELECT OBJECT_ID('tempdb..#temp_table') -- How about statistics DBCC SHOW_STATISTICS ('tempdb..#temp_table', 'id') END;
I don't understand, I get a message saying that there is no statistics created on column id
Could not locate statistics 'id' in the system catalogs.
Having said that, I have seen an article by Paul White where this technique is used and does indeed work.