MS SQL Server

How to Use the SQL Server Collate Command

This guide will cover the fundamentals of working with SQL Server collate command. Before we get there, what is an SQL Server collation?

What Is SQL Server Collation?

SQL Server collation refers to a set of rules that govern how to sort and compare character data in a database. SQL Server provides a wide range of collations for handling character data. These collations can handle data with conflicting languages and regions, allowing a database to be compatible with applications worldwide.

SQL Server Collation Levels

In the SQL Server, there are three main levels where you can define the collations:

  1. SQL Server Instance Level
  2. Database Level
  3. Column Level

Note that a collation name can either be a Windows collation or SQL Server-provided collation name.

You can specify the collation type when creating a database. If not specified when creating a database, SQL Server will default to the collation used by the SQL Server instance.

Similarly, if you do not define the collation when creating a column, SQL Server will default to the collation used in that database.

SQL Server Instance Level

You can set your preferred collation for your SQL Server instance during installation. If you have the SQL Server already installed, you can use the Installation Center Wizard to redefine your collation type.

To view the current collation for your SQL Server instance, open the SQL Server Management Studio.

Right-click your SQL Server instance and select the Properties option:

In the Properties window, select the General’s tab on the left menu. This will show the general information about your SQL Server instance, including the default collation type:

In our example, the default collation is set to SQL_Latin1_General_CP1_C1_AS. This is a SQL Server collation rather than a Windows collation, as indicated by the SQL_ prefix.

The other part contains the name of the collation, in this case, Latin1_General_CP_AS. The value CI indicates the collation is case insensitive, while AS means it is accent sensitive.

To get a more detailed description of the SQL Server default collation, use the sp_helpsort procedure as shown:

EXEC sp_helpsort;

The procedure should return information as shown:

Server DEFAULT collation
-----------------------------------------------------------------------------------------------------------------
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive FOR Unicode DATA, SQL Server Sort ORDER 52 ON Code Page 1252 FOR non-Unicode DATA

Database Level Collation

We can define the collation at the database level. As mentioned, unless explicitly specified, a database will inherit the collation of the SQL Server instance.

To view the collation of a database in the SQL Server Management Studio (SSMS), right-click your target database and open the Properties window:

In the Properties window, select the General’s tab and navigate to the Maintenance Section. You should see the database collation listed:

In our case, the database inherits the same collation as the SQL Server instance.

Set Database Collation
To set your desired collation during the database creation, you can use the query as shown below:

CREATE DATABASE sample_database
COLLATE SQL_Latin1_General_CP1_CS_AS;

In the query above, we create a database with the collation SQL_Latin1_General_CP1_CS_AS. It is similar to the SQL_Latin1_General_CI_AI, except it is case and accent sensitive, as indicated by CS and AS in the collation name:

Set Collation in an Existing Database
SQL Server allows you to change the collation after creation using the ALTER DATABASE command.

For example, the following query changes the collation of the database from SQL_Latin1_General_CP1_CS_AS to SQL_Slovak_CP1250_CS_AS:

USE sample_database;
ALTER DATABASE sample_database COLLATE SQL_Slovak_CP1250_CS_AS;

Before changing the collation of the database, ensure all connections to the database are closed. Otherwise, the query will fail.

SQL Server Shows the Supported Collations
To view the supported collations for your SQL Server version, use the query as shown below:

SELECT name, description FROM sys.fn_helpcollations();

If you are using SQL Server 2019, we have provided a list of the supported collations. Download the file in the resource below:

Column Level Collation

In most cases, you will want a character column to inherit a similar collation as the database. However, you can specify the collation for a column explicitly during column creation.

Keep in mind that you can only define a column collation if the column is of type char such as:

  1. VARCHAR
  2. NVARCHAR
  3. CHAR
  4. NTEXT
  5. TEXT

To set a column using T-SQL, use the example query as shown:

CREATE TABLE info(
id INT,
text_ VARCHAR(50) COLLATE SQL_EBCDIC280_CP1_CS_AS
);

To view the collation of a column, you can use the sp_help procedure as shown below:

EXEC sp_help info;

The command should return info about the column, including the collation as:

Conclusion

In this article, we explored the concept of SQL Server collations, what they are and how we can view or change collations at various levels, such as the SQL Server Instance Level, Database Level, and Column Level. We hope you found this article helpful. Check out the other Linux Hint articles for more tips and tutorials.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list