SELECT COL1+COL2 as CalcColumn,* FROM TABLE WITH (NOLOCK) WHERE 100<COL1+COL2

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 - o开发者_开发知识库nce in the selection and again in the condition?

I am using SQL Server 2000.

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.

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'.

SELECT COL1+COL2 as CalcColumn,* 
FROM TABLE WITH (NOLOCK) 
WHERE 100 < CalcColumn

hope that helps.

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