Introduction

You must have already heard the term “Collation” in SQL Server. Collation is a configuration that determines how character data sorting is done. This is an important setting that has a huge impact on how the SQL Server database engine behaves in dealing with character data. In this article, we aim to discuss collations in general and show a few examples of dealing with collations.

Where do I find collations?

You can find SQL collation at the server, database, and column level. Another important thing to know is that the collation setting need not be the same at the server, database, and column level. Also, you can update your queries to use specific collations. It is at this time that you will realize the importance of configuring the correct collation across your environment as there is a high possibility of unexpected issues if the collation is not consistent.

What are the different types of collations available?

You can get the full list of available collations by querying the system function sys.fn_helpcollations()

select * from sys.fn_helpcollations()
Image for post
Image for post
select * from sys.fn_helpcollations()
where name like '%Maori%'
Image for post
Image for post

What are the different options you see in the collation name?

For example, in this collation: Maori_100_CS_AI_KS_WS_SC_UTF8, you can see the various options in the collation name.

Image for post
Image for post
select * from sample
where firstname like '%Adam%'

Finding collation on SQL Server instance

You can get the server collation in SQL Server Management Studio (SSMS) by right-clicking the SQL instance, then clicking the “Properties” option and checking the “General” tab. This collation is selected by default at the installation of SQL Server.

Image for post
Image for post
select SERVERPROPERTY('collation'),
Image for post
Image for post

Finding collation of a SQL database

In SSMS, right-click the SQL database and go to the “Properties”. You can check the collation details in the “General” tab as shown below.

Image for post
Image for post
select DATABASEPROPERTYEX('Your DB Name','collation')
Image for post
Image for post

Finding collation of a column in a table

In SSMS, go to the table, then columns, and finally right-click the individual columns to view the “Properties”. If the column is of a character data type, you will see details of the collation.

Image for post
Image for post
Image for post
Image for post
select sc.name, sc.collation_name from sys.columns sc
inner join sys.tables t on sc.object_id=t.object_id
where t.name='t1' – enter your table name
Image for post
Image for post

Trying out different collations in SQL queries

In this section, we will see how the sort order gets impacted when different collations are used in queries. A sample table is created with 2 columns as shown below.

Image for post
Image for post
insert into emp
values (1,'mohammed')
insert into emp
values (2,'moinudheen')
insert into emp
values (3,'Mohammed')
insert into emp
values (4,'Moinudheen')
insert into emp
values (5,'MOHAMMED')
insert into emp
values (6,'MOINUDHEEN')
select * from emp order by fname collate SQL_Latin1_General_CP1_CS_AS 
select * from emp order by fname collate SQL_Latin1_General_CP1_CI_AS – this is default
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

Checking if it is possible to change collation at the instance level

In this section, we will review different scenarios where we may have to change the default collations. You may encounter situations, when servers or databases get handed over to you and they may not be meeting your standard policies, so you may need to change the collation. The default SQL Server collation is SQL_Latin1_General_CP1_CI_AS. Changing the collation at the SQL instance level is not straight forward. It requires scripting out all the objects in the user databases, exporting the data, dropping the user databases, rebuilding the master database with the new collation, creating the user databases, and then importing all the data. So, if you are installing new SQL instances, just make sure you get the collation right the first time, otherwise, you may have to do a lot of unwanted work later on. Explaining in detail the stages for changing collation at the instance level is beyond the scope of this article due to the detailed steps required for each of the stages.

Changing collation at the database level

Luckily, changing the database level collation is not as hard as changing the instance collation. We can update the collation using both SSMS and T-SQL. In SSMS, just right-click the database, go to “Properties” and click the “Options” tab on the left side. There, you can view the option to change the collation in the drop-down menu.

Image for post
Image for post
Image for post
Image for post
USE master;  
GO
ALTER DATABASE mo
COLLATE SQL_Latin1_General_CP1_CS_AS;
GO
select * from emp order by fname collate SQL_Latin1_General_CP1_CS_AS 
select * from emp order by fname collate SQL_Latin1_General_CP1_CI_AS – - this is default
Image for post
Image for post

Changing collation at the column level

In the previous section, you noticed that even after changing the database level collation, the collation of the existing columns in the tables remains unchanged. In this section, we will see how we can change the collation of the existing columns in the tables to match that of the database collation. In the previous section, you changed the database collation to SQL_Latin1_General_CP1_CS_AS. Next, you want to identify all the columns in the user tables that do not match this database collation. You may use this script for identifying those columns.

select so.name TableName,sc.name ColumnName, sc.collation_name CollationName from
sys.objects so inner join sys.columns sc on so.object_id=sc.object_id
where sc.collation_name!='SQL_Latin1_General_CP1_CS_AS' and so.[type] ='U'
Image for post
Image for post
use mo
go
ALTER TABLE dbo.emp ALTER COLUMN fname
nvarchar(20) COLLATE SQL_Latin1_General_CP1_CS_AS NULL;
GO
select * from emp order by fname collate SQL_Latin1_General_CP1_CS_AS – - this is default
select * from emp order by fname collate SQL_Latin1_General_CP1_CI_AS
Image for post
Image for post

Can we change the collation of system databases?

Changing the collation of system databases is not possible. If you try to change the collation of the system databases — master, model, msdb or tempdb, you will get this error message.

Image for post
Image for post

The known issue about collation conflict

Another common issue you may find is the error related to collation conflict especially while using temporary objects.

Written by

Awesome blog focused on databases and Microsoft, .NET and cloud technologies. http://codingsight.com/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store