Solved

char vs. nvarchar

Posted on 2004-09-28
9
1,399 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:
Scott Pletcher 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:Scott Pletcher
Scott Pletcher 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
Industry Leaders: 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!

 
LVL 1

Author Comment

by:dba123
ID: 12174393
nvarchar(100)
0
 
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:Scott Pletcher
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:Scott Pletcher
Scott Pletcher 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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
I have a large data set and a SSIS package. How can I load this file in multi threading?
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

685 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