CODEWITHSUNDEEP
sql-servert-sqlfeature-detectioncolumnstore
Mark Robinson
Mark Robinson

Reputation: 13278

Query to identify if current SQL Server version supports columnstore indexes

I have a stored procedure that creates some dynamic tables. If columnstore indexes are supported on the host version of SQL Server then I want to create a columnstore index, otherwise fallback to creating just a normal row store index.

I have found the dm_db_persisted_sku_features table but that just tells you what non-standard features are currently being used rather than what is supported:

SELECT * FROM sys.dm_db_persisted_sku_features

How can I determine from inside a query if the SQL Server version and edition support columnstore indexes?

Upvotes: 5

Views: 1112

Answers (1)

SqlZim
SqlZim

Reputation: 38023

You can check the compatibility level of the current database to see if 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 on editions that do not support columnstore indexes (e.g. 2014 Express)

Upvotes: 1

Related Questions