Just to summarise the experimental findings in the comments this seems to be an edge case that occurs when you have two computed columns in the same table, one persisted
and one not persisted and they both have the same definition.
In the plan for the query
SELECT id5p
FROM dbo.persist_test;
The table scan on persist_test
emits only the id
column. The next compute scalar along multiplies that by 5 and outputs a column called id5
despite the fact that this column is not even referenced in the query. The final compute scalar along takes the value of id5
and outputs that as a column called id5p
.
Using the trace flags explained in Query Optimizer Deep Dive – Part 2 (disclaimer: these trace flags are undocumented/unsupported) and looking at the query
SELECT id5,
id5p,
( id * 5 )
FROM dbo.persist_test
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606);
Gives the output
Tree Before Project Normalization
LogOp_Project
LogOp_Get TBL: dbo.persist_test dbo.persist_test TableID=1717581157 TableReferenceID=0 IsRow: COL: IsBaseRow1002
AncOp_PrjList
AncOp_PrjEl QCOL: [tempdb].[dbo].[persist_test].id5
ScaOp_Arithmetic x_aopMult
ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id
ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=5)
AncOp_PrjEl QCOL: [tempdb].[dbo].[persist_test].id5p
ScaOp_Arithmetic x_aopMult
ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id
ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=5)
AncOp_PrjEl COL: Expr1004
ScaOp_Arithmetic x_aopMult
ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id
ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=5)
Tree After Project Normalization
LogOp_Project
LogOp_Get TBL: dbo.persist_test dbo.persist_test TableID=1717581157 TableReferenceID=0 IsRow: COL: IsBaseRow1002
AncOp_PrjList
AncOp_PrjEl QCOL: [tempdb].[dbo].[persist_test].id5
ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id5
AncOp_PrjEl QCOL: [tempdb].[dbo].[persist_test].id5p
ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id5
AncOp_PrjEl COL: Expr1004
ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id5
So it appears that all the computed column definitions get expanded out then during the Project Normalization stage all the identical expressions get matched back to computed columns and it just happens to match id5
in this case. i.e. it does not give any preference to the persisted
column.
If the table is re-created with the following definition
CREATE TABLE dbo.persist_test (
id INT NOT NULL
, id5p AS (5 * id) PERSISTED
, id5 AS (5 * id)
);
Then a request for either id5
or id5p
will be satisfied from reading the persisted version of the data rather than doing the calculation at runtime so the matching appears to happen (at least in this case) in column order.
[tempdb].[dbo].[persist_test].id
and it calculates the value despite being persisted.