SQL Server: Differences between CHAR, NCHAR, VARCHAR and NVARCHAR

 SQL Server     sbo     Updated on Sep 11, 2014     VIKRAM     1031   

Introduction:

In this article i will explain differences between CHARNCHARVARCHAR and NVARCHAR in SQL Server.

Description:

In previous articles i explained few sql commands. Now i will explain differences between CHAR, NCHARVARCHAR and NVARCHAR in SQL Server.

CHAR:

char [ ( n ) ]

  • The storage size is n bytes.
  • The ISO synonym for char is character.

Note:

  1. When n is not specified in a data definition or variable declaration statement, the default length is 1.
  2. Use char when the sizes of the column data entries are consistent.

Example:

-- Declare a temporary variable.
Declare @text char

-- Assign value to temporary variable.
Set @text = 'hello'

-- Get temporary variable data.
Select @text as [Output]

Now what will be the output?

Output:

We got h as output. Because we didn't specified any length for char. It will take default length as 1.

VARCHAR:

varchar [ ( n | max ) ]

  • Variable-length, non-Unicode string data. 
  • n defines the string length and can be a value from 1 through 8,000. 
  • max indicates that the maximum storage size is 2^31-1 bytes (2 GB).
  • The storage size is the actual length of the data entered + 2 bytes.
  • The ISO synonyms for varchar are char varying or character varying.

Note:

  1. When n is not specified in a data definition or variable declaration statement, the default length is 1
  2. When n is not specified when using the CAST and CONVERT functions, the default length is 30.
  3. Use varchar when the sizes of the column data entries vary considerably.
  4. Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.

Example:

Declare @text varchar(40);
Set @text = 'AAAAA-BBBBB-CCCCC-DDDDD-EEEEE-FFFFF-GGGGG-HHHHH';

Select    @text as [Before Cast],
        LEN(@text) as [Initial Length], 
        CAST(@text as varchar) as [After Cast],
        LEN(CAST(@text as varchar)) as [Final Length]

Before casting the text length  is 40. After casting the text length is 30. Because we didn't specified any length for varchar while casting. By default it will take 30 as its length. That is why final output length is 30.

Output:

NCHAR:

nchar [ ( n ) ]

  • Fixed-length Unicode string data. 
  • n defines the string length and must be a value from 1 through 4,000.
  • The ISO synonyms for nchar are national char and national character.

NVARCHAR:

nvarchar [ ( n | max ) ]

  • Variable-length Unicode string data. 
  • n defines the string length and can be a value from 1 through 4,000. 
  • max indicates that the maximum storage size is 2^31-1 bytes (2 GB).  
  • The ISO synonyms for nvarchar are national char varying and national character varying.

Note:

  1. When n is not specified in a data definition or variable declaration statement, the default length is 1
  2. When n is not specified with the CAST function, the default length is 30.
  Related Articles
SQL Server: Variables
SQL Server: System Databases
SQL Server: Insert values into Identity Column
ASP.Net: List View Basic Example
JQuery: How to replace string in JQuery
Java Script: How to replace string in Java Script
C#: How to replace string in C#
SQL Server: How to replace string in SQL Server
C#: How to convert string to int in C#
SQL Server: How to get fixed length number format in SQL Server (or) How to prefix zeros to number, to get fixed length number format in SQL Server
  Recent Articles
SQL Server: Variables
SQL Server: System Databases
SQL Server: Insert values into Identity Column
ASP.Net: List View Basic Example
JQuery: How to replace string in JQuery
Java Script: How to replace string in Java Script
C#: How to replace string in C#
SQL Server: How to replace string in SQL Server
C#: How to convert string to int in C#
SQL Server: How to get fixed length number format in SQL Server (or) How to prefix zeros to number, to get fixed length number format in SQL Server