what is the difference between char, varchar, nvarchar in sql

Posted by Pkanwar under Sql Server on 9/14/2013 | Points: 10 | Views : 11426 | Status : [Member] | Replies : 8
Hi,

what is the difference between char, varchar, nvarchar in sql?
Please distinguish data type char, varchar and nvarchar as well as when we use this type of data types in sql?

Thanks




Responses

Posted by: Bandi on: 9/14/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
hi,
char is fixed length character data
varchar is variable length character data
nvarchar is for storing multi language characters

declare @charData char(10), @varcharData varchar(10) , @nvarcharData nvarchar(10)
set @charadata = 'chandu'
set @varchardata ='chandu'
set @nvarchardata ='chandu'
select @chardata, @varchardata, @nvarchardata

in the above sample data 1st variable stores data with remaining blank space padding('chandu ')
i.e. stores 10 chars data even though the data is 6 chars.....

2nd variable stores only 6 chars data in the memory.....

3rd variable can support chinese, japanese and so on.....
char(10) means even though you have

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/14/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down

Char DataType
Char datatype which is used to store fixed length of characters. Suppose if we declared char(50) it will allocates memory for 50 characters. Once we declare char(50) and insert only 10 characters of word then only 10 characters of memory will be usedand other 40 characters of memory will be wasted.

varchar DataType
Varchar means variable characters and it is used to store non-unicodecharacters. It will allocate the memory based on number characters inserted. Suppose if we declared varchar(50) it will allocates memory of 0 characters at the time of declaration. Once we declare varchar(50) and insert only 10 characters of word it will allocate memory for only 10 characters.

nvarchar DataType
nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database.nvarchar datatype will take twice as much space to store extended set of characters as required by other languages.So if we are not using other languages then it’s better to use varchardatatype instead ofnvarchar
refer
http://stackoverflow.com/questions/176514/what-is-the-difference-between-char-nchar-varchar-and-nvarchar-in-sql-server



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Satyapriyanayak on: 9/14/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
http://www.aspdotnet-suresh.com/2012/07/difference-between-char-varchar-and.html

If this post helps you mark it as answer
Thanks

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 9/14/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi

refer this
http://www.aspdotnet-suresh.com/2012/07/difference-between-char-varchar-and.html
http://searchsqlserver.techtarget.com/tip/Differences-between-varchar-and-nvarchar-in-SQL-Server
http://exacthelp.blogspot.in/2012/01/difference-between-varchar-and-nvarchar.html

Char DataType

Char datatype which is used to store fixed length of characters. Suppose if we declared char(50) it will allocates memory for 50 characters. Once we declare char(50) and insert only 10 characters of word then only 10 characters of memory will be used and other 40 characters of memory will be wasted.

varchar DataType

Varchar means variable characters and it is used to store non-unicode characters. It will allocate the memory based on number characters inserted. Suppose if we declared varchar(50) it will allocates memory of 0 characters at the time of declaration. Once we declare varchar(50) and insert only 10 characters of word it will allocate memory for only 10 characters.

nvarchar DataType

nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database. nvarchar datatype will take twice as much space to store extended set of characters as required by other languages.

So if we are not using other languages then it’s better to use varchar datatype instead of nvarchar

Mark as Answer if its helpful to you

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 9/16/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down

1. nchar and nvarchar can store Unicode characters.
2. char and varchar cannot store Unicode characters.
3. 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.
4. varchar and nvarchar are variable-length which will only use up spaces for the characters you store. It will not reserve storage like char or nchar.

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

You can alos see the below link :-

http://sqlhints.com/2011/12/23/difference-between-varchar-and-nvarchar/
Happy coding.
If it helps you or directs U towards the solution, MARK IT AS ANSWER


Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/19/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
>> when we use this type of data types in sql?

CHAR is used when you have fixed length data.. For instance, if you wish to use the username of 8 chars for login then we can decide to have UserName CHAR(8)
VARCHAR is for variable length data. For example obviously address column will have variable length data.. we cannot say the length of address field MUST be 100/500... In these scenarios we will go ahead with VARCHAR data type
NVARCHAR is for supporting multi languages too.. For example if your client is foreigners.... Your app must support multi-languages....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/24/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Mark is as answer

Refer posts which are opened by you...
If you solved/understood the solution Mark those threads as answered... so that you can help others too for getting quick solutions
http://www.dotnetfunda.com/forums/show/16232/why-we-use-transaction-in-sql

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthikaqpt on: 4/4/2016 [Member] Starter | Points: 25

Up
0
Down
CHAR data type:
It is a fixed length data type.
Uses static memory allocation.
Supports both Character & Numbers.
Used to store non-Unicode characters.

VARCHAR data type:
It is a variable length data type.
Uses dynamic memory allocation.
Supports both Character & Numbers.

NVARCHAR data type:
nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database.
It is a variable length data type. Read more differences from http://qualitypointtech.net/forum/viewtopic.php?f=14&t=8406

Software testing|SeoTools

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response