I have come to understand the importance of using columnstore indexes when my queries are aggregating and scanning across many millions of rows.

Since I mostly work with Azure SQL Database I was pretty exciting to hear about columnstore support in the standard tier, more specifically S3 and above. Why S3? Well it is all about the memory required for it to perform well and using S3 and above works best.

Single Database

So what happens if you try and create a columnstore index on a non – S3 database?

You will get – Msg 40536, Level 16, State 32, Line 21 ‘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.

Ok, what if we try and create one on something higher than S3? Maybe P1?

select * from sys.database_service_objectives

CREATE NONCLUSTERED COLUMNSTORE INDEX account_NCCI
ON t_account (accountkey, accountdescription, unitsold)

Works just as expected.

Checking The Execution Plan

So now we have the right service tier and it is utilising a columnstore nonclustered index (basic queries for demo purposes).

SELECT [SalesOrderID]
,[RevisionNumber]
,[OrderDate]
,[DueDate]
,[ShipDate]
FROM [SalesLT].[SalesOrderHeader]

GO

What happens if we go to a service tier lower than S3? For instance Basic?

select * from sys.database_service_objectives

SELECT [SalesOrderID]
      ,[RevisionNumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
 FROM [SalesLT].[SalesOrderHeader]
GO

It will not be chosen when the database executes the query. It becomes dormant.

Elastic Pools

What about if you are using elastic pools? I have a database called AWSDB which is part of an elastic pool. As you can see it is a standard elastic pool.

select * from sys.database_service_objectives

I run the code to create the nonclustered columnstore index but I get: Msg 40536, Level 16, State 32, Line 21 ‘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.

Digging into the portal I notice that this is a 50 eDTU pool.

The minimum requirement is 100 eDTU pool.

so I scale up and confirm that the index has been created.

So the key learning(s) from this post?

• For single Azure SQL Databases you need a minimum of S3 to create columnstore indexes.
• Your query will no longer use the columnstore index if you scale lower than S3.
• For elastic pools, your pool must be using 100 eDTUs or higher to create columnstore indexes.