Régler les problèmes de collation sur une base de données

Si vous restaurez une base de données sur un serveur dont la collation par défaut est différente, vous risquez deux problèmes :

• Des conflits de collation entre des requêtes interbase où on compare des colonnes de type CHAR ou VARCHAR, par exemple dans une clause de jointure ;
• Des conflits de collation dans du code qui travaille avec des tables temporaires.

Régler les problèmes de tables temporaires

Lorsque vous créez une table temporaire (table avec le symbole #), la collation des colonnes de type CHAR et VARCHAR est définie par la collation par défaut de la base de données tempdb. Ensuite, si vous effectuez des comparaisons avec des colonnes de la base de données courante, vous obtiendrez l’erreur 468 : Cannot resolve the collation conflict between....

La base de démonstration PachaDataFormation est en French_CI_AS. Sur une instance dont la collation est SQL_Latin1_General_CP1_CI_AS, et donc tempdb aussi, cette jointure échoue :

USE [PachaDataFormation];

CREATE TABLE #Contact (
    ContactId INT,
    Nom VARCHAR(50)
);

INSERT INTO #Contact (ContactId, Nom)
SELECT ContactId, Nom
FROM Contact.Contact
WHERE Nom LIKE 'Fera%';

SELECT c.ContactId, c.Nom, c.Prenom
FROM Contact.Contact AS c
JOIN #Contact AS t ON t.Nom = c.Nom;

Msg 468, Level 16, State 9, Line 13
Cannot resolve the collation conflict between "French_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Vous avez deux solutions pour régler ce problème :

1. Créer vos tables temporaires avec une collation explicite, en utilisant la clause COLLATE et le mot-clé DATABASE_DEFAULT :

   USE [PachaDataFormation];
   
   DROP TABLE IF EXISTS #Contact;
   
   CREATE TABLE #Contact (
       ContactId INT,
       Nom VARCHAR(50) COLLATE DATABASE_DEFAULT
   );
   
   INSERT INTO #Contact (ContactId, Nom)
   SELECT ContactId, Nom
   FROM Contact.Contact
   WHERE Nom LIKE 'Fera%';
   
   SELECT c.ContactId, c.Nom, c.Prenom
   FROM Contact.Contact AS c
   JOIN #Contact AS t ON t.Nom = c.Nom;
   

   Cela applique la collation par défaut de la base de données courante à la colonne Nom, et la jointure ne lève plus d’erreur.

2. Utiliser la fonctionnalité Partially Contained Database. Quand une base de données est marquée contained, ou autonome, la collation des tables temporaires créées dans son contexte est celle de la base de données.

Activer la fonctionnalité Partially Contained Database

Vous devez d’abord autoriser la fonctionnalité Partially Contained Database au niveau du serveur :

EXEC sys.sp_configure N'contained database authentication', N'1';
RECONFIGURE WITH OVERRIDE;
GO

Il faut ensuite activer la fonctionnalité sur la base de données. Cette action nécessite d’obtenir l’accès exclusif à la base de données. Nous utilisons donc la clause SINGLE_USER avec l’option ROLLBACK IMMEDIATE pour forcer la déconnexion de tous les utilisateurs et annuler toutes les transactions en cours :

USE master;
GO
ALTER DATABASE [PachaDataFormation] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [PachaDataFormation] SET CONTAINMENT = PARTIAL;
GO
ALTER DATABASE [PachaDataFormation] SET MULTI_USER;
GO