1

In a SELECT statement using a condition based on a calculated value, is it possible to include that calculated value without computing the calculated value twice - once in the selection and again in the condition?

I am using SQL Server 2000.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
AAsk
  • 1,431
  • 4
  • 17
  • 25

4 Answers4

3

You could put everything into a subquery and select from that

SQL Statement

SELECT *
FROM   (
         SELECT COL1 + COL2 as CalcColumn
         FROM   Table
       ) q
WHERE  100 < CalcColumn

but as to performance, I expect this to be slower than your original query.

Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
  • 3
    It won't be any slower. The plans will be the same. Of course the condition is completely unsargable and the OP would need an indexed computed column to avoid a scan. – Martin Smith Oct 13 '11 at 07:56
2

There should not be any significant performance loss if you use the query as you wrote it. SQL handles it for you, I believe. My question would be, why are you using software from the previous century?

I just tried 

SELECT Debit, Credit, Debit+Credit AS CalcColumn FROM JDT1 WHERE CalcColumn > 100

on SQL 2005 as suggested by a couple of guys and the error is: Msg 207, Level 16, State 1, Line 1 Invalid column name 'CalcColumn'.

Upendra Chaudhari
  • 6,473
  • 5
  • 25
  • 42
Piotr Farbiszewski
  • 71
  • 6
  • Thanks. I keep asking the same question and as yet do not have an answer! – AAsk Oct 13 '11 at 07:34
  • An explanation for why that error occurs is here: http://stackoverflow.com/questions/6545664/using-case-expression-column-in-where-clause/6545685#6545685 – ypercubeᵀᴹ Nov 13 '11 at 17:48
0
SELECT COL1+COL2 as CalcColumn,* 
FROM TABLE WITH (NOLOCK) 
WHERE 100 < CalcColumn

hope that helps.

Nathan
  • 1,520
  • 1
  • 12
  • 21
0

I usually create a view to also have reusable calculation columns.

CREATE VIEW TableView
AS
SELECT COL1+COL2 as CalcColumn,*  FROM TABLE WITH (NOLOCK)  

GO

SELECT * FROM TableView WHERE 100 < CalcColumn
Yves M.
  • 3,330
  • 14
  • 12