Collation levels and default collation

Collation levels and default collation:

Collation can be set at 4 levels in SQL Server:
1.       Server
2.       Database
3.       Columns
4.       Expression

But collations are compared either at columns level or in expressions. So I am starting from lower to upper levels. The collations of upper two levels (server and database) are only helpful in providing default collation to columns and expressions.
Expression collation: All literals, variables and parameters and functions without any input parameters, by default get the collation of database.

Column collation: When we create a table we can specify a collation explicitly for all character data type (char, nchar, varchar, nvarchar, text, ntext) columns. If not specified then collation of database would be the default collation of a column. The collation of a column can be changed by using an ALTER TABLE statement similar to the following:

ALTER TABLE TestTab ALTER COLUMN CharCol CHAR(10) COLLATE Greek_CS_AI

Database collation: When we create a new database we can specify collation. If not specify then collation of model database is assigned as default collation. To know the collation of database use below statement:

SELECT DATABASEPROPERTYEX('testDB', 'Collation') SQLCollation

Database collation can be changed using the ALTER DATABASE statement as below.:

ALTER DATABASE myDB COLLATE Greek_CS_AI

When collation of database is changed it does not automatically change the collation of all columns of all tables. But the new collation would be the default collation for new columns created hence forth and for expressions.

Server collation: This is set during SQL Server installation. It’s the default collation for system databases. Because this is the collation of model database so this would be the default collation for all new databases that would be created on this server. The server collation can not be changed. To query the server collation use the below statement:

SELECT CONVERT (varchar, SERVERPROPERTY('collation'))