Configuring SQL Server 2012 Collation

 

Collations allows for rule sorting, case sorting, and accent sensitivity of your data. Collation is very important and you must take it into consideration when installing a new SQL 2012 server, mounting databases and restoring database from backup. Locale requirements may be different for each database/server. Each character in data set is specified by the collation. SQL Server 2012 allows storing objects that have different collations within a database. Various collation settings can have a different effect on transactSQL queries. MS recommends that you use a single collation within your company.

Windows collations dictate set of rule for saving character data based on windows locale. It specifies how sorting is applied and which alphabet or language is used. Note that string comparisons for sorting in Unicode and non-unicode is compatible. Binary collations sort depending on the order of coded values that are set by the data and locale type. Data comparisons are based upon the code points that are in set ansi code page for non-uncode type data. Collations can be configured at three different levels:

Server Collations
The collation set during the installation automatically set the server collation. Keep in mind that Unicode-only collation is not supported at the server level, hence you cannot select it during installation. If you need to change the server collation installation, you must export all current databases and rebuild the master db. An easier route may be to set the collation at the DB level as explained below.

Database Collations
You may override the server collations by using the collate option when running “create database” sql statement. This is helpful if your new database requires a different collation than the server default. Note that “alter database” allow you to change collations on newly created databases but not on the system database.

Column and Expression Collations
Collations can also be set at the column level by using the COLLATE clause. Expression collations are set dynamically when sql statement is run and returned results are affected.

 

How to Change Server Collation

  1. Export all of your database and unmounts all use databases.
  2. Rerun setup and rebuild the master database by using below command:
    Setup /ACTION=REBUILDDATABASE /INSTANCENAME=Instance /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= Password ] /SQLCOLLATION=Collation
  3. Reimport all your data.

 

 

How to Change Database Collation

  1. Run below command to set collation for newly created database:
    CREATE DATABASE database_name COLLATE collation_name
  2. Run below command to set collation for existing database:
    ALTER DATABASE database_name COLLATE collation_name