Expand/Contract Schema Changes, Online Index Operations, Backfills, and Application Compatibility

Learning outcomes

Engine migration can be flawless and the application can still fail because schema deployment was treated as an instantaneous event. ServiceHub has old and new application versions running during rollout, so database changes must remain compatible across a transition window. The safest default is expand → backfill → switch → contract: add compatible structures, populate them in controlled batches, move application reads/writes, verify, then remove obsolete structures only after no supported client depends on them.

1. Expand first: preserve the old application contract

USE ServiceHubUpgradeLab;GOIF COL_LENGTH(N'lab24.WorkOrder',N'dispatch_zone') IS NULL    ALTER TABLE lab24.WorkOrder ADD dispatch_zone varchar(12) NULL;GOSELECT TOP (5) work_order_id,region_code,dispatch_zoneFROM lab24.WorkOrderORDER BY work_order_id;

The old application continues to work because existing columns remain unchanged. The new application can be deployed with logic that understands both the old source (region_code) and the new attribute (dispatch_zone) during the transition. Dual-write should be used only when necessary and for a bounded period because two sources of truth create reconciliation work.

2. Backfill in bounded, restartable batches

A single update of millions of rows can create a large transaction, log growth, blocking, replica/replication lag and an all-or-nothing recovery problem. A backfill should expose progress and be safe to rerun. Batch size is workload-dependent; the example uses a small teaching batch, not a universal tuning value.

USE ServiceHubUpgradeLab;GODECLARE @rows int = 1;WHILE @rows > 0BEGIN    UPDATE TOP (500) lab24.WorkOrder    SET dispatch_zone = CASE region_code        WHEN 'N01' THEN 'NORTH'        WHEN 'W02' THEN 'WEST'        WHEN 'E03' THEN 'EAST'        ELSE 'UNKNOWN' END    WHERE dispatch_zone IS NULL;    SET @rows = @@ROWCOUNT;    SELECT SYSUTCDATETIME() AS batch_time, @rows AS rows_updated,           SUM(CASE WHEN dispatch_zone IS NULL THEN 1 ELSE 0 END) AS rows_remaining    FROM lab24.WorkOrder;END;GO

In production, add pacing/timeout/telemetry appropriate to the system, observe transaction log and HA/data-movement lag, and stop when service health degrades. “Chunked” is not automatically safe if every chunk arrives faster than the downstream system can absorb.

3. Validate before enforcing the new invariant

SELECT    COUNT_BIG(*) AS total_rows,    SUM(CASE WHEN dispatch_zone IS NULL THEN 1 ELSE 0 END) AS null_rows,    SUM(CASE WHEN dispatch_zone NOT IN ('NORTH','WEST','EAST') THEN 1 ELSE 0 END) AS invalid_rowsFROM lab24.WorkOrder;GOIF OBJECT_ID(N'lab24.DF_WorkOrder_dispatch_zone',N'D') IS NULL    ALTER TABLE lab24.WorkOrder ADD CONSTRAINT DF_WorkOrder_dispatch_zone DEFAULT ('UNKNOWN') FOR dispatch_zone;GOIF OBJECT_ID(N'lab24.CK_WorkOrder_dispatch_zone',N'C') IS NULL    ALTER TABLE lab24.WorkOrder WITH CHECK    ADD CONSTRAINT CK_WorkOrder_dispatch_zone    CHECK (dispatch_zone IN ('NORTH','WEST','EAST','UNKNOWN'));GOALTER TABLE lab24.WorkOrder CHECK CONSTRAINT CK_WorkOrder_dispatch_zone;

A default controls future inserts that omit the column; it does not prove historic rows are valid. WITH CHECK validates existing rows while adding the check constraint. Converting the column to NOT NULL is another schema-modification operation and should occur only after all supported writers are compatible and a maintenance/blocking plan exists.

4. Online/resumable index operations are scoped tools, not magic

Online operations allow concurrent user access for supported index operations, but they still require short schema locks at phases such as start/end, consume extra resources, have object restrictions, and are not available in every edition. Resumable operations require online execution and keep additional state/storage while paused. In boxed SQL Server 2025, online create/rebuild and resumable online index operations are Enterprise features.

-- OPTIONAL: requires SQL Server 2025 Enterprise or free Enterprise Developer for lab use.USE ServiceHubUpgradeLab;GOIF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id=OBJECT_ID(N'lab24.WorkOrder') AND name=N'IX_lab24_DispatchOpened')BEGIN    CREATE INDEX IX_lab24_DispatchOpened      ON lab24.WorkOrder(dispatch_zone,opened_at)      INCLUDE(status,priority)      WITH      (          ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 2 MINUTES, ABORT_AFTER_WAIT = SELF)),          RESUMABLE = ON,          MAX_DURATION = 10 MINUTES      );END;GOSELECT * FROM sys.index_resumable_operationsWHERE object_id=OBJECT_ID(N'lab24.WorkOrder');

WAIT_AT_LOW_PRIORITY controls what happens while waiting for the schema-modification lock; it does not make the operation lock-free. A paused resumable operation also has ongoing storage/DML consequences. Standard/Express should not copy this syntax and hope it works—use edition-supported operations during a measured maintenance window.

5. Deliberately wrong: contract before clients have moved

Dropping or renaming an old column while an old application version still runs creates an immediate compatibility failure. A safer contract phase starts only after deployment telemetry proves that supported clients no longer depend on the old shape and a rollback window has closed.

SELECT    COUNT_BIG(*) AS rows_total,    SUM(CASE WHEN dispatch_zone IS NULL THEN 1 ELSE 0 END) AS rows_not_migratedFROM lab24.WorkOrder;SELECT name, is_disabled, is_not_trustedFROM sys.check_constraintsWHERE parent_object_id=OBJECT_ID(N'lab24.WorkOrder');-- Contract step intentionally NOT executed in this lab:-- ALTER TABLE lab24.WorkOrder DROP COLUMN region_code;-- Only do this after old application versions are retired and rollback criteria expire.

Chapter 24 treats schema changes as deployment choreography. Database DDL, backfill, application rollout and cleanup should be separately observable and independently reversible as far as the data model allows.

Authoritative references

• Perform index operations online
• Guidelines for online index operations
• Editions and supported features of SQL Server 2025
• CREATE INDEX
• ALTER TABLE