Can i drop a column of a table in SQL server which is having a non clustered index defined on it

Question

I have a table with huge record count . Some of the columns have a non clustered indexes defined on them .

we are required to alter/drop few columns which are having such indices defined.

I have directly alter/dropped the columns , but did not get any error like :

Alter statement failed . It succeeded. So , here is my question is :

Is it required to drop non clustered indexes on the columns which are going to be dropped/altered ?

why it did give any errors similar to case of constraints/keys defined on them ?

Updated :

What incase of alteting a column for its size ? Is it supposed to throw any error ?

Answer 1

Your question is worded kind of strangely to me and I'm having trouble following exactly what you're asking... but this is easy enough to test yourself.

You cannot drop a column that has an index, see:

CREATE TABLE tempThing (id int IDENTITY(1,1) PRIMARY key, someValue varchar(50))
GO
CREATE INDEX idxTemp ON dbo.tempThing (someValue)
GO

Then:

ALTER TABLE dbo.tempThing DROP COLUMN someValue

Gives error:

Msg 5074, Level 16, State 1, Line 1 The index 'idxTemp' is dependent on column 'someValue'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE DROP COLUMN someValue failed because one or more objects access this column.

Answer 2

I just tried this:

create table #t1(i1 int, i2 int)
create index a1 on #t1(i1)


alter table #t1 drop column i1

failed with the message alter table drop column i1 failed because one or more objects access this `column.

Are you sure you had indexes that referred to those dropped column and they were dropped successfully?