I have a column (named
RequestDate) which I need to change from
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
TableName IndexName IndexType ColumnOrdinal ColumnName ColumnType MyTable NULL HEAP 0 RequestDate datetime
I have 2 questions:
- Why the dropped index is still there?
- 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 )