Solved

char vs. nvarchar

Posted on 2004-09-28
9
1,393 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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 …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

792 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