Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 638
  • Last Modified:

Can you tell me datatype of MS SQL 2005?

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.
2 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
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 ...

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now