Finding Compressed Tables

Categories: News, Professional, SSC
Tags: ,
Comments: No Comments
Published on: February 25, 2011

Have you been working with compression?  Have you inherited a database that may or may not have some tables compressed?  On occasion you may want to know what the compression type being used on a table is.  There is a really easy way to figure that out.

This is also helpful for those of us who may have known this before, but had difficulty finding where they filed the information.

In SQL Server there is an object catalog view that can help you find just the information you seek.  The name of the view is sys.partitions.  To find the compression information is rather simple as well.  If you read the MSDN info about this view, you can quickly determine some easy queries to document the compression settings in your database.

One such possibility is (for instance to simply find tables that are compressed):

SELECT * FROM sys.partitions
	WHERE data_compression <> 0

And a little more informative might look something like this:

SELECT OBJECT_NAME(OBJECT_ID) AS ObjName
		,data_compression_desc AS CompressionType
	FROM sys.partitions
	WHERE OBJECTPROPERTY(OBJECT_ID,'ismsshipped') = 0

As you can see from that last query, I am limiting the results to User Created Objects.  This is just a quick code snippet to reference in case you need it.  Maybe, at a later date, I will venture into some pros and cons of compression as well as the types of compression available for the data in SQL Server.

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>






Calendar
February 2011
M T W T F S S
« Jan   Mar »
 123456
78910111213
14151617181920
21222324252627
28  
Content
SQLHelp

SQLHelp


Welcome , today is Friday, April 18, 2014