Why does simple ALTER TABLE command take so long on table with full-text index?

I have a large (~67 million rows) name-value table that has full-text indexing on the DataValue column.

If I try to run the following command:

ALTER TABLE VisitorData ADD NumericValue bit DEFAULT 0 NOT NULL;

It runs for 1 hour 10 minutes and still does not complete on a VisitorData table that contains ~67 million rows.

• Why is this taking so long and not completing?

• What can I do about it?

Here are more particulars about the table:

```
CREATE TABLE [dbo].VisitorData NOT NULL,
[DataValue] nvarchar NOT NULL,
[EncryptedDataValue] varbinary NULL,
[VisitorDataID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_VisitorData_VisitorDataID] PRIMARY KEY CLUSTERED (
[VisitorDataID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UNQ_VisitorData_VisitorId_DataName] UNIQUE NONCLUSTERED (
[VisitorID] ASC,
[DataName] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[VisitorData]
ADD CONSTRAINT [UNQ_VisitorData_VisitorDataID] UNIQUE NONCLUSTERED (

[VisitorDataID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[VisitorData]
WITH CHECK ADD
CONSTRAINT [FK_VisitorData_Visitors] FOREIGN KEY([VisitorID])
REFERENCES [dbo].[Visitors] ([VisitorID])
GO

ALTER TABLE [dbo].[VisitorData]
CHECK CONSTRAINT [FK_VisitorData_Visitors] GO

CREATE FULLTEXT CATALOG DBName_VisitorData_Catalog WITH ACCENT_SENSITIVITY = ON
CREATE FULLTEXT INDEX ON VisitorData ( DataValue Language 1033 )

The schema change it taking so long because you are assigning a default value to the column during the change and enforcing that with a non-nullable column, and it has to populate the column for 60+ million rows, which is an incredibly expensive operation. I'm not sure what your application requirements are but an approach that would make the schema change faster is to add it in as a nullable column with no default value and then perform an update in batches to assign 0 as the value for the column. After your update are done then you can apply another schema change to change the column to non-nullable and assign the default value.

StackExchange Database Administrators Q#12202, answer score: 18