How Find SQL Server collation

How to Find SQL Server Collation

Collations in SQL Server determine the sorting rules, case sensitivity, and accent sensitivity for your data. They play a crucial role when working with character data types like char and varchar, as they define the code page and the corresponding characters that can be represented.

Case Sensitivity

Case sensitivity refers to whether uppercase and lowercase characters are treated as distinct. For example, if the letters "A" and "a" (or "B" and "b") are considered different, the collation is case-sensitive. Computers differentiate between these letters using their ASCII values: "A" has an ASCII value of 65, while "a" has an ASCII value of 97. Similarly, "B" is 66, and "b" is 98.

Accent Sensitivity

Accent sensitivity determines whether characters with accents are considered different from their non-accented counterparts. For example, if "a" and "á" or "o" and "ó" are treated as the same, the collation is accent-insensitive. However, when treated differently, it is accent-sensitive. This distinction is based on the ASCII values of characters: "a" has a value of 97, while "á" has a value of 225; "o" is 111, and "ó" is 243.

Kana Sensitivity

Kana sensitivity occurs when Japanese kana characters, Hiragana and Katakana, are treated as distinct. When these characters are considered different in sorting, it is referred to as kana-sensitive collation.

Width Sensitivity

Width sensitivity refers to how single-byte characters (half-width) and their double-byte counterparts (full-width) are treated. When these characters are considered distinct, the collation is width-sensitive.

SQL Queries for Collation Insights

Here are a few SQL queries you can use to inspect and work with collations in SQL Server:

1. View Columns with Collations:

   This query retrieves the schema, table, column names, and their respective collation names:

   
   SELECT s.name AS 'Schema_Name', t.name AS Table_Name,
          c.name AS Column_Name, c.collation_name AS Collation
   FROM sys.schemas s
   INNER JOIN sys.tables t ON t.schema_id = s.schema_id
   INNER JOIN sys.columns c ON c.object_id = t.object_id
   WHERE collation_name IS NOT NULL
   ORDER BY Column_Name;
       

2. List All Supported Collations:

   You can use the following query to get a list of all supported collations:

   
   SELECT name, description FROM fn_helpcollations();
       

3. List All Databases with Collation Names:

   This query lists all databases along with their collation names:

   
   SELECT name, collation_name FROM sys.databases;
       

4. Find Server-Level Collation:

   To find the collation for the SQL Server instance, run the following query:

   
   SELECT @@servername AS server_name, 
          SERVERPROPERTY('Collation') AS Collation;
       

5. Comparing Different Collation Strings:

   If you need to compare columns with different collation settings, you can use the COLLATE clause as shown here:

   
   SELECT *
   FROM TABLE1
   INNER JOIN TABLE2 
     ON TABLE1.Col1 COLLATE Latin1_General_CS_AS = 
        TABLE2.Col1 COLLATE Latin1_General_CS_AS;
       

These queries can help you manage and investigate collation settings, ensuring your database handles string data according to the desired rules for case sensitivity, accent sensitivity, kana sensitivity, and width sensitivity.