SQL: Error, Expression services limit reached?

Question

"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?

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

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.

Answer 1

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.

Answer 2

The same issue happens to me when we tried to change the Database Compatibility Level to 150. It is not an issue when it is 140 or lower.