Cannot create a row that has sparse data of size

493 views Asked by At

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

0

There are 0 answers