Altering column with out dropping the index on it

Hi All,

I have a table test with one column (id int).

create table test (id int)

Then I created an index on this table column

create index tst_ndx on test (id)

Now I want to change the table column type to varchar.

alter table test alter column id varchar(10)

But it gave me the error

ALTER TABLE ALTER COLUMN id failed because INDEX tst_ndx accesses this column.

Is there anyway I can change the column datatype with out dropping and recreating this index.

Any help on this will be highly appreciated.

Thanks in advance
srikumar_pAsked:
Who is Participating?
 
Gustavo Perez BuenrostroConnect With a Mentor Commented:
srikumar_p,
You must drop and recreate the index to alter the column. If you specify ALTER COLUMN’s argument of ALTER TABLE statement you have to keep in mind that altered column cannot be used in an index, unless the column is varchar o varbinary data type. See “ALTER TABLE (T-SQL)” topic in BOL.
0
 
srikumar_pAuthor Commented:
That is what I do not want to do.

But it looks like there is no other way.

Thanks
0
All Courses

From novice to tech pro — start learning today.