January 12, 2011 at 2:53 pm
I read the help text about this built in stored procedure in sql server 2008 and it gives the impression that it will tell you table and index size savings if I enable compression on a table.
But it only returns information about compressing the indexes. It doesn't mention the savings for compressing the data in the table proper at all.
Is there some other procedure that returns this info for the table itself?
January 12, 2011 at 3:37 pm
Table got a clustered index? Does the compression results include the cluster?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2011 at 5:58 am
Yes, the table has a clustered index.
This morning, I created a table with no indexes and got what I expected, an index=0 record for the table. I created an index for the table and got 2 records, one for the index and one for the table.
Based on your question, I'm guessing that the compression savings are included in the clustered index record. So, I created a clustered index on the table and the "table" record (i.e., index=0) went away.
That's very confusing, to say the least.
January 13, 2011 at 7:55 am
The clustered index is the table.
When you have a table without a clustered index it's called a heap and it will appear as index_id = 0. When you put a clustered index onto the table, the heap is replaced with the clustered index. The clustered index has the actual data pages at the leaf level, ie it is the table. The clustered index has index_id = 1.
You will never encounter a situation where a table has both index_id 0 and 1, 0 means no clustered index, 1 means has a clustered index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply