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.


DROP TABLE [sparse_col]
GO
CREATE TABLE [dbo].[sparse_col]
  (
     [dt]        [DATETIME] NOT NULL,
     [value]     [INT] NULL,
     data        CHAR(500) NULL,
     sparse_data CHAR(7500) SPARSE NULL,
  )
GO
INSERT INTO [sparse_col]
SELECT Getdate(),
       0,
       'sparse_data',
       'sparse data'
GO 


The insert is successful without any errors.

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.


DECLARE @dbid INT;
SELECT @dbid = Db_id(); 


SELECT Object_name(object_id) AS [table_name],
       record_count,
       min_record_size_in_bytes,
       max_record_size_in_bytes,
       avg_record_size_in_bytes
FROM   sys.Dm_db_index_physical_stats(@dbid, NULL, NULL, NULL, 'Detailed')
WHERE  Object_name(object_id) = 'sparse_col' 




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.


DROP TABLE [sparse_col]
GO
CREATE TABLE [dbo].[sparse_col]
  (
     [dt]        [DATETIME] NOT NULL,
     [value]     [INT] NULL,
     data        CHAR(500) SPARSE NULL,
     sparse_data CHAR(7500) SPARSE NULL,
  )
GO 


INSERT INTO [sparse_col]
SELECT Getdate(),
       0,
       'some data',
       'sparse data' 

The insert fails with the error
Msg 576, Level 16, State 5, Line 1
Cannot create a row that has sparse data of size 8031 which is greater than the allowable maximum sparse data size of 8019.

The reason is that the total size consumed by sparse_data,data column is 8000 bytes + 31 bytes used for internal use which exceeds the total size allowed (8019 bytes) for sparse columns.

Reference: SQL Server 2008 Internals by Kalen Delaney :)