DIFFERENCE BETWEEN CHAR AND VARCHAR IN SQL SERVER

CHAR: The CHAR datatype is a fixed length data type in sql server. It is used to store fixed length type of string data or character string.

The size of CHAR datatype is 1 byte of space for each character and it also store non-unicode character. 

CHAR supports upto 8000 characters.

Performance is good while we using CHAR datatype as compared to VARCHAR datatype.

When the length of  string characters is less than the specified length or fixed length then it occupies all bytes to store the string characters.

The use of CHAR in sql server is as below:

DECLARE @NAME CHAR(10)

SET @NAME = 'RAMAN'

SELECT @NAME AS NAME

SELECT DATALENGTH(@NAME) AS NAME_DATALENGTH, LEN(@NAME) AS NAME_LEN 

After execution of above scripts, the result would be:

NAME: RAMAN

DATALENGTH: 10

LEN: 5

You can see the below screenshots for further clarification:

VARCHAR: The VARCHAR is most widely used into the sql server to store non-unicode variable length type of data or string data. 

VARCHAR stores data at 1 byte per character and it can store non-unicode characters or string data. 

VARCHAR also supports upto 8000 characters.

VARCHAR is a variable length and it takes less memory space. 

Performance is not good while we using VARCHAR datatype.

When the length of  string characters is less than the specified length then it occupies only number of  bytes to store the string characters rather than all bytes.

The use of VARCHAR in sql server is as below:

DECLARE @NAME VARCHAR(10)

SET @NAME = 'RAMAN'

SELECT @NAME AS NAME

SELECT DATALENGTH(@NAME) AS NAME_DATALENGTH, LEN(@NAME) AS NAME_LEN 

After execution of above scripts, the result would be:

NAME: RAMAN

DATALENGTH: 5

LEN: 5

You can see the below screenshots for further clarification:

                 - - - - - - Cheers, Happy to Help! - - - - - - 

HOW TO USE COALESCE FUNCTION IN SQL SERVER

COALESCE: The  COALESCE function is used to coalesce may rows into a single row or one row. The coalesce function is also used to handle the NULL values. The basic example of Coalesce function is as below:

Here, we have an Employee table with list of 20 records in TESTDB on my local server. Please see the below screenshot.

So, here we have an EMP_NAME column in which the employee names are displaying in each row individually. So now we will coalesce all names into a single row with the help of Coalesce function in sql server. The changes are as below.

DECLARE @employeeNames VARCHAR(8000)

SELECT @employeeNames = COALESCE(@employeeNames + ' , ', '') + CAST(EMP_NAME AS VARCHAR) FROM   Employee

SELECT @employeeNames 

It will retruns all the employee names into a single row as like this:

RAMAN SACHDEVA , ANAND SACHDEVA , PALKA SACHDEVA , RITESH SINGH , ARIJEET SINGH , TARUN SHARMA , KULWINDER SINGH , KRISHAN KUMAR , RAJINDER KUMAR , ATUL SHARMA , FATEH SINGH , RAJDEEP SINGH , JAGJEET SINGH , ANKUSH KUMAR , ROSHAN SINGH , SAURABH KUMAR , VARINDER SINGH , NITISH KUMAR , YUVRAJ SINGH , HARDEEP KAUR

Please see an above example into the below screenshot for further clarifications.

          - - - - - - Cheers, Happy to Help! - - - - - -