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.
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