How to Change Column Type in SQL Server

Why would I change a column type in SQL Server?

Business logic evolves. You may need more precision for money, store longer text, or migrate an INT flag to a BIT. ALTER TABLE … ALTER COLUMN lets you do this in-place without recreating the table.

What is the exact syntax?

Use ALTER TABLE table_name ALTER COLUMN column_name new_data_type [NULL|NOT NULL]. Optional WITH (ONLINE = ON) is available in Enterprise edition for online changes.

How do I change INT to DECIMAL for amounts?

Increase precision to avoid overflow:

ALTER TABLE Orders
ALTER COLUMN total_amount DECIMAL(10,2) NOT NULL;

How do I convert VARCHAR dates to DATETIME?

First update bad strings, then alter:

UPDATE Orders
SET order_date = TRY_CONVERT(DATETIME, order_date)
WHERE ISDATE(order_date)=0; -- fix invalid rows

ALTER TABLE Orders
ALTER COLUMN order_date DATETIME NOT NULL;

What if the column is referenced by indexes or constraints?

SQL Server automatically rebuilds indexes. Drop or alter constraints only when the new type conflicts, such as length reductions violating CHECK constraints.

Can I change a column used in FOREIGN KEY?

Data types of both sides must match exactly. Alter the child and parent columns in a single transaction:

BEGIN TRAN;
ALTER TABLE OrderItems ALTER COLUMN order_id BIGINT NOT NULL;
ALTER TABLE Orders ALTER COLUMN id BIGINT NOT NULL;
COMMIT;

Best practices for safe type changes

1️⃣ Backup the database.
2️⃣ Verify convertible data with TRY_CONVERT.
3️⃣ Use BEGIN TRAN and ROLLBACK on error.
4️⃣ Perform in maintenance windows.
5️⃣ Script index rebuilds for large tables.

Common mistakes to avoid

Implicit truncation—reducing VARCHAR length silently cuts data when ANSI_WARNINGS OFF. Always run a SELECT LEN() check first.

Ignoring NULLability—changing to NOT NULL fails if rows contain NULLs. UPDATE or supply a DEFAULT before the ALTER.

Quick reference recap

ALTER TABLE t ALTER COLUMN c NEWTYPE; Works on columns lacking incompatible data, auto-rebuilds indexes, and preserves permissions. Test in staging before production.