Edit

sys.dm_db_persisted_sku_features (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Some features of the Database Engine change the way that information is stored in the database files. These features are restricted to specific editions of SQL Server. A database that contains these features can't be moved to an edition of SQL Server that doesn't support them. Use the sys.dm_db_persisted_sku_features dynamic management view to list 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..

Permissions

For SQL Server 2019 (15.x) and previous versions, requires VIEW DATABASE STATE permission on the database.

For SQL Server 2022 (16.x) and later versions, requires VIEW DATABASE PERFORMANCE STATE permission on the database.

Remarks

If there are no features that may be restricted by a specific edition in the database, the view returns no rows.

sys.dm_db_persisted_sku_features may list the following database-changing features as restricted to specific SQL Server 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. For more information, see About Change Data Capture (SQL Server).

  • ColumnStoreIndex: Indicates that at least one table has a columnstore index. To enable a database to be moved to an edition of SQL Server that doesn't support this feature, use the DROP INDEX or ALTER INDEX statement to remove the columnstore index. For more information, see Columnstore indexes.

  • 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 that doesn't support this feature, 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 Data Compression.

  • MultipleFSContainers: Indicates that the database uses multiple FILESTREAM containers. The database has a FILESTREAM filegroup with multiple containers (files). For more information, see FILESTREAM (SQL Server).

  • InMemoryOLTP: Indicates that the database uses In-Memory OLTP. The database has a MEMORY_OPTIMIZED_DATA filegroup. For more information, see In-Memory OLTP (In-Memory Optimization).

  • 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).

Note

Starting with SQL Server 2016 (13.x) Service Pack 1, these features, except TransparentDataEncryption are available across multiple SQL Server editions, and not limited to Enterprise or Developer editions only.

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

For a list of features supported by the editions of SQL Server on Windows, see:

For a list of features supported by the editions of SQL Server on Linux, see: