char and nchar charactersets

Question

Ok, referencing What is the difference between char, nchar, varchar, and nvarchar in SQL Server?

it tells me that nchar and nvarchar can store UNICODE. So what can be used in CHAR and VARCHAR?

For `CHAR` and `VARCHAR` it depends upon the collation and code page what can be stored. Each byte can only represent one of 256 possible characters and it depends what characters are available in that collation. — Martin Smith, Mar 02 '11 at 20:12
I fixed the script in my answer. It now should give correct results! — Martin Smith, Mar 03 '11 at 13:16

score 1 · Accepted Answer · answered Mar 02 '11 at 22:19

1

nchar and nvarchar store UCS-2 characters (2 bytes per character, so not UTF-16 as many people assume)

char and varchar store ASCII characters where the supported codepage is controlled by the selected collation for the columns (or database/server collation for variables and parameters). More info on the collations can be found on msdn

answered Mar 02 '11 at 22:19

Filip De Vos

11,568
1
48
60

how do i find out? is this local specific? – Luke Mar 02 '11 at 22:45

Martin Smith · Answer 2 · 2011-03-03T12:19:13.763

In answer to the question "How can I find this out"

SET NOCOUNT ON;

IF DB_ID('Collations') IS NULL
    CREATE DATABASE Collations
GO

USE Collations

IF OBJECT_ID('Collations') IS NOT NULL
    DROP TABLE Collations

CREATE TABLE Collations
  (
     code TINYINT IDENTITY(0, 1)
  )

GO

INSERT INTO Collations
DEFAULT VALUES

GO 256

DECLARE @AlterScript NVARCHAR(MAX) = ''

SELECT @AlterScript = @AlterScript + ' 
RAISERROR(''Processing: ' + name + ''',0,1) WITH NOWAIT;
ALTER DATABASE [Collations] COLLATE ' + name + ';
ALTER TABLE Collations ADD ' + name + ' CHAR(1) COLLATE ' + name + ';
EXEC(''UPDATE Collations SET ' + name + '=CHAR(code)'');

'
FROM   sys.fn_helpcollations()
WHERE  name LIKE '%CS_AS'
       AND name NOT IN    /*Unicode Only Collations*/
                        ( 'Assamese_100_CS_AS', 'Bengali_100_CS_AS',
                         'Divehi_90_CS_AS', 'Divehi_100_CS_AS' ,
                         'Indic_General_90_CS_AS', 'Indic_General_100_CS_AS',
                             'Khmer_100_CS_AS', 'Lao_100_CS_AS',
                         'Maltese_100_CS_AS', 'Maori_100_CS_AS',
                         'Nepali_100_CS_AS', 'Pashto_100_CS_AS',
                         'Syriac_90_CS_AS', 'Syriac_100_CS_AS',
                         'Tibetan_100_CS_AS' )


EXEC (@AlterScript)


SELECT *
FROM   Collations

great code. But how do I find out which of them is used in char and nchar. I now found that in vs this stuff is in the Table Designer of the column. But, there is database default. So now i now, i can switch it, but what is the database default. Never mind. It´s not as important. I now know what i needed to know. — Luke, Mar 04 '11 at 15:32

score 0 · Answer 3 · answered Mar 02 '11 at 14:18

0

The difference is that nchar is used to store unicode data, allowing you to store multilingual data in your database tables. Languages that have an alphabet that can not be represented using the ASCII character set have an extended set of character codes that need to be saved and this datatype allows for this extension.

answered Mar 02 '11 at 14:18

Pooli

503
5
14

this does not answer the question, which is WHAT ENCODING TYPE IS CHAR OR VARCHAR – Luke Mar 02 '11 at 14:31

score 0 · Answer 4 · answered Mar 02 '11 at 14:20

0

NCHAR and NVARCHAR use 2 bytes per character for storage and have a limit of 4000 characters. CHAR and VARCHAR use one byte, and have a limit of 8000 characters.

If your application is English ONLY, or is in a language that does not require Unicode extended characters, you can use CHAR or VARCHAR.

answered Mar 02 '11 at 14:20

BJ Safdie

3,399
23
23

this does not answer the question, which is WHAT ENCODING TYPE IS CHAR OR VARCHAR – Luke Mar 02 '11 at 14:32
1

ASCII encoding. Your question was "What can be used in..." It was not clear that you were asking for the encoding. – BJ Safdie Mar 02 '11 at 15:02

char and nchar charactersets

4 Answers4