UNIQUEIDENTIFIER & Clustered index
Would be great if someone could point me where this is exhibited in easy to understand fashion.
create table t1 (guid uniqueidentifier default newid()) insert t1 default values go 100000
You could watch fragmentation change while running that or even easier, just monitor page splits using PerfMon. They'll be off the charts.
Or come to my lightning talk at SQLPASS this year. 🙂
Depending on how you are creating the UNIQUEIDENTIFIER, if you are using NewID or generating the guid then you will get lots of index fragmentation and lots of page splits as the id's will be anywhere in the index depending on what guid you get.
If you are using sequential id's then you won't get that.
What you want is to have a sequential id so you know all writes go to the end of the index and won't cause earlier page splits.
The second problem is that uniqueidentifiers are quite large and if they are the clustered index, all other indexes also include those so it is slower to use all indexes whether you include the uniqueidentifier or not.