CODEWITHSUNDEEP
sqlsql-server-2008database-administration
China Syndrome
China Syndrome

Reputation: 993

Need to exceed the 8k record limit when using wide columns/sparse tables

Need to exceed the 8k record limit in SQL Server 2008 when using wide columns/sparse tables.

Long story new client old system using survey system, pivoting data so all answers are a column

I have 1500 columns and now I am getting

Cannot create a row that has sparse data of size 9652 which is greater than the allowable maximum sparse data size of 8019.

I need to exceed the 8k record limit if possible

Upvotes: 2

Views: 511

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280351

Not possible, because SQL Server stores rows on 8K pages. The only way to do so would be to store some of the data off-row (e.g. using MAX or other LOB types for some of your columns). To your application, this will still look like it's on the same row, even though logically it is on a complete different area of disk.

If your sparse column set alone exceeds the limit, sorry, you'll need to look at a different way to store the data (either not pivoted, EAV, or simply use two tables joined by a key, each containing half of the column set). For the latter you can make this relatively transparent to users by using views and/or enforcing all data access / DML through stored procedures that understand the division.

Upvotes: 3

Related Questions