Recently, I was asked to install Denali for a client because one of the employees attended PASS 2010 and had seen a session about column based indexing. The client was really eager to begin testing in their environment to see what kind of performance boosts they would gain. As an obedient consultant, I installed Denali and created the database, file groups, partition function and a partition scheme for the test.

While doing all this, I researched as much as I could find (not much out there on Denali yet) on column based indexing in Denali. I found some, but not much. One that I did find, led me to a white paper “Column Store Indexes for Fast Data Warehouse Query Processing in SQL Server 11.0” (I know… the name could have been longer right J) written by one of Microsoft’s own, Eric Hanson. After reading the whitepaper, I started getting excited to see how it would work in a “real world” scenario. Hanson’s whitepaper had information about a table containing 1.44 billion rows getting queried in a little over 1 second. And another post, stated that a demonstration shown at PASS had 800 billion records only took only 19 seconds. I know--- right…Sounds pretty wicked indeed.

So, I looked at the CREATE COLUMNSTORE  INDEX example that Hanson included in the whitepaper and tried to mimic it.

Script 1: Hansen’s Example (shortened for brevity)

CREATE COLUMNSTORE INDEX cstore on [dbo].[catalog_sales]

([cs_sold_date_sk]

,[cs_sold_time_sk]

,[cs_ship_date_sk]

,[cs_bill_customer_sk]

…)

So, finally I was ready to create the column store and…BAM…I received the below error:

Msg 343, Level 15, State 1, Line 1

Unknown object type 'COLUMNSTORE' used in a CREATE, DROP, or ALTER statement.

Yup! Unknown object type ‘COLUMNSTORE’. Back to searching the globe for details on this new feature and I found that by reading the LessThanDot post that I found earlier stated that it wasn’t going to be in CTP1, but may be in CTP2. What a bummer! Has anyone else heard anything about this great feature and when it will be available? I guess we’ll just have to wait.

I hope that you have enjoyed this post. If you did, please take just a moment to rate it below! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs or find me at any of the below methods.

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works

Email: bmcdonald@pragmaticworks.com

Blogs: SQLBIGeek | SQLServerCentral | BIDN | SQLServerPedia

Twitter: @briankmcdonald

LinkedIn: http://tinyurl.com/BrianKMcDonald