Create UNIQUE Clustered Index
Published: 26th September 2014
This is a bug bear of mine but something which I see all over the place in people’s code… if you’re going to create a clustered index on a table (including temp tables) and the key you’re using is unique, then let SQL Server know. You’ll be surprised how much difference it makes.

Having asked around it seems the confusion comes from the misconception that a clustered index forces uniqueness, but that isn’t the case.

A Primary Key will force uniqueness and will, by default, create a clustered index in the background (unless you specifically state otherwise)… but this is a one way road. A clustered index does NOT enforce uniqueness by default and does NOT create a primary key.

To prove this, run the following code you’ll find there is no error:

if object_id('tempDB..#nonUnique') is not null drop table #nonUnique
go

create table #nonUnique
(
  
miscNum int
)
insert into #nonUnique
values(1), (1), (1), (2), (3), (4), (4), (5), (5), (6)
go

create clustered index ix_myTempNonUniqueClustered on #nonUnique(miscNum)
go

drop table #nonUnique
go


So why is this important?

Well, SQL Server needs all the information it can get in order to choose the ideal execution plan for our queries.

Without specifying uniqueness we’re telling SQL Server that there COULD be duplicates involved and therefore it will presume the join to be “Many to Many”… this means that we likely require a Hash join, a memory grant, and the raised cost could even invoke parallelism.

Here’s an example:

use AdventureWorks2012
go

select *
into #salesHeader
from sales.SalesOrderHeader

select *
into #salesHeader1
from sales.SalesOrderHeader
go

create clustered index ix_nonUniqueSales on #salesHeader(salesOrderID)
create clustered index ix_nonUniqueSales1 on #salesHeader1(salesOrderID)
go

select *
from #salesHeader s
join #salesHeader1 s1
on s.SalesOrderID = s1.SalesOrderID
go


Now let’s compare this to the same query, but telling SQL Server that the clustered key is unique and therefore allowing it to work with a One to One join…

use AdventureWorks2012
go

select *
into #salesHeader
from sales.SalesOrderHeader

select *
into #salesHeader1
from sales.SalesOrderHeader
go

create unique clustered index ix_nonUniqueSales on #salesHeader(salesOrderID)
create unique clustered index ix_nonUniqueSales1 on #salesHeader1(salesOrderID)
go

select *
from #salesHeader s
join #salesHeader1 s1
on s.SalesOrderID = s1.SalesOrderID
go


As you can see, just by specifying UNIQUE on the clustered index we have drastically reduced the duration, reduced the CPU, parallelism is no longer required, and we have no memory grant which will increase concurrency.

Therefore, when creating clustered indexes on any table, just remember to let SQL Server know if that index should be unique or not.

Note this also works in regard to your nonclustereds as well... if it's unique then tell SQL Server.
Comments:
NB: Comments will only appear once they have been moderated.