Collations

Collations

Collations are simply the rules used to store and sort data. Different languages follow different sorting rules. Microsoft is an international company, so SQL Server can be found in just about any country.

The default collation is determined by the Windows Locale in the operating system. If you installed SQL Server on a system that was installed in the United States, it would default to a Latin1_General collation, and data would be interpreted and displayed using the 1252 (Latin1 ANSI) code page.

Collations can be set at the server level so the same collation applies to all databases. This is the default. If you have a database that needs to be stored with a different language, you can set the collation on the database level. You can also set the collation on individual columns and even override the collation in queries.

If data is to be stored within a column in a different collation than the database, they should be defined as a Unicode column. While storing the data as Unicode data will double the storage space, it ensures that data can be moved between columns seamlessly.

Imagine a company that regularly does business with Spanish-speaking countries. Most of the data is stored in Latin1_Gerneral_CI_AS collation (Case Insensitive and Ascending based on the Latin1_General collation). However, occasionally employee listings must be created using the Spanish collation.

Instead of re-creating the entire database in a different collation, a computed column could be created with the Modern_Spanish_CI_AS collation (Case Insensitive and Ascending based on the Modern_Spanish collation).

The following script shows how computed columns could be created on the EmployeesTSQL table with different collations created on the computed columns.

Use Chapter2; 

Go 

ALTER TABLE EmployeesTSQL 

ADD FNameSpanish AS (FName) COLLATE Modern_Spanish_CI_AS;  

ALTER TABLE EmployeesTSQL ADD LNameSpanish AS (LName) COLLATE Modern_Spanish_CI_AS;

Computed columns can be created with a different collation. This will allow the data to be easily displayed differently without adding the additional storage.

To view the data in the different collation, we could use a simple SELECT, similar to the following. To allow you to see some data, use the INSERT statements first.
USE Chapter2; Go INSERT into EmployeesTSQL (EmpId,LName,FName,HireDate,Phone,Salary) VALUES(1,'Grzena','Dawn','Apr 14, 1974', '(757)555-5555',180000); INSERT into EmployeesTSQL (EmpId,LName,FName,HireDate,Phone,Salary) VALUES (2,'Villegas','Nimfa','Mar 31, 1962', '(757)555-4444',180000); GO SELECT FNameSpanish, LNameSpanish FROM EmployeesTSQL;

Additionally, if we needed to optimize the query to sort on the LNameSpanish column, we could create an index on the computed column.

Ruby