Menu
Does anyone happen to have a generic SQL statement that'll list all of the tables and indexes in a database, along with their current compression setting, for each partition?
Thanks.
EDIT: This is as far as I got in my attempt to query tables, but I'm not sure the join is correct (I'm getting duplicates, which seems to be caused by the presence of indexes)
SELECT [t].[name], [p].[partition_number], [p].[data_compression_desc] FROM [sys].[partitions] AS [p] INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id] 
566
To check the Compression ratio for a table, you can navigate to Table Definition. Go to Runtime Information. To see Compression Ratio, go to Columns tab. You can see the compression ratio in the Main Size Compression Ratio [%] column.
In SQL Server, you can query the sys. partitions system catalog view to find out whether or not a partition has been compressed. In particular, the data_compression column tells you whether it's compressed or not. The data_compression_desc column tells you what type of compression it uses.
To compress an index, expand the table that contains the index that you want to compress and then expand the Indexes folder. Right-click the table or index to compress, point to Storage and select Manage Compression.... In the Data Compression Wizard, on the Welcome to the Data Compression Wizard page, select Next.
I thought I'd share my final query. This'll give two result sets, the first of data compression for heaps and clustered indexes, and the second of index compression for non-clustered indexes.
SELECT [t].[name] AS [Table], [p].[partition_number] AS [Partition], [p].[data_compression_desc] AS [Compression] FROM [sys].[partitions] AS [p] INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id] WHERE [p].[index_id] in (0,1) SELECT [t].[name] AS [Table], [i].[name] AS [Index], [p].[partition_number] AS [Partition], [p].[data_compression_desc] AS [Compression] FROM [sys].[partitions] AS [p] INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id] INNER JOIN sys.indexes AS [i] ON [i].[object_id] = [p].[object_id] AND [i].[index_id] = [p].[index_id] WHERE [p].[index_id] > 1 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With