Set or Change the Database Collation

  • Article

This topic describes how set and change the database collation in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. If no collation is specified, the server collation is used.

In This Topic

  • Before you begin:

    Limitations and Restrictions

    Recommendations

    Security

  • To set or change the database collation, using:

    SQL Server Management Studio

    Transact-SQL

Before You Begin

Limitations and Restrictions

  • 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.

  • If the specified collation or the collation used by the referenced object uses a code page that is not supported by Windows, the Database Engine displays an error.

Recommendations

  • You can find the supported collation names in Windows Collation Name (Transact-SQL) and SQL Server Collation Name (Transact-SQL); or you can use the sys.fn_helpcollations (Transact-SQL) system function.

  • When you change the database collation, you change the following:

    • 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.

  • 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.

Security

Permissions

  • CREATE DATABASE
    Requires CREATE DATABASE permission in the master database, or requires CREATE ANY DATABASE, or ALTER ANY DATABASE permission.

  • ALTER DATABASE
    Requires ALTER permission on the database.

Arrow icon used with Back to Top link [Top]

Using SQL Server Management Studio

To set or change the database collation

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine, expand that instance, and then expand Databases.

  2. If you are creating a new database, right-click Databases and then click New Database. If you do not want the default collation, click the Options page, and select a collation from the Collation drop-down list.

    Alternatively, if the database already exists, right-click the database that you want and click Properties. Click the Options page, and select a collation from the Collation drop-down list.

  3. After you are finished, click OK.

Arrow icon used with Back to Top link [Top]

Using Transact-SQL

To set the database collation

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute. This example shows how to use the COLLATE clause to specify a collation name. The example creates the database MyOptionsTest that uses the Latin1_General_100_CS_AS_SC collation. After you create the database, execute the SELECT statement to verify the setting.

USE master;
GO
IF DB_ID (N'MyOptionsTest') IS NOT NULL
DROP DATABASE MyOptionsTest;
GO
CREATE DATABASE MyOptionsTest
COLLATE Latin1_General_100_CS_AS_SC;
GO

--Verify the collation setting.
SELECT name, collation_name
FROM sys.databases
WHERE name = N'MyOptionsTest';
GO

To change the database collation

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute. This example shows how to use the COLLATE clause in an ALTER DATABASE statement to change the collation name. Execute the SELECT statement to verify the change.

USE master;
GO
ALTER DATABASE MyOptionsTest
COLLATE French_CI_AS ;
GO

--Verify the collation setting.
SELECT name, collation_name
FROM sys.databases
WHERE name = N'MyOptionsTest';
GO

Arrow icon used with Back to Top link [Top]

See Also

Reference

sys.fn_helpcollations (Transact-SQL)

sys.databases (Transact-SQL)

SQL Server Collation Name (Transact-SQL)

Windows Collation Name (Transact-SQL)

COLLATE (Transact-SQL)

Collation Precedence (Transact-SQL)

CREATE TABLE (Transact-SQL)

CREATE DATABASE (Transact-SQL)

ALTER TABLE (Transact-SQL)

ALTER DATABASE (Transact-SQL)

Concepts

Collation and Unicode Support