Efficient SQL Data Type

I have been looking for a good answer regarding which Data type to use when designing a new table that would contain a comment field.  I searched books online and Google but couldn’t find a good answer.  Users want to be able to put long comments in this table.  But I don’t want to specify a Data type that would waste space.  We’ll be adding “comment” field to few other tables. So, I just wanted to know which data type would be more efficient.  By the way, we’re currently using SQL 2005 and will be moving to SQL 2008 soon.  My choices are
Nvarchar (max)
Text: What is the maximum number of characters allowed for 'Text' Data type
Ntext: I found that ntext is no longer supported as string function? Is this true.
Or just use varchar(n): I could just use varchar and specify number like 1000 to allow a long string.

Who is Participating?
ralmadaConnect With a Mentor Commented:
Forgot to mention that performance wise, nvarchar could also have significant overhead in indexing, thus affecting your performance. Why? Again you are indexing a much bigger table because of the extra space required by the nvarchar datatype. Of course it all depends on your environment and a couple of other factors. Here's a good reference from Microsoft discussing the effect on performance and storage of Unicode

Lee SavidgeCommented:
nvarchar(Max) can store maximum nvarchar.maximum characters but the actual size depends on the data. 2^31-1
ntext can store any number of characters, there is no limit

varchar and nvarchar, the difference is that data types prefixed with n are unicode compliant so I would use them.

In terms of efficiency, there is no real difference that I know of but I would use ntext and nvarchar over text and varchar.

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Bhavesh ShahLead AnalysistCommented:

Please check out this link.

Complete info of datatype from microsoft

If you are in SQL 2005 or above, never ever go for NTEXT or TEXT. Those are old SQL 2000 datatypes and they do not work with all the functions/commands in SQL. For instance, you cannot run an UPDATE command to a TEXT field directly. TEXT and NTEXT are quite a hassle, trust me. Plus per BOL, they will be removed in future versions of SQL...
"ntext, text, and image data types will be removed in a future version of MicrosoftSQL 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. " 
So your choices gets narrowed down to NVARCHAR(max) or VARCHAR(max).
Whether to go for NVARCHAR or VARCHAR(max) depends on what type of comments you want to store in there. Nvarchar is used to store unicode data, which is used to store multilingual data in your database tables. So I will use NVARCHAR only if you have special characters (such as Chinese characters, etc) in those comments. If not I will use the plain old VARCHAR(max). Why?nvarchar(max) takes twice as much space as varchar ;)
Hope this helps
vb7guyAuthor Commented:
Thank you ralmada.  I chose your answer because you gave more detailed answer then just a link.  I end up going with Varchar(2000) because users will not be writing a book in the comments field and to keep the table small.

And you are correct that ntext or text field will not be used in the future and since we'll be moving to SQL 2008, we want to limit data type conversions in the future.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.