Troubleshooting Row Size Errors
New: 5 December 2005
The SQL Server 2005 Database Engine sometimes performs sort operations before it evaluates expressions. Therefore, some queries that execute in SQL Server 2000 might return an error in SQL Server 2005. These queries typically specify the following:
- Very long char, nchar, varchar, and nvarchar fields in their result sets.
- Any clause that can cause the Database Engine to start a sort operation, such as the ORDER BY, GROUP BY, and DISTINCT clauses; and also JOIN clauses that the SQL Server query optimizer chooses to solve by using a Merge Join algorithm that requires sorting.
The SQL Server 2005 Database Engine may perform the sort operation before evaluating any expressions that appear earlier in the query. To do this, the Database Engine must create a worktable to store intermediate results. If this worktable requires creating any rows that exceed the limit of 8,060 bytes, the query returns an error.
For example, consider the following set of tables:
USE tempdb;
GO
CREATE TABLE t1
(ch char(6000), vch1 varchar(100), vch2 varchar(100), vch3 varchar(100));
CREATE TABLE t2(i int);
GO
INSERT t1 VALUES(REPLICATE('abc', 2000),
'123456789012345678901234567890', '123456789012345678901234567890',
'123456789012345678901234567890');
INSERT t2 VALUES(7);
INSERT t2 VALUES(13);
GO
Now consider the following query. This query executes in SQL Server 2000 but returns an error in SQL Server 2005:
SELECT ch+ch, vch1, vch2, vch3
FROM t1, t2
ORDER BY vch1, i;
GO
The query fails in SQL Server 2005 because the Database Engine evaluates the ORDER BY clause earlier than it evaluates the clause in SQL Server 2000, and because the query requests a very long char field in its result set. The worktable the Database Engine builds specifies one or more rows that exceed the limit of 8,060 bytes.
To run the query successfully, convert any long fields in the select list to the varchar(max) or nvarchar(max) data types by using CAST or CONVERT. For example, the following query executes successfully in SQL Server 2005:
SELECT CONVERT(varchar(max),ch+ch), vch1, vch2, vch3
FROM t1, t2
ORDER BY vch1, i;
GO
See Also
Other Resources
Troubleshooting Queries
CAST and CONVERT (Transact-SQL)