Difference Betwixt Char, Varchar, Nchar Together With Nvarchar Information Type Inwards Sql Server Database

What is the departure betwixt char and varchar in SQL, followed past times nchar and nvarchar, is i of the pop SQL interview questions, in addition to surprisingly non every programmer knows this basic difference. If y'all become amongst the name, which y'all should, therefore y'all tin figure out that char is a fixed length information type piece varchar should hold upwards variable length information type. Though all char, varchar, nchar, in addition to nvarchar are used to shop text or String data in that place are subtle differences betwixt them. As I said char is fixed length, which agency a variable or a column e.g. Zipcode char(10) volition accept alone 10 bytes to shop data, including space.

On the other mitt a varchar variable or column volition accept variable space, depending upon information y'all shop + ii additional bytes for storing length. For representative a varchar column advert varchar name(20) volition accept half dozen bytes if y'all shop "Jack" (4 + 2) in addition to vii bytes if y'all shop "Jones" (5 + 2).

In gild to larn meliorate performance, y'all should purpose char for fixed length columns e.g. zipcode, where every row are nether for certain length e.g. half dozen for India, in addition to v + four digits postal codes for USA. On the  other hand, for a variable length column it's meliorate to purpose varchar information type to salvage the space,  which is lost inward instance of char type, if actual information is ever way less than capacity.

In detail this query is side past times side inward serial of duet of pop SQL interview question, e.g. difference betwixt WHERE in addition to HAVING clause and  writing SQL query to bring together 3 tables. If y'all come upwards across whatever other interesting SQL queries than y'all tin besides portion amongst us, if y'all don't know answer, nosotros volition endeavour to honor out together.

NCHAR vs NVARCHAR inward SQL

character, which agency ii bytes for each character, 1 for grapheme + 1 for Unicode. Apart from this primal question, y'all often run across duet of follow-up questions similar when to purpose char in addition to varchar inward database? or to a greater extent than oft what is departure betwixt char(20) in addition to varchar(20) variables, equally they tin alone shop twenty bytes to shop data. Well, psyche departure is that varchar powerfulness accept less bytes, depending upon length of information y'all shop on it. For representative if nosotros shop "USA" inward a variable of type char(20) in addition to varchar(20) than start volition accept twenty bytes, piece minute volition accept alone v bytes (3 +2 ), precisely surgery of char columns are meliorate inward SELECT query than varchar columns. Similarly if nosotros purpose nchar(20) in addition to nvarchar(20) for storing "SQL" string than start volition accept xl bytes (2*20) in addition to minute variable volition accept 8 bytes (3*2 +2).

Here is a summary of how much infinite a char(10), varchar(10), nchar(10) in addition to nvarchar(10) variable takes for storing same information :

declare @cData char(10)

set @cData = 'SQL' -- 10 bytes

set @cData = 'Java' -- 10 bytes

declare @vcData varchar(10)

set @vcData = 'SQL' --  3 + ii = v bytes

set @vcData = 'Java' -- four + ii = half dozen bytes

declare @ncData nchar(10)

set @ncData = 'SQL' -- 10*2 = twenty bytes

set @ncData = 'Java' -- 10*2 = twenty bytes

declare @nvcData varchar(10)

set @nvcData = 'SQL' -- 3*2+2 = 8 bytes

set @nvcData = 'Java' -- 4*2+2 = 10 bytes

Thanks guys, that's all on difference betwixt char in addition to varchar information types inward SQL in addition to nchar vs nvarchar. I promise this noesis of basic, non alone helps inward SQL interviews precisely besides choosing correct type for your columns inward tables in addition to variables inward your stored procedures. Let me know if y'all guide maintain whatever uncertainty or query on char or varchar variables.


Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

Share this post