Disclaimer: This is work in progress intended to consolidate information from various sources. It considers only on-premise SQL Server, for other platforms please refer to the documentation.
Last updated: 20-Feb-2024
[SQL Server] Folding
- {def} the process by which the optimizer is able to properly determine certain types of indexable expressions even when the column in the expression is involved in a subexpression or nestled in a function
- is an optimization over older versions of SQL Server in which the optimizer was unable to use an index to service a query clause when the table column was involved in an expression or buried in a function [1]
- {type} constant folding
- some constant expression is evaluated early
- foldable constant expressions [2]
- arithmetical expressions
- logical expressions
- built-in functions whose input doesn’t depend of contextual information,
- e. g. SET options, language settings, database options, encryption keys
- deterministic built-in functions are foldable, with some exceptions
- certain forms of the LIKE predicate
- [SQL Server 2012+] deterministic methods of CLR user-defined types [3]
- [SQL Server 2012+] deterministic scalar-valued CLR user-defined functions [3]
- nonfoldable expressions [2]
- expressions whose results depend on a local variable or parameter
- user-defined functions
- both T-SQL and CLR
- expressions whose results depend on language settings.
- expressions whose results depend on SET options.
- expressions whose results depend on server configuration options.
- nonconstant expressions such as an expression whose result depends on the value of a column.
- nondeterministic functions
- if the output is a large object type, then the expressions are not folded
- e.g. text, image, nvarchar(max), varchar(max), varbinary(max), XML
- {benefit} the expression does not have to be evaluated repeatedly at run time [2]
- {benefit} the value of the expression after it is evaluated is used by the query optimizer to estimate the size of the result set of the portion of the query [2]
- e.g. TotalDue > 117.00 + 1000.00
- {type} nonconstant folding
- some expressions that are not constant folded but whose arguments are known at compile time, whether the arguments are parameters or constants, are evaluated by the result-set size (cardinality) estimator that is part of the optimizer during optimization [2]
- deterministic functions:
- e.g. UPPER, LOWER, RTRIM
- e.g. DATEPART( YY only ), GetDate, CAST, CONVERT
- operators
- arithmetic operators: +, -, *, /, unary -,
- logical Operators: AND, OR, NOT
- comparison operators: <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULL
Previous Post
<<||>> Next Post
References:
[1] Ken Henderson (2003) Guru's Guide to SQL Server Architecture and Internals
[2] Microsoft Learn (2012) SQL Server: Troubleshooting Poor Query Performance:
Constant Folding and Expression Evaluation During Cardinality Estimation [link]
[3] Microsoft Learn (2025) SQL Server: Query processing architecture guide [link]
[4] SQLShack (2021) Query Optimization in SQL Server for beginners, by Esat Erkec [link]
[3] Microsoft Learn (2025) SQL Server: Query processing architecture guide [link]
[4] SQLShack (2021) Query Optimization in SQL Server for beginners, by Esat Erkec [link]