Row length exceeds 8060 bytes

[Dabase]

Hi,

I am trying to create a new table in an SQL Server 2000 database.

After I run the script to create the database, I get the following message:

Warning: The table 'tblTest' has been created but its maximum row size (11023) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.


Is it possible to increase the maximum row size, or perhaps another solution (apart from modifying the individual field datatypes and sizes)?

RDMS: SQL Server 2000 with SP3


Thanks
Dabase

 

[SQLBI]

In SQL Server a row cannot span more than 1 extent, or 8060 bytes. Nothing you can do about it.

My suggestion would be to try to normalise your database to cut down the size of your table.



Cheers,
Leigh

You're only as good as your last backup!

 

[SQLBI]

Sorry, cannot span more than 1 page. An Extent is 8 contiguous pages.

Too early for me lol.


Cheers,
Leigh

You're only as good as your last backup!

 

[Dabase]

LeighMoore,

I appreciate your quick response.



Thanks
Dabase

 

[mrdenny]

Your best bet to fix this will be to redesign the tables. If you post the table schema we can probably make some useful suggestions.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLSister]

The process you need to go through is first to examine your data types and the sizes they represent.

If you have large field sizes, can they be cut down. How many fields do you have? Are all those fields absolutely necessary? If you have fiwelds such as Phone_1, phone_2, phone_3 they should be broken down to a related tables.

If you truly need each of the fields and you are certain that the business rules will prevent all of them from having the full number of saved characters, you may not have a problem. However, the safer route is to take the less used fields and put them ion a related table that has the id field from the first table and a 1-1 relationship.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[Dabase]

Hi,

Thanks to all of you for your suggestions. I have decided to redesign the table and to create related tables.

Thanks
Dabase