What are the benefits of using VARCHAR(max), NVARCHAR(max) and VARBINARY(max) instead of TEXT, NTEXT and IMAGE?

Question

I know TEXT, NTEXT and IMAGE are deprecated and will, eventually, be removed from SQL Server.

But, besides this deprecation, why are VARCHAR(max), NVARCHAR(max) and VARBINARY(max) datatypes better? Why did Microsoft chose them?

I noticed VARCHAR and NVARCHAR can be used with = and GROUP BY, while the old types can't. Are there more benefits?

It's not just `=`. Hardly any of the string functions work on `text` fields and the new datatypes are much easier to work with. e.g, to do a replace against a text field you need to mess around with `TEXTPTR`s [like here](http://stackoverflow.com/a/304643/73226). For `varchar(max)` you just call `REPLACE` — Martin Smith, Oct 02 '12 at 07:51
Potential duplicate: http://stackoverflow.com/questions/564755/sql-server-text-type-vs-varchar-data-type — Bridge, Oct 02 '12 at 07:51
What do you expect to gain from this question? You have a choice between "data types that are consistent with the similar, non-max data types" or "data types that Microsoft are shouting loudly not to use". Which types are you actually going to use? — Damien_The_Unbeliever, Oct 02 '12 at 07:52
@Damien_The_Unbeliever: I expect to get to know what other advantages are there. Text, etc. are not going to be discontinued right away, not even in the next SQL Server, so is it really worth it to transform thousands of columns right away? Or can it wait? I see lots of insight in this kind of questions. — John Assymptoth, Oct 02 '12 at 10:31

score 6 · Answer 1 · answered Oct 02 '12 at 07:59

The data types were deprecated because with the advancement of SQL Server, it is no longer necessary to treat large blocks of text (TEXT) or binary data (IMAGE) any different to VARCHAR or BINARY. With storage not really being a problem anymore, we can now do SUBSTRING(<varchar(max)>, 1234567, 400) to your heart's content whereas in the old days, 1 MB seemed like a lot and required special handling, such as the functions in this article.

IMAGE carries similar baggage to TEXT, which requires storing the size in a special way rather than expanding VARBINARY to cater for anything all the way to 2GB.

However, the same reasons for having specialized BLOBs and CLOBs is still valid under memory/storage constraints, so SQL Server CE continues to have the IMAGE type and it's not going away anytime soon on that front.

score 1 · Answer 2 · edited May 23 '17 at 11:56

1

Because they are deprecated or not going to be supported

Important ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

What are the benefits of using VARCHAR(max), NVARCHAR(max) and VARBINARY(max) instead of TEXT, NTEXT and IMAGE?

2 Answers2