SQL Server Maximum row size

Question

Came across this error today. Wondering if anyone can tell me what it means:

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

Is that 8094 bytes? Characters? Fields? Is this a problem joining multiple tables that are exceeding some limit?

Answer 1

That used to be a problem in SQL 2000, but I thought that was fixed in 2005.

Answer 2

8094 bytes.

If you list some more information about what you are doing it might help us to figure out the actual cause.

Answer 3

@AndyMan: I'm actually using SQL 2000 (I'm at work).

Answer 4

This post does a fair job at trying to explain it (in the context of SQL 2005):

http://www.consortioservices.com/Blog/2008/02/28/MaximumRowSizeInSQLServer2005ToTheLimit.aspx

Answer 5

In SQL 2000, the row limit is 8K bytes, which is the same size as a page in memory.

[Edit]

In 2005, the page size is the same (8K), but the database uses pointers on the row in the page to point to other pages that contain larger fields. This allows 2005 to overcome the 8K row size limitation.

Answer 6

Thanks Jeremiah. Those are the answers I was seeking.

Answer 7

The problem that seems to catch a lot of people, is that you can create a table that by definition would hold more than 8K of data, and it will accept it just fine. And the table will work fine, up until the point you actually try to insert more than 8K of data into the table.

So, let's say you create a table with an integer field, for the primary key, and 10 varchar(1000) fields. The table would work fine most of the time, as the number of times you would fill up all 10 of your varchar(1000) fields would be very few. Howerver, in the even that you tried to put 1000 characters in each of your fields, it would give you the error mentioned in this question.

Answer 8

@Erik Kibbee: Yeah, we have several tables with rows that are too large. I've brought the issue up a number of times (our database is an absolute mess), but it's a corporate shop, and no one wants to hear about structural problems.

From the look of things, NHibernate has actually been hiding this problem from us for a while. But everything blew up today on a Remove call (of all things).

Answer 9

FYI, running this SQL command on your DB can fix the problem if it is caused by space that needs to be reclaimed after dropping variable length columns:

DBCC CLEANTABLE (0,[dbo.TableName])

See: http://msdn.microsoft.com/en-us/library/ms174418.aspx