Menu
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
224
Server-level collation for Microsoft SQL Server If you don't choose a different collation, the server-level collation defaults to SQL_Latin1_General_CP1_CI_AS. The server collation is applied by default to all databases and database objects. You can't change the collation when you restore from a DB snapshot.
The SQL_Latin1_General_CP1_CI_AS collation is a SQL collation and the rules around sorting data for unicode and non-unicode data are different. The Latin1_General_CI_AS collation is a Windows collation and the rules around sorting unicode and non-unicode data are the same.
The collate clause is used for case sensitive and case insensitive searches in the columns of the SQL server. There are two types of collate clause present: SQL_Latin1_General_CP1_CS_AS for case sensitive. SQL_Latin1_General_CP1_CI_AS for case insensitive.
To view the collation setting of a databaseIn Object Explorer, connect to an instance of the Database Engine and on the toolbar, click New Query. In the query window, enter the following statement that uses the sys. databases system catalog view. SELECT name, collation_name FROM sys.
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.
174
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
