SQL 2008 - varchar(max) vs text data types

sql sql-server-2008 varchar sqldatatypes varcharmax
Madam Zu Zu picture Madam Zu Zu · Sep 12, 2011 · Viewed 14.6k times · Source

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!

Answer

Aaron Bertrand picture Aaron Bertrand · Sep 12, 2011

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.

Related questions

Converting Varchar to NVarchar?

I know when converting from nvarchar to varchar, some data will be lost/changed. However, are there any risks of data changing when converting a varchar data type to an nvarchar?

Read More
Changing the maximum length of a varchar column?

I'm trying to update the length of a varchar column from 255 characters to 500 without losing the contents. I've dropped and re-created tables before but I've never been exposed to the alter statement which is what I believe I need to …

Read More
How to get the size of a varchar[n] field in one SQL statement?

Suppose that I have a SQL table that has a varchar[1000] field called "Remarks". I would like to craft a single SQL statement, which when executed, will return 1000, or whatever the size of the varchar field might be changed to …

Read More