Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Finding Compressed Tables

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.

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.