SQL Tips by Namwar Rizvi

May 28, 2007

Use VARCHAR(MAX),NVARCHAR(MAX) instead of TEXT, NTEXT

Filed under: New Features,SQL Server 2005,tips,TSQL,Tuning — namwar @ 8:06 PM

Have you ever faced the problem of assigning more than 8000 characters to varchar data type or more than 4000 characters to nvarchar data type? If your answer is yes, then I can understand your frustration due to this small limit.
Since SQL Server uses 8KB page to store data to disk therefore, it does not allow you more than 8000 characters in varchar or 4000 (2 Bytes per Unicode character) in nvarchar.

But wait, don’t get disappointed, Microsoft came up with Varchar(MAX), NVarchar(MAX) and VarBinary(MAX) data types in SQL Server 2005 which allows you to save upto 2GB in a single variable. The best part is that It allows you to use these data types as stored procedure parameters, internal variables etc.

So stop using TEXT and NTEXT data types because they are just there for backward computability and will be deprecated in next versions of SQL Server.

About these ads

6 Comments »

  1. Very Very Thanks.

    Comment by Rasoul Ghaffary — December 19, 2009 @ 10:38 AM | Reply

  2. Thanks a lot for the information.

    Comment by geekiatrics — May 4, 2010 @ 10:06 PM | Reply

  3. HI SIR
    I am really strungling with the term varchara and char. If possible I would to know I do you convert in number such the example of VARCHAR (20) how do you calculate to get the number

    Comment by DANIEL — July 11, 2010 @ 2:09 PM | Reply

  4. Data is successfully stored to that field.

    But i can’t retrive that value from that field (varchar(max)).

    Comment by S. Saravanan — January 6, 2011 @ 3:24 PM | Reply

  5. what should i do in cases that i need to user the nvarchar property with verylong text
    for example showing forgin languages text such as html page content?

    Comment by מערכת ניהול תוכן — September 17, 2011 @ 5:34 PM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: