Microsoft® SQL Server® 2012 Bible

136

Part II: Building Databases and Working with Data

kanatype-insensitive, width-sensitive

...
SQL_Latin1_General_CP1_CI_AI
Latin1-General,case-insensitive,
accent-insensitive,
kanatype-insensitive, width-insensitive
for Unicode Data, SQL Server Sort Order
54 on Code Page 1252 for non-Unicode
Data
...

The following query reports the current server collation:

SELECT SERVERPROPERTY('Collation') AS ServerCollation;

Result:

ServerCollation

------------------------

SQL_Latin1_General_CP1_CI_AS

Although the server collation setting is determined during setup, you can set the colla-

tion property for a database or a column using the COLLATE keyword. The following code

changes the AdventureWorks database collation so that it becomes case-sensitive:

CREATE DATABASE CollateChange

GO

ALTER DATABASE CollateChange

COLLATE SQL_Latin1_General_CP1_CS_AS;

GO

SELECT DATABASEPROPERTYEX('CollateChange','Collation')

AS DatabaseCollation;

Result:

DatabaseCollation

----------------------------------

SQL_Latin1_General_CP1_CS_AS

Not only can SQL Server set the collation at the server, database, and column levels, colla-

tion can even be set at the individual query level. The following query sorts according to

the Danish collation, without regard to case or accents:

Use AdventureWorks

SELECT *

FROM Production.Product

ORDER BY Name

COLLATE Danish_Norwegian_CI_AI;

Not all queries need to be sorted, but for those that do, the ORDER BY clause combined

with the many possible collations yields tremendous fl exibility in sorting the result set.

c06.indd 136c06.indd 136 7/30/2012 4:16:07 PM7/30/2012 4:16:07 PM

http://www.it-ebooks.info