5

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?

Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
Mark Robinson
  • 13,128
  • 13
  • 63
  • 81
  • Why not check the *edition*? Only Enterprise editions support columnstores before SQL Server 2016 SP1 – Panagiotis Kanavos Feb 13 '17 at 17:22
  • I could but then I have to navigate the vagaries of SQL Server versions / service packs. Plus it isn't a very future proof solution. – Mark Robinson Feb 13 '17 at 17:22
  • Actually, it is. Before 2016 SP1, only Enterprise and Developer editions supported columnstores. You can check that with `select SERVERPROPERTY('Edition')`. 2016 SP1 added support for everything. – Panagiotis Kanavos Feb 13 '17 at 17:25
  • there is no fool proof solution,you will have to check edition of SQL server,compatibility level of database as well – TheGameiswar Feb 13 '17 at 17:25
  • @TheGameiswar server properties arent' affected by compatibility levels – Panagiotis Kanavos Feb 13 '17 at 17:26
  • @PanagiotisKanavos:i meant to say,op should check compatibility level of database even though he is on suported edition/version,if he wants to check before creating column store indexes – TheGameiswar Feb 13 '17 at 17:27
  • @MarkRobinson check [What to tell what SQL Server Version you are running](https://www.mssqltips.com/sqlservertip/1140/how-to-tell-what-sql-server-version-you-are-running/). The `Edition` and `Product Level' properties don't requrie parsing. ProductVersion requires minimal parsing. – Panagiotis Kanavos Feb 13 '17 at 17:28
  • 2
    @MarkRobinson in any case, what you ask is not a *product feature*. Perhaps, you should check for the existence of columnstore specific views, eg `SELECT * from sys.system_objects where name='column_store_dictionaries'` – Panagiotis Kanavos Feb 13 '17 at 17:31
  • @TheGameiswar you are right, if someone set a database to the 2008 R2 columnstores wouldn't work either. – Panagiotis Kanavos Feb 13 '17 at 17:32
  • @TheGameiswar SQL Server will create a columnstore index even in compatibility level 10, but process it in [Row Mode](https://msdn.microsoft.com/en-us/library/bb510680.aspx) – Panagiotis Kanavos Feb 13 '17 at 17:39

1 Answers1

1

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)

SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • The compatibility level won't help. Even with compatibility level 10, SQL Server 2014+ will [allow columnstore indexes](https://msdn.microsoft.com/en-us/library/bb510680.aspx). They'll be processed in row mode though – Panagiotis Kanavos Feb 13 '17 at 17:41
  • If OP wants to know if he __should__ create columnstore indexes on a database, wouldn't this return the best course of action? – SqlZim Feb 13 '17 at 17:43