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):
[codesyntax lang=”tsql”]
select * from sys.partitions where data_compression <> 0
[/codesyntax]
And a little more informative might look something like this:
[codesyntax lang=”tsql”]
Select OBJECT_NAME(object_id) as ObjName ,data_compression_desc as CompressionType From sys.partitions Where OBJECTPROPERTY(object_id,'ismsshipped') = 0
[/codesyntax]
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.
