Msg 5074, Level 16, State 1, Line 2 The index ' ' is dependent on column ' '

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

In this instance, you’ll have to drop the index(es) referencing the column before you can alter the column. If you try to disable the index via ALTER INDEX indexname on table DISABLE, you’ll get the same error…no can do. Have to get rid of it first. Here’s an example:

DROP TABLE idxTest

GO

CREATE TABLE idxTest

    (ID INT

    ,isDeleted BIT NULL

    )

GO

INSERT idxTest VALUES (1,0)

GO

CREATE INDEX idx_test ON idxTest (ID, isdeleted)

GO

--ALTER TABLE idxTest

-- ALTER COLUMN isDeleted BIT NOT null

GO

DROP INDEX idxTest.idx_test

GO

ALTER TABLE idxTest

    ALTER COLUMN isDeleted BIT NOT null

GO

CREATE INDEX idx_test ON idxTest (ID, isdeleted)

GO

Notice that I’ve commented out the ALTER TABLE, but you can uncomment and give it a try.

Thanks for reading,
Lee

----------------------