How to Change a Column Type in SQL Server
Last updated August 26, 2026 · By the SaturnSQL team
Use ALTER TABLE ... ALTER COLUMN with the full new definition, including NULL or NOT NULL. If you leave nullability out, SQL Server resets the column to nullable under default settings, a common surprise.
ALTER TABLE orders ALTER COLUMN amount DECIMAL(14,2) NOT NULL;Check for values that will not convert
TRY_CONVERT returns NULL instead of erroring, so you can count offending rows before the ALTER.
SELECT COUNT(*) AS bad_rows
FROM events
WHERE TRY_CONVERT(INT, external_ref) IS NULL
AND external_ref IS NOT NULL;ALTER COLUMN fails while indexes, defaults, or check constraints reference the column; drop those first and recreate them after.
What runs instantly and what rewrites every page
Widening a fixed-width type in place is a metadata-only change and returns immediately. Anything that changes the on-disk representation rewrites the table under a schema modification lock, and adding NOT NULL to an existing column always scans it. The one that surprises people is VARCHAR(8000) to VARCHAR(MAX): it is a size change on paper but moves the data off-row, so it rewrites.
ALTER TABLE dbo.orders ALTER COLUMN status VARCHAR(50) NOT NULL; -- metadata only
ALTER TABLE dbo.orders ALTER COLUMN notes VARCHAR(MAX) NULL; -- rewritesCommon errors
Msg 5074, Level 16, State 1, Line 1 The object 'df_orders_status' is dependent on column 'status'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER COLUMN status failed because one or more objects access this column.
A default constraint, index, computed column, statistic or schema-bound view depends on the column. SQL Server names the first blocking object in the 5074 message and then reports the failure in 4922. There can be several, so fixing one and retrying may just surface the next.
List every dependency first, drop them, change the type, then recreate them. Default constraints get autogenerated names unless you named them, so look the name up rather than guessing.
SELECT d.name AS default_constraint
FROM sys.default_constraints d
JOIN sys.columns c ON c.object_id = d.parent_object_id AND c.column_id = d.parent_column_id
WHERE d.parent_object_id = OBJECT_ID('dbo.orders') AND c.name = 'status';
ALTER TABLE dbo.orders DROP CONSTRAINT df_orders_status;
ALTER TABLE dbo.orders ALTER COLUMN status INT NOT NULL;
ALTER TABLE dbo.orders ADD CONSTRAINT df_orders_status DEFAULT 0 FOR status;Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric. The statement has been terminated.
At least one existing value will not convert. The message never tells you which row, which is what makes it annoying.
Find the offending rows with TRY_CAST before altering anything: it returns NULL where a conversion would fail instead of aborting.
SELECT id, total
FROM dbo.orders
WHERE total IS NOT NULL AND TRY_CAST(total AS DECIMAL(10,2)) IS NULL;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free