char and nchar charactersets

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

it tells me that nchar and nvarchar can sto开发者_开发知识库re UNICODE. So what can be used in CHAR and VARCHAR?

---

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

---

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

---

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.

---

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.