Showing posts with label expressions. Show all posts
Showing posts with label expressions. Show all posts

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

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]
Related Posts Plugin for WordPress, Blogger...

About Me

My photo
Koeln, NRW, Germany
IT Professional with more than 24 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.