Identifying SQL Server 2005 Enterprise Edition features in use

I recently stumbled across a great system view, sys.dm_db_persisted_sku_features, which identifies any enterprise features in use in the current database, which would prevent you from moving this database to a Standard Edition instance. Unfortunately, it appears in SQL 2008, and I wanted to run this check on a SQL 2005 system.

There are a number of server-level features of SQL 2005 that require Enterprise Edition, but only two database-level features – partitioning and the VarDecimal storage format. Both are easy to check for, so I put together this quick query to do it:

select * from
   (-- VarDecimal Storage Format
    select case
             when max(objectproperty(object_id, N'TableHasVarDecimalStorageFormat')) = 0
               then ''
             else 'VarDecimal Storage'
           end as feature_name
      from sys.objects
    UNION ALL
    -- Partitioning
    select case
             when max(partition_number) > 1
               then 'Partitioning'
             else ''
           end
      from sys.partitions
) t
where feature_name <> ''

On a SQL 2005 server, this query will serve the same purpose that sys.dm_db_persisted_sku_features does on SQL 2008/2012 servers.

3 thoughts on “Identifying SQL Server 2005 Enterprise Edition features in use”

  1. This is extremely useful for us still using archaic systems such as MSSQL 2005 – Many thanks for your contribution.
    I was wondering why “sys.dm_db_persisted_sku_features” wasn’t working 😀

  2. I tried execute this query on SQl Server 2005 and got error message (see below)
    select * from
    (– VarDecimal Storage Format
    select case
    when max(objectproperty(object_id, N’TableHasVarDecimalStorageFormat’)) = 0
    then ”
    else ‘VarDecimal Storage’
    end as feature_name
    from sys.objects
    UNION ALL
    — Partitioning
    select case
    when max(partition_number) > 1
    then ‘Partitioning’
    else ”
    end
    from sys.partitions
    ) t
    where feature_name <> ”

    Msg 102, Level 15, State 1, Line 12
    Incorrect syntax near ‘;’.

    When I click on message it show line

    when max(partition_number) > 1

    1. It looks like it’s converting the “>” to “>” when it gets displayed for you to copy, so you’ll need to change that back to “>”. It’s also doing it in the last line where it converts “<>” to “<>” so replace that one too and it will work as expected. Not sure why it’s doing that, but I’ll try to get the code sample updated so it doesn’t cause issues for anybody else – thanks for letting me know!

Leave a Reply

Your email address will not be published.

Why ask?