Error message when you run a query in SQL Server 2005: "Internal error: An expression services limit has been reached" (913050)

SYMPTOMS

When you run a query in Microsoft SQL Server 2005, you may receive the following error message:

Server: Msg 8632, Level 17, State 2, Line 1
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

CAUSE

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. For example, the following query only has one expression:

select a, b + c, d + e

This expression retrieves all five columns, calculates the addition operators, and sends three projected results to the client.

In SQL Server 2005, queries are internally normalized and simplified in a different way than in Microsoft SQL Server 2000. Therefore, some queries that contain lots of constants and identifiers may successfully compile in SQL Server 2000. However, the same queries cannot run in SQL Server 2005.

The test for the number of identifiers and constants is performed after SQL Server expands all referenced identifiers and constants. For example, the following items may be expanded:

The asterisk (*) in the select list
A view
A computed column definition

If the number after the expansion exceeds the limit, the query cannot run.

WORKAROUND

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.