Sql-server – Statistics on temp tables

sql serverstatisticstemporary-tables

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 @I=@I+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.

https://sqlkiwi.blogspot.com/2012/08/temporary-tables-in-stored-procedures.html

Any ideas?

Best Answer

You need to explicitly create a statistics object called id

CREATE PROCEDURE dbo.Demo
AS
BEGIN
SET NOCOUNT ON
-- Declare table variable
CREATE TABLE #temp_table (ID INT)
DECLARE @I INT = 0

CREATE STATISTICS id ON #temp_table (ID)

-- Insert 10K rows
    WHILE @I < 100
    BEGIN
        INSERT INTO #temp_table VALUES (@I)

        SET @I=@I+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;

EXEC dbo.Demo

When left to its own devices, the statistics objects created by the system have odd names like _WA_Sys_00000002_5F141958

Just note that when you create the statistics matters -- you'll need to move the CREATE STATISTICS command to after you populate the temp table with data if you want it to show anything, or possibly change your query to need to update stats, like SELECT * FROM #temp_table WHERE ID > 0.

Related Question