💠🛠️🗒️SQL Server: Folding [Notes]

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]