Can you tell me datatype of MS SQL 2005?

Posted on 2007-10-12
Last Modified: 2012-06-21
What's the difference between nvarchar, varchar, and char?
Can you tell me the advantages and disadvantages?
Also, If I need to use different language, which one is the best?
Thank you.
Question by:erin027
    LVL 142

    Accepted Solution

    char/nchar vs varchar/nvarchar  have only 1 difference: char data types will right-pad the data with spaces up to the size defined, while varchar will not occupy any more space than data is saved.
    say you have a field last_name varchar(100) vs last_name char(100). varchar version would store in average some 30-35 characters (depends a bit on the country), per record, while the char version would store 100 characters, whatever the length of the name is.

    char/varchar vs nchar/nvarchar  has some differences:
    nchar/nvarchar occupy 2 bytes per character instead of 1 byte per characer for the char/varchar version.
    this is due to the fact that in nvarchar you can store multiple "languages" (to be exact, one has to say characters from different code pages), in a unicode format. with varchar, you cannot do that, you have only 1 fixed code page available.
    LVL 18

    Assisted Solution

    varchar is a variable length data type meaning that varchar(10) will be at max 10 characters wide, or less when no more are needed to store the value.
    char is a fixed size data type meaning that char(10) will always consist out of 10 characters, even wehn less are needed to store the values. In that case, spaces are added to the right.
    nvarchar is the unicode sister of varchar meaning that every character will consume two bytes in stead of 1 in a regular varchar and thus for storing the same value, nvarchar needs double the space compared to varchar.
    Same goes for nchar compared to char data type.

    If you do not need unicode characters, do avoid using nchar and nvarchar datatypes, as they consume double the space which is useless and might even slow the database down by doing double the amount of I/O.

    Hope this helps ...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    737 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