Revisions to Does SQL Server Only Perform Calculations In A SELECT List Once?

refined answer one last time.

Source Link

edited Nov 8, 2019 at 7:32

18.3k
14
54
115

If the first part of your calculation is an actual calculation (Col1 + Col2) and not a function, then the individual calculations are performed for each "calculation" step.

So ifIf we replace <CalculationA> from your statement with a valid calculation using ColA and ColB from a table, and repeat this for each subsequent <CalculationB>,... step, then the actual task of calculating the result will be performed for each step individually.

To reproduce my statement paste the following code snippets into SQL Server Management Studio and run. Make sure you have turned on the option Include Actual Execution Plan.

If you click on the Query Execution Plan in SSMS and right-click to show the actual plan:

.. you will find the following XML (focussingfocusing on the Compute Scalar portion):

Yes, andif the calculations are actual sums of columns as per the example. The last calculation would be the sum of CalculationA + CalculationB.

In this example: yes.

In Randi's answer: no.

If the first part of your calculation is actual calculation (Col1 + Col2) then the individual calculations are performed for each "calculation" step.

So if we replace <CalculationA> from your statement with a valid calculation using ColA and ColB from a table, and repeat this for each subsequent <CalculationB>,... step, then the actual task of calculating the result will be performed for each step individually.

To reproduce paste the following code snippets into SQL Server Management Studio and run. Make sure you have turned on the option Include Actual Execution Plan.

If you click on the Query Execution Plan in SSMS and right-click to show the actual plan you will find the following XML (focussing on the Compute Scalar portion):

Yes, and the last calculation would be the sum of CalculationA + CalculationB

If the first part of your calculation is an actual calculation (Col1 + Col2) and not a function, then the individual calculations are performed for each "calculation" step.

If we replace <CalculationA> from your statement with a valid calculation using ColA and ColB from a table, and repeat this for each subsequent <CalculationB>,... step, then the actual task of calculating the result will be performed for each step individually.

To reproduce my statement paste the following code snippets into SQL Server Management Studio and run. Make sure you have turned on the option Include Actual Execution Plan.

If you click on the Query Execution Plan in SSMS and right-click to show the actual plan:

.. you will find the following XML (focusing on the Compute Scalar portion):

Yes, if the calculations are actual sums of columns as per the example. The last calculation would be the sum of CalculationA + CalculationB.

In this example: yes.

In Randi's answer: no.

added picture and altered code

Source Link

edited Nov 7, 2019 at 14:05

John K. N.

18.3k
14
54
115

To reproduce paste the following code snippets into SQL Server Management Studio and run. Make sure you have turned on the option Include Actual Execution Plan.

It creates a database, a table, populates the table and performs a calculation which produces an execution plan.

CREATE DATABASE Q252661
GO
USE Q252661
GO

CREATE TABLE dbo.Q252661_TableA  (
    ColA  INT,
    ColB  INT,
    ColC  INT,
    ColD  INT)   

GO

INSERT INTO Q252661_TableA
(
    ColA,
    ColB,
    ColC,
    ColD
)
VALUES
(
    1,
    2,
    3,
    4
),(
    2,
    4,
    8,
    16
)

GO

SET SHOWPLAN_XML ON
GO
SELECT  ColA + ColB AS ColA, 
        ColC + ColD AS ColB,
        ColA + ColB + ColC + ColD AS ColC
FROM    Q252661_TableA

GO
SET SHOWPLAN_XML OFF

To reproduce paste the following code snippets into SQL Server Management Studio and run

CREATE DATABASE Q252661
GO
USE Q252661
GO

CREATE TABLE dbo.Q252661_TableA  (
    ColA  INT,
    ColB  INT,
    ColC  INT,
    ColD  INT)   

GO

INSERT INTO Q252661_TableA
(
    ColA,
    ColB,
    ColC,
    ColD
)
VALUES
(
    1,
    2,
    3,
    4
),(
    2,
    4,
    8,
    16
)

GO

SET SHOWPLAN_XML ON
GO
SELECT  ColA + ColB AS ColA, 
        ColC + ColD AS ColB,
        ColA + ColB + ColC + ColD AS ColC
FROM    Q252661_TableA

GO
SET SHOWPLAN_XML OFF

To reproduce paste the following code snippets into SQL Server Management Studio and run. Make sure you have turned on the option Include Actual Execution Plan.

It creates a database, a table, populates the table and performs a calculation which produces an execution plan.

CREATE DATABASE Q252661
GO
USE Q252661
GO

CREATE TABLE dbo.Q252661_TableA  (
    ColA  INT,
    ColB  INT,
    ColC  INT,
    ColD  INT)   

GO

INSERT INTO Q252661_TableA
(
    ColA,
    ColB,
    ColC,
    ColD
)
VALUES
(
    1,
    2,
    3,
    4
),(
    2,
    4,
    8,
    16
)

GO
SELECT  ColA + ColB AS ColA, 
        ColC + ColD AS ColB,
        ColA + ColB + ColC + ColD AS ColC
FROM    Q252661_TableA

GO

Source Link

created Nov 7, 2019 at 13:56

John K. N.

18.3k
14
54
115

If the first part of your calculation is actual calculation (Col1 + Col2) then the individual calculations are performed for each "calculation" step.

SELECT <CalculationA> As ColA,
       <CalculationB> As ColB,
       <CalculationA> + <CalculationB> As ColC
FROM TableA

So if we replace <CalculationA> from your statement with a valid calculation using ColA and ColB from a table, and repeat this for each subsequent <CalculationB>,... step, then the actual task of calculating the result will be performed for each step individually.

To reproduce paste the following code snippets into SQL Server Management Studio and run

CREATE DATABASE Q252661
GO
USE Q252661
GO

CREATE TABLE dbo.Q252661_TableA  (
    ColA  INT,
    ColB  INT,
    ColC  INT,
    ColD  INT)   

GO

INSERT INTO Q252661_TableA
(
    ColA,
    ColB,
    ColC,
    ColD
)
VALUES
(
    1,
    2,
    3,
    4
),(
    2,
    4,
    8,
    16
)

GO

SET SHOWPLAN_XML ON
GO
SELECT  ColA + ColB AS ColA, 
        ColC + ColD AS ColB,
        ColA + ColB + ColC + ColD AS ColC
FROM    Q252661_TableA

GO
SET SHOWPLAN_XML OFF

The query will run and produce a graphical execution plan similar to the following:

^{Graphical Execution Plan of ADDing values}

As in Randi's answer we'll focus on the Compute Scalar operator.

If you click on the Query Execution Plan in SSMS and right-click to show the actual plan you will find the following XML (focussing on the Compute Scalar portion):

          <ComputeScalar>
            <DefinedValues>
              <DefinedValue>
                <ColumnReference Column="Expr1003" />
                <ScalarOperator ScalarString="[Q252661].[dbo].[Q252661_TableA].[ColA]+[Q252661].[dbo].[Q252661_TableA].[ColB]">
                  <Arithmetic Operation="ADD">
                    <ScalarOperator>
                      <Identifier>
                        <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColA" />
                      </Identifier>
                    </ScalarOperator>
                    <ScalarOperator>
                      <Identifier>
                        <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColB" />
                      </Identifier>
                    </ScalarOperator>
                  </Arithmetic>
                </ScalarOperator>
              </DefinedValue>
              <DefinedValue>
                <ColumnReference Column="Expr1004" />
                <ScalarOperator ScalarString="[Q252661].[dbo].[Q252661_TableA].[ColC]+[Q252661].[dbo].[Q252661_TableA].[ColD]">
                  <Arithmetic Operation="ADD">
                    <ScalarOperator>
                      <Identifier>
                        <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColC" />
                      </Identifier>
                    </ScalarOperator>
                    <ScalarOperator>
                      <Identifier>
                        <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColD" />
                      </Identifier>
                    </ScalarOperator>
                  </Arithmetic>
                </ScalarOperator>
              </DefinedValue>
              <DefinedValue>
                <ColumnReference Column="Expr1005" />
                <ScalarOperator ScalarString="[Q252661].[dbo].[Q252661_TableA].[ColA]+[Q252661].[dbo].[Q252661_TableA].[ColB]+[Q252661].[dbo].[Q252661_TableA].[ColC]+[Q252661].[dbo].[Q252661_TableA].[ColD]">
                  <Arithmetic Operation="ADD">
                    <ScalarOperator>
                      <Arithmetic Operation="ADD">
                        <ScalarOperator>
                          <Arithmetic Operation="ADD">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColA" />
                              </Identifier>
                            </ScalarOperator>
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColB" />
                              </Identifier>
                            </ScalarOperator>
                          </Arithmetic>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColC" />
                          </Identifier>
                        </ScalarOperator>
                      </Arithmetic>
                    </ScalarOperator>
                    <ScalarOperator>
                      <Identifier>
                        <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColD" />
                      </Identifier>
                    </ScalarOperator>
                  </Arithmetic>
                </ScalarOperator>
              </DefinedValue>
            </DefinedValues>
            <RelOp AvgRowSize="23" EstimateCPU="8.07E-05" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2" LogicalOp="Table Scan" NodeId="1" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032842" TableCardinality="2">
              <OutputList>
                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColA" />
                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColB" />
                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColC" />
                <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColD" />
              </OutputList>
              <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColA" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColB" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColC" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" Column="ColD" />
                  </DefinedValue>
                </DefinedValues>
                <Object Database="[Q252661]" Schema="[dbo]" Table="[Q252661_TableA]" IndexKind="Heap" Storage="RowStore" />
              </TableScan>
            </RelOp>
          </ComputeScalar>

So each individual calculation is performed again and again in the case of the values being retrieved from an actual table. The following XML fragment is from the above summary:

                <ScalarOperator ScalarString="[Q252661].[dbo].[Q252661_TableA].[ColA]+[Q252661].[dbo].[Q252661_TableA].[ColB]">
                  <Arithmetic Operation="ADD">

There are five <Arithmetic Operation="ADD"> steps in the execution plan.

Answering your Question

Would CalculationA and CalculationB, each be calculated twice?

Yes, and the last calculation would be the sum of CalculationA + CalculationB

Or would the optimizer be clever enough to calculate them once and use the result twice?

It depends on what you are calculating.

My assumption is that it would perform the calculation twice.

You are right for certain calculations.

In which case, depending upon the calculations involved, might it be better to use a derived table, or nested view?

Correct.

Once you have finished you can drop your database again:

USE [master]
GO
DROP DATABASE Q252661

Stack Exchange Network

Return to Answer

Answering your Question