Sorted data in tables in SQL Server using COLLATE clause

The COLLATE is a clause apply to the database, column or string expression, set collation for data.

The COLLATE clause inherit the collation of the current database.
The COLLATE clause can be specified at several levels and may be use in:
– creating or alter database
– creating or alter table column
– casting the character expression

Objects of instance-level, such as logins, databases have the default collation of the instance.
Objects within a database, such as tables, views, column have the default collation of the database.

The COLLATE clause can be applied only for character data types: char, varchar, nchar, nvarchar, text, ntext.

Check your collation for Your server and instance.
Right click the instance node in Object Explorer window. Choose Properties from the list.
zz1
In Properties window You see name of collation.
zz3

You may execute the system function fn_helpcollations to retrieve a list of all the valid collation names for Windows collations and SQL Server collations:

SELECT name, description
FROM fn_helpcollations();

zz4

Name of collation may contain frase:

BIN - binary sort
BIN2 - binary code point sort

In BIN collation only the first character is sorted according to the code point, remaining characters are sorted according to their byte values. In BIN2 collation all characters are sorted according to their code points.
CI - case insensitive
CS - case sensitive

In CS lowercase letters are first in results list, after them their uppercase versions(a,A,b,B)
AI - accent insensitive
AS - accent sensitive

In AS unaccented letters are first in results list, after them their accented versions(a,á)
KS - kanatype sensitive
In KS are sorted two types of Japanese kana characters: Hiragana and Katakana
WS - width sensitive
In WS are sorted  full-width and half-width representation of the same character
SC - supplementary characters

On my server the default collation is Polish_CI_AS.
So data is sorted in case insensitive and accent sensitive. It is my table person:
vv1
And my sorted data in person table by name column  with CI and AS:

SELECT[id],[name]
FROM [BazaTest].[dbo].[person]
ORDER BY name

vv2

CS and AS :

SELECT[id],[name]
FROM [BazaTest].[dbo].[person]
ORDER BY name COLLATE Polish_CS_AS

vv3

CS and AI :

SELECT[id],[name]
FROM [BazaTest].[dbo].[person]
ORDER BY name COLLATE Polish_CS_AI

vv5

CI and AI :

SELECT[id],[name]
FROM [BazaTest].[dbo].[person]
ORDER BY name COLLATE Polish_CI_AI

vv6

BIN :

SELECT[id],[name]
FROM [BazaTest].[dbo].[person]
ORDER BY name COLLATE Polish_BIN

vv7

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.