Solved

Efficient SQL Data Type

Posted on 2010-08-25
7
368 Views
Last Modified: 2012-08-14
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
Comment
Question by:vb7guy
7 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 33520244
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 33520247
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 33520256
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33520363
Hi,

Please check out this link.

Complete info of datatype from microsoft

http://msdn.microsoft.com/en-us/library/ms187752.aspx
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33525430
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
 
LVL 41

Accepted Solution

by:
ralmada earned 250 total points
ID: 33525531
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
 

Author Closing Comment

by:vb7guy
ID: 33529860
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now