April 8, 2005 at 10:53 am
Does anyone know of a way to drop a formula on a computed column w/out dropping and recreating the table...this DBA is baffled on this one!!
Thanks!
"Keep Your Stick On the Ice" ..Red Green
April 8, 2005 at 11:42 am
what is the problem with
alter table [tblname] drop column [colname]
alter table [tblname] add [computedcolumn] as newformula
note: If you have schemabound objects you will have to remove the binding first
hth
* Noel
April 8, 2005 at 11:48 am
I think Jeff is trying to do the opposite...
You can try something like this :
if object_id('Test') > 0
DROP TABLE Test
GO
CREATE TABLE [dbo].[Test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Double_id] AS ([id] * 2)
) ON [PRIMARY]
GO
Insert into dbo.Test Default Values
Insert into dbo.Test Default Values
Insert into dbo.Test Default Values
Select id, Double_id from dbo.Test
GO
Alter table test
add double_id2 int null
GO
Update dbo.Test set double_id2 = id * 2
Select * from dbo.Test
GO
ALTER TABLE TEST
drop column double_id
go
exec sp_rename 'Test.double_id2', 'double_id', 'COLUMN'
Select * from dbo.Test
GO
DROP TABLE TEST
April 8, 2005 at 11:56 am
Ahh..
but what advantage are you getting doing that ?
why not just create an index if is query speed what he is looking for?
oh well ... let him say what's the purpose
* Noel
April 8, 2005 at 12:08 pm
I didn't consider that before answering. But even then he didn't make any mention of why he wanted to drop the formula on the column; let's all wait for his reply...
April 11, 2005 at 10:24 am
My reply... 🙂
Why do I want to drop the formula (not the column)? The formula does not allow the column to be updated from the application since the formula is always being applied. At times the formulat isn't useful. The development staff is putting an insert trigger in place so the column is updateable.
I was curious as to if there was a way to drop the formula without dropping the table or the column. I didn't find a way. We did however get our changes into our QA environment.
"Keep Your Stick On the Ice" ..Red Green
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply