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