Add a computed clumn to an existing table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Add a computed clumn to an existing table

Author

Topic

chemarket
Starting Member

3 Posts

Posted - 2007-04-05 : 01:14:14

Hi,

Have used Sql for while, but I am still new. I got a small question below.

In table A, I have two columns, A and B. Now I want to add a new column,C, to this table where in each row, the value in column C is the larger value in Cloumn A and B.

What is the right script to realize this change? Hope I can get some help from the gurus here.

Thanks a lot.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-05 : 01:29:15

You dont need C column

Use it when selecting data

Select a,b,case when a>b then a else b end as c from table

Madhivanan

Failing to plan is Planning to fail

chemarket
Starting Member

3 Posts

Posted - 2007-04-05 : 01:37:00

Madhivanan,

Thank you for the hints. You are right. If there are only two columns, A and B, we can use Case-when to solve it.

Actually, in my project, I have five columns. Using Case-When may seem tedious. So I am looking for a better way to do it. Do you have any other idea?

-Chemarket

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-05 : 02:28:01

Do you want to find maximun value in five columns?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-05 : 02:54:05

SELECT ID, MAX(Value) FROM
(
SELECT ID, Col1 AS Value FROM Table1 UNION ALL
SELECT ID, Col2 FROM Table1 UNION ALL
SELECT ID, Col3 FROM Table1 UNION ALL
SELECT ID, Col4 FROM Table1 UNION ALL
SELECT ID, Col5 FROM Table1
) AS x
GROUP BY ID

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-04-05 : 03:18:03

You could use a COMPUTED column in your table. Personally I have grown to hate these because they seem to cause problems during the lifetime of the project. (Problems with INSTEAD OF triggers, cannot do INSERT INTO without a column list [missing the Computed Column(s)], possible application issues with ArithAbort settings, and so on)

You could use a TRIGGER and calculate the value for every row when it is Inserted or Updated, and then store this value in a "real" column. This requires some CPU effort when a record is changed, but no calculation effort when it is Selected. Thus good for a low Insert/Update, and high Select, situation.

You could have a VIEW that calculates the column, then your code is centralised. This is like the Computed Column approach, but does not have the drawbacks I mentioned.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-05 : 03:51:16

Create a VIEW with this code

SELECT ID, MAX(Value) AS MaxValue FROM
(
SELECT ID, Col1 AS Value FROM Table1 UNION ALL
SELECT ID, Col2 FROM Table1 UNION ALL
SELECT ID, Col3 FROM Table1 UNION ALL
SELECT ID, Col4 FROM Table1 UNION ALL
SELECT ID, Col5 FROM Table1
) AS x
GROUP BY ID

Run

ALTER Table1
ADD MaxValue AS (SELECT MaxValue FROM VIEW AS v where v.ID = ID

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-05 : 03:57:07

ALTER Table1
ADD MaxValue AS (SELECT MaxValue FROM VIEW AS v where v.ID = ID)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-04-05 : 04:01:50

I wouldn't add the VIEW as a computed column (for the reasons I gave earlier), I would JOIN it when required.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-05 : 04:20:21

I wouldn't add computed column as it can be easily done with SELECT statement when needed

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-04-05 : 06:02:03

"it can be easily done with SELECT statement when needed"

I would take the view that putting some CASE statement logic in lots of SELECT statements makes it harder to fix a bug later - but centralising in a VIEW or Computed Column avoids that

Kristen

chemarket
Starting Member

3 Posts

Posted - 2007-04-05 : 09:47:52

Thank you guys. The advice above helped me a lot.

-chemark

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-05 : 10:46:49

This problem is also an indicator of a de-normalized table design.

More than likely, this column should be in a related table with a one-to-many relationship to the current table. Then finding the max would be trivial.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources