Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Create UNIQUE Clustered Index
Published: Apr 22, 2016
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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.