Changing the size of a column referenced by a schema-bound view in SQL Server

sql

data-migration

schema-binding

database-design

byAlex Kataev·Oct 13, 2024

Adjust the size of a schema-bound view column by following these steps:

Euthanize the view: DROP VIEW dbo.ViewName;
Mutate the column: ALTER TABLE dbo.TableName ALTER COLUMN ColumnName NewType(NewSize);
Resurrect the view using schema binding: CREATE VIEW dbo.ViewName WITH SCHEMABINDING AS SELECT ...;

-- Old Yeller the view
DROP VIEW dbo.ViewName;

-- Bijou the column width
ALTER TABLE dbo.TableName ALTER COLUMN ColumnName VARCHAR(150); -- Because BIGGER is always BETTER, right?

-- Phoenix the view with schema binding
CREATE VIEW dbo.ViewName WITH SCHEMABINDING AS SELECT ColumnName FROM dbo.TableName;

Ensure to boostrap and test-drive in a deveopment environment first.

Deep Dive: Constraints & Triggers

If the column is referenced by constraints or triggers, ensure to tweak or disable them before adjusting the column size. Constraint & triggers are like stage moms and divas, they demand attention and could fumble your performance.

Triggers & Potential Data Problems

Triggers attached to a column could need adaptation to accommodate new datatype or size. It's like changing the actor but keeping the script, 😲 awkward!

A New Hope: Data Migration

When it gets complicated, create a duplicate for your column with the new attributes, do a stunt double switch, and casually kill off the original:

-- Say hello to the stunt double
ALTER TABLE dbo.TableName ADD NewColumnName VARCHAR(150);

-- Leave no trace, copy data
UPDATE dbo.TableName SET NewColumnName = ColumnName;

-- Dispose of the original
ALTER TABLE dbo.TableName DROP COLUMN ColumnName;

-- Disguise the stunt double
EXEC sp_RENAME 'dbo.TableName.NewColumnName' , 'ColumnName', 'COLUMN';

-- The show must go on - recreate the view
CREATE VIEW dbo.ViewName WITH SCHEMABINDING AS SELECT ColumnName FROM dbo.TableName;

Erring on the side of caution

Keeping Dependencies in Check

Disabling SCHEMABINDING, making changes, and enabling it again is sort of like asking your noisy neighbor to leave while you fix your apartment and then grudgingly inviting him back.

Direct Column Alterations: The Brave Route

Modifying tables directly is more straightforward. It's the Hulk route - smashing through walls, no need to tip-toe around dependencies.

The Detective Work: Dependency Assessment

Like pre and post surgery checks, assess the impact on applications and data before and after making changes.

The Cautious Warrior: Advanced Measures

Replication: The Doppelgänger Dilemma

If you use your database for replication, column modifications can create copies. It's important to understand the replication layout to avoid disrupting harmony.

Constraints and Undercover Values

When tweaking default constraints, use the disguise-remove-tweak-reapply strategy:

-- Unmask the default constraint
ALTER TABLE dbo.TableName DROP CONSTRAINT DF_ConstraintName;

-- Swing the hammer and modify the column width
ALTER TABLE dbo.TableName ALTER COLUMN ColumnName VARCHAR(150);

-- Reapply the mask
ALTER TABLE dbo.TableName ADD CONSTRAINT DF_ConstraintName DEFAULT (DefaultValue) FOR ColumnName;