SQL:错误，已达到表达式服务限制? [英] SQL: Error, Expression services limit reached?

问题描述

内部错误:已达到表达式服务限制.请在查询中查找潜在的复杂表达式，并尝试简化它们."

"Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them."

有没有人以前见过这个并找到了一个好的解决方法?

Has anyone seen this before and found a good workaround?

我通过将我的 SQL 查询本质上分为两部分并将第一个 SQL 选择查询写入临时表和第二部分，一个新的 SQL 选择语句从临时表中选择并使用了大量 CROSS 来解决这个问题APPLY 运算符来计算级联计算列.

I managed to get around this issue by splitting my SQL query into two parts essentially and writing the first SQL select query to a temp table and the second part, a new SQL select statement selects from the temporary table and uses alot of CROSS APPLY operator to Calculate cascading computed columns.

这是第二部分外观的示例，但我使用了更多交叉应用来生成作为计算的新列:

This is an example of how the second part looks but I'm using alot more Cross Applys to produce new columns which are calculations:

Select * from #tempTable        

cross apply
    (
      select HmmLowestSalePrice =
       round(((OurSellingPrice + 1.5) / 0.95) - (CompetitorsLowestSalePrice) + 0.08, 2)
    ) as HmmLowestSalePrice 

cross apply
    (
      select checkLowestSP =
       case 
        when adjust = 'No Room' then 'No Room'
        when OrginalTestSalePrice >= CompetitorsLowestSalePrice then 'Minus'
        when OrginalTeslSalePrice < CompetitorsLowestSalePrice then 'Ok'
      end
) as checkLowestSP  

cross apply
    (
        select AdjustFinalNewTestSP =
        case
        when FinalNewTestShipping < 0 Then  NewTestSalePrice - (FinalNewTestShipping)
        when FinalNewTestShipping >= 0 Then NewTestSalePrice
        end
) as AdjustFinalNewTestSP

cross apply
    (
      select CheckFinalSalePriceWithWP  =
      case 
        when round(NewAdminSalePrice, 2) >= round(wholePrice, 2) then 'Ok'

        when round(NewAdminSalePrice, 2) < round(wholePrice, 2) then 'Check'
      end
    ) as CheckFinalPriceWithWP 


DROP TABLE #tempTable

我的目标是将其放入 sql 报告中，如果只有 1 个用户，它就可以正常工作，因为 #tempTable 将在同一执行中创建和删除，结果显示在正确报告.但是将来如果有并发用户，我担心他们会写入相同的 #tempTable 会影响结果吗?

My goal to to put this into a sql report and it work fine if there is 1 user only as the #tempTable will get created and dropped in the same execution and the results are displayed in the report correctly. But in the future if there are concurrent users I'm concerned that they will be writing to the same #tempTable which will affect the results?

我已经考虑将其放入存储过程，但仍然收到上述错误消息.

I've looked at putting this into stored procedures but still get the error message above.

推荐答案

出现此问题的原因是 SQL Server 限制了单个查询表达式中可以包含的标识符和常量的数量.限制为 65,535.在 SQL Server 展开所有引用的标识符和常量之后，执行标识符和常量数量的测试.在 SQL Server 2005 及更高版本中，查询在内部进行了规范化和简化.这包括*(星号)、计算列等.

This issue occurs because SQL Server limits the number of identifiers and constants that can be contained in a single expression of a query. The limit is 65,535. The test for the number of identifiers and constants is performed after SQL Server expands all referenced identifiers and constants. In SQL Server 2005 and above, queries are internally normalized and simplified. And that includes *(asterisk), computed columns etc.

为了解决此问题，请重写您的查询.在查询的最大表达式中引用较少的标识符和常量.您必须确保查询的每个表达式中的标识符和常量的数量不超过限制.为此，您可能必须将查询分解为多个查询.然后，创建一个临时的中间结果.

In order to work around this issue, rewrite your query. Reference fewer identifiers and constants in the largest expression in the query. You must make sure that the number of identifiers and constants in each expression of the query does not exceed the limit. To do this, you may have to break down a query into more than one single query. Then, create a temporary intermediate result.

这篇关于SQL:错误，已达到表达式服务限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！