Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

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.

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:

  • 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. For more information, see Storing Decimal Data As Variable Length.

  • 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. For more information, see Modifying Partitioned Tables and Indexes.

  • 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 Understanding Transparent Data Encryption (TDE).

  • ChangeCapture. Indicates that a database has change data capture enabled. To remove change data capture, use the sys.sp_cdc_disable_db stored procedure. For more information, see Configuring Change Data Capture.

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
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.