Is it possible to create indexes on a temp table when using SELECT INTO?

Ian R. O'Brien picture Ian R. O'Brien · Dec 21, 2012 · Viewed 20.3k times · Source

I am loading data from a CSV file into a temp staging table and this temp table is being queried a lot. I looked at my execution plan and saw that a lot of the time is spent scanning the temp table.

Is there any way to create index on this table when I SELECT INTO it?

SELECT *    
FROM TradeTable.staging.Security s
WHERE (
    s.Identifier IS NOT NULL
    OR s.ConstituentTicker IS NOT NULL
    OR s.CompositeTicker IS NOT NULL
    OR s.CUSIP IS NOT NULL
    OR s.ISIN IS NOT NULL
    OR s.SEDOL IS NOT NULL
    OR s.eSignalTicker IS NOT NULL)

enter image description here

Answer

Martin Smith picture Martin Smith · Dec 21, 2012

The table created by SELECT INTO is always a heap. If you want a PK/Identity column you can either do as you suggest in the comments

CREATE TABLE #T
(
Id INT IDENTITY(1,1) PRIMARY KEY,
/*Other Columns*/
)

INSERT INTO #T 
SELECT *
FROM TradeTable.staging.Security

Or avoid the explicit CREATE and need to list all columns out with

SELECT TOP (0) IDENTITY(int,1,1) As Id, *
INTO #T
FROM TradeTable.staging.Security

ALTER TABLE #T ADD PRIMARY KEY(Id)

INSERT INTO #T 
SELECT *
FROM TradeTable.staging.Security