SQL Server Reference a Calculated Column

sql-server tsql calculated-columns
mhinton picture mhinton · Jan 5, 2009 · Viewed 34.2k times · Source

I have a select statement with calculated columns and I would like to use the value of one calculated column in another. Is this possible? Here is a contrived example to show what I am trying to do.

SELECT [calcval1] = CASE Statement, [calcval2] = [calcval1] * .25

Answer

Michael Haren picture Michael Haren · Jan 5, 2009

No.

All the results of a single row from a select are atomic. That is, you can view them all as if they occur in parallel and cannot depend on each other.

If you're referring to computed columns, then you need to update the formula's input for the result to change during a select.

Think of computed columns as macros or mini-views which inject a little calculation whenever you call them.

For example, these columns will be identical, always:

-- assume that 'Calc' is a computed column equal to Salaray*.25
SELECT Calc, Salary*.25 Calc2 FROM YourTable

Also keep in mind that the persisted option doesn't change any of this. It keeps the value around which is nice for indexing, but the atomicity doesn't change.

Related questions

Use a calculated column in a where clause

I'm trying to use a calculated column in a where clause. I've trying everything from CROSS APPLY, to sub-query select but it does not give me the anything near what I need. My query so far: SELECT p.Code, c.…

Read More
Create Computed Column using data from another table

I have a SQL Server 2008 R2 database. This database has two tables called Pictures and PictureUse. Picture table has the following columns: Id (int) PictureName (nvarchar(max)) CreateDate (datetime ) PictureUse table has the following columns : Id (int) Pictureid (int) CreateDate (…

Read More
Identity key on varchar column - T-SQL

I want to know if I can create an Identity (auto increment on a Varchar column. and how can I make it a primary key and create foreign key references on other table. This is the code i have - …

Read More