5

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?

4

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.

  • I realized that the key thing here (in your answer) as far as I'm concerned, is the "change your query to need to update stats" because, even without issuing a CREATE STATISTICS, my sample sp will not fail as long as the query involves using that column (which is precisely what Paul White did in his article). – Martin Surasky May 18 '17 at 17:44
  • 1
    I changed my original SP to include a WHERE clause in the SELECT * FROM #temp_table and things were good (like I said, even without the CREATE STATISTICS statement). – Martin Surasky May 18 '17 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.