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

Computed Vs Persisted Column

Computed Columns

Tables in SQL Server 2005 can also have computed columns. A computed column is an expression that defines the value of the column. By default, data is not contained within the column, but instead it is a virtual column.

As an example, a parts table could have an InventoryCost column created as a computed column. The expression would be QuantityOnHand * Cost (assuming we have both these columns in the table).

Be aware that the computed columns can be included in SELECT lists, WHERE clauses, ORDER BY clauses, and more. Computed columns can’t have data inserted or updated via INSERT or UPDATE statements.

The following script would create the InventoryCost computed column within a table. The bolded line shows the computed column.

USE Chapter2; GO CREATE TABLE ComputedColumnTest (       QuantityOnHand int NULL,         Cost money NULL,         InventoryCost AS (QuantityOnHand * Cost) );

Persisted Column

A persisted column is a computed column that actually stores the computed data within the table, instead of computing the value of the data each time it is queried.

When data is inserted or updated into a table with a persisted computed column, SQL Server generates the value by using the formula of the computed column and storing the result with the rest of the table.

When should you use a persisted computed column over a nonpersisted computed column? Generally, simple operations or computations are quicker as nonpersisted computed columns. This is because processors and memory are quicker than disk I/O. For complex operations, a persisted computed column may be quicker. Additionally, a special case exists where you can use a persisted computed column to create an index. We’ll explain this later in the section.

The only change to the script is to add the word PERSISTED to the end of the table definition.

USE Chapter2; GO CREATE TABLE ComputedColumnTestPersisted (        ComputedColumnTestID int NOT NULL,        QuantityOnHand int NULL,        Cost money NULL,        InventoryCost AS (QuantityOnHand * Cost) PERSISTED, CONSTRAINT PK_ComputedColumnTest_ComputedColumnTestID PRIMARY KEY CLUSTERED (ComputedColumnTestID) );

Guidelines with Computed and Persisted Columns

Some general guidelines to consider when using both computed and persisted columns relate to their cost in terms of resource usage. The whole purpose of using these features is to improve the performance of the database, but occasionally they can have a detrimental effect.

Use a computed column when data is derived from other columns using simple mathematical calculations. This is especially true for tables that hold relatively static data.

• Avoid the use of complex calculations or functions in computed columns. The overhead cost of complex calculations in any computed columns could result in poorer performance of the table.

• Avoid the use of persisted columns on dynamic data.

  A table with frequent inserts, updates, and deletes within a table will cause significant overhead on a persisted computed column as the data is constantly recalculated and stored. Generally, frequently queried data perform better as a persisted computed column, while frequently updated data perform better as a nonpersisted computed column.

• Use error-checking to protect against mathematic errors.

  As an example, a check for zero or null data before performing a divide will avoid a divide by zero error.