Menu
What if I want to use database to store different groups of special characters, how do I choose which collation to use? For example, if I set collation to Croatian and want to use Russian cyrillic, japanese characters except croatian special characters - which collation should I use?
Thanks, Ilija
805
SQL Server installation configuration sets the default collation for the created instance (Latin1_General_CI_AI). New databases will be configured with the default collation of the SQL Server. Users can change the collation settings at the database level but not at the SQL Server level.
To view the collation setting of a database In 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 get the server collation in SQL Server Management Studio (SSMS) by right-clicking the SQL instance, then clicking the “Properties” option and checking the “General” tab. This collation is selected by default at the installation of SQL Server.
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.
That means you can store Croatian, Russian and Japanese in the same column.
But when you want to compare (WHERE MyColumn = @foo) or sort (ORDER BY MyColumn) you'll not get what you expect because of the collation.
However, you can use the COLLATE clause to change it if needed.
eg ORDER BY MyColumn COLLATE Japanese_something
I'd go for your most common option that covers most of your data. MSDN has this maybe useful article
104
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
