Changing a column data type when the table already has rows
Changing a column's type on an empty table is nothing. Doing it on a table with rows in it has three outcomes, and only two of them tell you what happened.
Making a column bigger is free
Widening a column is a metadata change. It finishes instantly whatever the table's size, and nothing is rewritten:
ALTER TABLE dbo.Part ALTER COLUMN Code varchar(100) NOT NULL;
That is worth knowing on its own, because the table designer in Management Studio will sometimes want to rebuild the whole table for this.
Making it smaller fails on the data
If a single row holds something longer than the new size, the statement is refused and the column stays exactly as it was:
ALTER TABLE dbo.Part ALTER COLUMN Code varchar(20) NOT NULL;
Msg 8152, Level 16, State 14
String or binary data would be truncated.
The statement has been terminated.
Nothing is lost — the whole statement is rolled back — but the message does not say which row or which column, so on a wide table you are left hunting. Find them first instead:
SELECT PartId, Code, LEN(Code) AS length
FROM dbo.Part
WHERE LEN(Code) > 20
ORDER BY length DESC;
Changing the type fails on the first value that will not convert
ALTER TABLE dbo.Part ALTER COLUMN Qty int NOT NULL;
Msg 245, Level 16, State 1
Conversion failed when converting the varchar value 'unknown' to data type int.
The statement has been terminated.
This one at least names the value. It names only the first one it met, though, so fixing it and running again can produce the same message with a different value, several times over.
Ask for all of them at once. TRY_CONVERT returns null instead of failing, which turns the problem into a query:
SELECT PartId, Qty
FROM dbo.Part
WHERE TRY_CONVERT(int, Qty) IS NULL
AND Qty IS NOT NULL;
PartId Qty
3 unknown
Now you have the list, and the decision is yours: correct them, empty them, or move them somewhere before the column changes under them.
Watch for values that look convertible and are not. '12.00' will not become an int, though it is perfectly good as a decimal. Neither will a number with a thousands separator, a leading currency symbol, or a trailing space in some collations.
And the one that does not fail at all
This is the one to be careful with, because nothing goes wrong and you find out months later.
Converting nvarchar to varchar does not error on characters the destination cannot hold. It replaces them with a question mark and carries on:
-- three rows: an e-acute, a Chinese character, and a euro sign
SELECT Id, UNICODE(RIGHT(Val, 1)) AS last_char_code FROM dbo.Txt;
Id last_char_code
1 233 -- é
2 21271 -- 北
3 8364 -- €
ALTER TABLE dbo.Txt ALTER COLUMN Val varchar(50);
SELECT Id, UNICODE(RIGHT(Val, 1)) AS last_char_code FROM dbo.Txt;
Id last_char_code
1 233 -- é survived
2 63 -- ? the character is gone
3 8364 -- € survived
No error. No warning. Row 2 now ends in a question mark and the original character is not recoverable from the table.
Which characters survive depends on your collation's code page, not on anything in the statement. The run above was on SQL_Latin1_General_CP1_CI_AS, where the accented letter and the euro sign are present and the Chinese character is not. A different collation gives a different answer, which is why this cannot be judged by looking at the data and deciding it is "mostly English".
Check before you convert, not after:
SELECT Id, Val
FROM dbo.Txt
WHERE Val <> CAST(CAST(Val AS varchar(50)) AS nvarchar(50));
Anything that comes back will lose something. If that is not acceptable, the answer is to leave the column as nvarchar — the saving from the change is rarely worth what it costs here.
What will block the change
Two kinds of dependency behave differently, and the difference is not obvious.
An index on the column does not prevent widening it, but does prevent changing its type:
CREATE INDEX IX_Ix_Code ON dbo.Ix(Code);
ALTER TABLE dbo.Ix ALTER COLUMN Code varchar(60) NOT NULL; -- fine
ALTER TABLE dbo.Ix ALTER COLUMN Code int NOT NULL; -- refused
Msg 5074, Level 16, State 1
The index 'IX_Ix_Code' is dependent on column 'Code'.
Msg 4922, Level 16, State 9
ALTER TABLE ALTER COLUMN Code failed because one or more objects access this column.
A computed column is stricter: it blocks even a widening.
CREATE TABLE dbo.Comp (Id int PRIMARY KEY, First varchar(20), Whole AS (First + '!'));
ALTER TABLE dbo.Comp ALTER COLUMN First varchar(40);
Msg 5074, Level 16, State 1
The column 'Whole' is dependent on column 'First'.
A CHECK constraint on the column, by contrast, allows a widening without complaint.
For the fuller picture of what else answers with that 4922, and how to find it before you start, see ALTER COLUMN failed because one or more objects access this column.
The order to work in
- Widening? Run it. There is nothing to check.
- Narrowing or changing the type? Find the rows that will not fit with
LENorTRY_CONVERT, and deal with them first. - Going from
nvarchartovarchar? Run the round-trip comparison above. This is the only one of the three that will not tell you afterwards. - Refused with a 5074? Drop the index or the computed column, make the change, put it back — and script it before you drop anything.
Seeing it before you run it
In WoodFireERD, changing a column's type in the Columns section changes your copy of the schema and nothing else. Pending changes then shows the migration script, with the statements in the order SQL Server needs them — the index dropped before the column changes and created again afterwards, rather than a single ALTER that stops with a 5074.
What it cannot tell you is whether your rows will convert. Nothing can, short of asking the database — so run the TRY_CONVERT query above against the real table before you run the script.