Sql: Error, Expression Services Limit Reached

SQL Server - Internal error: An expression services limit has been reached

Please re-write your query!

This issue occurs because SQL Server limits the number of identifiers and constants that can be contained in a single expression of a query. This limit is 65,535.

one approach might be :

You can split the select query to multiple select queries.Store the
result in temp tables and combine the results at the end.

more info

CASE Statement - An expression services limit has been reached

As I mentioned in the comment, I would suggest you create a "lookup" table for the post codes, then all you need to do is JOIN to the table, and not have a "messy" and large CASE expression (T-SQL doesn't support Case (Switch) statements).

So your lookup table would look a little like this:

CREATE TABLE dbo.PostcodeRegion (Postcode varchar(2),
                                 Region varchar(20));
GO
--Sample data
INSERT INTO dbo.PostcodeRegion (Postcode,Region)
VALUES('DG','Scotland'),
      ('BT','Ireland'),
      ('LL','Wales');

And then your query would just do a LEFT JOIN:

SELECT RPA.CardCode,
       RPA.CardName,
       RPA.PostCode,
       COALESCE(PR.Region,'No Region') AS Region
FROM [dbo].[REPS-PostcodeABBR] RPA --T0 is a poor choice of an alias, there is no T not 0 in "REPS-PostcodeABBR"
     LEFT JOIN dbo.PostcodeRegion PR ON RPA.PostCodeABR = PR.Region;

Note you would likely want to INDEX the table as well, and/or apply a UNIQUE CONSTRAINT or PRIMARY KEY to the PostCode column.

How can I reduce the complexity of this expression?

It looks like you want to figure out how to compare that list... you can do that with a sub query.

First, use OPENROWSET to bring in your values from excel into a TEMP TABLE

IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL DROP TABLE #tempTable

SELECT * INTO #tempTable
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=C:\yourFolder\yourFile.xlsx',
    [ProductList$]);

Now you can use this for your IN clause, via a sub-select.

SELECT
CASE 
   WHEN ApplicationDate BETWEEN @START and @END
       AND (convert(varchar,LEFT(AAA.LastName,2)+LEFT(BBB.CITY,2)+BBB.ZIPCode+RIGHT(CCC.SSN,4)) in (select * from #tempTable) THEN 'True'
       ELSE 'False'
   END as SomeNewColumn
FROM 
   SomeTable

ObjectSet from Entity Framwork is too big

Using EF will always become into a problem when performance is critical or it's needed a better control.

After try several options, I realized that the unique options to solve my problem are:

• Delete tables that I'm not using anymore.

• Create a method that makes the query to each entity that compose de entitySet independently. This solution improve the performance of the query.

• Use a microORM as Dapper at this case and another critical points wich require better performance, mantaining EF for the rest of the system. (This one is my favourite)

Limit to number of Items in list for WHERE clause SQL query

Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.

Error 8623:

The query processor ran out of internal resources and could not
produce a query plan. This is a rare event and only expected for
extremely complex queries or queries that reference a very large
number of tables or partitions. Please simplify the query. If you
believe you have received this message in error, contact Customer
Support Services for more information.

Error 8632:

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

microsoft docs

---

Related Topics