Setting and Changing the Database Collation
When you create a new database, you can specify a collation by using one of the following:
The COLLATE clause of the CREATE DATABASE statement.
SQL Server Management Studio.
The Database.Collation property in SQL Server Management Objects (SMO).
If no collation is specified, the server collation is used.
Note
Windows Unicode-only collations can only be used with the COLLATE clause to apply collations to the nchar, nvarchar, and ntext data types on column level and expression-level data; they cannot be used with the COLLATE clause to change the collation of a database or server instance.
You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.
When you change the database collation, you change the following:
The default collation for the database. This new default collation is applied to all columns, user-defined data types, variables, and parameters subsequently created in the database. It is also used when resolving the object identifiers specified in SQL statements against the objects defined in the database.
Any char, varchar, text, nchar, nvarchar, or ntext columns in system tables are changed to the new collation.
All existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values for stored procedures and user-defined functions are changed to the new collation.
The char, varchar, text, nchar, nvarchar, or ntext system data types, and all user-defined data types based on these system data types, are changed to the new default collation.