44. Schema Evolution & Maintenance -- Growing the Database

Level: Advanced SQL Server for real projects

ℹ️ What You'll Learn

• Safe schema changes in production
• Table and column alterations
• Adding/removing constraints
• Index reorganization
• Backward compatibility
• Zero-downtime deployment
• Migration tracking
• Rollback strategies
• Database maintenance tasks

Databases grow. Schema changes happen. This article teaches production-safe evolution patterns.

Scenario 1: Adding a Column Safely

Add column without downtime:

-- Step 1: Add column with default (non-nullable only if default provided)
ALTER TABLE Student
ADD StudentCode NVARCHAR(20) NULL;  -- Start nullable

-- Step 2: Populate with generated values
UPDATE Student
SET StudentCode = CONCAT('STU-', YEAR(GETDATE()), '-', Id)
WHERE StudentCode IS NULL;

-- Step 3: Add constraint (if required)
-- Make NOT NULL only after all rows populated
ALTER TABLE Student
ALTER COLUMN StudentCode NVARCHAR(20) NOT NULL;

-- Step 4: Create index if needed
CREATE INDEX IX_Student_StudentCode ON Student(StudentCode);

-- Step 5: Update procedure/view to use new column
ALTER VIEW vw_StudentListing AS
SELECT 
    Id,
    Name,
    StudentCode,  -- NEW
    ClassName,
    Status
FROM Student;

-- Step 6: Remove OLD procedures that don't use StudentCode (if exists)
-- DROP PROCEDURE sp_GetStudentOldFormat;  -- Only if truly obsolete

-- Verification
SELECT TOP 5 Id, Name, StudentCode FROM Student;

Safe: Add -> Nullable -> Populate -> Constrain -> Index.

Scenario 2: Removing a Column Safely

Deprecate before deleting:

-- Step 1: Mark as deprecated in AuditLog
INSERT INTO AuditLog (SchoolId, Action, EntityName, NewValues, ChangedBy, ChangedAt)
VALUES (NULL, 'ColumnDeprecation', 'Student', 'Column LegacyId marked for removal', 'SYSTEM', GETDATE());

-- Step 2: Update views/procedures to NOT use column
-- Find references
SELECT OBJECT_NAME(OBJECT_ID) AS ObjectName
FROM sys.sql_modules
WHERE DEFINITION LIKE '%LegacyId%';

-- Step 3: Update all references
ALTER PROCEDURE sp_GetStudent
    @StudentId INT
AS
BEGIN
    SELECT 
        Id,
        Name,
        ClassName,
        -- LegacyId removed
        Status
    FROM Student
    WHERE Id = @StudentId;
END;

-- Step 4: Wait 1-2 releases for code to deploy
-- Keep column for backward compatibility

-- Step 5: After safe period, drop column
ALTER TABLE Student
DROP COLUMN LegacyId;

-- Step 6: Update schema documentation
-- "LegacyId removed in version 2.0. Use Id instead."

Deprecate: Mark -> Update code -> Wait -> Remove.

Scenario 3: Adding Constraint Without Blocking

Add FK constraint safely:

-- Issue: Adding FK to 10M row table locks table
-- Solution: Add with NOT TRUSTED first

-- Step 1: Add constraint but don't check existing data
ALTER TABLE ExamResult
WITH NOCHECK ADD CONSTRAINT FK_ExamResult_Student
FOREIGN KEY (StudentId) REFERENCES Student(Id);

-- Step 2: Verify data integrity first
SELECT *
FROM ExamResult er
WHERE NOT EXISTS (SELECT 1 FROM Student s WHERE s.Id = er.StudentId);

-- Step 3: Mark constraint as TRUSTED
ALTER TABLE ExamResult
WITH CHECK CHECK CONSTRAINT FK_ExamResult_Student;

-- Verification
SELECT OBJECT_NAME(parent_object_id) AS TableName,
       name AS ConstraintName,
       type_desc
FROM sys.foreign_keys
WHERE OBJECT_NAME(parent_object_id) = 'ExamResult';

FK Add: NOCHECK -> Verify -> TRUSTED. Prevents full table lock.

Scenario 4: Renaming Column With Backward Compat

Support old column name temporarily:

-- Step 1: Add new column (if not already exists)
ALTER TABLE Student
ADD FullName NVARCHAR(100) NULL;

-- Step 2: Populate from old column
UPDATE Student
SET FullName = Name
WHERE FullName IS NULL;

-- Step 3: Create synonym/view for backward compatibility
CREATE VIEW vw_StudentLegacy AS
SELECT 
    Id,
    Name,  -- Keep old name visible
    FullName,
    ClassName,
    Status
FROM Student;

-- Step 4: Update procedures to use new column name
ALTER PROCEDURE sp_UpdateStudent
    @StudentId INT,
    @FullName NVARCHAR(100)
AS
BEGIN
    UPDATE Student
    SET FullName = @FullName
    WHERE Id = @StudentId;
END;

-- Step 5: After deprecation period, drop old column
-- ALTER TABLE Student DROP COLUMN Name;

-- Backward compatibility maintained via view
SELECT * FROM vw_StudentLegacy;  -- Old code still works

Rename: Add new -> Populate -> Create view -> Update code -> After period: drop.

Scenario 5: Index Maintenance & Reorganization

Keep indexes healthy:

-- Procedure to check fragmentation
CREATE PROCEDURE sp_CheckIndexFragmentation
    @SchoolId INT = NULL
AS
BEGIN
    SELECT 
        OBJECT_NAME(ips.object_id) AS TableName,
        i.name AS IndexName,
        ips.avg_fragmentation_in_percent AS FragmentationPercent,
        ips.page_count AS PageCount,
        CASE 
            WHEN ips.avg_fragmentation_in_percent < 10 THEN 'OK'
            WHEN ips.avg_fragmentation_in_percent < 30 THEN 'REORGANIZE'
            ELSE 'REBUILD'
        END AS Action
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
    INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
    WHERE ips.avg_fragmentation_in_percent > 0
    AND ips.page_count > 1000  -- Only large indexes
    ORDER BY ips.avg_fragmentation_in_percent DESC;
END;

-- Run fragmentation check
EXEC sp_CheckIndexFragmentation;

-- Procedure to rebuild/reorganize indexes
CREATE PROCEDURE sp_MaintainIndexes
    @TableName NVARCHAR(100) = NULL,
    @RebuildThreshold DECIMAL(5,2) = 30,
    @ReorganizeThreshold DECIMAL(5,2) = 10
AS
BEGIN
    DECLARE @Sql NVARCHAR(MAX);
    
    -- Rebuild indexes > 30% fragmentation
    DECLARE idx_cursor CURSOR FOR
        SELECT 
            OBJECT_NAME(ips.object_id) AS TableName,
            i.name AS IndexName,
            ips.avg_fragmentation_in_percent
        FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
        INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
        WHERE ips.avg_fragmentation_in_percent > @RebuildThreshold
        AND ips.page_count > 1000
        AND (@TableName IS NULL OR OBJECT_NAME(ips.object_id) = @TableName);
    
    OPEN idx_cursor;
    
    DECLARE @Table NVARCHAR(100), @Index NVARCHAR(100), @Frag DECIMAL(5,2);
    
    FETCH NEXT FROM idx_cursor INTO @Table, @Index, @Frag;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Sql = CONCAT('ALTER INDEX ', @Index, ' ON ', @Table, ' REBUILD;');
        EXEC sp_executesql @Sql;
        
        PRINT CONCAT('Rebuilt: ', @Table, '.', @Index, ' (Frag: ', @Frag, '%)');
        
        FETCH NEXT FROM idx_cursor INTO @Table, @Index, @Frag;
    END;
    
    CLOSE idx_cursor;
    DEALLOCATE idx_cursor;
    
    -- Reorganize indexes 10-30% fragmentation
    DECLARE idx_cursor2 CURSOR FOR
        SELECT 
            OBJECT_NAME(ips.object_id),
            i.name,
            ips.avg_fragmentation_in_percent
        FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
        INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
        WHERE ips.avg_fragmentation_in_percent BETWEEN @ReorganizeThreshold AND @RebuildThreshold
        AND ips.page_count > 1000;
    
    OPEN idx_cursor2;
    
    FETCH NEXT FROM idx_cursor2 INTO @Table, @Index, @Frag;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Sql = CONCAT('ALTER INDEX ', @Index, ' ON ', @Table, ' REORGANIZE;');
        EXEC sp_executesql @Sql;
        
        PRINT CONCAT('Reorganized: ', @Table, '.', @Index, ' (Frag: ', @Frag, '%)');
        
        FETCH NEXT FROM idx_cursor2 INTO @Table, @Index, @Frag;
    END;
    
    CLOSE idx_cursor2;
    DEALLOCATE idx_cursor2;
END;

-- Schedule maintenance
EXEC sp_MaintainIndexes @RebuildThreshold = 30, @ReorganizeThreshold = 10;

Fragmentation: <10% OK, 10-30% reorganize, >30% rebuild.

Scenario 6: Schema Versioning & Rollback Strategy

Track schema versions:

-- Schema version tracking table
CREATE TABLE SchemaVersion (
    VersionId INT PRIMARY KEY IDENTITY(1,1),
    VersionNumber NVARCHAR(20),
    DeployedAt DATETIME DEFAULT GETDATE(),
    DeployedBy NVARCHAR(100),
    ChangeDescription NVARCHAR(MAX),
    RollbackScript NVARCHAR(MAX),
    IsDeployed BIT DEFAULT 0
);

-- Insert version record
INSERT INTO SchemaVersion (VersionNumber, DeployedBy, ChangeDescription, RollbackScript, IsDeployed)
VALUES (
    '2.1.0',
    'DBA_User',
    'Added StudentCode column, added index IX_Student_StudentCode',
    'ALTER TABLE Student DROP COLUMN StudentCode; DROP INDEX IX_Student_StudentCode ON Student;',
    1
);

-- Procedure to apply pending migrations
CREATE PROCEDURE sp_ApplyMigrations
    @DryRun BIT = 0
AS
BEGIN
    DECLARE @VersionId INT;
    DECLARE @Sql NVARCHAR(MAX);
    
    DECLARE version_cursor CURSOR FOR
        SELECT VersionId
        FROM SchemaVersion
        WHERE IsDeployed = 0
        ORDER BY VersionId;
    
    OPEN version_cursor;
    
    FETCH NEXT FROM version_cursor INTO @VersionId;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Get migration script
        SELECT @Sql = ChangeDescription FROM SchemaVersion WHERE VersionId = @VersionId;
        
        IF @DryRun = 0
        BEGIN
            -- Execute migration
            EXEC sp_executesql @Sql;
            
            -- Mark as deployed
            UPDATE SchemaVersion SET IsDeployed = 1 WHERE VersionId = @VersionId;
        END
        ELSE
        BEGIN
            -- Dry run: show what would happen
            PRINT CONCAT('Would execute: ', @Sql);
        END;
        
        FETCH NEXT FROM version_cursor INTO @VersionId;
    END;
    
    CLOSE version_cursor;
    DEALLOCATE version_cursor;
END;

-- Procedure to rollback
CREATE PROCEDURE sp_RollbackVersion
    @VersionId INT
AS
BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY
        DECLARE @RollbackSql NVARCHAR(MAX);
        
        SELECT @RollbackSql = RollbackScript FROM SchemaVersion WHERE VersionId = @VersionId;
        
        IF @RollbackSql IS NOT NULL
        BEGIN
            EXEC sp_executesql @RollbackSql;
            
            UPDATE SchemaVersion SET IsDeployed = 0 WHERE VersionId = @VersionId;
            
            COMMIT TRANSACTION;
        END
        ELSE
        BEGIN
            THROW 50001, 'No rollback script available', 1;
        END;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

-- View schema history
SELECT VersionNumber, DeployedAt, DeployedBy, ChangeDescription, IsDeployed
FROM SchemaVersion
ORDER BY VersionId DESC;

Versioning: Track changes -> Deploy -> Rollback available.

---

🎯 Q1: How do I add a column safely to a large table?

1. Add column (nullable)
2. Populate with values
3. Make NOT NULL if needed
4. Add index
5. Update views/procedures

No downtime: Column is nullable until fully populated.

🎯 Q2: How do I remove a column without breaking code?

Deprecate: Mark -> Create view with old name -> Update code -> Wait -> Drop

Keep backward compat view active for 1-2 releases.

🎯 Q3: How do I add FK constraint without locking?

Add with NOCHECK -> Verify existing data -> Mark TRUSTED

Avoids full table scan lock.

🎯 Q4: How do I rename a column safely?

Add new column -> Populate -> Create view for old name -> Update code -> Wait -> Drop old

View masks column rename from clients.

🎯 Q5: How do I detect and fix index fragmentation?

SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats
< 10%: OK
10-30%: REORGANIZE
> 30%: REBUILD

Schedule maintenance weekly.

🎯 Q6: How do I track and rollback schema changes?

SchemaVersion table: version -> deployAt -> rollback script.

EXEC sp_ApplyMigrations (apply pending) EXEC sp_RollbackVersion (revert)

Keep scripts for all changes.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Schema evolution and database maintenance in SQL Server. Try these prompts:

• "Show how to add a column safely without downtime"
• "How do I deprecate and remove a column?"
• "How do I add a foreign key constraint without blocking?"
• "How do I check and fix index fragmentation?"
• "Show schema versioning with rollback capability"
• "Quiz me: 5 questions about migrations, constraints, maintenance"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

45. Graduation & Capstone -- Full SMS Application ->