Cold and Un-Calculating
I had to mess around a bit with a calculated column today, and I thought I'd share what I found, 'cause it really ticked me off (which, honestly, doesn't take much).
So, you've got a spiffy little calculated column in your database that's been there for a few years, and the table's full of data. Let's say you defined the column similar to this:
create table foo {
ID int not null IDENTITY(1,1),
Radius decimal (7,2),
Circumference as (2 * pi() * Radius)
}
(If you don't know a little basic geometry, we'll never get along).
OK, so let's say at some point, you live in a state that allegedly wants to make the value of pi 3, so you now want to get rid of the calculated column and just do the math in your application. OK, so you want to get rid of the caulculation bit and turn it into a real column.
Hmm…I spent about a half hour looking through books online and google and didn't get a result. Turns out, you can't remove the calculatedness from a calculated column. There's only really one good reason for this. What's the data type of the Circumference column? I'm waiting. You don't know? Well, the reason you don't know is that you can't declare the type for the value either. Let's say your calculated column was defined as 42 * Radius, and your Radius was an Int. Your column will end up with an int data type, but what happens if you did 42.0 * Radius? Hmm…a float, a numeric, a decimal…who knows.
At any rate, the only way to change it is to drop the column and put it back with the data type you want. Fortunately, that doesn't suck as much as I thought it would (100,000 row table in about 3 seconds).