David George David George - 1 year ago 109
SQL Question

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)...)


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?

Wil Wil
Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download