Solved

char vs. nvarchar

Posted on 2004-09-28
9
1,381 Views
Last Modified: 2012-05-05
I understand the difference between char and nvarchar...basically the length.

char:  Fixed-length non-Unicode character values with a maximum length of 8000 characters
nvarchar: Variable-length Unicode data wtih a maximum length of 4000 characters.

But, the problem is, most books don't give you a real world example of typical fields such as

Phone
Address
City....and so on

I have always used nvarchar for things like address, city, state, etc. because they don't require a long data type because of performance issues.  

However this is what i don't know:

1) What is Fixed-length
2) What is non-Unicode vs. Unicode
3) Why is it standard to create a phone field as nvarchar instead of an int

things like this.  Can someone answer my 3 questions here.
0
Comment
Question by:dba123
  • 5
  • 4
9 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 12174090
1) Column always takes the same number of bytes in every row; defined as CHAR(length).

2) Non-Unicode is designated by VARCHAR(); one byte of storage per byte displayed.
Unicode is designated by NVARCHAR(); two bytes of storage for every byte displayed, but because of that can handle unusual/special characters, such as accented vowels in foreign languages, etc..

3) Actually, I think it's standard to create a phone field as VARCHAR().  Int is not used because of the old rule about "unless you need to do math on a column, make it a char/varchar".  Int would be more efficient, really, since it would take less space.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 12174103
1) So, for example, CHAR(100) will always store 100 bytes in every row, even if the column is empty (has been assigned no value).  VARCHAR(100), in contrast, will require no bytes (beyond the overhead two required to store the length) when it's empty.
0
 
LVL 1

Author Comment

by:dba123
ID: 12174392
what about nvarchar though....I have been using that for phone and address fields all along and so have many places I've coded at.
0
 
LVL 1

Author Comment

by:dba123
ID: 12174393
nvarchar(100)
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:dba123
ID: 12174403
I assume because it's variable length, that if there is no data in the field, then the field length is 0 unlike Char where you are saying 100 bytes are stored in every row?  or are you talking about every field here...even if the field is NULL?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12174474
It'll work, but it's a huge waste of space -- twice what it should need -- since no extended characters will ever be needed in a phone number.
0
 
LVL 1

Author Comment

by:dba123
ID: 12183994
so nvarchar(100) takes up space even if the field is null?
0
 
LVL 1

Author Comment

by:dba123
ID: 12184007
so I don't undrestand, you're saying use varchar instead of nvarchar for phone?  what about address and so on?  I understand teh extended character thing but not the space.  If a field is of nvarchar vs char and the field for that particular record is null at a specific point in time, does that field still store anything in memory to hold that null?
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 12184027
No, a NULL never takes up any space.  But, this number:

(999)-999-9999

would take 15 bytes if VARCHAR(100) but 30 bytes for NVARCHAR(100).
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

706 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

18 Experts available now in Live!

Get 1:1 Help Now