Character Map for Varchar & Nvarchar

Character Map for Varchar & Nvarchar

                                  This post I want to discuss about Character Map supported for SQL Server varchar & nvarchar data types. Everyone knows varchar is variable length, non-Unicode string data. And if we take nvarchar it's variable-length, UNICODE UCS-2 character set. And also ANSI_PADDING is always ON for nvarchar.

Following example will help you find both supported Character

;WITH tblNumbers AS

(

    SELECT 1 AS Number

    UNION ALL

    SELECT Number+1

        FROM tblNumbers

        WHERE Number+1<257

)

SELECT Number AS ASCII_Value,

CAST(CHAR(Number) AS varchar(10)) COLLATE SQL_Latin1_General_CP1_CI_AS AS ASCII_Char_General,

CAST(CHAR(Number) AS varchar(10)) COLLATE Finnish_Swedish_CI_AS AS ASCII_Char_Swedish, 

CAST(CHAR(Number) AS varchar(10)) COLLATE DANISH_NORWEGIAN_CI_AS AS ASCII_Char_DANISH,

CAST(CHAR(Number) AS nvarchar(10)) COLLATE SQL_Latin1_General_CP1_CI_AS AS ASCII_Char_General_nvarchar,

CAST(CHAR(Number) AS nvarchar(10)) COLLATE Finnish_Swedish_CI_AS AS ASCII_Char_Swedish_nvarchar, 

CAST(CHAR(Number) AS nvarchar(10)) COLLATE DANISH_NORWEGIAN_CI_AS AS ASCII_Char_DANISH_nvarchar

FROM tblNumbers

OPTION (MAXRECURSION 257)

Now let me explain which Character not supported varchar.

SELECT CAST(N'ɽ' as varchar(2)) [varchar],CAST(N'ɽ' AS nvarchar(2)) [nvarchar];
SELECT CAST(N'Ȏ ȣ ǧ ǒ ƾ ƛ A ® ¶ Ĥ Ʒ' as varchar(50)) [varchar],CAST(N'Ȏ ȣ ǧ ǒ ƾ ƛ A ® ¶ Ĥ Ʒ' AS nvarchar(50)) [nvarchar];

Hope now you have clear idea what is different between varchar and nvarchar.
Cheers...
and Most welcome your comments and ideas...