DBA Posts - What is Collation? What Collation will you pick while installing SQL Server?

A collation in SQL Server is set of rules that govern the proper use of characters and alphabets of any language (Polish, Greek) that you select during installation or while creating the database in existing sql server. Few example of Collations used in SQL Server are given below  

a)      SQL_Latin1_General_CP1_CI_AS

b)      SQL_Latin1_General_CP1250_CI_AS

c)      SQL_Latin1_General_CP1256_CI_AS
Why collation is important in SQL Server?
Since collation reflects and follows the rules depending upon the language, region and sorting pattern, it is very important to select right collation in SQL Server. I usually divide collation in two basic configuration parts, and you will see why I have divided it in two parts. 

1-      SQL Server default Collation

2-      SQL Server Database Collation

 When you install SQL Server default or Named instance, during installation, you can select          appropriate collation that would be SQL Server default collation. When I say SQL Server default collation, it means whenever you will create any database with default setting on existing sql server, it will default that database’s collation to SQL Server default collation also known as model database collation (in some communities) as all the databases are created based on model database settings in SQL Server.

How can you find SQL Server Default Collation?

      Method 1.

a)      Connect to SQL Server database engine

b)      Right click on the server and go to properties

c)      Find the column showing Server Collation

Fig 1- How to find default collation of an existing SQL Server
Fig 2- How to find default collation of an existing SQL Server

     Method 2.

Run the below T-SQL command in master database
        SELECT CONVERT (varchar, SERVERPROPERTY('collation'));




How to find Databases collation?

    Method 1.

 Run the below T-SQL command in master database
         SELECT name, collation_name FROM sys.databases


    Method 2.

a) Right click on the database
b) Go to properties of the database
c) Click on option
d) First column reflects the current collation of database.
Note: If database collation is not set while creating the database, it will be the server default collaction, collation of databases can be changed after you create a database using above method as well.

2 comments: