An interesting post popped up on the SQLDownUnder mailing list today, to do with the error message “Cannot sort a row of size 8365, which is greater than the allowable maximum row of 8094″ when running a query which has had multiple tables joined together under SQL Server 2000 (2005 has “Row Overflow” functionality, where long rows are split among different pages).
In this case, it was a DISTINCT clause causing the results to be sorted, but it could happen to any recordset that has had two tables with large row sizes joined together, and then sorted by both tables. If the recordset is to be sorted by just one table, then SQL Server will usually elect to sort that one table first, then perform a nested loop join onto the other table.
So, what do you do if you want to sort on two columns, one from each table? For example, imagine we had Books and BookReviews, and we wanted to get a list of all Books and Reviews for a particular author, ordered by the number of stars given to the review, and the book’s title.
CREATE TABLE Book (ISBN CHAR(15), Title VARCHAR(100), AuthorID INT, Description VARCHAR(7900), PRIMARY KEY (ISBN))
CREATE TABLE BookReview (ReviewId INT IDENTITY, ISBN CHAR(15), Review VARCHAR(7900), NumStars INT, ReviewDate DATETIME)
(Design for illustration purposes — in reality a TEXT field would be better than limiting reviews to 7900 characters)
SELECT b.ISBN, b.Title, b.Description, br.ReviewDate, br.NumStars, br.Review
FROM Book b
LEFT JOIN BookReview br ON b.ISBN = br.ISBN
WHERE AuthorID = @AuthorID
ORDER BY br.NumStars, b.Title
This code will fail on the condition that the total size of the joined recordsets is greater than 8094 (for example, if the Description and the Review are both 5000 bytes long).
The best option is to perform the sort first, and then add the wide columns to the recordset:
SELECT b.ISBN, b.Title, b.Description, br.ReviewDate, br.NumStars, br.Review
(SELECT RowNumber, Book.ISBN, BookReview.ReviewID
FROM Book
LEFT JOIN BookReview ON Book.ISBN = BookReview.ISBN
ORDER BY BookReview.NumStars, Book.Title) sorted
JOIN Book b ON sorted.ISBN = b.ISBN
JOIN BookReview br ON sorted.ReviewID = br.ReviewID
ORDER BY sorted.RowNumber
RowNumber then needs to be generated by your favourite method, for example joining onto a predefined table containing positive integers.