Collations are tricky.
There is a default collation for your server/instance. There is a default collation for each database, and there is a collation for each column of each table that is varchar or nvarchar (including the old text and ntext).
Code:
-- Server Collation
Select SERVERPROPERTY('Collation')
-- Database Collation
Select collation_name From sys.databases Where database_id = DB_ID()
-- Column Collation
Select Distinct collation_name From sys.columns where collation_name > ''
Server collations are used when creating a new database. If you do not specify the collation for a new database, it will take the server's collation.
Database collations are used when creating new columns that are strings (char, nchar, varchar, nvarchar, text, ntext).
From my experience, the collation within a database is usually consistent. Meaning... the database default collation usually matches the column collations.
Unfortunately, changing all of this may not solve the real problem. The typical problem with collations usually involves temp tables and table variables. You see, when you create a temp table or table variable, it uses the collation of TempDB's default collation (unless you specify otherwise). The typical error is something like, "Cannot resolve collation conflict"
Mismatched collations are not easy to deal with.
I wrote a free SQL Server utility several years ago that can detect this problem. There are actually 2 checks. One checks for mismatched collations between the user database and tempdb. The other checks for mismatches between the default collation for the database and all of the string columns.
SQLCop is a free utility and can be downloaded here:
-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
