TIP - How to find out the collation in SQL Server 2005?

In SQL Server 2005, you can set collations at the following levels:

- Server Level

o You set the collation of the server at the time of setup.

o The only way to change that is to re-install SQL Server with the right collation.

o To find out the existing collation of your server, you can use:

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

SELECT * from ::fn_helpcollations()

- Database Level

o This is specified at the time of database creation using the COLLATE clause

o You can easily change the database level collation by using the ALTER DATABASE statement

o You can find out the database collation as follows:

SELECT CONVERT (varchar, DATABASEPROPERTYEX('database_name','collation'))

- Column Level

 

o You can specify this at column creation using the COLLATE clause of the CREATE TABLE statement.

o It can easily be altered by using the ALTER TABLE statement

o To find out the column collation, you can use sp_help on the table to find out the collation of each column.

Collation could also be specified at the Expression that you are trying to evaluate.