SQL 2008 - varchar(max) vs text data types

Question

Possible Duplicate:
SQL Server Text type vs. varchar data type
Using varchar(MAX) vs TEXT on SQL Server

from this article, it looks like they both have the same size capacity: http://msdn.microsoft.com/en-us/library/ms143432.aspx

is that truly correct? what are the real differences?

i just migrated some data from oracle (clob and made it into a varchar(max), but it looks like it truncated some of the values anyway, should i use TEXT instead?

Thank you!

TEXT is becoming depracated - http://andrewm-developmentnotes.blogspot.com/2011/03/text-type-deprecated-in-sql-server-2008.html — StuartLC, Sep 12 '11 at 15:23
This thread has the answer you are looking for: http://stackoverflow.com/questions/564755/sql-server-text-type-vs-varchar-data-type — Icarus, Sep 12 '11 at 15:23
http://stackoverflow.com/questions/834788/using-varcharmax-vs-text-on-sql-server — NullUserException, Sep 12 '11 at 15:25
what code did you use to migrate the data? I think you have a problme with how you did it not what the datatype was. — HLGEM, Sep 12 '11 at 17:56
Look at the link http://stackoverflow.com/q/28980502/1805776 — vicky, Oct 18 '16 at 09:44

score 13 · Accepted Answer · answered Sep 12 '11 at 15:23

13

No, you should not use TEXT. For one, it has been deprecated since SQL Server 2005, and also you will find that many string operations do not work against it. Do not use TEXT, NTEXT or IMAGE at all - use VARCHAR(MAX), NVARCHAR(MAX) or VARBINARY(MAX) instead.

answered Sep 12 '11 at 15:23

Aaron Bertrand

272,866
37
466
490

Damn Microsoft. Why can't they stick to conventions and use text like MySQL and Postgres? – Henley Nov 18 '13 at 19:30
1

@Henley because they used text already and it sucked. Why couldn't MySQL follow the standard and use offset/fetch instead of this silly limit crap? Mud flies both ways... – Aaron Bertrand Nov 18 '13 at 20:11
not if there's a lot of wind – shiggity Sep 15 '17 at 18:45

Remus Rusanu · Answer 2 · 2011-09-12T15:38:49.583

The legacy types (TEXT, NTEXT, IMAGE) are deprecated and support for them will be dropped in a future version of SQL Server.

There are significant differences in how you use them. The new MAX types support efficient updates with the .WRITE syntax. The legacy types have an arcane set of functions to achieve the same (TEXTPTR, UPDATETEXT, sp_invalidate_testptr).

The new MAX types can be used in functions like REPLACE or SUBSTRING, any function that accepts a VARCHAR(N) will also accept a VARCHAR(MAX).

The legacy types do not support implicit conversion which the MAX types support, see Data Type Conversions.

Certain engine features work with MAX types, but not with the legacy ones, eg. online operations (in SQL 11 they support tables with BLOBs)

score 2 · Answer 3 · answered Sep 12 '11 at 15:26

The linked article only deals with space allocation. At a minimum, know that text, ntext and image data types will be removed from future releases of SQL Server so for new tables, use varchar(max), nvarchar(max) or varbinary(max).

Of equal importance would be how one interacts with those values. Would you rather use the standard column = 'really long value' or UPDATETEXT and other less common operations to work on a column because of the data type?

SQL 2008 - varchar(max) vs text data types

3 Answers3

Linked