Résoudre les conflits de collation SQL Server sans faux matchs

Une jointure peut fonctionner sur une instance SQL Server puis échouer après restauration de la base sur une autre, car les textes comparés ont des collations différentes. Ajouter COLLATE jusqu'à disparition de l'erreur peut rétablir l'exécution tout en changeant les lignes associées. Casse, accents et ordre de tri font partie du contrat des données.

Commencez par le sens de l'identifiant. Les codes clients abc et ABC désignent-ils le même client? Une recherche de noms doit-elle ignorer les accents tout en préservant l'orthographe affichée? Le conflit technique ne peut être correctement résolu sans cette décision.

Examiner les niveaux concernés

Les paramètres du serveur, de la base et des colonnes ne sont pas interchangeables.

SELECT SERVERPROPERTY('Collation') AS ServerCollation,
       DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseCollation,
       DATABASEPROPERTYEX(N'tempdb', 'Collation') AS TempdbCollation;

SELECT name AS ColumnName, collation_name
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.Customers')
  AND collation_name IS NOT NULL;

Remplacez dbo.Customers par la table concernée. Une colonne peut avoir une collation différente du défaut de la base. Une base restaurée conserve ses propres paramètres, alors que des colonnes temporaires ordinaires héritent souvent du défaut de tempdb sur une installation classique. Cela explique un échec apparaissant seulement après migration du serveur.

Unicode ne supprime pas les règles de collation. nvarchar modifie la représentation des caractères, mais la comparaison dépend toujours d'une règle. L'exemple le montre directement.

SELECT
 CASE WHEN N'Cafe' COLLATE Latin1_General_100_CI_AI = N'café'
      THEN 1 ELSE 0 END AS InsensitiveMatch,
 CASE WHEN N'Cafe' COLLATE Latin1_General_100_CS_AS = N'café'
      THEN 1 ELSE 0 END AS SensitiveMatch;

InsensitiveMatch vaut 1 et SensitiveMatch vaut 0. La première comparaison ignore casse et accents; la seconde les distingue. Aucune n'est universellement correcte. Une recherche de produits et une contrainte d'unicité peuvent demander des règles différentes.

Utilisez des littéraux Unicode et des paramètres correctement typés pour les entrées multilingues. Un COLLATE tardif ne reconstitue pas des caractères perdus lors d'un passage par une page de codes incompatible. Corrigez d'abord l'ingestion et les types des paramètres.

Aligner la table intermédiaire

Si la colonne permanente utilise le défaut de la base courante, la colonne temporaire peut le demander explicitement.

-- Suitable when the target column uses the current database default.
CREATE TABLE #Incoming (
    CustomerCode nvarchar(50) COLLATE DATABASE_DEFAULT NOT NULL
);
CREATE INDEX IX_Incoming_Code ON #Incoming(CustomerCode);

Cela évite d'hériter accidentellement d'un défaut tempdb sans rapport. Ce n'est pas universel: si la colonne cible possède une collation explicite différente, alignez la colonne intermédiaire sur cette règle réelle. Vérifiez également le contexte de base lors de la création.

Pour une jointure interbases occasionnelle, un COLLATE sur une expression peut convenir. Choisissez consciemment la règle et inspectez le plan réel. Appliquer une autre collation à une grande colonne indexée peut imposer une conversion et empêcher l'utilisation directe de son ordre existant. Aligner et indexer une petite entrée intermédiaire peut être une meilleure frontière répétable.

Entourer toutes les comparaisons de LOWER ou UPPER ne remplace pas cette réflexion. Cela ajoute du calcul par ligne, peut gêner les index et ne définit pas nécessairement les règles d'accents et de langue. Des clés de recherche normalisées doivent être produites selon un contrat explicite commun.

Considérer le changement comme une migration

Modifier le défaut de la base ne réécrit pas automatiquement les collations des colonnes existantes. Une migration doit inventorier colonnes, index, contraintes, expressions calculées et consommateurs interbases. Les scripts de déploiement et les nouveaux objets doivent aussi être cohérents.

Avant de reconstruire un index unique, recherchez les valeurs devenant égales sous la nouvelle règle. Deux clés ne différant que par la casse ou un accent peuvent entrer en collision. Résolvez-les avec une correspondance approuvée, sans supprimer arbitrairement un client et ses relations.

Le tri influence aussi pagination et export. Ajoutez un départage unique aux résultats ordonnés et testez des valeurs multilingues représentatives, pas seulement de l'ASCII. Incluez casse mélangée, accents, chaînes vides et véritables formats de codes acceptés par l'application.

Enfin, validez résultat et coût. Comparez les identifiants associés, les lignes intermédiaires sans correspondance et les doublons, puis les lectures et le plan. L'exécution réussie prouve seulement la disparition de l'erreur syntaxique; les relations métier et les performances restent à vérifier.

Références techniques: Microsoft Learn: Collation and Unicode · Microsoft Learn: COLLATE.