Hi 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|
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?