Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there any data loss if a table is being altered to change an existing column from varbinary(8000) to varbinary(MAX)?

share|improve this question
add comment

migrated from serverfault.com Mar 26 '13 at 15:32

This question came from our site for professional system and network administrators.

1 Answer

up vote 4 down vote accepted

No, you won't lose data.

SQL Server will do an in place conversion, as a transaction. This means if anything fails, it will rollback.

Note, this is more than a simple metadata change so it may take some time because data has to be moved around

share|improve this answer
    
Are you sure this isn't just metadata? However varbinary(8000) is stored, varbinary(max) can store it in the exact same format, so I can't see why it'd need to make any physical changes. I'm not sure though - haven't tested. –  Mark S. Rasmussen Mar 26 '13 at 12:12
    
varbinary(max) likely is not stored in the same format - it is stored outside the rows. I believe it is clearly documented. varbinary(8000) stores in the row, varbinary(max) out of the row. –  TomTom Mar 26 '13 at 12:17
1  
Sorry @TomTom but that's not correct. Varbinary(MAX) and varbinary(8000) can both be stored in-row as well as out-of-row. Image (the classic varbinary(MAX)) type on the other hand, is always stored out of row. –  Mark S. Rasmussen Mar 26 '13 at 12:18
    
@TomTom You can read about how both the varbinary(8000) and varbinary(MAX) will store it in row, or overflow using a BLOB INLINE ROOT structure for too large values in What is the size of the LOB pointer for MAX types. Or if you want to look at source, feel free to look at the complex column handling for types 2 & 4 in OrcaMDF's Record source –  Mark S. Rasmussen Mar 26 '13 at 12:23
1  
@TomTom. It is controlled by sp_tableoption msdn.microsoft.com/en-us/library/ms173530(v=sql.105).aspx The "old" setting is deprecated which could have made in more than a metadata change. Otherwise, upto 8000 is "in row" unless set off with the new setting. I haven't looked at this forever BTW hence my vagueness above –  gbn Mar 26 '13 at 12:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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