Alter TEXT, IMAGE or NTEXT to varchar(max), nvarchar(max), varbinary(max)

Question

in order to alter the column from TEXT, IMAGE or NTEXT to varchar(max), nvarchar(max), varbinary(max), execute the command

alter table tablename alter column colname varchar(max).

• the time required will be base on the size of the column ?
• if the original row is less than 8000b , after execute the alter command , will these rows put back to in-row-data allocation ?
• any other consideration before changing these columns types ?

Answer 1

Hi sakuraime,

the time required will be base on the size of the column ?

I think the time required may be related to the amount of data, and the table will be locked for a period of time when the metadata is modified.

if the original row is less than 8000b , after execute the alter command , will these rows put back to in-row-data allocation ?

SQL Server stores data in pages of 8KB and with a max. of 8060 bytes per row. If you are using varchar, nvarchar, varbinary, sql_variant datatypes then the column length will be limited to 8000 bytes each and the max. length of the row should fall below 8060 bytes. This limit is NOT checked against the varchar(max) data types.
If the row sizes exceeds 8060 bytes then there are certain performance implications as SQL Server enforces 8KB page size. SQL Server moves the max. width column in these scenarios to a separate page in the ROW_OVERFLOW_DATA allocation unit, and leaves a 24 byte pointer in the current page. Please refer to this thread which might help.

any other consideration before changing these columns types

You need to be prepared to handle potentially large transactions, so make sure you have enough log space to avoid running out of log space due to large transactions.

Best Regards,
Amelia

Answer 2

the time required will be base on the size of the column ?

No. My experience is that these are metadata-only operations.

if the original row is less than 8000b , after execute the alter command , will these rows put back to in-row-data allocation ?

It follows from this observation that this is not the case. But if you rebuild the table that may happen. (I don't know on the top of my head, so I leave it to you to test.)

any other consideration before changing these columns types ?

Just do it. Well, you actually are using WRITETEXT or UPDATETEXT that code will break.