2

There are tons of questions asking "what is char/varchar vs nchar/nvarchar?" The answers always state that nchar/nvarchar store strings as unicode and char/varchar do not. So what do char/varchar store strings as? Is it ASCII? Or some other character set?

Legion
  • 3,922
  • 8
  • 51
  • 95

5 Answers5

7

So what do char/varchar store strings as? Is it ASCII? Or some other character set?

The character set for char/varchar is defined by the collation of the column or variable. The 128 ASCII characters are the same for all collations, using code points 0-127. The remaining characters supported by the collation are mapped to code points according to the collation's code page.

Most collations use single-byte codes which provide 128 additional characters in the 128-255 code point range. These are sometimes called extended ASCII characters, but that is a misnomer since these are not standard ASCII characters and the characters assigned to code points may differ depending on the code page.

SQL Server also supports a few double-byte collations (code pages 932,936,949,950). These collations also store the 128 ASCII characters as single bytes and use 2 bytes for other characters (similarly to UTF-8).

The query below lists the code page of each collation:

SELECT  name AS CollationName
      , COLLATIONPROPERTY(name, 'CodePage') AS CollationCodePage
FROM    fn_helpcollations();
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
0

I was going to flag this as duplicate. But the question seems to be different to (What is the difference between varchar and nvarchar?), for example, in that the OP isn't understanding the use of said data types.

It's not that nvarchar stores strings as unicode. It's that nvarchar stores unicode data and varchar stores non unicode data.

Varchar stores Nonunicode data which is a subset of unicode data

varchar uses a byte (8 bits) to save each character which means it has just 8 bits to save this data and a character set of 2^8 (that's 256) symbols

nvarchar on the other hand uses Unicode and needs 2 bytes (or 16 bits) meaning it can save (2^16) (that's 65536) symbols. Use nvarchar when you're not sticking to the standard 255 character set or you require symbols or characters in different languages.

Use this query to give you the complete character set varchar allows:

DECLARE @cnt INT = 0;
DECLARE @ASCTABLE TABLE(NUM int, ascChar varchar);
WHILE @cnt < 256
BEGIN
   insert into @ASCTABLE (NUM, ascChar) values (@cnt, char(@cnt))
   SET @cnt = @cnt + 1;
END
select * from @ASCTABLE;

You'll notice if the you increase the counter limit to above 256, it'll simply return null for anything above 255

EDIT: Here's a site listing all the unicode characters (http://unicode-table.com/en/) nvarchar supports all these characters. varchar supports just the first 255 characters from this set.

Community
  • 1
  • 1
Ash
  • 5,786
  • 5
  • 22
  • 42
  • I meant that in the context of the non-unicode data supported by varchar (255 symbols) making it a subset of unicode data supported by nvarchar (65536 symbols including all the symbols supported by varchar) – Ash Jul 08 '16 at 01:02
0

CHAR and VARCHAR store data as one byte of storage for each character, but NVARCHAR or NCHAR data uses 2 bytes of storage per character to include (or in the event a surrogate pair is need, 4 bytes) the National code. By choosing CHAR or VARCHAR you are limiting your use case to one language in addition to English. This is then determined by collation.

So stored strings will look as 'This is a string' in CHAR or VARCHAR data type and then for a NVARCHAR or NCHAR data type an additional character is used to define the languages supported N'This is a string in Unicode'.

Under the hood VARCHAR and CHAR strings are stored in Windows-1252

Use cases generally, are for multi-language support otherwise no point in having the additional overhead of another byte of storage. I am currently working on a project where everything is being modified for a web application to support another language and this has resulted in modifying the SQL tables to use NVARCHAR and NCHAR data types.

Lorantz
  • 39
  • 4
-1

It can be a single-byte character set. Char and varchar allocate one byte per character with 2 additional bytes for varchar.

John Percival Hackworth
  • 11,395
  • 2
  • 29
  • 38
-1

here is a short difference between char/varchar:

    Char(4) takes 4b

    varchar(4) takes 6b

    or

    Char(40) takes 40b

    varchar(40) takes 6b

    or

    Char(400) takes 400b

    varchar(400) takes 6b
Jason Clark
  • 1,307
  • 6
  • 26
  • 51