Solved

char vs. nvarchar

Posted on 2004-09-28
9
1,402 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
[X]
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
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query Works in SQL 2008 & 2012 But Not SQL 2016 15 99
get count of orders by customer Sql Server table. 3 46
mssql 7 32
SQL Job Failed 6 29
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 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