All geek questions in one place

A query to determine if the current version of SQL Server supports column indexes

I have a stored procedure that creates some dynamic tables. If column indexes are supported in the SQL Server host version, then I want to create a column index, otherwise the backup will create a regular row storage index.

I found the dm_db_persisted_sku_features table, but this just tells you which non-standard functions are currently in use, and not what is supported:

 SELECT * FROM sys.dm_db_persisted_sku_features

How can I determine from within a query if SQL Server versions and versions support column column indices?

+5
source share
1 answer

You can check the compatibility level of the current database to make sure it is compatible with 2012+ features.

 select ColumnStore = case when compatibility_level >= 110 and (serverproperty ('edition') like 'Enterprise%' or serverproperty ('edition') like 'Developer%') then 1 when compatibility_level >= 130 and serverproperty ('productlevel') != 'RTM' then 1 else 0 end from sys.databases where name = db_name()

Note:

 SELECT * from sys.system_objects where name='column_store_dictionaries'

exists in editions that do not support column indexes (e.g. 2014 Express)

+1
source

Source: https://habr.com/ru/post/1264173/

More articles:


All Articles