Problems with Maximum column size

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problems with Maximum column size

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-12-08 : 10:41:13

When I try to run a SP, I get the following error:

Cannot sort a row of size 8107, which is greater than the allowable maximum of 8094.

I believe I am getting this error because I have exceeded the maximum column size

Can I do anything to remove this error, and get the result

One of the column of my Table is Nvarchar 3000.

Is there a maximum size limit for database tables

regards

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-08 : 11:54:15

There is a maximum size for an individual table row; the error message told you that is is 8094.

The total maximum size for all columns definitions in a table can exceed 8094, but an individual phyical row cannot exceed 8094.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-09 : 01:15:50

In SQL Server help file read maximum capacity specifications for more information

Madhivanan

Failing to plan is Planning to fail

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-12-12 : 04:51:14

Actually, this is the structure of my table:

ProductID nvarchar (50)
ProductName nvarchar (50)
ShortDescription nvarchar (1000)
LongDescription nvarchar (3000)
< Some other columns >

I copied the content of ShortDescription & LongDescription into Word document and looked at the word count:

ShortDescriotion contains = 652 characters with spaces
LongDescription contains = 959 characters with spaces

So the total data contained in one row is below 8094

So not sure why I get size limit error?
kind regards

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-12-12 : 06:29:23

Try using substring in order by clause
e.q.
Select Productid, productname, ShortDescription, LongDescription
From Product
Order By substring(ShortDescriotion,1,700), substring(LongDescription ,1,970)

Surendra

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-12 : 08:49:00

NVARCHAR data takes up two bytes per character. You can use the DATALENGTH function to get the size of a column in a row, and then add them up to get the total size of the row.

quote:
Originally posted by hasanali00

Actually, this is the structure of my table:

ProductID nvarchar (50)
ProductName nvarchar (50)
ShortDescription nvarchar (1000)
LongDescription nvarchar (3000)
< Some other columns >

I copied the content of ShortDescription & LongDescription into Word document and looked at the word count:

ShortDescriotion contains = 652 characters with spaces
LongDescription contains = 959 characters with spaces

So the total data contained in one row is below 8094

So not sure why I get size limit error?
kind regards

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-12-14 : 05:57:42

A row size of 8094 is not much.

Do you store a lengthy data in MSSQL, like web pages ??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources