Why a dropped index is still there, and does it prevent a table from being altered?

Posted on

Question :

I have a column (named RequestDate) which I need to change from NULL to NOT NULL, but there’s a clustered index on that column, so I first need to drop that index.

Originally, the index was created using:

CREATE CLUSTERED INDEX IX_RequestDate   
    ON [MyDB].[dbo].[MyTable] (RequestDate); 

To drop it, I executed:

Drop Index IX_RequestDate On [MyDB].[dbo].[MyTable]

And then tried to change the column nullity using:

ALTER TABLE [MyDB].[dbo].[MyTable] ALTER COLUMN [RequestDate] DATETIME NOT NULL

But got an error:

The object ‘MyTable’ is dependent on column ‘RequestDate’.

I then listed MyTable‘s indexes using this query, and the index is still there, though now it doesn’t have a name and its type is HEAP (originally CLUSTERED):

TableName   IndexName   IndexType   ColumnOrdinal   ColumnName     ColumnType
MyTable        NULL       HEAP         0            RequestDate     datetime

I have 2 questions:

  1. Why the dropped index is still there?
  2. Did I got the dependency error above because of this still-existing index?
    If so, how can I remove it?
    If not, is there something else I should check? I’d like to avoid dropping the table.

Note that there are no constraints on this table.

I’m using SQL Server 2014, OS Windows Server 2012 R2, and the following is the output of SCRIPT TABLE AS -> CREATE TO (I changed some of the column names):

CREATE TABLE [dbo].[MyTable](
    [RequestDate] [datetime] NULL,
    [UserName] [nvarchar](50) NULL,
    [HostName] [nvarchar](20) NULL,
    [RequestContent] [ntext] NULL,
    [ResponseContent] [ntext] NULL,
    [RequestStatus] [int] NULL,
    [ErrorMessage] [ntext] NULL,
    [Duration] [float] NULL,
    [ServiceName] [nvarchar](100) NULL,
    [Direction] [int] NULL,
    [RequestId] [uniqueidentifier] NOT NULL,
    [IsRetry] [bit] NULL,
    [CallerId] [nvarchar](100) NULL
)

Answer :

  1. Why the dropped index is still there?

In fact that index is no longer there, the column is the one still there and what was once a Clustered Index is now a Heap.

  1. Did I got the dependency error above because of this still-existing index?

No, because the index no longer exists.

If not, is there something else I should check? I’d like to avoid
dropping the table.

You can View the Dependencies of a Table as suggested by Erik Darling.


As you mentioned in your comments, you found a VIEW using this method and if you check the CREATE VIEW doc, you’ll see the option SCHEMABINDING could cause the error you mentioned:

SCHEMABINDING
Binds the view to the schema of the underlying table or
tables. When SCHEMABINDING is specified, the base table or tables
cannot be modified in a way that would affect the view definition. The
view definition itself must first be modified or dropped to remove
dependencies on the table that is to be modified. […]

Leave a Reply

Your email address will not be published.