How can Temp Table creation time be reduced in terms of CPU time?

Inside a frequently executed stored procedure (sometimes up to 10-15 times per second), there are two particular statements that show up in Top 10 heaviest queries by cumulative CPU impact

These are DDL statements to create #Temp tables:

1.

SELECT cast(0 as int) as rowId
     , Column1 as tColumn1
     , Column2 as tColumn2
     , ...
     , Column14 as tColumn14
     , cast(0 as datetime) as tUTC
     , -1 as tRefId
INTO #TempTable1
FROM Table1 WITH(NOLOCK)
WHERE 0=1

-

SELECT    tColumn1, ..., tColumn14, tUtc, tRefId
INTO    #TempTable2
FROM    #TempTable1
WHERE    0=1

Any of above DDL, take on average 10-15 ms CPU time, simply to create a #temp table

Changing stored procedure's logic, to make it NOT create temp tables, is not an option

Question: How can temp table creation time be sped up, in terms of CPU time ?

SELECT  ...  INTO  ... WHERE 0=1

might be a convenient way to create an empty table but I would expect a simple CREATE TABLE DDL statement to be more efficient.

There is no need for the CREATE TABLE to go through the plan compilation process (to compile an execution plan that gets simplified down to a select from a constant scan) and then execute it.

Potentially more importantly (for this discussion) the CREATE TABLE version inside a stored procedure may also allow better use of SQL Server Temporary Object Caching to avoid much of the work required for creation at all.

For this to happen you need to avoid certain constructs that prevent caching. See the linked post for more details. One of those is 'Perform "DDL" after object creation'.

CREATE TABLE allows indexes and constraints to be declaratively defined at creation time.

SELECT ... INTO would require them to be added post create so this route is more likely to end up in a state where the table does not meet the requirements for caching.

StackExchange Database Administrators Q#305656, answer score: 9