_ninthbit (_ninthbit) wrote in databases,

T-SQL field aliases

Is there a way to use field aliases in T-SQL  like:

    (field1 + field2) as myNewField  - - declare some calculated field with alias
    , myField*2 as myDoubleField - - use it in expression for next field
where myDoubleField > 1 - - use it in where clause

  • Post a new comment


    Anonymous comments are disabled in this journal

    default userpic

    Your IP address will be recorded 


You can do a calculation in the where though.

"WHERE ((field1 + field2) * 2) > 1"

How many records are you talking about?
about 900,000
By the way how many times does SQL Server engine calculate (field1+field2)*2 when i write thie expression both in select list and where clause? In my opinion - 2. So it's a perfomance issue. I don't want db engine to make the same calculations two times.
With 900,000 records, it would make the calculation 900,000 + the number of records that actually fit the where clause.

Are these records fairly static? You may want to change your database to include a field that is the result of the calculation. You could then periodically update the field with the results of the calculation:

UPDATE tablename SET field3 = (field1 + field2)*2 WHERE field3 IS NULL

Of if field1 or field2 are periodically changed:

UPDATE tablename SET field3 = (field1 + field2)*2

At that point you could even index on the new calculated field for faster queries.