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

Question

SELECT DISTINCT tblJobReq.JobReqId
              , tblJobReq.JobStatusId
              , tblJobClass.JobClassId
              , tblJobClass.Title
              , tblJobReq.JobClassSubTitle
              , tblJobAnnouncement.JobClassDesc
              , tblJobAnnouncement.EndDate
              , blJobAnnouncement.AgencyMktgVerbage
              , tblJobAnnouncement.SpecInfo
              , tblJobAnnouncement.Benefits
              , tblSalary.MinRateSal
              , tblSalary.MaxRateSal
              , tblSalary.MinRateHour
              , tblSalary.MaxRateHour
              , tblJobClass.StatementEval
              , tblJobReq.ApprovalDate
              , tblJobReq.RecruiterId
              , tblJobReq.AgencyId 

FROM ((tblJobReq 
    LEFT JOIN tblJobAnnouncement ON tblJobReq.JobReqId = tblJobAnnouncement.JobReqId) 
    INNER JOIN tblJobClass ON tblJobReq.JobClassId = tblJobClass.JobClassId)

    LEFT JOIN tblSalary ON tblJobClass.SalaryCode = tblSalary.SalaryCode 

WHERE (tblJobReq.JobClassId in (SELECT JobClassId 
                                from tblJobClass 
                                WHERE tblJobClass.Title like '%Family Therapist%'))

When i try to execute the query it results in the following error.

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

I checked and didn't find any solution. The only way is to truncate (substring())the "tblJobAnnouncement.JobClassDesc" in the query which has column size of around 8000.

Do we have any work around so that i need not truncate the values. Or Can this query be optimised? Any setting in SQL Server 2000?

Answer 1

The [non obvious] reason why SQL needs to SORT is the DISTINCT keyword.

Depending on the data and underlying table structures, you may be able to do away with this DISTINCT, and hence not trigger this error.

You readily found the alternative solution which is to truncate some of the fields in the SELECT list.

Edit: Answering "Can you please explain how DISTINCT would be the reason here?"
Generally, the fashion in which the DISTINCT requirement is satisfied varies with

• the data context (expected number of rows, presence/absence of index, size of row...)
• the version/make of the SQL implementation (the query optimizer in particular receives new or modified heuristics with each new version, sometimes resulting in alternate query plans for various constructs in various contexts)

Yet, all the possible plans associated with a "DISTINCT query" involve *some form* of sorting of the qualifying records. In its simplest form, the plan "fist" produces the list of qualifying rows (records) (the list of records which satisfy the WHERE/JOINs/etc. parts of the query) and then sorts this list (which possibly includes some duplicates), only retaining the very first occurrence of each distinct row. In other cases, for example when only a few columns are selected and when some index(es) covering these columns is(are) available, no explicit sorting step is used in the query plan but the reliance on an index implicitly implies the "sortability" of the underlying columns. In other cases yet, steps involving various forms of merging or hashing are selected by the query optimizer, and these too, eventually, imply the ability of comparing two rows.
Bottom line: DISTINCT implies some sorting.

In the specific case of the question, the error reported by SQL Server and preventing the completion of the query is that "Sorting is not possible on rows bigger than..." AND, the DISTINCT keyword is the only apparent reason for the query to require any sorting (BTW many other SQL constructs imply sorting: for example UNION) hence the idea of removing the DISTINCT (if it is logically possible).
In fact you should remove it, for test purposes, to assert that, without DISTINCT, the query completes OK (if only including some duplicates). Once this fact is confirmed, and if effectively the query could produce duplicate rows, look into ways of producing a duplicate-free query without the DISTINCT keyword; constructs involving subqueries can sometimes be used for this purpose.

---

An unrelated hint, is to use table aliases, using a short string to avoid repeating these long table names. For example (only did a few tables, but you get the idea...)

SELECT DISTINCT JR.JobReqId, JR.JobStatusId, 
  tblJobClass.JobClassId, tblJobClass.Title,
  JR.JobClassSubTitle, JA.JobClassDesc, JA.EndDate, JA.AgencyMktgVerbage,    
  JA.SpecInfo, JA.Benefits,
  S.MinRateSal, S.MaxRateSal, S.MinRateHour, S.MaxRateHour,
  tblJobClass.StatementEval, 
  JR.ApprovalDate, JR.RecruiterId, JR.AgencyId 
FROM (
(tblJobReq AS JR 
 LEFT JOIN tblJobAnnouncement AS JA ON JR.JobReqId = JA.JobReqId) 
INNER JOIN tblJobClass ON tblJobReq.JobClassId = tblJobClass.JobClassId)
LEFT JOIN tblSalary AS S ON tblJobClass.SalaryCode = S.SalaryCode
WHERE (JR.JobClassId in 
(SELECT JobClassId from tblJobClass 
WHERE tblJobClass.Title like '%Family Therapist%'))