• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • 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)
Varchar(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.
 

0
vb7guy
Asked:
vb7guy
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.

Lee
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Bhavesh ShahLead AnalysistCommented:
Hi,

Please check out this link.

Complete info of datatype from microsoft

http://msdn.microsoft.com/en-us/library/ms187752.aspx
0
 
ralmadaCommented:
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...
http://msdn.microsoft.com/en-us/library/ms187993.aspx
"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
 
0
 
ralmadaCommented:
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
http://msdn.microsoft.com/en-us/library/ms189617.aspx 

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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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