Monday, March 26, 2012

Saving large text file in table

Have created an SQL Server 2000 table which includes two columns each data type varchar(8000) in order to accomodate a semi-large amount of text (want to avoid using text data type). I enter the max in the first column but I keep getting an error of "Cannot create a row of size 12066 which is greater than the allowable maximum of 8060" when I try to save any more in the second column of the table. What am I doing wrong?

SQL Server stores rows (and indexes) in pages. Pages are 8K in size. There is some required header information (i.e. next block in chain, etc.), which drops the usable space for rows to 8060. So a single row cannot exceed 8060 bytes and rows cannot span multiple pages. You have two choices:

Split the table into two tables:
Table1

Id int (PK)
Text1 varchar(8000)

Table2
Id int (PK and FK to Table1)
Text2 varchar(8000)

Use the text data type:
The text data type stores the data in separate data pages which can span multiple pages. In the row, you simply store a pointer to the first data page. You can optimize the table so that the text type is stored inline with the row so long as it doesn't exceed a certain size.

|||Only had a couple of pages that were over the 8060 limit and using two tables works perfect. Thanks for the advice.|||

Glad I could help. You can always throw an updatable view overtop of the two tables so that they appear as one table to queries. Just write a view that INNER JOINs the two tables and then write INSTEAD OF INSERT, UPDATE, and DELETE triggers.

No comments:

Post a Comment