Msg 35321 rebuilding Columnstore Index on SQL Azure.

Applies to: Azure SQL Database.

Date created: January 18, 2019.
 

Problem Description.
 

After scaling an Azure SQL Database down to Standard S0 from Standard S3, I tried to rebuild with the Online=On option indexes on a table that has a Columnstore Index and I received the following error:

Msg 35321, Level 16, State 1, Line 1
Table 'dbo.XXXXXX' uses a clustered columnstore index. Columnstore indexes are 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.

The Transact-SQL statement I used to rebuild indexes on the tables was the following:

ALTER INDEX ALL ON [dbo].[XXXXXX] REBUILD with (ONLINE=ON)

Trying to execute a simple SELECT statement on the table returned the same error message.

Msg 35321, Level 16, State 1, Line 1
Table 'dbo.XXXXXX' uses a clustered columnstore index. Columnstore indexes are 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.

Cause.

The cause of this error is explained below:

"Q. What happens if I create a columnstore in a database and then scale down below S3?

A. The scaling operation will succeed, but the columnstore will not be chosen when the database executes a query. The index will be dormant with the exception that it will be updated to keep up with data changes in the table. When the database is scaled back to S3 or above, the columnstore index will automatically start being used again. One of the implications of this is if a table has only a clustered columnstore index, and is scaled down below S3. The clustered columnstore index can’t be used to retrieve data, so the data cannot be accessed until the database is scaled back up."
Source: https://azure.microsoft.com/en-us/blog/columnstore-support-in-standard-tier-azure-sql-databases/?wt.mc_id=MVP?

Solution.

Rebuilding indexes on the table without the Online Option enable does not work either.

After scaling the Azure SQL Database back to S3 or later you will be able to run the ALTER INDEX and SELECT statement successfully.

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.