SQL Collation related

Question

Dear All,
In the Azure SQL , i am trying to write the joins based on some tables where it says below error.

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

As of my understanding , collation is DB Level or Server level property , but not at the column level which we can change.

--> In the table i have created for some columns it says the collation as SQL_Latin1_General_CP1_CI_AS", but for some columns it says NULL as Collation. is this possible?

Please share your thoughts around to handle this error from table level , instead of converting the collations while joining the tables.

As an alternative, i tried to convert the collation while joining the table, it works fine.. but wanted to understand why a table has NULL as a collation for some columns.

Note : Same error is not coming in On-Prem SQL server. Its in Azure SQL DB only.

Many thanks in advance.

Answer 1

Hi,

As of my understanding , collation is DB Level or Server level property , but not at the column level which we can change.

You are totally wrong

A COLLATE can be configure in the instance level, DB level, Column Level, and query level.

In fact, a good architect which worked well with global databases will probably make sure to configure the COLUMN COLLATE in many cases, in order to prevent issues.

For full information you can check the documentation regarding ALTER TABLE or regarding CREATE TABLE. Check the options of using COLLATE for the column configuration

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699

A simple example:

CREATE TABLE MyTbl (  
    id int,  
    txt VARCHAR(100) COLLATE Latin1_General_BIN2  
)  
GO  

but for some columns it says NULL as Collation. is this possible?

Obviously is this is what you get then the answer is yes. It's a fact if you get it :-)

The explanation is that not all data type has a COLLATE for example INT will show null in the COLLATE name

use tempdb  
GO  
  
DROP TABLE IF EXISTS MyTbl  
GO  
  
CREATE TABLE MyTbl (  
    id int,  
    txt VARCHAR(100) COLLATE Latin1_General_BIN2  
)  
GO  
  
SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME, COLUMN_NAME, COLLATION_NAME  
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = 'MyTbl'  
GO  

If you want some more more focused explanation regarding your specific case and you cannot teach us to read minds, then please provide a fully reproducible information, starting with queries to create the table and insert a few rows and information about your database COLLATE and the table column collate

Answer 2

As an alternative, i tried to convert the collation while joining the table, it works fine.. but wanted to understand why a table has NULL as a collation for some columns.

That is because they are not string columns. Only columns of the types char, varchar, nchar, nvarchar (and the deprecated data types text and ntext) have a collation. Whereas on the other hand numeric columns, date/time columns etc do not have a collation.

Collations can be set on four levels in SQL Server:

• Server level
• Database level (default taken from server).
• Column level (default taken from database)
• Expression level (default taken from column).

It's little interesting that you say that you get this error only in Azure SQL DB and not on-prem. The most common source for collation conflicts is when you restore a database with one collation on a server with a different collation. This often leads to that joins with temp tables blow up, because in temp tables the collation defaults to the server collation. However, this is not the case on Azure SQL DB - here temp tables default to the database collation.

But there are obviously more ways that you can get this error. For instance, maybe the Azure SQL database was created with one collation, and someone scripted tables from an on-prem database with a different collation and created them in Azure SQL DB without paying attention or removing the COLLATE clause.

There are certainly situations where you need to mix collations in a database. For instance, it can be a good idea give string columns that are codes of some sort a binary collation. Or you may need to support multiple languages.

However, I don't see any point in mixing SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS, as they are quite similar to each other. (In fact, I believe that for n(var)char, they are entirely equivalent.) So first run:

   SELECT databasepropertyex(db_name(), 'collation')  

to determine the collation for your database. Then run

   ALTER TABLE tbl ALTER COLUMN col type COLLATE xxx [NOT] NULL  

for the columns with the deviating collation. Observe this:

• You must include the exact data type the actual NULL/NOT NULL condition for the column.
• If the column is indexed (including keys), or there are any CHECK constraint on it, you first need to drop the index or constraint and reapply after the ALTER operation.

Answer 3

Note : Same error is not coming in On-Prem SQL server. Its in Azure SQL DB only.

This symptom suggests there is difference in column collations. Run the query below for the tables in your problem query against your on-prem and Azure SQL database to note differences in column collation.

SELECT   
	 SERVERPROPERTY('Collation') AS ServerCollation  
	,DATABASEPROPERTYEX('gedcomweb','Collation') AS DatabaseCollation  
	,OBJECT_SCHEMA_NAME(c.object_id) AS SchemaName  
	,OBJECT_NAME(c.object_id) AS TableName  
	,c.name AS ColumnName  
	,c.collation_name AS ColumnColation  
FROM sys.columns AS c  
WHERE   
	c.collation_name IS NOT NULL  
	AND c.object_id IN (  
		 OBJECT_ID(N'dbo.YourTable1')  
		,OBJECT_ID(N'dbo.YourTable2')  
	)  
ORDER BY SchemaName, TableName, ColumnName;