How do I change a column from null to not null when an index depends on that column without recreating the index?
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 tableCREATE NONCLUSTERED INDEX TestColumnIdx ON Test ([Column]);GO --< Create the indexALTER 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 constraintSelect * From Test Where [Column] Is NULL;GO --< Check the plan: it has "Constant Scan" nowDROP TABLE Test;GO --< Clean-up