Collation is an important aspect of SQL Server that determines how string comparison and sorting operations are performed. It is essential to know the collation used by a SQL Server instance and database to ensure proper data manipulation and retrieval. In this article, we will explore different methods to identify the collation in SQL Server.

Identify SQL Server Database Collation using T-SQL

To identify the collation used by a specific database, you can execute the following T-SQL script:

DECLARE @DatabaseName as SYSNAME
SET @DatabaseName = 'AdventureWorks'

SELECT 
 DB_NAME(DB_ID(@DatabaseName)) AS DatabaseName,
 DATABASEPROPERTYEX(@DatabaseName, 'Collation') AS CollationUsedBySQLServerDatabase

This script will display the name of the database and the collation used by it.

If you want to view the collation for all databases on the SQL Server instance, you can use the following T-SQL script:

USE Master

SELECT
 NAME, 
 COLLATION_NAME
FROM sys.Databases
 ORDER BY DATABASE_ID ASC

This script will list the name of each database along with its corresponding collation.

Additionally, you can retrieve a list of all collations supported by SQL Server 2005 and above versions using the following T-SQL script:

SELECT * FROM fn_helpcollations()

This script will provide a comprehensive list of all collations supported by SQL Server.

Identify SQL Server Database Collation using SQL Server Management Studio

If you prefer using SQL Server Management Studio (SSMS) to identify the collation, follow these steps:

1. Connect to the SQL Server database instance using SSMS.
2. In Object Explorer, navigate to root | Databases | AdventureWorks.
3. Right-click on the AdventureWorks database and select “Properties” from the drop-down list.
4. In the Database Properties window, under the Maintenance section, you will find the collation used by the database.

Identify SQL Server Instance Collation using T-SQL

To identify the collation used by the SQL Server instance, execute the following T-SQL script:

USE Master

SELECT SERVERPROPERTY('collation') AS SQLServerCollation

This script will display the collation used by the SQL Server instance.

Identify SQL Server Instance Collation using SQL Server Management Studio

If you prefer using SQL Server Management Studio (SSMS) to identify the collation of the SQL Server instance, follow these steps:

1. Connect to the SQL Server database instance using SSMS.
2. In Object Explorer, right-click on the SQL Server instance and select “Properties” from the drop-down list.
3. In the Server Properties window, the collation used by the instance will be displayed.

By following these methods, you can easily identify the collation used by a SQL Server instance and database. Understanding the collation is crucial for efficient data manipulation and retrieval in SQL Server.