Geek Answers Handbook

Cluster index on a temporary table

I am trying to optimize a procedure that has the following code:

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 this by moving the CLUSTERED index into a table declaration (more caching friendly), but c3 is not unique, so this does not work:

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

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

+4
source share
3 answers

No, no ... the existence of the ability to define clustering as an option in creating a table is support for declaring primary keys and the unique constraints of the columns that indexes themselves create. In other words, CLUSTERED in the CREATE TABLE statement indicates whether to index or not cluster an index created using the UNIQUE , which is important because a table can have only one clustered index.

+1
source

This can be done by adding an identity column, for example:

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

The inclusion of rowID in the index ensures that it is unique, even if c3 not.

You are checking the index created with:

 EXEC tempdb.dbo.sp_helpindex '#t1'
0
source

Yes, it is possible in SQL Server 2014 and above, Create a table in MSDN . Since 2014, you can specify inline indexes using 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
0
source

More articles:


All Articles