Skip to content

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

Latest commit

 

History

History
81 lines (63 loc) · 7.17 KB

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

File metadata and controls

81 lines (63 loc) · 7.17 KB
title sys.dm_db_persisted_sku_features (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 08/23/2017
ms.prod sql-non-specified
ms.reviewer
ms.suite
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic language-reference
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
dev_langs
TSQL
helpviewer_keywords
editions [SQL Server], feature restrictions
sys.dm_db_persisted_sku_features dynamic management view
ms.assetid b4b29e97-b523-41b9-9528-6d4e84b89e09
caps.latest.revision 26
author BYHAM
ms.author rickbyh
manager jhubbard

sys.dm_db_persisted_sku_features (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx_md]

Some features of the [!INCLUDEssDEnoversion] change the way that [!INCLUDEssDE] stores information in the database files. These features are restricted to specific editions of [!INCLUDEssNoVersion]. A database that contains these features cannot be moved to an edition of [!INCLUDEssNoVersion] that does not 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.

Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssKatmai] through [!INCLUDEssCurrent]).

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 [!INCLUDEssNoVersion]. This feature must be removed before the database can be migrated to all available editions of [!INCLUDEssNoVersion].
feature_id int Feature ID that is associated with the feature. [!INCLUDEssInternalOnly].

Permissions

Requires VIEW DATABASE STATE permission on the database.

Remarks

If no features that may be restricted by a specific 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 specific [!INCLUDEssNoVersion] editions:

   Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssSQL11] through [!INCLUDEssCurrent]).  

  • 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 [!INCLUDEssNoVersion] that does not 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).

 Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssSQL14] through [!INCLUDEssCurrent]).

  • 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 [!INCLUDEssNoVersion] 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 [!INCLUDEssSQL15] Service Pack 1, these features are available across multiple [!INCLUDEssNoVersion] 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

See Also

Dynamic Management Views and Functions (Transact-SQL)
Database Related Dynamic Management Views (Transact-SQL)
Editions and supported features of SQL Server 2016
Editions and supported features of SQL Server 2017