March 3, 2009 at 10:03 am
in a book im reading, it states that if the row in your table exceeds 8060 bytes in size, row compression cannot be used, as it has to succeed each time.
Can someone explain this to me in more detail. i have done a web search but found nothing describing row compression restrictions like this.
March 3, 2009 at 10:14 am
Did you look that up in SQL Server 2008 Books Online to confirm that statement it is true?
Did you do any testing to confirm this?
March 3, 2009 at 10:16 am
no testing done. im at home without access to a windows pc with sql server installed.
i read it in MCTS 70-432 exam training kit by microsoft press. just want to know why this limitation is imposed.
March 3, 2009 at 10:19 am
actually, just found this in an MSDN article:
A table cannot be enabled for compression when the maximum row size plus the compression overhead exceeds the maximum row size of 8060 bytes. For example, a table that has the columns c1 char(8000) and c2 char(53) cannot be compressed because of the additional compression overhead. When the vardecimal storage format is used, the row-size check is performed when the format is enabled. For row and page compression, the row-size check is performed when the object is initially compressed, and then checked as each row is inserted or modified. Compression enforces the following two rules:
* An update to a fixed-length type must always succeed.
* Disabling data compression must always succeed. Even if the compressed row fits on the page, which means that it is less than 8060 bytes; SQL Server prevents updates that would not fit on the row when it is uncompressed.
http://msdn.microsoft.com/en-us/library/cc280449.aspx
still seems like a strange limitation.
March 3, 2009 at 10:25 am
winston Smith (3/3/2009)
in a book im reading, it states that if the row in your table exceeds 8060 bytes in size, row compression cannot be used, as it has to succeed each time.
That's correct. 8060 is the max size permitted for a row as it's the available space on a database page. One of the rules of data compression is that decompression may not fail. Hence no single row may be larger when decompressed than the available space on a database page.
The pertinent quotes from BoL (Creating Compressed Tables and Indexes):
Compression can allow more rows to be stored on a page, but does not change the maximum row size of a table or index.
A table cannot be enabled for compression when the maximum row size plus the compression overhead exceeds the maximum row size of 8060 bytes. For example, a table that has the columns c1 char(8000) and c2 char(53) cannot be compressed because of the additional compression overhead. When the vardecimal storage format is used, the row-size check is performed when the format is enabled. For row and page compression, the row-size check is performed when the object is initially compressed, and then checked as each row is inserted or modified. Compression enforces the following two rules:
An update to a fixed-length type must always succeed.
Disabling data compression must always succeed. Even if the compressed row fits on the page, which means that it is less than 8060 bytes; SQL Server prevents updates that would not fit on the row when it is uncompressed.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply