Clustered index on temp table

sql sql-server tsql temp-tables clustered-index
David George picture David George · Oct 5, 2011 · Viewed 13.3k times · Source

I'm trying to optimize a procedure that has code like the following:

CREATE TABLE #t1 (c1 int, c2 varchar(20), c3(varchar(50)...)

CREATE CLUSTERED INDEX ix_t1 ON #t1(c3) ON [PRIMARY]

I wanted to improve that by moving the CLUSTERED index into the table declaration (more caching friendly), but c3 is not unique so this doesn't work:

CREATE TABLE #t1 (c1 int, c2 varchar(20), c3 varchar(50)..., UNIQUE CLUSTERED (c3))

Is there a way to declare a clustered that is not unique in the temp table declaration?

Answer

Peter Henell picture Peter Henell · Oct 4, 2017

Yes, it is possible in SQL Server 2014 and above, Create table on MSDN. From 2014 you can specify the indexes inline with the create table statement.

 if object_id('tempdb..#t1') is not null drop table #t1;

CREATE TABLE #t1 (
    c1 int, 
    c2 varchar(20), 
    c3 varchar(50), 

    index [CIX_c3] CLUSTERED (c3),
    index [IX_c1] nonclustered (c1)
)

insert #t1(c3) values ('a'), ('a'), ('a')

select * from #t1

Related questions

Temporary table in SQL server causing ' There is already an object named' error

I have the following issue in SQL Server, I have some code that looks like this: DROP TABLE #TMPGUARDIAN CREATE TABLE #TMPGUARDIAN( LAST_NAME NVARCHAR(30), FRST_NAME NVARCHAR(30)) SELECT LAST_NAME,FRST_NAME INTO #TMPGUARDIAN FROM TBL_PEOPLE When I …

Read More
TSQL select into Temp table from dynamic sql

This seems relatively simple, but apparently it's not. I need to create a temp table based on an existing table via the select into syntax: SELECT * INTO #TEMPTABLE FROM EXISTING_TABLE The problem is, the existing table name is accepted …

Read More
Is it possible to have temp tables in a function?

Apparently, I can't use them. I'm getting an error message like: Invalid use of a side-effecting operator 'SELECT' within a function If I want to do something like this: select bleh into #temp from Blah ... inside a function.

Read More