Skip to content

Latest commit

 

History

History
78 lines (55 loc) · 6.39 KB

sys-dm-db-persisted-sku-features-transact-sql.md

File metadata and controls

78 lines (55 loc) · 6.39 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs ms.custom monikerRange
sys.dm_db_persisted_sku_features (Transact-SQL)
sys.dm_db_persisted_sku_features (Transact-SQL)
rwestMSFT
randolphwest
06/23/2023
sql
system-objects
reference
sys.dm_db_persisted_sku_features_TSQL
sys.dm_db_persisted_sku_features
dm_db_persisted_sku_features_TSQL
dm_db_persisted_sku_features
editions [SQL Server]
sys.dm_db_persisted_sku_features dynamic management view
TSQL
linux-related-content
>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current||=fabric

sys.dm_db_persisted_sku_features (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw]

Some features of the [!INCLUDE ssDE] change the way that information is stored in the database files. These features are restricted to specific editions of [!INCLUDE ssNoVersion]. A database that contains these features can't be moved to an edition of [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion]. This feature must be removed before the database can be migrated to all available editions of [!INCLUDE ssNoVersion].
feature_id int Feature ID that is associated with the feature. [!INCLUDE ssInternalOnly].

Permissions

For [!INCLUDE sssql19-md] and previous versions, requires VIEW DATABASE STATE permission on the database.

For [!INCLUDE sssql22-md] 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 [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion] 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 [!INCLUDE sssql16-md] Service Pack 1, these features, except TransparentDataEncryption are available across multiple [!INCLUDE ssNoVersion] 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

[!INCLUDE editions-supported-features-windows]

[!INCLUDE editions-supported-features-linux]

Related content