Why doesn't SQL Server match computed column to an index when the expression is trivial?

Question

I have a table with two computed columns (indexed, but not persisted). The first computed column matches an existing column exactly. The second computed column is an expression that adds 0 to the value.

I've read Properly Persisted Computed Columns by Paul White where he writes about a similar example, but maybe not exactly the same.

Here's my repro:

DROP TABLE IF EXISTS dbo.TEST;

CREATE TABLE dbo.TEST (
    Id INT IDENTITY PRIMARY KEY CLUSTERED,
    ColumnA INT NOT NULL,
    ColumnAAlias as ColumnA,
    ColumnB INT NOT NULL,
    ColumnBAlias as ColumnB+0
)

CREATE NONCLUSTERED INDEX IX_TEST_ColumnAAlias
ON dbo.TEST (ColumnAAlias);
GO

CREATE NONCLUSTERED INDEX IX_TEST_ColumnBAlias
ON dbo.TEST (ColumnBAlias);
GO

INSERT dbo.TEST (ColumnA, ColumnB)
SELECT value, value
FROM generate_series(1, 1000000);
GO

SELECT TOP 1 NULL
FROM dbo.TEST
WHERE ColumnAAlias < 100
ORDER BY ColumnAAlias DESC
/* OPTION (QUERYTRACEON 176) -- no difference */ 

SELECT TOP 1 NULL
FROM dbo.TEST
WHERE ColumnBAlias < 100
ORDER BY ColumnBAlias DESC
/* OPTION (QUERYTRACEON 176) -- no difference */

The query plans are different here.

I would love to be able to make use of IX_TEST_ColumnAAlias somehow without creating a new index on ColumnA because it's a very large table where maintenance windows are scarce and new indexes are awkward.

Erik Darling · Accepted Answer · 2025-12-11 00:24:43Z

waiting for godunk

I'm sure you'll get a Better Answer©️ eventually, but I currently have no other reasons to live, so here goes.

It's been said here in many other answers about computed columns that the optimizer expands them early on to explore plan alternatives, and may match back to them later. Or it may not. It's sort of like indexed view expansion.

trace flags

Using the original version of your query with trace flags added:

SELECT TOP (1) 
    NULL
FROM dbo.TEST 
WHERE ColumnAAlias < 100
ORDER BY 
    ColumnAAlias DESC
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606, RECOMPILE);

ColumnA is in the project here...

*** Tree Before Project Normalization ***
        LogOp_Project
            LogOp_Top Columns : QCOL: [tempdb].[dbo].[TEST].ColumnA Descending NoTies
                LogOp_Select
                    LogOp_Get TBL: dbo.TEST dbo.TEST TableID=1413580074 TableReferenceID=0 IsRow: COL: IsBaseRow1000 
                    ScaOp_Comp x_cmpLt
                        ScaOp_Identifier QCOL: [tempdb].[dbo].[TEST].ColumnA
                        ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=100)
                ScaOp_Const TI(bigint,Null,ML=8) XVAR(bigint,Not Owned,Value=1)
                ScaOp_Const TI(bigint,ML=8) XVAR(bigint,Not Owned,Value=0)
            AncOp_PrjList 
                AncOp_PrjEl COL: Expr1003 
                    ScaOp_Const TI(int,Null,ML=4) XVAR(int,Not Owned,Value=NULL)

And ColumnA is in the project here...

*** Tree After Project Normalization ***
        LogOp_Project
            LogOp_Top Columns : QCOL: [tempdb].[dbo].[TEST].ColumnA Descending NoTies
                LogOp_Select
                    LogOp_Get TBL: dbo.TEST dbo.TEST TableID=1413580074 TableReferenceID=0 IsRow: COL: IsBaseRow1000 
                    ScaOp_Comp x_cmpLt
                        ScaOp_Identifier QCOL: [tempdb].[dbo].[TEST].ColumnA
                        ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=100)
                ScaOp_Const TI(bigint,Null,ML=8) XVAR(bigint,Not Owned,Value=1)
                ScaOp_Const TI(bigint,ML=8) XVAR(bigint,Not Owned,Value=0)
            AncOp_PrjList 
                AncOp_PrjEl COL: Expr1003 
                    ScaOp_Const TI(int,Null,ML=4) XVAR(int,Not Owned,Value=NULL)

I don't see any difference when specifically hinting the nonclustered index, but the query plan is an absolute disaster.

SELECT TOP (1) 
    NULL
FROM dbo.TEST 
    WITH (INDEX = IX_TEST_ColumnAAlias)
WHERE ColumnAAlias < 100
ORDER BY 
    ColumnAAlias DESC
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606, RECOMPILE);

The disaster:

Why does this happen? I'm not sure. It's certainly a strange arrangement to just have a computed column that is a direct copy of another column.

I don't want to call it a bug. It may just be something that wasn't regarded as a serious enough scenario to put effort into, but ignoring an indexed column to do a full scan and predicate lookup is not a great showing.

stupid workarounds

No answer would be complete without them, so here goes.

Since your column is not persisted (which is why TF 176 is ineffective), and it seems unlikely you're willing to go that route given the restrictions in your question, we can't try the usual tricks with check constraints and whatnot.

Filtered Index

For the specific query you're using, you could change the index definition to this:

CREATE INDEX 
    IX_TEST_ColumnAAliasFiltered
ON dbo.TEST 
    (ColumnAAlias)
INCLUDE
    (ColumnA)
WHERE
    (ColumnA < 100);

You're likely already aware that having ColumnA as an include or secondary key column results in a scan anyway, so this at least reduces the number of rows to be scanned. It's quite dull, though.

Indexed View

This also matches to the original query:

CREATE OR ALTER VIEW  
    dbo.ColumnAAlias
WITH 
    SCHEMABINDING
AS 
SELECT
    t.Id,
    t.ColumnAAlias
FROM dbo.TEST AS t
-- WHERE t.ColumnAAlias < 100;
GO 

CREATE UNIQUE CLUSTERED INDEX
    BecauseYouHaveTo
ON dbo.ColumnAAlias
    (Id)
WITH
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

Yay.

Unquoting the where clause in the filtered index of course is a better plan, but I doubt your real life scenario is this simple so I won't belabor the obvious as a victory.

Stack Exchange Network

Why doesn't SQL Server match computed column to an index when the expression is trivial?

1 Answer 1

waiting for godunk

trace flags

stupid workarounds

Your Answer

Hot Network Questions

Why doesn't SQL Server match computed column to an index when the expression is trivial?

1 Answer 1

waiting for godunk

trace flags

stupid workarounds

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions