[dba-SQLServer] Update query leaves null

A calculated column derives its value when the row is added. If the column is indexed, the index derives its value when the row is added or updated. A calculated column MUST derive its contents from the row of interest. 

A trivial example:

CREATE TABLE dbo.CalcExample_tbl
 (
PK int identity(1,1)
Column1 varchar(20) NOT NULL,
Column2 varchar(20) NOT NULL,
CalcColumn AS Left(Column1,5)+Left(Column2,5)   
)
 
Now you can index CalcColumn as if it physically existed. The further point is that the index holds the already-calculated values and only has to add new rows' values or update old rows' values, so there is no overhead other than what you might expect in a similar situation with a physical column. (That is, there is overhead, but not an extra and inordinate amount.)

Net net, you store the formula once and the column once (in the index, not the table).

Having created this table, add two rows:

1JohnColbyJohnColby2ArthurFullerArthuFulle



Note that the calc column subtracted the sixth letter from my given name and surname, while yours survived intact.

Hth,
Arthur

----- Original Message ----
From: JWColby <jwcolby at colbyconsulting.com>
To: dba-sqlserver at databaseadvisors.com
Sent: Monday, August 14, 2006 12:57:48 PM
Subject: Re: [dba-SQLServer] Update query leaves null

Tell me more.  Just remember that this is a table with (ATM) 60 million
records and will grow, so whatever it does can't be going out to lunch for
the next week calculating something.

John W. Colby
Colby Consulting
www.ColbyConsulting.com