char vs. nvarchar

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.
LVL 1
dba123Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
dba123Author Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dba123Author Commented:
nvarchar(100)
0
 
dba123Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
dba123Author Commented:
so nvarchar(100) takes up space even if the field is null?
0
 
dba123Author Commented:
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
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
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.

All Courses

From novice to tech pro — start learning today.