← Back to Blog

SQL Server Schema Migrations: A Practical Guide

SQL Server is the quiet workhorse of enterprise databases. It powers everything from small business apps to Fortune 500 data warehouses. Yet when it comes to schema migration tooling, SQL Server often feels like an afterthought. Most open-source diff tools support PostgreSQL and MySQL, then stop.

This guide covers the SQL Server-specific syntax, pitfalls, and strategies you need to manage schema migrations safely. Whether you're migrating from another dialect or just trying to keep your SQL Server schemas in sync, these patterns will save you hours of trial and error.

How SQL Server ALTER TABLE Differs

SQL Server's ALTER TABLE syntax has quirks that surprise developers coming from PostgreSQL or MySQL. Here are the ones that matter most.

1. No COLUMN Keyword Required for ADD

In PostgreSQL and MySQL, you write ADD COLUMN. In SQL Server, COLUMN is optional:

-- PostgreSQL / MySQL
ALTER TABLE users ADD COLUMN avatar_url NVARCHAR(500);

-- SQL Server (both work, but this is idiomatic)
ALTER TABLE users ADD avatar_url NVARCHAR(500);

SchemaLens generates the SQL Server idiomatic form automatically.

2. IDENTITY Instead of AUTO_INCREMENT

SQL Server uses IDENTITY(seed, increment) for auto-incrementing columns:

CREATE TABLE users (
  id INT IDENTITY(1,1) PRIMARY KEY,
  name NVARCHAR(100) NOT NULL
);

Unlike MySQL's AUTO_INCREMENT or PostgreSQL's SERIAL, SQL Server's IDENTITY is a column property, not a pseudo-type. You can't add IDENTITY to an existing column — you must recreate the table.

3. Bracket-Quoted Identifiers

SQL Server uses square brackets for quoted identifiers:

CREATE TABLE [Order Details] (
  [Order ID] INT NOT NULL,
  [Product Name] NVARCHAR(255)
);

This matters for diff tools because [Order ID] and Order ID are the same column. A naive parser that doesn't strip brackets will flag them as different. SchemaLens normalizes bracket quotes internally.

4. Named Default Constraints

SQL Server requires named constraints to drop defaults. If you add a default without naming it, SQL Server generates a cryptic name like DF__users__role_id__12345678. Good luck dropping that later.

Best practice: always name your defaults explicitly:

ALTER TABLE users
  ADD CONSTRAINT DF_users_role_id DEFAULT 1 FOR role_id;

SchemaLens generates DF_table_column convention names so you don't have to look up system-generated names.

5. CLUSTERED vs NONCLUSTERED

SQL Server distinguishes between clustered and non-clustered primary keys:

CREATE TABLE users (
  id INT IDENTITY(1,1),
  name NVARCHAR(100),
  CONSTRAINT PK_users PRIMARY KEY CLUSTERED (id)
);

Most diff tools ignore this distinction. SchemaLens parses both keywords and preserves them in output.

The SQL Server Migration Workflow

Step 1: Export Your Schema

Use SQL Server Management Studio (SSMS) or sqlcmd:

-- SSMS: Right-click database → Tasks → Generate Scripts → Schema only

-- sqlcmd (command line)
sqlcmd -S server_name -d database_name -E -Q "EXEC sp_helptext 'sp_helpdb'" -- for objects

-- Or use mssql-scripter
mssql-scripter -S server_name -d database_name --schema-and-data false > schema.sql

Clean the export to keep only CREATE TABLE, CREATE INDEX, and ALTER TABLE statements.

Step 2: Diff Your Schemas

Paste your old and new schemas into a diff tool that understands SQL Server syntax. Look for:

• Columns added or removed
• Type changes (especially NVARCHAR length changes)
• Nullability changes
• Default value additions or removals
• Constraint changes (PK, FK, UNIQUE, CHECK)
• Index additions or removals

Step 3: Review Generated Migration

SQL Server migration scripts should be reviewed for these specific risks:

• Table locks: ALTER TABLE on large tables can acquire schema modification locks. Consider using ONLINE = ON for Enterprise Edition.
• Data loss: ALTER COLUMN that narrows a type (e.g., NVARCHAR(500) → NVARCHAR(100)) will truncate data.
• Identity gaps: Adding an IDENTITY column to an existing table requires table recreation.
• Foreign key blocking: Adding a FK to a large table validates all existing rows, which can be slow.

Common SQL Server Migration Patterns

Adding a Column with a Default

ALTER TABLE users
  ADD is_active BIT NOT NULL CONSTRAINT DF_users_is_active DEFAULT 1;

In SQL Server 2012+, this is an online operation for non-nullable columns with defaults. The existing rows are backfilled efficiently without a full table rewrite.

Changing a Column Type

ALTER TABLE users
  ALTER COLUMN name NVARCHAR(255);

Note: ALTER COLUMN in SQL Server does not support changing nullability and type in the same statement if the column contains data. You may need multiple steps.

Dropping a Default Constraint

ALTER TABLE users
  DROP CONSTRAINT DF_users_is_active;

This is why naming your defaults explicitly is critical. Without a name, you have to query system tables to find the generated constraint name.

Adding a Foreign Key

ALTER TABLE orders
  ADD CONSTRAINT FK_orders_users
  FOREIGN KEY (user_id) REFERENCES users(id);

For large tables, consider creating the FK with NOCHECK first, then enabling it:

ALTER TABLE orders WITH NOCHECK ADD CONSTRAINT FK_orders_users
  FOREIGN KEY (user_id) REFERENCES users(id);

ALTER TABLE orders WITH CHECK CHECK CONSTRAINT FK_orders_users;

This avoids blocking the table during the initial validation.

SQL Server vs. PostgreSQL vs. MySQL

Feature	SQL Server	PostgreSQL	MySQL
Auto-increment	IDENTITY(1,1)	SERIAL / GENERATED	AUTO_INCREMENT
Add column	ADD col type	ADD COLUMN col type	ADD COLUMN col type
Change type	ALTER COLUMN	ALTER COLUMN ... TYPE	MODIFY COLUMN
Default constraints	Named, required for drop	Inline, no name needed	Inline, no name needed
Quoted identifiers	[identifier]	"identifier"	`identifier`
Online ALTER	WITH (ONLINE = ON)	Limited support	ALGORITHM = INPLACE

Diffing SQL Server Schemas with SchemaLens

SchemaLens now supports SQL Server as a first-class dialect. Here's how to use it:

1. Select SQL Server from the dialect dropdown.
2. Paste your old schema in Schema A and your new schema in Schema B.
3. Click Compare Schemas.
4. Review the visual diff and generated migration SQL.

SchemaLens handles SQL Server-specific syntax:

• Bracket-quoted identifiers ([table name])
• IDENTITY(1,1) columns
• CLUSTERED / NONCLUSTERED keywords
• Named default constraints (DF_table_column)
• NVARCHAR, DATETIME, BIT types