SQL: error, Expression service limit reached?

Question

"Internal error: expression service limit has been reached. Look at 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 problem by dividing my SQL query into two parts essentially and writing the first SQL select query to a temporary table, and in the second part the new SQL select statement selects from the temporary table and uses a lot of CROSS APPLY to calculate cascaded computed columns.

This is an example of what the second part looks like, but I use more Cross Applys to create new columns that 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 is to put this in a sql report, and it works fine if there is 1 user, only when #tempTable is created and deleted in the same execution, and the results will be displayed correctly in the report. But in the future, if there are concurrent users, am I worried that they will write the same #tempTable that will affect the results?

I looked at how to include this in stored procedures, but still getting 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 query expression. The limit is 65,535. The test for the number of identifiers and constants is performed after SQL Server extends all reference identifiers and constants. In SQL Server 2005 and later, queries are internally normalized and simplified. And this includes * (asterisk), calculated columns, etc.

To work around this problem, rewrite your request. Link to fewer identifiers and constants in the largest expression in the request. You must ensure that the number of identifiers and constants in each query expression does not exceed the limit. To do this, you may need to split the request into multiple requests. Then create a temporary interim result.

Answer 2

I had this problem and fixed it by deleting the UNIQUE index on my table. For some reason, this seems to be causing this error, although it cannot understand why.

By the way, the same query works with several other indexes.

Answer 3

What worked for me, replacing several COALESCE ISNULL when possible