Hi Sql Experts,
At my company, we have authored a piece of software that allows users to examine data by viewing it in a grid, and authoring expressions against that data. Our software ultimately distills those expressions authored in the application to sql expressions used in a SELECT. These expressions are all linear (no aggregations/grouping), and once a user has authored an expression, they can reference that expression in future expressions. This allows users to eventually (and quite accidentally) author an expression that is too complex for sql server. In that case, we will see the typical expression services limit error -
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
The obvious answer is to simplify/reduce the expressions used in the query (and ultimately, in the application), but, that's difficult because from a technical perspective, it's difficult to offer advice on how the user can know they are writing something excessively complex (because we cannot predict the complexity of the expression they are writing), and also from a business perspective, when we do tell them to simplify the expressions, it makes the tool less useful.
We've uncovered different techniques of carving up what would have been large expressions into digestible chunks. Pre-2019, taking those expressions and stuffing them in scalar functions worked. But in 2019, those scalar functions are inlined and negates the usefulness of those. Temp tables work, but it's very difficult to know how to pre-calculate certain expressions to break them up programatically. It also slows down the query.
So I'd like to reach out to this community for opinions, or even optimistically, answers. Do you have insight into how the expression service behaves (where in the query planning/execution process the 65k limit is applied for instance), or have ideas on how to carve up expressions using querying techniques (CTE's, subqueries, etc), in a way that is reliable in the arbitrary nature of the expressions defined by the user?
Thanks in advance.