When selecting data from a table we can select rows which contain more than 8094 bytes.

The problem arises when trying to sort those rows.

in SQL Server 2000 this code:

CREATE TABLE t1 (id INT IDENTITY(1,1), c1 VARCHAR(8000))
CREATE TABLE t2 (id INT IDENTITY(1,1), c2 VARCHAR(8000)) 
INSERT INTO t1 (c1)
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000)
INSERT INTO t2 (c2)
SELECT REPLICATE('b', 8000) UNION ALL
SELECT REPLICATE('b', 8000) UNION ALL
SELECT REPLICATE('b', 8000) UNION ALL
SELECT REPLICATE('b', 8000) UNION ALL
SELECT REPLICATE('b', 8000) UNION ALL
SELECT REPLICATE('b', 8000) UNION ALL
SELECT REPLICATE('b', 8000)
SELECT    *
FROM    t1
JOIN t2 ON t1.id = t2.id
ORDER BY c1, c2 desc
DROP TABLE t1
DROP TABLE t2

results in this error:

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

When trying this same thing in SQL Server 2005 it works OK.

It was a nice surprise i must say.

Note that the acctual length of the row is considered and not the defined one.

So for varchar(8000) with only 1000 characters, the row size is 1000 bytes and not 8000 bytes.