Sql Server Collation

Question

The book I am reading says that

SQL Server supports two kinds of character data types—regular and Unicode. Regular data types include CHAR and VARCHAR, and Unicode data types include NCHAR and NVARCHAR. The difference is that regular characters use one byte of storage for each character, while Unicode characters require two bytes per character. With one byte of storage per character, a choice of a regular character type for a column restricts you to only one language in addition to English because only 256 (2^8) different characters can be represented by a single byte.

What I came to know by this is, if I use Varchar then I can use only one language(For ex. Hindi, an Indian Language) along with English.
But When I run this

Create Table NameTable
(
    NameColumn varchar(MAX) COLLATE Indic_General_90_CI_AS_KS
)

It shows me error "Collation 'Indic_General_90_CI_AS_KS' is supported on Unicode data types only and cannot be applied to char, varchar or text data types."

So where have I misunderstood the author?
Thanks

Answer 1

You can find a list of collations here, along with the encoding type

---

Certain collations will apply only to 1-byte encodings -- 127 bits are used for normal ASCII and 128 are available for other characters -- hindi probably does not fit in 128 characters so a 1-byte collation does not apply to it.

You will have to use a nvarchar (or other 'n' prefixed character type).

-- edit --

French_CI_AS as a non-english example

One of the things collations enable is language and locale specific ordering of characters. Therefore French != latin.

Another example Arabic_CI_AS

This is a 1-byte encoding with the arabic alphabet.

Answer 2

you can use this name = N'مرحبا كيف حالك'