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.
In Properties
window You see name of collation.
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();
Name of collation may contain frase:
BIN - binary sort
BIN2 - binary code point sortIn 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 sensitiveIn CS lowercase letters are first in results list, after them their uppercase versions(a,A,b,B)
AI - accent insensitive
AS - accent sensitiveIn 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 sensitiveIn 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
:
And my sorted data in person table by name
column with CI and AS
:
SELECT[id],[name] FROM [BazaTest].[dbo].[person] ORDER BY name
CS and AS
:
SELECT[id],[name] FROM [BazaTest].[dbo].[person] ORDER BY name COLLATE Polish_CS_AS
CS and AI
:
SELECT[id],[name] FROM [BazaTest].[dbo].[person] ORDER BY name COLLATE Polish_CS_AI
CI and AI
:
SELECT[id],[name] FROM [BazaTest].[dbo].[person] ORDER BY name COLLATE Polish_CI_AI
BIN
:
SELECT[id],[name] FROM [BazaTest].[dbo].[person] ORDER BY name COLLATE Polish_BIN