Altering a column which has an index defined on it

Hello,

I'm trying the following test (which works like a charm on Oracle)

create table x1(c1 numeric(10), c2 numeric(5,1))
create index x1_c2_idx on x1(c2)
alter table x1 alter column c2 numeric(9,1)

I get the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'x1_c2_idx' is dependent on column 'c2'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN c2 failed because one or more objects access this column.

Is there a way to alter the column WITHOUT dropping the index ?



Regards,

Tal Olier
otal@.mercury.co.ilNo, you must drop the index first.|||Originally posted by Paul Young
No, you must drop the index first.

Thanks.

Alter UserDefined Datatype

How can I alter a userdefined datatype.

eg: A user defined datatype of Dt_Quantity numeric(16,0)

Want to change it to numeric(16,3).

Is it possible?

If it requires a system catelog updation please help.

This requirment is came after the implementation of the DataBase.

Please help me fast|||which version of sql server are you using ?|||Using the SQL Server 2005|||

you have to drop it and recreate...

From BOL :

Note: User-defined types cannot be modified after they are created, because changes could invalidate data in the tables or indexes. To modify a type, you must either drop the type and then re-create it, or issue an ALTER ASSEMBLY statement by using the WITH UNCHECKED DATA clause. For more information, see ALTER ASSEMBLY (Transact-SQL).

Madhu

alter column to not null that has null values

I have to change numeric columns in 2005 table to not null and default value 0.

What I usually do is an update on the columns setting value to 0 where is null. I know you can use 'with values' when adding a column with default 0 and not null to an existing table.

Can something like this be done for altering a column or do I need to do the update?

Thanks

You need to use UPDATE first and then ALTER. ALTER TABLE table ALTER COLUMN only supports changing the type definition, collation and nullability.

alter column

hi,
I have a production table with 2.6 million records, I need
to alter one column from numeric to varchar, does it will
affect the users? blocking the table? ThanksHi,
Obviously Yes....Dont do it when users are accessing the table.
Thanks
Hari
MCDBA
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:088d01c3ced5$34b021e0$a601280a@.phx.gbl...
> hi,
> I have a production table with 2.6 million records, I need
> to alter one column from numeric to varchar, does it will
> affect the users? blocking the table? Thanks|||Changing the column data type (ALTER TABLE ... ALTER COLUMN) will acquire a
schema modification lock for the duration of the operation. In this case,
all data pages be updated so you probably want to do this during a
maintenance window.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:088d01c3ced5$34b021e0$a601280a@.phx.gbl...
> hi,
> I have a production table with 2.6 million records, I need
> to alter one column from numeric to varchar, does it will
> affect the users? blocking the table? Thanks