Subject When do calculated columns calculate?
Author Daniel Miller
Seems a silly question, but:
Given two columns:
A SmallInt
B SmallInt
And it so happens that the data that will be stored will always be a four digit integer for "A" and a single digit integer for "B", so I can make calculated column "C":
(cast(A as char(5)) || cast(B as char(1)))
as a char(6).  Now - I don't need to index on this field because I index on the "raw" A & B (they're a composite primary/foreign key).  However, there are times when I want to compare primary keys to determine if they are the same or different records.  Writing statements like:
where T1.A=T2.A and T1.B=T2.B
can certainly be done...but I like the simplicity of:
where T1.C = T2.C
or more importantly for some of my queries:
where T1.C <> T2.C
Which is a roundabout way of getting to my question but I figured I'd give the background - since this typically results in someone pointing out I'm missing something simple that will eliminate my question altogether!  But assuming this application is still valid...I only need this calculated value (in two tables) for a specific query for limiting purposes.  All other insert, update, and select operations will never reference this calculated value.  If my select and/or insert statements don't reference the calculated column - does the server still need to calculate it for each row?  Or only when explicitly referenced in the select statement?
I'd rather have a slight performance impact for a single query than something application-wide - and I'd prefer not to store a duplicated value.  Though if the calculation happens regardless I'll probably switch to an insert/update trigger to store the value and then use that for primary/foreign keys as needed.