Getting statistics on a temp table in SQL Server

This site talks about how the statistics for a temp table can be out of date, i.e, can be stored for the last run

https://www.sql.kiwi/2012/08/temporary-tables-in-stored-procedures.html

Navigate to the section "Using a Temporary Table"

I've tried to replicate this with the code below.

The first issue was that the column "somecolumn" wasn't recognised. The answer to that seemed to be that the actual column names on temp tables get encoded, and the answer was to use "create statistics"

The second issue is that I'm not seeing the same results. The first time the SP is called I do get statistics out, i.e.

but the second time I don't

Here's the code. The table "employee" is a local table, to run this just replace it with some table in your db. The actual data doesn't matter, just so long as the table has a few rows

ALTER PROCEDURE dbo.TempTableTest
   @INDEX INT, @ROWCOUNT INT
AS
BEGIN

CREATE TABLE #TEMP
(
  SOMECOLUMN INT,
  SOMECOLUMN2 uniqueidentifier,
  SOMECOLUMN3 uniqueidentifier,
)
CREATE STATISTICS MYSTATS ON #TEMP (SOMECOLUMN)

insert into #temp
select top (@rowcount) 1,newid(), newid() from employee empa
join employee empb on 1=1
    
--UPDATE STATISTICS #Temp;

select count(*) FROM #TEMP 
select * FROM #TEMP WHERE SOMECOLUMN=@INDEX

-- this must follow the select on the #temp table

DBCC SHOW_STATISTICS (N'tempdb..#Temp', 'MYSTATS')
        WITH STAT_HEADER, HISTOGRAM;

        DROP TABLE #temp;
END
GO

Run this with

-- EXEC TempTableTest 2, 400

The first issue was that the column "somecolumn" wasn't recognised.

Wasn't recognised by DBCC SHOW_STATISTICS?

Running your code with the CREATE STATISTICS commented out (and using a convenient local table):

ALTER PROCEDURE dbo.TempTableTest
   @INDEX INT, @ROWCOUNT INT
AS
BEGIN

CREATE TABLE #TEMP
(
  SOMECOLUMN INT,
  SOMECOLUMN2 uniqueidentifier,
  SOMECOLUMN3 uniqueidentifier,
)
--CREATE STATISTICS MYSTATS ON #TEMP (SOMECOLUMN)

insert into #temp
select top (@rowcount) 1,newid(), newid() from dbo.Numbers AS N
join dbo.Numbers AS N2 ON 1 = 1
    
--UPDATE STATISTICS #Temp;

select count(*) FROM #TEMP 
select * FROM #TEMP WHERE SOMECOLUMN=@INDEX

-- this must follow the select on the #temp table

DBCC SHOW_STATISTICS (N'tempdb..#Temp', 'SOMECOLUMN')
        WITH STAT_HEADER, HISTOGRAM;

        DROP TABLE #temp;
END
GO
EXECUTE dbo.TempTableTest
    @INDEX = 2,   -- int
    @ROWCOUNT = 400 -- int

I see consistent results on the first and subsequent runs:

The answer to that seemed to be that the actual column names on temp tables get encoded, and the answer was to use "create statistics"

I think by "encoded" you are referring to the usual naming convention for automatically created statistics, i.e. prefix, column ordinal, hex representation of the object id.

For example, in _WA_Sys_00000002_5F141958, _WA_Sys_ is the prefix, 2 is the column ordinal, and 5F141958 is the object id of the temporary table in hexadecimal. The object id will change if the temporary table is not cached.

Using CREATE STATISTICS is not a good idea. As I wrote in Temporary Table Caching Explained:

Statistics created using an explicit CREATE STATISTICS statement are not linked to a cached temporary object, because CREATE STATISTICS is considered DDL, and prevents caching from occurring in the first place.

In other words, explicitly creating statistics prevents the behaviour you are hoping to observe.

The second issue is that I'm not seeing the same results. The first time the SP is called I do get statistics out

See above.

---

This is a moderately complex topic. Most people find they need to read both articles several times to really understand all the nuances. I tried to cover as many questions and edge cases as possible in the articles. I simply don't have time to respond to everyone individually to coach them through all the detail. Even so, I hope the above is of some assistance.