ms sql - adding a computed column to a very large table.

Factual Questions
1

I have to deal with a quite badly designed table on a regular basis. The table stores money amounts as varchar. I also often need to get the value of one column minus another. So I have to type this out every time…

cast(total_bet_amount as money)-cast(refund_amount as money) as handle.

It can get quite tedius if I need to get some info quickly.

So I thought about adding a computer column with the above calculation.

I am guessing the value is never stored? So it shouldn’t increase the size of the table too much?

The table is about 5.7gb in size and contains about 21 million rows.

2

Computed columns are computed each time, unless you declare them as PERSISTED. They’re basically the same thing for columns as views are for tables.

3

Thanks. I was able to add the non-persisted column.

But then I thought I’d do the same for some dates which are also stored as strings.

to test my forumula I first run it as a normal query…

“convert(datetime,_date,11)”

works without any problems.

But if I then attempt to use the above in a computed column it says ‘error validating the formula for column’.

Any ideas?

4

got the answer to the second question using stack overflow. (used an alter table query to add the new computed column)

But thanks for answering the first question. It should be much easier to use the db from now on. no more having to write out a long lines to get figures, and no more excel mangling my dates.