How do I change a column from null to not null when an index depends on that column without recreating the index?

Question

I have a column Column which is declared to be NULL DEFAULT(GETUTCDATE()) and there's a non-clustered index that includes this column. I want to change this column to be NOT NULL DEFAULT(GETUTCDATE()) and when I run ALTER TABLE ALTER COLUMN statement the SQL Azure service says it cannot change the column because there's an index depending on this column.

That's a production database and the table holds about ten million records. So I'd rather not drop and recreate the index because that would slow down the database (especially creating the index can take minutes).

How can I change the column without recreating the index?

Answer 1

Table column does not have to be altered to enforce NOT NULL. Instead a new constraint can be added to the table:

ALTER TABLE [Table] WITH CHECK
   ADD CONSTRAINT [TableColumnNotNull] CHECK ([Column] Is NOT NULL);

That would not affect the index but the optimizer would use this constraint to improve performance:

CREATE TABLE Test (ID bigint PRIMARY KEY, [Column] DATE NULL DEFAULT(GETUTCDATE()));
GO --< Create test table

CREATE NONCLUSTERED INDEX TestColumnIdx ON Test ([Column]);
GO --< Create the index

ALTER TABLE Test ALTER COLUMN [Column] DATE NOT NULL;
GO --< That won't work: the index 'TestColumnIdx' is dependent on column 'Column'

Select * From Test Where [Column] Is NULL;
GO --< Check the plan: it has "Index Seek (NonClustered)"

ALTER TABLE Test WITH CHECK ADD CONSTRAINT TestColumnNotNull CHECK ([Column] Is NOT NULL);
GO --< Add a "stand-alone" NOT NULL constraint

Select * From Test Where [Column] Is NULL;
GO --< Check the plan: it has "Constant Scan" now

DROP TABLE Test;
GO --< Clean-up