Thursday, July 12, 2018

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...  
 

No comments:

Post a Comment