SQL Denali Makes Even Fast Look Slow

August 09, 2011

I’ve seen a lot of performance testing over the years, every so often I’m even impressed a bit by the results. Here is one however that will melt the mind of anyone working against large SQL databases: SQL Denali + 1.4 billion with a b rows = 455x increase from 2008 R2. No, I didn’t fat finger an extra 5 there. It was that big.

How? By using a new feature called a Column Store Index (aka Column Indexing). For those wanting the details on how it works and how they tested it you can find the PDF here. Not interested? You sure? I promise, its really short.

One major caveat however is that not all data types are supported within it. Most wouldn’t make sense for this type of use anyway but one in particular was a pain €“> uniqueidentifier. I’ve long used uniqueidentifier as my ID for tables (makes n-tier, replication and merging databases a lot easier). The other gotcha types are the (max) text columns. If you thought you could get away with just setting everything to (max) then you can mark today as when you found out that was a bad idea. You can find the other limitations and restrictions here.

Looking at the performance I really could have used this feature more than once in the past.