1

I have a table with one pk column (bigint), one non sparse bigint, 30 k sparse columns (20 k bit, 5 k int, 5 k nvarchar(255)) and a column set.

When I try to update a nvarchar column with '.' in a row where this column contains currently null I get:

"Cannot create a row that has sparse data of size 8028 which is greater than the allowable maximum sparse data size of 8023."

When I sum up the data in the non null columns for this row I get

a) << 8023 bytes, when using 0.125 per bit, 4 per int and 2 + datalength(..) per nvarchar.

And I get

b) >> 8023 when using 5 per bit, 8 per int and 4 + datalength(..) per nvarchar.

So the bytes per column in b) cannot be the byte count sql server uses to calc the max of 8023.

So I'm confused now why sql server says the row is full.

Is this a (known) bug in sql server 2012?

Thank you

Now I tried that:

create table sparsetable (
 id bigint identity primary key
 , dummy1 nchar(4000) sparse
 , dummy2 nchar(4000) sparse
 , dummy3 nchar(4000) sparse
 , b1 bit sparse
 , b2 bit sparse
 , b3 bit sparse
 , b4 bit sparse
 , b5 bit sparse

 , i1 int sparse
 , i2 int sparse
 , i3 int sparse
 , i4 int sparse
 , i5 int sparse

 , v1 nvarchar(255) sparse
 , v2 nvarchar(255) sparse
 , v3 nvarchar(255) sparse
 , v4 nvarchar(255) sparse
 , v5 nvarchar(255) sparse
 )

insert into sparsetable (dummy1, b1, i1, v1) values ('x', 1, 1, '123') --Cannot create a row that has sparse data of size 8054 which is greater than the allowable maximum sparse data size of 8019.

insert into sparsetable (dummy1, b1, i1) values ('x', 1, 1) --Cannot create a row that has sparse data of size 8042 which is greater than the allowable maximum sparse data size of 8019.

insert into sparsetable (dummy1, b1) values ('x', 1) --Cannot create a row that has sparse data of size 8032 which is greater than the allowable maximum sparse data size of 8019.

Can anybody explain the numbers?

Thank you

Ickbinet
  • 277
  • 3
  • 12
  • Are the columns nullable? 30k nullable columns start off using 3,750 bytes for the is null bits... – Stan Jun 05 '15 at 13:47
  • @Stan - by definition, [sparse columns](https://msdn.microsoft.com/en-gb/library/cc280604.aspx) have to be nullable. – Damien_The_Unbeliever Jun 05 '15 at 13:57
  • What are the actual numbers you're working with - how many bits, ints and nvarchars are non-null in the value you're attempting to insert? – Damien_The_Unbeliever Jun 05 '15 at 14:09
  • Also worth asking if you're familiar with the "in memory considerations" section of the page I've linked to in previous comment: "keep in mind that an additional 2 bytes of overhead are required for each non-null sparse column in the table when a row is being updated" – Damien_The_Unbeliever Jun 05 '15 at 14:16
  • OK, when I add 2 bytes for each column which has non-null values I still get less than 6000 bytes... – Ickbinet Jun 07 '15 at 09:44

0 Answers0