Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Efficient SQL Data Type

Posted on 2010-08-25
7
375 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
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!

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

839 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