Total Column Length is greater than 8086 bytes

[wg26]

Hi Everyone:

Can anyone please tell me what will happen if I have a table, its total column length is greater than one page's capacity which is(8086 bytes)...Does SQL just stores data into two pages for one record or it just stores 8086 bytes data and we lose rest of the data? Any idea? Thanks alot

 

[karluk]

I'm afraid you will get an error if you try to insert a row that is over the maximum. SQL Server has never had the capability of splitting a row across multiple pages, and I don't think this limitation has been solved in SQL Server 2000.

Of course a lot of time this problem is more theoretical than real. Varchar columns are defined to be much larger than the data they will typically hold. If you are really likely to hit the limit you will have to code your application to deal with overflow.

 

[uncleroydee]

How does one go about determining which records in a database are at or near the 8086 byte limit?

 

[wg26]

When you design your table, you have each column with different data type and each data type or column has it own length, you should add all the columns together and see what is the total length(or Byte) ...that is how you determine how big your each record is....

 

[SQLSister]

What you can do is create two tables with a one to one relationship and split the data that way. Try to put the fields which are most frequently accessed or updated together in the same table if possible to minimize having to write queries with joins.