Everyone knows about the basic difference between CHAR and VARCHAR data types. In this article apart from the basic difference, will discuss on one more interesting difference which I have observed recently.
CHAR Data Type is a Fixed Length Data Type. For example if you declare a variable/column of CHAR (10) data type, then it will always take 10 bytes irrespective of whether you are storing 1 character or 10 character in this variable or column. And in this example as we have declared this variable/column as CHAR(10), so we can store max 10 characters in this column.
On the other hand VARCHAR is a variable length Data Type. For example if you declare a variable/column of VARCHAR (10) data type, it will take the no. of bytes equal to the number of characters stored in this column. So, in this variable/column if you are storing only one character then it will take only one byte and if we are storing 10 characters then it will take 10 bytes. And in this example as we have declared this variable/column as VARCHAR (10), so we can store max 10 characters in this column.
Below example illustrates the basic difference explained above:
DECLARE @CharName Char(20) = 'Basavaraj', @VarCharName VarChar(20) = 'Basavaraj' SELECT DATALENGTH(@CharName) CharSpaceUsed, DATALENGTH(@VarCharName) VarCharSpaceUsed
Result:
CharSpaceUsed VarCharSpaceUsed ------------- ---------------- 20 9 (1 row(s) affected)
Below is an interesting difference, which I have observed recently while writing some script.
Concatenation of CHAR variables:
DECLARE @FirstName Char(20) = 'Basavaraj', @LastName Char(20) = 'Biradar' IF @FirstName + ' ' + @LastName = 'Basavaraj Biradar' PRINT 'I was Expecting' ELSE PRINT 'Surprise to me ...' SELECT @FirstName + ' ' + @LastName AS Name, len(@FirstName + ' ' + @LastName) AS Length
Result:
Surprise to me …
Name Length
—————————————– ———–
Basavaraj Biradar 28
(1 row(s) affected)
Concatenation of VARCHAR variables:
DECLARE @FirstName VarChar(20) = 'Basavaraj', @LastName VarChar(20) = 'Biradar' IF @FirstName + ' ' + @LastName = 'Basavaraj Biradar' PRINT 'I was Expecting' ELSE PRINT 'Surprise to me ...' SELECT @FirstName + ' ' + @LastName AS Name, len(@FirstName + ' ' + @LastName) AS Length
Result:
I was Expecting Name Length ----------------------------------------- ----------- Basavaraj Biradar 17 (1 row(s) affected)
So, it is clear from the above examples that during concatenation of CHAR data type variables, it includes space in-place of unused space in the result of concatenation.
[ALSO READ] You may like to read below other popular articles on differences
Varchar Vs NVarchar
Varchar Vs Varchar(MAX)
Union Vs Union All
DateTime Vs DateTime2
SET QUOTED_IDENTIFIER ON Vs OFF
Stored Procedure Vs User Defined Function
Primary Key Vs Unique Key
RAISERROR Vs THROW
Temporary Table Vs Table Variable
LEN() Vs DATALENGTH()
Sequence Vs Identity
hi ,
Thanks……………good explanation..
Hi SQL hints,
DB2 iSeries server, we can define a field firstname varchar(50) allocate(12)
this means the row contains 12 characters for the field, any further characters go to overflow area. As long as 99% of rows stay within 12 characters, logical access is extremely fast.
Question please for SQL Server
does varchar(12) achieve same fast result (12 = to the allocated physical size in the row)
Is their a tool to maintain the 12 in varchar(12)
What is the difference between LEN and DATALENGTH
Hi Kris,
You can go through the article Difference between Len() and Datalength() functions in Sql Server and let me know your feedback…
Thanks,
Basavaraj
Can you clarify?
I ran below statement
DECLARE @FirstName Char(20) = ‘Basavaraj’,
@LastName Char(20) = ‘Biradar’
IF @FirstName + ‘ ‘ + @LastName = ‘Basavaraj Biradar’
PRINT ‘I was Expecting’
ELSE
PRINT ‘Surprise to me …’
SELECT @FirstName + ‘ ‘ + @LastName AS Name,
len(@FirstName + ‘ ‘ + @LastName) AS Length,
datalength(@FirstName + ‘ ‘ + @LastName) AS [DataLength]
Output is :
Basavaraj Biradar 28 41
Output
Name Length DataLength
Basavaraj Biradar 28 41
Hi Kris,
plz explain difference between before trigger and after trigger with examples.
Hi Santosh,
For this you may like to go through the article:
https://sqlhints.com/2016/03/09/triggers-in-sql-server/
Thanks,
Basavaraj
Hello, use below one
DECLARE @FirstName VarChar(20) = ‘Basavaraj’,
@LastName VarChar(20) = ‘Biradar’
IF @FirstName + ‘ ‘ + @LastName = ‘Basavaraj Biradar’
begin
PRINT ‘I was Expecting’
end
ELSE
begin
PRINT ‘Surprise to me …’
SELECT @FirstName + ‘ ‘ + @LastName AS Name,
len(@FirstName + ‘ ‘ + @LastName) AS Length
end
Output: I was Expecting
Hi, use below one
DECLARE @FirstName VarChar(20) = ‘Basavaraj’,
@LastName VarChar(20) = ‘Biradar’
IF @FirstName + ‘ ‘ + @LastName = ‘Basavaraj Biradar’
begin
PRINT ‘I was Expecting’
end
ELSE
begin
PRINT ‘Surprise to me …’
SELECT @FirstName + ‘ ‘ + @LastName AS Name,
len(@FirstName + ‘ ‘ + @LastName) AS Length
end
Output: I was Expecting