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;
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.
Based on my testing you need a minimum of Standard (S3) database to use clustered columnstore and create it online mode.
Pingback: Dew Drop – August 7, 2019 (#3005) | Morning Dew
Pingback: Clustered Columnstore and Azure SQL DB – Curated SQL