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

Question

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

sakuraime 2,341

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 ?

AmeliaGu-MSFT 14,006 Reputation points Microsoft External Staff

2021-09-07T08:49:17.557+00:00

Hi sakuraime,

How are things going? Do the answers help you?
Please feel free to let us know if you have any other question. If you find any post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia

2 answers

Your answer

AmeliaGu-MSFT 14,006 Reputation points Microsoft External Staff

2021-09-07T08:49:17.557+00:00

Hi sakuraime,

How are things going? Do the answers help you?
Please feel free to let us know if you have any other question. If you find any post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia

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

sakuraime 2,341 Reputation points

2021-08-09T14:09:49.867+00:00

so this is a ddl or dml operation ?
AmeliaGu-MSFT 14,006 Reputation points Microsoft External Staff

2021-08-10T08:19:06.75+00:00

Hi sakuraime,

Thanks for your reply.
It is a DDL operation.

Best Regards,
Amelia

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

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.

sakuraime 2,341 Reputation points

2021-08-12T02:42:39.427+00:00

No. My experience is that these are metadata-only operations <<< in this way I will need to check if there are many TLOG will be generated .. thanks
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-08-12T20:50:22.45+00:00
You don't have to check the transaction log to test this. You can wrap the command in SET STATISTICS IO ON:;

SET STATISTICS IO ON ALTER TABLE ... SET STATISTICS IO OFF

If there is not output about reads etc, it is a metadata only operation.
sakuraime 2,341 Reputation points

2021-08-13T01:38:07.457+00:00

it shows the following
Dan Guzman 9,401 Reputation points

2021-08-13T11:02:12.783+00:00
Changing from ntext to varchar(MAX) is not a meta-data only change because thtis is a fundamental data type change from Unicode to non-Unicode. Every row will be updated and you may experience character loss if the target collation doesn't have a character matching the source character.

I expect these changes will require only changes to meta-data:

text --> varchar(MAX)

ntext --> nvarchar(MAX)

image --> varbinary(MAX)
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-08-13T20:49:29.15+00:00

thtis is a fundamental data type change from Unicode to non-Unicode.

To be precise, it's a change from UTF-16 to some specific code page. Which on SQL 2019 could be UTF-8, and thus Unicode. Whatever, it is a different storage format, and therefore a size-of-data operation.

Thanks for Dan for spotting this. I only marked Sakuraime's post for follow-up, and might have missed this detail.
sakuraime 2,341 Reputation points

2021-08-17T01:51:55.927+00:00

tthanks for sharing ,

but here is focusing under same collation

text --> varchar(MAX)

ntext --> nvarchar(MAX)

image --> varbinary(MAX)

I am still finding a free time to verify whether the above change induce allocation changes like : LOB allocation back to IN-ROW-DATA.
sakuraime 2,341 Reputation points

2021-08-17T02:20:21.57+00:00

just did some test, the current allocation won't change (LOB will still stay in LOB allocation) .

the new records , whenever fit into a page will go directly to in-row-data allocation .

Share via

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

2 answers

Your answer