Modifier le collation d'une base de données SQL Server

Plus tôt aujourd'hui, j'ai remarqué une erreur qui traînait dans un système roulant sous SQL Server. Le collation d'une base de données avait été changé et comme tous les objets enfants héritent de la propriété au moment de la création (default collation), la comparaison des chaînes de caractères s'effectuait incorrectement dans certaines tables.

Sachant qu'un collation French_CS_AS effectue une évaluation du texte en étant sensible à la case (CS) et en tenant compte de la présence des accents (AS), une chaîne comme "Élections" n'équivaudra pas à "elections". Ça peut être embêtant si on s'attend à ce qu'ils correspondent à l'intérieur d'une requête SQL.

Pour connaître le collation actuellement configuré :

SELECT DATABASEPROPERTYEX('dbname', 'Collation')

Pour réinitialiser le collation à utiliser au niveau BD pour les futures tables, on doit choisir le collation parmi la liste suivante :

SELECT * FROM ::fn_helpcollations()

Je vais donc écraser le collation French_CS_AS par French_CI_AI (Case Insensitive / Accents Insensitive). Je pourrais être tenté de faire simplement ceci :

ALTER DATABASE dbname COLLATE French_CI_AI

Mais il est possible qu'un message d'erreur s'affiche s'il y a des locks actifs (par exemple des utilisateurs connectés, des transactions en cours, etc) :

Server: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Server: Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'dbname' cannot be set to French_CI_AI.

Pour contourner ce problème de lock, on peut faire basculer la base de données en mode "utilisateur unique", effectuer le changement et le rétablir en mode multi-utilisateurs :

ALTER DATABASE dbname SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE dbname COLLATE French_CI_AI
GO
ALTER DATABASE dbname  SET MULTI_USER

À noter que ça rétablira la propriété globale au niveau de la BD mais pas les collates conservés dans les champs textes individuels des tables existantes (char, nchar, varchar, nvarchar, text, ntext). Dans le cas où vous auriez besoin de changer le collate partout, vous devrez créer un script qui boucle dans tous les champs et qui appliquera le changement en exécutant un ALTER TABLE ALTER COLUMN...

SELECT so.name as tablename, sc.name as columnname, sc.length, st.name as type
FROM sysobjects as so
INNER JOIN syscolumns as sc ON sc.id = so.id
INNER JOIN systypes as st ON st.xtype = sc.xtype
WHERE so.type = 'U'
AND st.name IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext')
AND so.name NOT LIKE 'dt%'
ORDER BY tablename, columnname

Faites attention aux champs textes avec des clés étrangères, aux types text et ntext, car vous pourriez avoir de mauvaises surprises... À suivre.