Azure SQL Database Clustered Columnstore Index

You can read about columnstore indexes here (https://azure.microsoft.com/en-gb/blog/transforming-your-data-in-azure-sql-database-to-columnstore-format/). I won’t rehash the material but high level, these index types are optimized for analytical queries and high compression of data (up to 100x). This format is perfect for the large data sets that can be efficiently compressed using this format and analytical queries with complex calculations that use subset of the table columns.

The latest release of Azure SQL Database enables you to transform your row-store tables to the columnstore format without blocking incoming transactions using the online version of columnstore index build. ONLINE = ON is the key here.

What tier do you need to create one of these things? Let’s see.

CREATE CLUSTERED  COLUMNSTORE INDEX cciSales ON [SalesLT].[ProductModelProductDescription] WITH ( ONLINE = ON )

But I get this message, Msg 40536, Level 16, State 32, Line 1

‘COLUMNSTORE’ is not supported in this service tier of the database. See Books Online for more details on feature support in different service tiers of Windows Azure SQL Database.

What am I currently running?

SELECT  D.name,  SL.*
FROM sys.databases D
JOIN sys.database_service_objectives SL
ON D.database_id = SL.database_id;

basictier.JPG

You cannot use this within the Basic tier. I select a different database that is S0 and re-run the columnstore code. I get the same message so I scale up to S1 and still I cannot run the code.

s1database.JPG

Based on my testing you need a minimum of Standard (S3) database to use clustered columnstore and create it online mode.

onlinemode.JPG

Advertisements

2 thoughts on “Azure SQL Database Clustered Columnstore Index

  1. Pingback: Dew Drop – August 7, 2019 (#3005) | Morning Dew

  2. Pingback: Clustered Columnstore and Azure SQL DB – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s