i

Description

A printing press

Source

John Farey · original page

Licence

PD Public domain

Original photo, unretouched. The background is a version of it stylised by the site.

"Generating code: inlining and hard-coded values"

Pay once so that each execution costs less: the result of a reference SELECT becomes a literal value the optimizer can exploit.

In brief

• Problem: the optimizer ignores the contents of the reference tables you join.
• Measurement: 100 actual rows; 25,000 estimated with a join, a variable or a table parameter; 100 with a literal, RECOMPILE or a generated object.
• Inline: inline table-valued function, scalar inlining (2019), generated unfolding.
• Rule: a generated object that is deterministic, reproducible, and regenerated when the reference data changes.

On this page

1. 1. The problem: the optimizer does not know the contents of your reference tables
2. 2. Measurement: what the optimizer believes depending on how you write it
3. 3. The idea: unfold the reference table into a generated object
4. 4. The three meanings of “inline”
5. 5. Generating a deterministic function
6. 6. Filtered indexes: the literal makes the difference
7. 7. When is it worth it?
8. 8. Generating cleanly
9. 9. Risks
10. 10. Alternatives
11. Sources and verification

“Compiling on the fly” means producing the text of a T-SQL object from data, then having the engine compile it. Applied to a small reference table, it lets you hard-code the result of a SELECT in the code, and give the optimizer values it could not have guessed. This page explains the benefit, the mechanism, its limits and the right way to go about it.

1. The problem: the optimizer does not know the contents of your reference tables#

A query that filters on “the Americas zone” must first find, through a join, the countries of that zone. When choosing its plan, the optimizer does not know which ones they are, and therefore what share of the data they represent. It then relies on average statistics. With skewed data, the gap from reality can be several orders of magnitude.

2. Measurement: what the optimizer believes depending on how you write it#

Test data set: 100,000 facts very unevenly distributed across four countries (the “AM” zone contains only the United States, 100 rows).

CREATE TABLE dbo.Zones (ZoneId int NOT NULL PRIMARY KEY, Code nvarchar(10) NOT NULL);
CREATE TABLE dbo.Pays  (PaysId int NOT NULL PRIMARY KEY, Code nchar(2) NOT NULL, ZoneId int NOT NULL REFERENCES dbo.Zones (ZoneId));
CREATE TABLE dbo.Faits (FaitId int NOT NULL PRIMARY KEY, PaysId int NOT NULL, Statut tinyint NOT NULL, Montant decimal(18,2) NOT NULL);
CREATE INDEX IX_Faits_Pays ON dbo.Faits (PaysId) INCLUDE (Statut, Montant);

INSERT INTO dbo.Zones VALUES (1, N'EU'), (2, N'AM');
INSERT INTO dbo.Pays  VALUES (1, N'FR', 1), (2, N'DE', 1), (3, N'IT', 1), (4, N'US', 2);

-- 100,000 highly skewed facts: FR 98,000 · DE 1,000 · IT 900 · US 100
WITH N AS (SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
           FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b)
INSERT INTO dbo.Faits (FaitId, PaysId, Statut, Montant)
SELECT n,
       CASE WHEN n <= 98000 THEN 1 WHEN n <= 99000 THEN 2 WHEN n <= 99900 THEN 3 ELSE 4 END,
       CASE WHEN n % 1000 = 0 THEN 9 ELSE 1 END,          -- Status 9: only 100 rows
       (n % 500) / 10.0
FROM N;
UPDATE STATISTICS dbo.Faits WITH FULLSCAN;

-- a) join to the reference tables
SELECT f.FaitId, f.Montant
FROM   dbo.Faits AS f
JOIN   dbo.Pays  AS p ON p.PaysId = f.PaysId
JOIN   dbo.Zones AS z ON z.ZoneId = p.ZoneId
WHERE  z.Code = N'AM';
GO
-- b) local variable
DECLARE @p int = 4;
SELECT FaitId, Montant FROM dbo.Faits WHERE PaysId = @p;
GO
-- c) variable + OPTION (RECOMPILE)
DECLARE @p int = 4;
SELECT FaitId, Montant FROM dbo.Faits WHERE PaysId = @p OPTION (RECOMPILE);
GO
-- d) literal
SELECT FaitId, Montant FROM dbo.Faits WHERE PaysId = 4;

-- To read the estimates: SET STATISTICS XML ON before the query,
-- or “Include Actual Execution Plan” in SSMS, then compare “Estimated Number of Rows”.

The three forms that know the value (c, d, e) estimate 100 rows; the three that do not know it (a, b, table parameter) estimate 25,000 or 50,000. The explanations rely on the documentation: “the value of a local variable is not known at optimization time”, whereas OPTION (RECOMPILE) “turns parameters and local variables into compile-time constants”.

3. The idea: unfold the reference table into a generated object#

Instead of joining the reference table at each execution, you generate an object whose text contains the values:

-- Generation (at deployment, or whenever the reference table is updated)
DECLARE @liste nvarchar(max) =
    (SELECT STRING_AGG(CAST(p.PaysId AS nvarchar(20)), N',') WITHIN GROUP (ORDER BY p.PaysId)
     FROM   dbo.Pays AS p
     JOIN   dbo.Zones AS z ON z.ZoneId = p.ZoneId
     WHERE  z.Code = N'AM');

DECLARE @sql nvarchar(max) =
    N'CREATE OR ALTER VIEW dbo.v_Faits_AM AS' + NCHAR(10) +
    N'SELECT FaitId, PaysId, Montant FROM dbo.Faits WHERE PaysId IN (' + ISNULL(@liste, N'NULL') + N');';

PRINT @sql;                      -- keep a trace of the generated code
EXEC sys.sp_executesql @sql;     -- compile the object

-- Text produced:
--   CREATE OR ALTER VIEW dbo.v_Faits_AM AS
--   SELECT FaitId, PaysId, Montant FROM dbo.Faits WHERE PaysId IN (4);

-- Equivalence with the join version, in both directions (no rows expected)
SELECT f.FaitId FROM dbo.Faits AS f JOIN dbo.Pays AS p ON p.PaysId = f.PaysId JOIN dbo.Zones AS z ON z.ZoneId = p.ZoneId
WHERE z.Code = N'AM'
EXCEPT SELECT FaitId FROM dbo.v_Faits_AM;

SELECT FaitId FROM dbo.v_Faits_AM
EXCEPT
SELECT f.FaitId FROM dbo.Faits AS f JOIN dbo.Pays AS p ON p.PaysId = f.PaysId JOIN dbo.Zones AS z ON z.ZoneId = p.ZoneId
WHERE z.Code = N'AM';

The generated view is estimated at 100 rows (row e of the table): same accuracy as a literal, with no recompilation on each call. If the zone does not exist, the empty list is replaced by IN (NULL) and the view returns zero rows.

4. The three meanings of “inline”#

Generated unfolding is the only one of the three that does not depend on any engine rule: you decide what gets “inlined”. It also addresses cases the other two do not cover (reference values, filtered indexes).

5. Generating a deterministic function#

The same principle makes it possible to replace a scalar function that queries a reference table with a function that contains that reference table (a CASE):

-- Generating a deterministic scalar function from the reference table
DECLARE @cases nvarchar(max) =
    (SELECT STRING_AGG(CAST(N'WHEN ' + CAST(PaysId AS nvarchar(20)) + N' THEN N' + QUOTENAME(Code, '''') AS nvarchar(max)), NCHAR(10))
            WITHIN GROUP (ORDER BY PaysId)
     FROM dbo.Pays);

DECLARE @sql nvarchar(max) =
    N'CREATE OR ALTER FUNCTION dbo.f_CodePays (@PaysId int) RETURNS nchar(2) WITH SCHEMABINDING AS' + NCHAR(10) +
    N'BEGIN' + NCHAR(10) +
    N'RETURN CASE @PaysId' + NCHAR(10) + @cases + NCHAR(10) + N'ELSE NULL END;' + NCHAR(10) +
    N'END;';

PRINT @sql;
EXEC sys.sp_executesql @sql;

-- Checks: deterministic, and a candidate for inlining (SQL Server 2019)
SELECT OBJECTPROPERTY(OBJECT_ID(N'dbo.f_CodePays'), 'IsDeterministic') AS est_deterministe;
SELECT is_inlineable FROM sys.sql_modules WHERE object_id = OBJECT_ID(N'dbo.f_CodePays');
GO
-- Because it is deterministic: persisted and indexed computed column
ALTER TABLE dbo.Faits ADD CodePays AS dbo.f_CodePays(PaysId) PERSISTED;
GO
CREATE INDEX IX_Faits_CodePays ON dbo.Faits (CodePays);
GO

Text produced:

CREATE OR ALTER FUNCTION dbo.f_CodePays (@PaysId int) RETURNS nchar(2) WITH SCHEMABINDING AS
BEGIN
RETURN CASE @PaysId
WHEN 1 THEN N'FR'
WHEN 2 THEN N'DE'
WHEN 3 THEN N'IT'
WHEN 4 THEN N'US'
ELSE NULL END;
END;

The checks give est_deterministe = 1 and is_inlineable = 1. Because the function is deterministic and schema-bound, it can feed a persisted, indexed computed column (see Determinism): the country code is stored and indexed, with no calculation at read time.

6. Filtered indexes: the literal makes the difference#

For a filtered index to serve a query, the query's predicate must be a subset of the index's predicate (documentation). With a variable, the plan must remain valid whatever the value: the optimizer cannot always prove the inclusion.

CREATE INDEX IX_Faits_Statut9 ON dbo.Faits (PaysId) INCLUDE (Montant) WHERE Statut = 9;   -- filtered index

GO
-- a) literal
SELECT PaysId, Montant FROM dbo.Faits WHERE Statut = 9;
GO
-- b) variable
DECLARE @s tinyint = 9;
SELECT PaysId, Montant FROM dbo.Faits WHERE Statut = @s;
GO
-- c) variable + OPTION (RECOMPILE)
DECLARE @s tinyint = 9;
SELECT PaysId, Montant FROM dbo.Faits WHERE Statut = @s OPTION (RECOMPILE);

7. When is it worth it?#

8. Generating cleanly#

1. A deterministic generator: same reference data, same text (explicit order, no timestamp in the body); the differences between two generations become readable.
2. Idempotent: CREATE OR ALTER (SQL Server 2016 SP1 and later); replayable at will.
3. Safe: QUOTENAME, CAST, empty list handled, size under control.
4. Traced: generated text logged, date and fingerprint of the reference data in an extended property.
5. Triggered at the right time: when the reference data is updated, or during deployment.
6. Tested: equivalence with the join version (EXCEPT in both directions), then estimate and plan.
7. Deterministic on the object side: SCHEMABINDING and no time-dependent function, to open the door to inlining and indexing.

-- Generating cleanly: validations and template
DECLARE @liste nvarchar(max) = (SELECT STRING_AGG(CAST(PaysId AS nvarchar(20)), N',') WITHIN GROUP (ORDER BY PaysId)
                                FROM dbo.Pays AS p JOIN dbo.Zones AS z ON z.ZoneId = p.ZoneId WHERE z.Code = N'XX');   -- unknown zone
-- 1) empty list: IN () is a syntax error -> IN (NULL), which returns no rows
-- 2) numeric values: CAST AS nvarchar; strings: QUOTENAME(value, '''') ; never raw concatenation
-- 3) explicit order (WITHIN GROUP): the generated text is identical from one execution to the next
-- 4) no timestamp in the object body: the generation date goes in an extended property
DECLARE @sql nvarchar(max) =
    N'CREATE OR ALTER VIEW dbo.v_Faits_XX AS' + NCHAR(10) +
    N'SELECT FaitId, PaysId, Montant FROM dbo.Faits WHERE PaysId IN (' + ISNULL(@liste, N'NULL') + N');';
EXEC sys.sp_executesql @sql;
SELECT COUNT(*) AS nb_lignes FROM dbo.v_Faits_XX;      -- 0

9. Risks#

10. Alternatives#

Sources and verification#

• Microsoft Learn — Query processing architecture guide — constant folding, variables and parameters, RECOMPILE, cached plans, sp_executesql
• Microsoft Learn — Create filtered indexes — the query predicate must be a subset of the index predicate
• Microsoft Learn — Scalar UDF Inlining — scalar function inlining (SQL Server 2019)
• Microsoft Learn — Deterministic and nondeterministic functions — determinism and SCHEMABINDING
• Microsoft Learn — STRING_AGG — aggregating the generated text
• Microsoft Learn — Query hints (RECOMPILE) — RECOMPILE hint