FIX: SELECT with ORDER BY on an Oversized Table Fails

ID: Q175659

The information in this article applies to:

Microsoft SQL Server version 6.5

BUG #: 16074 (WINDOWS: 6.5)

SYMPTOMS

If you create an "oversized" table (that is, a table with a width greater than 1,962 bytes), you will receive the following message:

The total row size, 2575, for table 't1' exceeds the maximum number of
bytes per row, 1962.

If this table does not have an index and you execute a query with an ORDER BY clause (such as "SELECT * from t1 ORDER BY c1"), you will receive the following message:

Msg 416, Level 16 State 1
Create of work table failed because the row size would be 2575.
This exceeds the maximum allowable size of a row in a table, 2014.

WORKAROUND

To avoid this problem, use a statement similar to the following to create an index on the table:


   create index ti on t1(c1)

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.

MORE INFORMATION

The following statements will reproduce the problem:


   create table t1
   (c1 varchar(255) not null,
    c2 varchar(255) not null,
    c3 varchar(255) not null,
    c4 varchar(255) not null,
    c5 varchar(255) not null,
    c6 varchar(255) not null,
    c7 varchar(255) not null,
    c8 varchar(255) not null,
    c9 varchar(255) not null,
    c10 varchar(255) not null)
   go
   insert t1 values("A","Z","Z","Z","Z","Z","Z","Z","Z","Z")
   insert t1 values("B","Z","Z","Z","Z","Z","Z","Z","Z","Z")
   insert t1 values("C","Z","Z","Z","Z","Z","Z","Z","Z","Z")
   insert t1 values("D","Z","Z","Z","Z","Z","Z","Z","Z","Z")
   insert t1 values("E","Z","Z","Z","Z","Z","Z","Z","Z","Z")
   insert t1 values("F","Z","Z","Z","Z","Z","Z","Z","Z","Z")
   insert t1 values("G","Z","Z","Z","Z","Z","Z","Z","Z","Z")
   insert t1 values("H","Z","Z","Z","Z","Z","Z","Z","Z","Z")
   insert t1 values("I","Z","Z","Z","Z","Z","Z","Z","Z","Z")
   insert t1 values("J","Z","Z","Z","Z","Z","Z","Z","Z","Z")
   go

Additional query words: sp sp2


Keywords          : kbusage SSrvGen SSrvTran_SQL kbbug6.50 kbfix6.50.sp2 
Version           : WINNT:6.5
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: April 16, 1999