New Members FAQ
Talk to Webmaster
Join our online
for Bloggers, Content Writers and Webmasters
Finding Enterprise Only Features in Your SQL Server 2008 Database
Microsoft added a new DMV, sys.dm_db_persisted_sku_features to SQL Server 2008 that you can use to determine whether you have any Enterprise Edition only features in a particular database.
-- Look for Enterprise only features in the current database
SELECT feature_name, feature_id
ORDER BY feature_name;
The four possible results you can get in the feature_name column of the query are Compression, Partitioning, TransparentDataEncryption, ChangeCapture. Compression is for data compression, Partitioning is for table or index partitioning, TransparentDataCompression is for transparent data compression, while ChangeCapture is for change data capture.
If you get any rows back from the query, that means that you are using one of these Enterprise-only features in your database, and you will not be able to restore the database to a SQL Server instance that is running on a lower SKU (such as Standard Edition). To be more precise, the database will go through the full restore process, and then fail at the end (since it has no way of knowing that any of these features is being used until the restore is complete).
It would be nice if the DMV gave multiple rows of output, with an object_id for each occurrence of the feature for things like data compression and partitioning. In the meantime, the query below will help you find the tables that are using data compression.
-- Get Table names, row counts,
-- and compression status for clustered index (SQL 2008 Only)
SELECT OBJECT_NAME(object_id) AS [Table Name],
SUM(Rows) AS [Row Count], data_compression_desc AS [Compression]
WHERE index_id < 2 --ignore the partitions from the non-clustered index if any
AND OBJECT_NAME(object_id) NOT LIKE 'sys%'
AND OBJECT_NAME(object_id) NOT LIKE 'queue_%'
AND OBJECT_NAME(object_id) NOT LIKE 'filestream_tombstone%'
GROUP BY object_id, data_compression_desc
ORDER BY SUM(Rows) DESC;
Ms sql 2008 - MAXRECURSION
How to get Last row (record) from a table
Read related articles:
SQL 2008 features
SQL Enterprise Edition
Did you like this resource? Share it with your friends and show your love!
Responses to "Finding Enterprise Only Features in Your SQL Server 2008 Database"
No responses found. Be the first to respond...
Notify me by email when others post comments to this article.
Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
No HTML formatting and links to other web sites are allowed.
This is a strictly moderated site. Absolutely no spam allowed.
to fill automatically.
(Will not be published, but
to validate comment)
Type the numbers and letters shown on the left.
Return to Resources
Post New Resource
Post resources and
MS Office 2010 released
Server 502 error in Gmail signup or Gmail login!
Nokia broadens lawful row with Apple to take in iPad
New InstallAware 9 R2 Integrates with Microsoft Visual Studio 2010
Tag posting guidelines
(No tags found.)
Follow us on Twitter:
Last 7 Days
Asheej T K
Awards & Gifts
Articles Rss Feeds
Forum Rss Feeds
Revenue Sharing sites
Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India
2005 - 2012 All Rights Reserved.
.NET and other trademarks mentioned in this site belong to Microsoft and other respective trademark owners.
Articles, tutorials and all other content offered here is for educational purpose only.
We are not associated with Microsoft or its partners.