In this post I discuss creating Clustered Columnstore Indexes everywhere. In other words, which clustered columnstore indexes you can create depending on your SQL Server version.
Because in a previous post I mentioned I could create clustered columnstore indexes in SQL Server 2019 regardless of the database compatibility level.
To clarify, it appears you can do this regardless of the compatibility level of the database.
Because I was discussing this I had to make sure I was accurate. So I tested creating clustered columnstore indexes on SQL Server 2016 and above, using various compatibility levels.
In truth, I could have also tested this for SQL Server 2014. However, due to it’s age and limitations I decided to leave it out.
SQL Server 2016
First thing I did was tested a database that was in SQL Server 2008 compatibility mode on a SQL Server 2016 installation.
It failed when I tried to create a clustered columnstore index on a table that had a varchar(max) column. Which I had expected.
However, as soon as I removed the varchar(max) column it worked.
To confirm this and to make sure it worked I ran a select against the table and checked the execution plan. Which clearly showed it had indeed used the clustered columnstore index.
SQL Server 2017
I then tested this on a SQL Server 2017 installation. With the database in SQL Server 2008 compatibility mode, which also worked.
In addition, it also worked for a table which had a varchar(max) column type. Which is in fact supported in SQL Server 2017.
SQL Server 2019
In fact, I tested on the SQL Server 2019 installation I discussed in my last post, which you can read here. Because I had recently upgraded it to CTP 3 which was released last week.
I wanted to check the behaviour was still the same, and indeed it was.
Results
As you can see from the results it appears you are limited to what clustered columnstore indexes you can create in your database by the version of SQL Server installed.
Instead of the database compatibility level. You can view the limitations and restrictions from Microsoft in detail here.
Using to your advantage
Generally speaking, one of the main reasons that migrations are delayed are due to applications requiring certain versions of SQL Server.
However, I am aware that at the moment Microsoft are approaching vendors. With an aim to try and encourage them to support by database compatibility level instead.
Personally, I think this is a good thing to do. It can help speed up a lot of SQL Server migrations.
Now, if your vendor has already adopted this then you could use this behaviour to your advantage. You can discuss it with your colleagues and make them aware of the potential advantages.
Which can pursuade your colleagues that doing a migration is a good idea. In fact, you can explain even if they upgrade from SQL Server 2016 they can still get gains.
Because from SQL Server 2017 onwards you can create clustered columnstore indexes for tables that have varchar(max) columns.
Final word
Well I hope this post has given you an insight into what clustered columnstore indexes you can build where. In addition, how you can use this to your advantage.
Of course, if you have any views about any of this you can leave a comment.
Be First to Comment