Sample Header Ad - 728x90

Are there two levels of constant folding?

2 votes
1 answer
390 views
Are there two levels of constant folding, one in the conversion tree and one in the simplification phase? If I run the following query
SELECT 
    P.[ProductID]
FROM 
    Production.Product AS P
WHERE
    P.[ProductID] = 1 + 2 * 3
    OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 8606, QUERYTRACEON 8607, QUERYTRACEON 3604);
In the conversion tree, I find one constant, so I would call that basic constant folding? like [Paul Holmes](https://github.com/paulholmes-london/SQL-Server-Examples/blob/master/Logical%20Trees%20Step-By-Step/Logical%20Trees%20Part%202%20-%20Overview%20of%20Trees%20and%20Operators.sql) says in this example but if I run this query from [Paul White](https://www.sql.kiwi/2012/04/query-optimizer-deep-dive-part-1/)
use AdventureWorks2016
begin tran 
SELECT
    P.[Name]
FROM Production.Product AS P
WHERE
    P.[Name] LIKE 
        SUBSTRING(LEFT(CHAR(ASCII(CHAR(68))), 1) + '%', 1, 2)
OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 8606, QUERYTRACEON 8607, QUERYTRACEON 3604);
commit tran
and
begin tran 
SELECT
    P.[Name]
FROM Production.Product AS P
WHERE
    P.[Name] LIKE 
        'd%'
OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 8606, QUERYTRACEON 8607, QUERYTRACEON 3604);
commit tran
I don't see much difference between both in terms of conversion trees so the constant folding happened here and their simplified trees has the same operators? Are there different stages in constant folding? Where are they? what did I do wrong here, so I did not see it in the simplification phase?
Asked by Suleyman Essa (167 rep)
Jan 21, 2025, 12:03 PM
Last activity: Jan 21, 2025, 01:02 PM