Difference Betwixt Varchar Together With Char Information Type Inwards Sql Server

Hello all, today, I am going to portion an interesting SQL Server interview enquiry which volition non exclusively assistance y'all inwards your interview but besides inwards your day-to-day work. It explains 1 of the key concepts of SQL Server, the difference betwixt VARCHAR too CHAR information type. I am sure, y'all all guide maintain used both of them numerous fourth dimension but because it's hence mutual many of us ignore the divergence betwixt them too when asked to select betwixt VARCHAR too CHAR on interviews, they neglect to hand a convincing reason. The divergence is non simply of import from interview quest of sentiment but besides from a robust database pattern because an wrong selection of information type non exclusively boundary what sort of information y'all tin set on but besides waste product precious infinite too makes your query slower, specially when y'all guide maintain to bargain alongside the massive sum of data.

In enterprise to empathize the difference, it's besides of import to empathize the similarity betwixt them so, let's starting fourth dimension alongside that. VARCHAR too CHAR both stores character, text, or String information similar name, address, etc. One of the of import exceptional to know hither is that both stores non-Unicode characters too at that topographic point is a dissever information type NCHAR too NVARCHAR for storing Unicode characters.

The key divergence betwixt CHAR too VARCHAR is that the quondam is a fixed-length information type piece after is a variable length information type. Yes, the VAR stands for variable length inwards VARCHAR. To hand y'all an example,  CHAR(10) is a fixed-length non-Unicode string of length 10 piece VARCHAR(10) is a variable-length non-Unicode string alongside a maximum length of 10.

This agency the actual length volition depend upon the data. For example, if y'all are going to sore a unmarried grapheme string similar "Y" hence VARCHAR volition accept less infinite than CHAR because it volition accommodate depending upon the length of data.  Typically, y'all would purpose the char if all information values are 10 characters too varchar if the lengths vary

It's improve to purpose the information type that volition accept less space. In SQL Server 2005, 2008, 2012 too 2014, NVARCHAR takes to a greater extent than infinite than VARCHAR information type, almost 2x equally much infinite equally VARCHAR.

So, purpose VARCHAR if y'all know that all your information would endure inwards ASCII encoding, but if y'all are going to shop Unicode string similar storing information from dissimilar languages hence y'all demand to purpose NVARCHAR to back upwards Unicode data. NVARCHAR is must if y'all intend to back upwards internationalization (i18n). You tin further Microsoft SQL for Beginners  to larn to a greater extent than close char, varchar, nchar, too nvarchar information type inwards SQL

Similarities betwixt CHAR vs VARCHAR inwards SQL

Now, that y'all empathize the telephone substitution similarity too divergence betwixt char too varchar, let's encounter simply about to a greater extent than of import points for revision:

1) Both stores non-Unicode characters.
2) Both are grapheme information types.
3) Both accept 1 byte to shop 1 character.

4) The maximum length of both CHAR too VARCHAR information type is 8000 characters inwards SQL Server. Maximum length is defined inwards parenthesis e.g. maximum length of CHAR(6) is vi grapheme too the maximum length of VARCHAR(6) is besides vi character.

The size (9000) given to the type 'varchar' exceeds the maximum allowed for whatever information type (8000).

DECLARE @abc AS CHAR (8000);

DECLARE @abcd AS VARCHAR (8000);

Don't confuse length too size here, the length hither represents how many characters a CHAR or VARCHAR variable tin take, too size represents the storage bytes they accept to shop those characters. See answer)

What is the divergence betwixt UNION too UNION ALL inwards SQL? (answer)

The divergence betwixt TRUNCATE too DELETE inwards SQL? (answer)

The divergence betwixt self too equi-join inwards SQL? (answer)

Top v SQL too Database Course for Programmers (courses)

The divergence betwixt WHERE too HAVING clause inwards SQL? (answer)

The divergence betwixt LEFT too RIGHT OUTER JOIN inwards SQL? (answer)

How to uncovering duplicate records inwards a table? (query)

Difference betwixt isNull() too Collasce() inwards SQL Server? (answer)

10 SQL Queries from Programming Job Interviews (queries)

5 Courses to Learn SQL Server too T-SQL (Courses)

Thanks for reading this article hence far. If y'all similar this SQL Interview enquiry too my explanation hence delight portion alongside your friends too colleagues. If y'all guide maintain whatever questions or feedback hence delight drib a note.

P.S. - If y'all demand to a greater extent than SQL Interview Question for practice, y'all tin besides cheque out 200+ SQL Interview Questions online course on Udemy which non exclusively rank y'all the mutual SQL questions but besides explicate the reply inwards exceptional to fill upwards gaps inwards your learning. 

Share this post