20 February 2025

💠🛠️🗒️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


    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]

    No comments:

    Related Posts Plugin for WordPress, Blogger...

    About Me

    My photo
    Koeln, NRW, Germany
    IT Professional with more than 25 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.