SQL Server Collation Settings Instance, Database and Column Levels

By: Manvendra Singh | Updated: 2022-07-25 | Comments | Related: > Database Configurations

Problem

This article will explain the steps to get various properties for collation settings in the Microsoft SQL Server database engine.

Solution

Collation is a setting used in SQL Server to define sorting rules, case, and accent properties to store data. SQL Server supports storing objects with different collation settings in user databases. Collation can be set at various levels like SQL Server instance level, database level, and column level. We can also use distinct collations to return the desired result as per the specified collation at the expression level. Although you can easily change the collation at the database level, at the instance level it is quite a tedious process. Do this, you would need to export all data and rebuild the master database, which is the main system database, with the new collation name.

You must be aware that changing collation will change the behavior, nature, and characteristics of the data and its accessibility so you should carefully consider all aspects if you want to change the collation of any column, database, or SQL Server instance.

Finding Current Collation Settings

You can run these commands in SQL Server Management Studio to see the current collation settings.

SQL Server Instance Level

This T-SQL command will return the collation setting for the SQL Server instance.

SELECT CONVERT(sysname, serverproperty(N'collation')) AS [Collation]

SQL Server Database Level

This T-SQL command will return a list of all databases and the collation settings.

SELECT name, collation_name FROM master.sys.databases

SQL Server Column Level

This T-SQL command will provide a list of all table attributes and the collation for each column. For this example we are looking at table "master.sys.databases".

sp_help 'master.sys.databases'

Using COLLATIONPROPERTY to get Database Collation Information

There are some properties of collation like code page, version, LCID, etc. If you want to get the properties of the collation, then you should use the SQL Server system function COLLATIONPROPERTY. We can use this system function to get the property details of any specified collation. We need to specify the name of collation and the name of property to get its result set. If you do not specify a collation or specify a NULL value then result will also return as NULL.

The syntax to use this function is below:

COLLATIONPROPERTY( collation_name , property )

Collation_name is the name of a specific collation. You can get a list of all collations supported by SQL Server by using another system function sys.fn_helpcollations which I will explain later in this article.
Property is the name of the property for which you want to return the result. Below is a list of collation properties that can be returned using this function.
- CodePage is the non-Unicode page of the collation
- ComparisonStyle is the Windows comparison style of the collation
- LCID is the Windows locale ID of the collation
- Version this shows the value of the collation version. You might get a value from 0 to 3. Output can be correlated based on the below rules. If your collation name has a "140" number in it, then the version will return 3. Similarly, the output will return 2 for collations with "100" in the name and it will return 1 for collations with "90" in the name. All other collations will return 0.

List Collation Properties

This SQL query returns the database server name, server level collation, and all the properties of the collation.

SELECT SERVERPROPERTY ('SERVERNAME') AS [DB SERVER Name],
       SERVERPROPERTY ('Collation') AS [Collation],
       COLLATIONPROPERTY ('SQL_Latin1_General_CP1_CI_AS', 'CodePage') AS [CodePage],
       COLLATIONPROPERTY ('SQL_Latin1_General_CP1_CI_AS', 'ComparisonStyle') AS [ComparisonStyle],
       COLLATIONPROPERTY ('SQL_Latin1_General_CP1_CI_AS', 'LCID') AS [LCID],
       COLLATIONPROPERTY ('SQL_Latin1_General_CP1_CI_AS', 'Version') AS [Collation Version]

The output of the above SQL query is shown below. The collation version is 0, because the collation name does have 90, 100 or 140 values in the name.

Use COLLATIONPROPERTY with NULL

COLLATIONPROPERTY returns NULL if you specify a NULL value for the collation name or you do not specify the collation.

SELECT COLLATIONPROPERTY (NULL, 'Version'),
       COLLATIONPROPERTY (NULL, 'CodePage'),
       COLLATIONPROPERTY ('', 'Version'),
       COLLATIONPROPERTY ('', 'CodePage')

The output of the above statement returns NULL for all of these settings.

List all collations using sys.fn_helpcollations

If you are not sure about collation names then you can use sys.fn_helpcollations.

Below is a SQL query to show a list of collation names in which the word "SQL_Latin1" is in the name.

SELECT * 
FROM   sys.fn_helpcollations()
WHERE  name LIKE '%SQL_Latin1%'
GO

Here is the output of the above SQL query and we can see all collations that have "SQL_Latin1" in the name.

Now, you can use a collation from the above list and get its properties using the COLLATIONPROPERTY system function as follows.

SELECT COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AI', 'Version') AS [Collation Version]

Here is the output:

Use SQL function COLLATIONPROPERTY with sys.fn_helpcollations

We can also use both functions COLLATIONPROPERTY and sys.fn_helpcollations together to get useful information. Suppose I want to return all collation names which are on version 1, which means all these collations have "90" in their name. As I have stated above, the version should be 1 if there is "90" showing in the collation name, so we will pass a value of 1 in the WHERE clause.

SELECT name, description
FROM   sys.fn_helpcollations()  
WHERE  COLLATIONPROPERTY(name, 'Version') = 1;

The query returns all collation names that have a collation version of 1.

Similarly, we can get a list of collations based on other properties. Let’s take another example where I have listed all collations that have LCID equal to 1033.

SELECT name, description
FROM   sys.fn_helpcollations()  
WHERE  COLLATIONPROPERTY(name, 'LCID') = 1033;

Here are the results.

Next Steps

This article has explained 2 system functions based on SQL Server collation. I have also explained how to get a list of all collation names or a list of all collation names based on a specific property with the help of these 2 system functions. Please share your feedback in the comments section below.

About the author

Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-07-25

SQL Server Collation Properties using the COLLATIONPROPERTY Function