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!
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.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With