Sql-server – SQL2008 – Alter Table Alter Column to increase length results in data loss

sql server

My understanding was that you could alter the length of a column without losing any data as long as you modified the table via TSQL.

I extended the length of a column to 510 characters from 255, data type and everything else was left alone.

Since pushing this change to prod some users have said their data has gone missing. I'm yet to confirm whether the users are accurate in their assessment but it's a little concerning that I modified the column and they're reporting data loss.

I thought I'd gone about it in a safe and recommended way? Could I have lost data by increasing the column length via TSQL?

Best Answer

Typically you would get an error message and the operation would be prevented, e.g.

create table #name (name nvarchar(20))
insert #name (name) values ('Robert Carnegie')
select * from #name
ALTER TABLE #name ALTER COLUMN name nvarchar(10)  -- error and halts here
SELECT * FROM #name

(1 row(s) affected)
name
--------------------
Robert Carnegie

(1 row(s) affected)

Msg 8152, Level 16, State 13, Line 10
String or binary data would be truncated.
The statement has been terminated.

However, as explained in https://blog.sqlauthority.com/2015/02/14/sql-server-msg-8152-level-16-state-14-string-or-binary-data-would-be-truncated/ that error message isn't produced after the command SET ANSI_WARNINGS OFF.

You could have applied the command to the wrong table or column.

You could have mistaken the column sizes e.g. originally nvarchar(2555) or now nvarchar(51). Or maybe there had already been an undocumented change in the production database to make the column width 600 characters - or maybe "max" - so, again, you'd be reducing it. But you should get the error message and the change wouldn't happen.

You could have blocked someone else's transaction storing new data to the table - although I think making a varchar column longer may not even require altering the data pages. The actual storage wouldn't change. But - I don't know - SQL Server may check all of the data anyway for any ALTER TABLE ALTER COLUMN command.

Related Question