Skip to content

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

Latest commit

 

History

History
81 lines (57 loc) · 6.46 KB

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

File metadata and controls

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

sys.dm_db_persisted_sku_features (Transact-SQL)

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

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