Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The three SELECT statements in this code

USE [tempdb];
GO

SET NOCOUNT ON;

CREATE TABLE dbo.persist_test (
      id            INT NOT NULL
    , id5           AS (id * 5)
    , id5p          AS (id * 5) PERSISTED
);

INSERT INTO dbo.persist_test (id)
VALUES (1), (2), (3);

SELECT id
FROM dbo.persist_test;

SELECT id5
FROM dbo.persist_test;

SELECT id5p
FROM dbo.persist_test;

DROP TABLE dbo.persist_test;

generate this plan:

execution plan

Why does the final SELECT, which is selecting a persisted value, generate a Compute Scalar operator?

share|improve this question
3  
See @SqlKiwi's answer here: Why does the Execution Plan include a user-defined function call for a computed column that is persisted?. In your query the output column list from the table is only [tempdb].[dbo].[persist_test].id and it calculates the value despite being persisted. –  Martin Smith Jun 1 '12 at 21:04
1  
My WTF question is why does it show 2 "Compute Scalar" ? –  ypercube Jun 1 '12 at 22:27
1  
@ypercube - The first compute scalar calculates id*5 and outputs the result of that calculation as column id5 then the second compute scalar takes that calculated column and outputs the value of that as column id5p. Not sure why the intermediate step is there. If id5 is removed from the table the plan just has one compute scalar. –  Martin Smith Jun 1 '12 at 22:37
2  
So I presume that at some stage in optimisation / algebrization it recognizes that id5p is the same formula as id5 and associates these two columns together. Which ever one it encounters first becomes the primary source for the other column. –  Martin Smith Jun 1 '12 at 23:18
2  
@MartinSmith: to add in your last comment, defining id5 AS (7+id) and id5p AS (id+7) PERSISTED results in 2 vs 1 "Compute Scalar" which shows the limit of the algebrization. –  ypercube Jun 1 '12 at 23:28

1 Answer 1

up vote 11 down vote accepted

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.

share|improve this answer
1  
This quirk of computed column matching also means the following table can never support an index seek: CREATE TABLE #Test ( id integer NOT NULL, id5 AS id * 5, id5n AS id * 5 UNIQUE NONCLUSTERED, id5c AS id * 5 UNIQUE CLUSTERED, ); –  Paul White Jun 3 '12 at 11:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.