9

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?

Community
  • 1
  • 1
sharptooth
  • 159,303
  • 82
  • 478
  • 911
  • Pertinent: http://stackoverflow.com/questions/1258380/preserve-sql-indexes-while-altering-column-datatype – nshah Aug 27 '14 at 06:43
  • I've tested this on SQL Server 2012 SP1 on a local server (i.e. not Azure), and it also doesn't work. So most likely it will be necessary to drop the index and re-create it on Azure. ALTER TABLE Children ALTER COLUMN ChildName VARCHAR(50) NOT NULL produced: 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, it is necessary to check that there are no NULLs in the column anyways, which would be an error ev – Reversed Engineer Aug 27 '14 at 06:51

1 Answers1

8

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
Y.B.
  • 3,316
  • 10
  • 22
  • 2
    Upvoted because it provides a solution with the constraints set by the question, but for long term supportability it seems like ideally the column would be altered at some point when the time it takes won't impact users. – Ryan Jun 13 '19 at 20:30