SQL Interview: 40 Replacing text data type with varchar

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Advanced

Question:

You have a table in your database that contains six columns that are defined with the text data type.

When you run checks on your database, these columns are often flagged with deprecation warnings, and so you change each column as follows:

ALTER TABLE SomeTable ALTER COLUMN Column1 varchar(max);

Will this leave you with a table that’s the same as if you had created it with the varchar(max) data type? If not, how can you change that?

Answer:

By default, varchar(max) defaults to having its data inrow, and using overflow pages when the data gets too large. The text data type always stored the data off page and used pointers.

When you change the data type as above, the data is not migrated, and is left with the pointer structure.

You can change that by using options in ALTER TABLE REBUILD.

2025-04-25