SQL Server - difference between char(10) and nvarchar(10)

Asked By aman on 18-Jul-11 03:49 AM
hi all and thanks for the previous replies.....

i m confused between these two data types char(10) and nvarchar(10)

when i exceed the value of char data to 11 it shows error and exactly when i doo the same for nvarchar(10) the same error occures......please help me out

thanks,
Aman khan
Kalit Sikka replied to aman on 18-Jul-11 03:53 AM
char - stores character data with fixed length
example: char(10) 10 character memory is allocated on the sql server memory location. suppose if your are not
utilizing 10 character memory means memory will be wasted.
varchar: Variable-length, non-Unicode character data. The database collation determines which code page the data is stored using. if your are not utilizing memory means memory will be collected.
nvarchar: Variable-length Unicode character data. Dependent on the database collation for comparisons. we can store international languages like Chinese, Japanese, hindi and Tamil languages
Reena Jain replied to aman on 18-Jul-11 03:54 AM
Hi,

  • nchar and nvarchar can store Unicode characters.
  • char and varchar cannot store Unicode characters.
  • char and nchar are fixed-length which will reserve storage space for number of characters you specify even if you don't use up all that space.
Hope this will help you
Reena Jain replied to aman on 18-Jul-11 03:55 AM
Hi,

here is more difference for you

  •     char: fixed-length character data with a maximum length of 8000 characters.
  •     nchar: fixed-length unicode data with a maximum length of 4000 characters.
  •     Char = 8 bit length
  •     NChar = 16 bit length

Hope this will help you
Reena Jain replied to aman on 18-Jul-11 04:08 AM
Hi,

so more brief detail
 
char : - it stores fixed length of character. if you declare char(50) then it allocates memory for 50 characters. if you store 10 character word then it store it in 10 characters memory location and other 40 character's memory location will be wasted.

varchar : - varchar means variable characters. it allocates memory as per data stored into this. If you specify varchar(50) at the time of declaration then it allocates 0 memory location at the starting if you specify, it can be null. When you store 10 characters word it will allocate memory for 10 characters and store into that. So there will be no memory loss. It stores only non-unicode characters.

nvarchar : - nvarchar means unicode variable characters. it allocates memory as same as varchar. But It stores unicode characters.

Hope this will help you
Jitendra Faye replied to aman on 18-Jul-11 04:24 AM

char type: the English (ASCII) characters take up one byte, a character takes 2 bytes, CHAR stores fixed-length data is very convenient, CHAR field of high-level index of efficiency, such as the definition of char (10), then Whether you store data on whether the 10 bytes to 10 bytes to be accounted for in the space. Because it is a fixed length, so the speed and high efficiency.

Varchar Type: Varchar type does not fill a space, such as varchar (100), but its value is "qian", then its value is "qian"
But not the same as char, such as char (100), its value is "qian", but in fact it is in the database "qian" (qian after a total of 96 spaces, is to fill it to 100 bytes) .

Since char is fixed length, so its speed is much faster than varchar! Up to trouble, but procedures that use functions like trim on both sides of the space to get rid of!


     
VARCHAR store variable-length data, but there is no CHAR high storage efficiency. If the value of a field may not be a fixed length, we only know that it can not be more than 10 characters, it is defined as VARCHAR (10) is the best buy. The actual length of the VARCHAR type is the actual length of its value +1. Why is "+1" mean? This is a byte used to store the length of how much actual use.

Nchar Nvarchar  type and how it all about? In order to convert with a variety of other characters, such as Chinese, phonetic symbols and so on, each in English (ASCII) characters take up 2 bytes, can occupy a two-byte characters, all characters occupy 2 bytes.

Riley K replied to aman on 18-Jul-11 04:25 AM
char[(n)] - Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.


nvarchar(n) - Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.
dipa ahuja replied to aman on 18-Jul-11 04:25 AM
char[(n)] - Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes.

nvarchar(n) - Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length

varchar[(n)] - Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length.
Devil Scorpio replied to aman on 18-Jul-11 05:47 AM
Hi aman,

In case of Char datatype, the length will be remain fixed.
Example
suppose if u declared char of 10 length, that means always it will occupy 10 character length, whether u are storing string of 10 length or not. In case of storing string less than 10 length, there will be wastage of memory as this datatype is static in nature.

In case of nVarChar datatype, the length will be remain dynamic.
Example
suppose if u declared nVaChar of 10 length, that means it wont occupy 10 character length, whether u are storing string of 10 length or not. In case of storing string less than 10 length, Whatever length of string u are storing, it will ocupy that much length of string. This datatype is dynamic in nature, adjust the length as per the string size.

if u r using nvarchar(10), then variation of length will happen till 10 length because declared length is the maximum length. The variation of length happens below the maximum declared length.