Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query to alter the Computed Column Expand / Collapse
Author
Message
Posted Tuesday, April 14, 2009 7:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 06, 2010 9:35 AM
Points: 2, Visits: 5
Tim,

I read your article “The Mighty, Mighty Computed Column” and it helped me a lot to understand the concept of computed column. I have a query. I have a computed column in my table and what I want is to alter this column and its computed criteria. Please check the example for more info:

Create table Dummy
(
A Decimal (12, 0),
B Decimal (12, 0),
C Decimal (12, 0),
D As (A + B - C) –Computed column
)
I want to change D as
D (Case When B = 0 Then 0 Else (A + B - C) End)

How can I do that? Please help

Post #696531
Posted Tuesday, April 14, 2009 10:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 10,910, Visits: 12,545
From BOL (bolding mine):

ALTER COLUMN

Specifies that the named column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or lower. For more information, see sp_dbcmptlevel (Transact-SQL).

The modified column cannot be any one of the following:

* A column with a timestamp data type.
* The ROWGUIDCOL for the table.
* A computed column or used in a computed column.
* Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, the new size is equal to or larger than the old size, and the index is not the result of a PRIMARY KEY constraint.
* Used in statistics generated by the CREATE STATISTICS statement unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or greater than the old size, or if the column is changed from not null to null. First, remove the statistics using the DROP STATISTICS statement. Statistics that are automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.
* Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.
* Used in a CHECK or UNIQUE constraint. However, changing the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.
* Associated with a default definition. However, the length, precision, or scale of a column can be changed if the data type is not changed.
The data type of text, ntext and image columns can be changed only in the following ways:
o text to varchar(max), nvarchar(max), or xml
o ntext to varchar(max), nvarchar(max), or xml
o image to varbinary(max)


So you would need to Drop the computed column and re-add it.

 ALTER TABLE [Dummy] DROP COLUMN D 
GO

ALTER TABLE DUMMY ADD D AS (CASE WHEN B = 0 THEN 0 ELSE A+B-C End)





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #696767
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse