Export (0) Print
Expand All

sys.dm_db_persisted_sku_features (Transact-SQL)

Some features of the SQL Server Database Engine change the way that Database Engine stores information in the database files. These features are restricted to specific editions of SQL Server. A database that contains these features cannot be moved to an edition of SQL Server that does not support them Use the sys.dm_db_persisted_sku_features dynamic management view to list all edition-specific features that are enabled in the current database.

Applies to: SQL Server (SQL Server 2008 through current version).

Column name

Data type

Description

feature_name

sysname

External name of the feature that is enabled in the database but not supported on the all the editions of SQL Server. This feature must be removed before the database can be migrated to all available editions of SQL Server.

feature_id

Int

Feature ID that is associated with the feature. Identified for informational purposes only. Not supported. Future compatibility is not guaranteed..

Requires VIEW DATABASE STATE permission on the database.

If no features that are restricted by edition are used by the database, the view returns no rows.

sys.dm_db_persisted_sku_features may list the following database-changing features as restricted to the SQL Server Enterprise or Developer editions:

  • ChangeCapture. Indicates that a database has change data capture enabled. To remove change data capture, use the sys.sp_cdc_disable_db stored procedure.

  • ColumnStoreIndex. Indicates that at least one table has an xVelocity memory-optimized columnstore index. To enable a database to be moved to an edition of SQL Server other than Enterprise or Developer, use the DROP INDEX or ALTER INDEX statement to remove the columnstore index.

    Applies to: SQL Server (SQL Server 2012 through current version).

  • Compression. Indicates that at least one table or index uses data compression or the vardecimal storage format. To enable a database to be moved to an edition of SQL Server other than Enterprise or Developer, use the ALTER TABLE or ALTER INDEX statement to remove data compression. To remove vardecimal storage format, use the sp_tableoption statement.

  • InMemoryOLTP. Indicates that the database uses In-Memory OLTP. The database has a MEMORY_OPTIMIZED_DATA filegroup.

    Applies to: SQL Server (SQL Server 2014 through current version).

  • Partitioning. Indicates that the database contains partitioned tables, partitioned indexes, partition schemes, or partition functions. To enable a database to be moved to an edition of SQL Server other than Enterprise or Developer, it is insufficient to modify the table to be on a single partition. You must remove the partitioned table. If the table contains data, use SWITCH PARTITION to convert each partition into a nonpartitioned table. Then delete the partitioned table, the partition scheme, and the partition function.

  • TransparentDataEncryption. Indicates that a database is encrypted by using transparent data encryption. To remove transparent data encryption, use the ALTER DATABASE statement. For more information, see Transparent Data Encryption (TDE).

To determine whether a database uses any features that are restricted to specific editions, execute the following statement in the database:

SELECT feature_name FROM sys.dm_db_persisted_sku_features ;
GO

Community Additions

ADD
Show:
© 2014 Microsoft