Wednesday, April 7, 2010

Sparse column - Maximum size of a row

Just to clarify, by including a sparse column, the maximum size of the row DOES NOT reduce to 8018 bytes.Quite a few sites have mentioned that sparse column reduces the max row size to 8018 , but its not true.What is true is that the total size occupied by sparse columns alone shouldn't exceed 8019 bytes. The row size ( sparse columns size + normal columns size ) can be greater than 8018 bytes and has the normal row limitation of 8060 bytes. The script provided below illustrates the same.

Note that the sparse column 'sparse_data' has a size of 7500 bytes.Total size of the row when all columns have non null value will exceed 8018 bytes.Actual size of the row can be checked using DMV dm_db_index_physical_stats as shown below.

The size of the row is 8045 bytes, which is above 8018 bytes.Thus its clear that adding a column as sparse column doesnt reduce the size of the row to be 8018 bytes.

Now let us see an example which generates the actual error.Run the following script.The script used earlier is slightly modified by changing the 'data' column to sparse. Rest of the structure remains the same with no changes done to the length of the columns.So, the sparse columns on the table are data,sparse whose combined size are 8000 + few bytes used for internal use for storing sparse data.

No comments:

About Me

I am Nagaraj ( aka Raj ), a SQL DBA handling one of the busiest OLTP systems in the city. Just like you I am awe struck by MS SQL Server. As I explore more and more, I will be sharing the same here.Let us have a good time by sharing and learning!!!