Although this topic came quite late to my attention it is more actual than ever. I see lots of people favorite sequential INT/BIGINT as PK but - as Jeff has mentioned - it does not scale. I would say it sucks 🙂
The topic of the article is about OLTP! OLTP in general do not work with big data sets like DWH do. So the problem of index fragmentation is not a big issue here!
The demo from Erikur (where Identity wins!) has nothing to do with a heavy load scenario from real world OLTP systems. So if just one process is hitting a table you won't run into "last page contention".
My tests which I show whenever I do a session about DML optimization shows that a random value (whether GUID or others) will win always.
A big problem can be a big table with billions of rows because of the traversal access to the pages where the new record has to be stored.
In this case I favorite HEAPS over C.I. with a NONCLUSTERED PK. Most of my tests and workload optimizations at customer sites did win the race with heaps and an intelligent underlying file layout (e.g. I use to implement databases with high DML operational workload on multiple files in a filegroup!)
My personal advice here is:
- You can use an ascending key in business environment with <= 1000 transactions / min
- If your system should scale you should think about a redesign of the PK data type and value AND usage of C.I vs. HEAP
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)