When I wrote the previous post around SQL SERVER – CREATE TABLE Failed Because Column ‘C1024′ in Table ‘myTable’ Exceeds the Maximum of 1024 Columns, I thought it was a simple concept. Lesser did I know that there can be something wrong. One of my blog reader said they were getting some sort of an error when adding a sparse column to a table. I was surprised to why one should get an error? I asked for further details because this was strange.
The mail stated they are getting:
Msg 511, Level 16, State 1, Line 6 Cannot create a row of size 10019 which is greater than the allowable maximum row size of 8060. The statement has been terminated.
Just like a strange error gets me going. I started to dig into why are they were getting this error. This lead to an interesting learning for me with sparse columns.
Using sparse columns reduces the maximum size of a row from 8060 bytes to 8018 bytes because of the additional overhead used when storing non-NULL values. However, for most of the normal scenarios this should not be a concern, since by definition, most of the sparse columns in a row will contain NULL values. Therefore, size reduction obtained by not storing any data for NULL values in a sparse column easily compensates for this fixed overhead. This still doesn’t tell us why we are getting the above error. Let us understand the basics.
When you convert a nonsparse column to a sparse column, the sparse column will consume more space for nonnull values. Therefore, when the row size is near 4,009 bytes, the operation can fail. The reason is when we modify an existing table to add our space column, the data pages need to have enough space to make a copy of the original row, make changes and then delete the original row. The error is because of insufficient space on the data pages. Let us do a simple script to get the error:
--Create a column of size 6000 (note this is NCHAR)
CREATE TABLE sparse_tbl (c1 NCHAR(3000))
INSERT INTO sparse_tbl VALUES (REPLICATE ('z',100))
--Modify the column to sparse
ALTER TABLE sparse_tbl ALTER COLUMN c1 ADD SPARSE
A similar error is encountered if you go ahead and remove a sparse column from an existing table with data where the rows have more than 4009 bytes. In case of a new table, this is not encountered in general because there is no data to work with or move.
So the next question was, how can I alter or add or remove sparse columns which have data more than 4009 bytes then? In this case:
- Create a new table for the operation
- Insert into new table using the old table
- Delete the old table and rename the new table
If we follow a process to do the same, there is nothing difficult. I have always felt there are ways to work with data and work around things. I am hope in future versions Microsoft might change this behavior but this is the current workaround. Hope you have never encountered this error before in your code. Let me know your thoughts.
Reference: Pinal Dave (https://blog.sqlauthority.com)
