无法对大小为 8130 的行进行排序，该行大于允许的最大值 8094

发布于 2024-08-27 02:37:02 字数 1500 浏览 6 评论 0原文

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%'))

当我尝试执行查询时，会导致以下错误。

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

我查了一下，没有找到任何解决办法。唯一的方法是截断 (substring()) 查询中的“tblJobAnnouncement.JobClassDesc”，该查询的列大小约为 8000。

我们是否有任何解决办法，以便我不需要截断值。或者这个查询可以优化吗？ SQL Server 2000 中有什么设置吗？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

北凤男飞 2024-09-03 02:37:02

SQL 需要SORT的[不明显]原因是DISTINCT关键字。

根据数据和基础表结构，您也许能够消除此 DISTINCT，从而不会触发此错误。

您很容易找到替代解决方案，即截断 SELECT 列表中的某些字段。

编辑：回答“您能否解释一下 DISTINCT 是这里的原因吗？”
一般来说，满足 DISTINCT 要求的方式随

数据上下文（预期的行数、索引的存在/不存在、行的大小...）、
SQL 实现的版本/品牌（特别是查询优化器接收每个新版本的新的或修改的启发式，有时会导致在不同的上下文中针对不同的构造产生替代的查询计划）

然而，与“DISTINCT查询”相关的所有可能的计划都涉及*某种形式*合格记录的排序。在最简单的形式中，计划“拳头”生成符合条件的行（记录）列表（满足查询的 WHERE/JOIN/等部分的记录列表），然后对该列表进行排序（可能包括一些重复项），仅保留每个不同行的第一次出现。在其他情况下，例如，当仅选择几列并且覆盖这些列的某些索引可用时，查询计划中不会使用显式排序步骤，但对索引的依赖隐含着“可排序性” ” 底层列。在其他情况下，查询优化器会选择涉及各种形式的合并或散列的步骤，这些步骤最终也意味着比较两行的能力。
底线：DISTINCT 意味着某种排序。

在问题的具体情况下，SQL Server 报告并阻止查询完成的错误是“排序不可能对大于...”并且，DISTINCT 关键字是查询需要任何排序的唯一明显原因（顺便说一句，许多其他 SQL 结构暗示排序：例如 UNION），因此删除 DISTINCT 的想法（如果逻辑上可能的话）。< br>
事实上，您应该删除它，出于测试目的，以断言在没有 DISTINCT 的情况下，查询可以正常完成（如果只包含一些重复项）。一旦确认了这一事实，并且如果查询实际上可以产生重复行，请研究在不使用 DISTINCT 关键字的情况下产生无重复查询的方法；涉及子查询的结构有时可以用于此目的。

一个不相关的提示是使用表别名，使用短字符串来避免重复这些长表名称。例如（只做了几张桌子，但你明白了......）

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%'))

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%'))

回复收藏 0 原文

孤独难免 2024-09-03 02:37:02

仅供参考，如果问题是由删除可变长度列后需要回收的空间引起的，则在数据库上运行此 SQL 命令可以解决该问题：

DBCC CLEANTABLE (0,[dbo.TableName])

请参阅：http://msdn.microsoft.com/en-us/library/ms174418.aspx

FYI, running this SQL command on your DB can fix the problem if it is caused by space that needs to be reclaimed after dropping variable length columns:

DBCC CLEANTABLE (0,[dbo.TableName])

See: http://msdn.microsoft.com/en-us/library/ms174418.aspx

回复收藏 0 原文

暮色兮凉城 2024-09-03 02:37:02

这是 SQL Server 2000 的限制。您可以：

将其拆分为两个查询并在其他地方组合

从 TableA JOIN TableB 中选择 ID、ColumnA、ColumnB
从表 A 中选择 ID、列 C、列 D 连接表 B

适当截断列
```
向左选择(LongColumn,2000)...
```

从 SELECT 中删除任何冗余列

SELECT ColumnA、ColumnB、--IDColumnNotUsedInOutput
来自表A

从 SQL Server 2000 迁移

This is a limitation of SQL Server 2000. You can:

Split it into two queries and combine elsewhere

SELECT ID, ColumnA, ColumnB FROM TableA JOIN TableB
SELECT ID, ColumnC, ColumnD FROM TableA JOIN TableB

Truncate the columns appropriately
```
SELECT LEFT(LongColumn,2000)...
```

Remove any redundant columns from the SELECT

SELECT ColumnA, ColumnB, --IDColumnNotUsedInOutput
FROM TableA

Migrate off of SQL Server 2000

回复收藏 0 原文

~没有更多了~

关于作者

桃扇骨

暂无简介

0 文章

0 评论

23 人气

关注发私信

友情链接

文江博客

无法对大小为 8130 的行进行排序，该行大于允许的最大值 8094

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（3）

关于作者

相关话题

热门标签

推荐作者

linfzu01

§对你不离不弃

可遇━不可求

枕梦

qq_3LFa8Q

JP

友情链接

无法对大小为 8130 的行进行排序，该行大于允许的最大值 8094

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（3）

关于作者

相关话题

热门标签

推荐作者

linfzu01

§对你不离不弃

可遇━不可求

枕梦

qq_3LFa8Q

JP

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。