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 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:

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