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.
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.
See Also
Concepts
Setting and Changing the Server Collation
Setting and Changing the Column Collation
Setting Expression Collation
Identifier Collation