0

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?

5
  • 1
    From what datatype to what datatype? Please post the change script. Also, you might want to confirm what your users are experiencing first. Also did the Collation of the column change? There was another issue recently related to changing system Collation and users experiencing unexpected filtering. Jul 19, 2017 at 22:02
  • Data type is unchanged as is the collation of the column. I suspect I need to confirm the user behaviour. The script was: alter table <table> alter column <column> nvarchar(biggernumber) [not] null the original column was 255 I upped it to 510 everything else remained unchanged. Jul 19, 2017 at 22:49
  • 2
    Yes, always confirm with the user(s) first so that you know exactly what you are debugging and what you are asking others about. But you can still post the ALTER command to the question along with what the original column specification was (including NULL vs NOT NULL). Jul 19, 2017 at 22:51
  • Updated post above... Jul 19, 2017 at 22:52
  • @SolomonRutzky is right. Please provide the actual script, not just a description. Jul 19, 2017 at 23:21

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.