SQL Server - No se puede resolver el conflicto de collation entre ... en la operación equal to.

¿Qué es una COLECCIÓN?

La intercalación no es más que la forma de codificar caracteres que utiliza una base de datos para interpretarlos.

Una intercalación es una agrupación de estos caracteres en un orden determinado (cada intercalación tiene un orden diferente), donde “A” es un carácter diferente de “a”, si la intercalación distingue entre mayúsculas y minúsculas (diferenciación de mayúsculas y minúsculas) y “a” es diferente de “á”, si la intercalación distingue acentos (diferenciación de acentos).

COLLATION tiene tres niveles de jerarquía:
– Servidor
– Base de datos
- Columna

Si la base de datos se crea sin especificar qué intercalación se utilizará, se creará con la intercalación del servidor (idioma del sistema operativo). Cuando se crea una tabla sin especificar la clasificación de las columnas de texto (VARCHAR, NVARCHAR, CHAR, etc.), la clasificación de la base de datos se utilizará como clasificación de las tablas.

En SQL Server, el nombre de intercalación sigue el siguiente patrón de nomenclatura:
SQL_IntercalaciónDesignador_CaseSensibilidad_Sensibilidad al acento_KanatypeSensible_AnchoSensibilidad

Ejemplo de intercalación:
SQL_Latín1_General_CP1_CS_HACIA

Dónde:

• IntercalaciónDesignador: Especifica las reglas de agrupación básicas utilizadas por la agrupación de Windows, donde las reglas de clasificación se basan en el alfabeto o el idioma.
• Sensibilidad entre mayúsculas y minúsculas: CI especifica que no distingue entre mayúsculas y minúsculas, CS especifica que distingue entre mayúsculas y minúsculas.
• Sensibilidad al acento: AI especifica que no distingue los acentos, AS especifica que sí lo hace.
• KanatypeSensible: Omitido especifica que es insensible a los caracteres kana, KS especifica que es insensible a los caracteres kana.
• AnchoSensibilidad: Omitido especifica que no es sensible al ancho, WS especifica que sí es sensible al ancho.

Si una columna utiliza COLLATION distingue entre mayúsculas y minúsculas (CS), una consulta como SELECT * FROM Table WHERE Column LIKE '%Oracle%' devolverá el registro "Oracle", pero no devolverá el registro "oracle".

Lo mismo sucede con una columna que utiliza un COLLATION sensible al acento (AS). Una consulta como SELECT * FROM Table WHERE Column LIKE '%JOÃO%' devolverá el registro "João", pero no devolverá el registro "Joao".

Para consultar la lista completa de colaciones por región e idioma, accede a este enlace, recordando que el más utilizado en portugués (Brasil) es SQL_Latin1_General_CP1_CI_AI (o SQL_Latin1_General_CP1_CS_AS).

Referencias:
https://docs.microsoft.com/pt-br/sql/t-sql/statements/collations
https://docs.microsoft.com/pt-br/sql/relational-databases/collations/collation-and-unicode-support

Simulando el error

Una manera fácil de simular este error es crear una tabla donde dos o más columnas tengan diferentes intercalaciones e intentar hacer una comparación WHERE entre estas columnas, una concatenación o crear dos tablas con diferentes columnas de intercalación e intentar realizar una unión entre las 2 columnas.

Vale la pena recordar que este error solo ocurre al comparar o manipular dos columnas de TEXTO (VARCHAR, NVARCHAR, CHAR, etc.) de diferentes intercalaciones.

IF (OBJECT_ID('tempdb..#Teste1') IS NOT NULL) DROP TABLE #Teste1
CREATE TABLE #Teste1 (
    Id INT IDENTITY(1,1),
    Nome1 VARCHAR(100),
    Nome2 VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS
)

INSERT INTO #Teste1
SELECT 'SQL Server', 'Oracle'

SELECT A.Nome2 + ' é melhor que ' + A.Nome1
FROM #Teste1	A

Realizar la consulta sin cambiar la estructura.

Una forma simple, rápida y práctica de solucionar este problema temporalmente o cuando no tiene acceso para cambiar la estructura de la tabla/base de datos, es usar el operador COLLATE en la selección misma, que convertirá todos los datos de la columna a una intercalación específica y luego podrá comparar y trabajar con los datos normalmente.

SELECT A.Nome1 COLLATE SQL_Latin1_General_CP1_CS_AS + ' é melhor que ' + A.Nome2
FROM #Teste1	A

Aún puedes usar la intercalación DATABASE_DEFAULT en las dos columnas involucradas, para garantizar la compatibilidad entre ellas, convirtiendo las dos columnas a la misma intercalación de la base de datos:

SELECT *
FROM #Teste1		A
LEFT JOIN #Teste1	B	ON	A.Nome1 COLLATE DATABASE_DEFAULT = B.Nome2 COLLATE DATABASE_DEFAULT

Esta es una solución temporal y alternativa, que no debe aplicarse a grandes volúmenes de datos, ya que el rendimiento no es ideal, ya que es necesario leer y convertir toda la columna para luego trabajar con los datos.

Cómo identificar la base de datos COLLATION

El primer paso al identificar un problema de conflicto de intercalación entre bases de datos es identificar primero la intercalación de las bases de datos involucradas para intentar comprender si el problema está en esta ubicación. Para identificar la intercalación de una base de datos, podemos usar el comando sp_helpdb:

También podemos utilizar la vista de catálogo sys.databases:

SELECT name, collation_name, compatibility_level
FROM sys.databases
WHERE name IN ('master', 'msdb', 'model', 'tempdb')

Y también use la función DATABASEPROPERTYEX:

SELECT DATABASEPROPERTYEX('master', 'Collation')

Cómo identificar la COLECCIÓN de una columna

Después de analizar la recopilación de las bases de datos involucradas y verificar que ya están usando la misma codificación, analicemos ahora las columnas involucradas.

Hay varias formas de realizar esta verificación, como sys.columns:

SELECT A.name, A.column_id, B.name, A.max_length, A.[precision], A.scale, A.collation_name
FROM msdb.sys.columns A
JOIN msdb.sys.types B ON A.user_type_id = B.user_type_id
WHERE [object_id] = OBJECT_ID('msdb.dbo.sysjobs')

También podemos obtener esta información analizando las vistas de la base de datos INFORMACIÓN_SCHEMA:

SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE, COLLATION_NAME
FROM msdb.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'sysjobs'

Cómo cambiar la COLECCIÓN de la columna

Una solución definitiva para resolver el problema de intercalación reportado es cambiar la columna que tiene una codificación de caracteres diferente al resto, con el fin de estandarizar la tabla para que todas las columnas usen la misma codificación.

Para hacer esto, simplemente use el siguiente comando:

ALTER TABLE #Teste1
ALTER COLUMN Nome2 VARCHAR(100) COLLATE DATABASE_DEFAULT

Con este comando, cambiamos la columna Nombre2 en nuestro ejemplo a la misma clasificación en la base de datos. Si es necesario, puede cambiar la intercalación a una de su elección, ignorando la intercalación predeterminada de la base de datos:

ALTER TABLE #Teste1
ALTER COLUMN Nome2 VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AI

Cómo cambiar la COLECCIÓN de la base de datos

Otra solución definitiva a este problema de conflicto entre intercalaciones es cambiar la intercalación predeterminada de la base de datos.

Esto es especialmente útil cuando las columnas no tienen una definición de intercalación (usando el estándar de base de datos) y estás realizando uniones y/o manipulación con cadenas entre columnas de diferentes bases de datos, con diferentes intercalaciones y estás enfrentando este problema.

Comando de ejemplo para cambiar la clasificación de la base de datos:

ALTER DATABASE MeuBanco COLLATE SQL_Latin1_General_CP1_CS_AS