How to change a column from null to non-null when the index depends on that column without re-creating the index?

Question

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

That the production database and table contain about ten million records. Therefore, I would prefer to abandon and recreate the index, because it will slow down the database (especially creating the index can take several minutes).

How to change a column without re-creating the index?

Answer 1

The table column must not be modified to force NOT NULL. Instead, you can add a new constraint to the table:

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

This will not affect the index, but the optimizer will use this limitation 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 

Answer 2

I tested this on SQL Server 2012 SP1 on a local server (i.e. not Azure) and it also does not work. Therefore, most likely, you will have to abandon the index and recreate it on Azure.

 ALTER TABLE Children ALTER COLUMN ChildName VARCHAR(50) NOT NULL 

produced by:

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

(Of course, you need to check that the column is not NULL, which would be an error even if there was no index in the column)

Sorry, I do not have enough reputation to comment ...