CODEWITHSUNDEEP
sqlsql-servert-sqltemp-tablesclustered-index
David Mathis
David Mathis

Reputation: 267

Clustered index on temp table

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?

Upvotes: 8

Views: 24207

Answers (3)

Greg V
Greg V

Reputation: 11

This can be done by adding an identity column, such as:

CREATE TABLE #t1 (rowID int not null identity(1,1),
                     c1 int, c2 varchar(20), c3 varchar(50),
                     UNIQUE CLUSTERED (c3,rowID)
                 )

Including the rowID in the index will insure that it is unique, even if c3 is not.

You verify the index created with:

EXEC tempdb.dbo.sp_helpindex '#t1'

Upvotes: 1

Peter Henell
Peter Henell

Reputation: 2466

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

Upvotes: 5

Wil
Wil

Reputation: 4270

No there is not...the existence of the ability to define clustered as an option in table creation is to support declaring primary key and unique column constraints, which themselves create indexes. In other words, CLUSTERED in the CREATE TABLE statement is specifying whether or not the index created by the UNIQUE constraint should be clustered or nonclustered, which is important because a table can only have one clustered index.

Upvotes: 1

Related Questions