• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

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.

1 Solution
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.

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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
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

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.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now