SQL Ninja PK Clustered or Non Clustered for GUID

imageHi today I would like to share with you experiment I did small experiment for checking improvement of operations in table with primary key that has UNIQUEIDENTIFIER (GUID) type and it is PRIMARY KEY in this table. I want to check what if PK will be CLUSTERED (by default) and what if PK will be NONCLUSTERED index. There is very common question on any job interview so, probably all of you already know the different between both index kinds. But I would explain that CLUSTERED index is sort physically and NONCLUSTERED is not. For both test tables I used 1 million rows and I measured INSERT and SELECT statements times. Below you can find test script. Script is very simple and collect data from SQL Statistics. If you like to reproduce this experiment please run every part finished with go statement 1 by 1 in order. I hope TSQL code it is simple to understand.

use tempdb
go

create table TestTableClustered (
	ID uniqueidentifier not null,
	Number bigint not null identity(1,1),
	Stamp timestamp not null,
	constraint PK_TestTableClustered primary key clustered (ID)
)
create nonclustered index IX_TestTableClustered on TestTableClustered (Number)

create table TestTableNonClustered (
	ID uniqueidentifier not null,
	Number bigint not null identity(1,1),
	Stamp timestamp not null,
	constraint PK_TestTableNonClustered primary key clustered (Number)
)
create nonclustered index IX_TestTableNonClustered on TestTableNonClustered (ID)
go

declare @count int = 1000000
set statistics time on
;with n(n) as (
select 1 n
union all
select n+1 from n where n < @count
)
insert into TestTableClustered(ID) select NEWID() from n
option (maxrecursion 0)
set statistics time off
go

declare @count int = 1000000
set statistics time on
;with n(n) as (
select 1 n
union all
select n+1 from n where n < @count
)
insert into TestTableNonClustered(ID) select NEWID() from n
option (maxrecursion 0)
set statistics time off
go

set statistics time on
select * from TestTableClustered order by Number
set statistics time off
go

set statistics time on
select * from TestTableNonClustered order by Number
set statistics time off
go

drop index IX_TestTableClustered on TestTableClustered
drop table TestTableClustered
drop index IX_TestTableNonClustered on TestTableNonClustered
drop table TestTableNonClustered
go

And here you have results for above scripts. I dropped results for SELECT statements, so it is not whole output of the scripts, but just last result set.

Operation Clustered Time Clustered CPU Non Clustered Time Non Clustered CPU
INSERT 17363 16442 17253 16380
SELECT 13119 2871 11263 359

As you can see this experiment clearly shows that selecting from table with NONCLUSTERED index as PRIMARY KEY is a lot more efficient.That is because CLUSTRED index makes a lot of fragmentation in data. Please let me know if you had such dilemma in your coding experience so far?

p ;).

3 Replies to “SQL Ninja PK Clustered or Non Clustered for GUID”

  1. I never knew there was a difference between Clustered and Non Clustered, until now, thanks a lot piotr

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.