Difference between char and varchar in sql server

In sql sever following are major differences between char and varchar

1.  Data type char is example of fixed length memory allocation. For example:

DECLARE @Value AS CHAR(10) = 'Exact'

SELECT DATALENGTH(@Value)

Output: 10

So, Sql sever has allocated 10 byte memory spaces while total number of characters in the constant 'Exact' is only 5.

Data type varchar is example of variable length memory allocation. For example:

DECLARE @Value AS VARCHAR(10) = 'Exact'

SELECT DATALENGTH(@Value)

Output: 5

So in case of varchar sqlsever use dynamic allocation.

2. If total numbers of characters is less than size of char data type then sql sever embeds that number of space characters at the end but not in varchar data type. For example:

What will be output of following sql query  ?

DECLARE @First AS CHAR(10) = 'Exact'

DECLARE @Last AS CHAR(10) = ' Help'

IF (@First + @Last = 'Exact Help')

    SELECT 'Equal'

ELSE

    SELECT 'Not Equal'

Output: Not Equal

Explanation: In memory variable @First has stored like 'Exact     ' and variable @Second ' Help     '

So, @First + @Last = 'Exact       Help     '

It is obvious that is not equal to 'Exact Help'

3. When ANSI_PADDING is off then a column of any table of type CHAR NULL is same as VARCHAR NULL. For example:

Example 1:

SET ANSI_PADDING OFF

CREATE TABLE CharTest_1(

    Value CHAR(10) NULL

)

INSERT INTO CharTest_1 VALUES ('Exact'),('Help')

SELECT DATALENGTH(Value) FROM CharTest_1 

Output:

5

4

Explanation: Here sql server is treating CHAR(10) NULL as VARCHAR(10) NULL.

Example 2:

SET ANSI_PADDING ON

CREATE TABLE CharTest_1(

    Value CHAR(10) NULL

)

INSERT INTO CharTest_1 VALUES ('Exact'),('Help')

SELECT DATALENGTH(Value) FROM CharTest_1 

Output:

10

10

Example 2:

SET ANSI_PADDING OFF

CREATE TABLE CharTest_2(

    Value CHAR(10) NOT NULL

)

INSERT INTO CharTest_2 VALUES ('Exact'),('Help')

SELECT DATALENGTH(Value) FROM CharTest_2 

Output:

10

10

4. VARCHAR keep additional two bytes at the end to keep the length of the string while CHAR is fixed length so it doesn't keep the length information.

5. When we perform update query on CHAR column is doesn't move that row since data always fits in the allocated space while when we update VARCHAR column then there is need to move the row. 

Advantage of using CHAR data type in sql server:

1. Query performance is better since no need to move the column while updating.

2. No need to store the length of string in last two bytes.

Disadvantage of using CHAR data type in sql server:

1. It takes too much more spaces than varchar since it is fixed length and we don't know the length of string which to be store.

2. It is not good for compression operation since it embeds space characters at the end.

Advantage of using VARCHAR data type in sql server:

1. Since it is variable length so it takes less memory spaces.

Disadvantage of using VARCHAR data type in sql server:

1. Decrease the performance of some sql queries.

So, we should use CHAR data type of a column only if we known the length of the string is fixed otherwise we should always use varchar.

How to perform case sensitive comparison in sql server
Sql server string comparison is case sensitive or not
Insert default value when data or parameter is null in sql server
SQL SERVER PPT