Comment changer le classement d'une base de données

Par Arian Papillon le lundi 9 mars 2020, 11:17 - Lien permanent

Question très souvent posée : comment peut on changer le classement (collation) d’une base de données ? Passer par exemple de case sensitive à case insensitive ? Vous verrez ici que l'opération est loin d'être simple !

Cet article vous propose aussi un script qui pourra vous y aider...

La tentative de changer ce paramètre uniquement dans les options de bases de données est bien entendu vouée à l’échec : le classement est configuré au niveau de chaque colonne de notre base de données, et c’est ici qu’il faudra d'abord intervenir…

Un petit rappel :

Pour rappel, lorsque vous installez une instance SQL Server, vous spécifiez un classement par défaut. Ce paramètre va déterminer la configuration des bases de données système, il est donc impossible de le modifier sauf à les reconstruire entièrement.
Une base de données possède aussi un classement par défaut. Si vous ne spécifiez rien au moment de sa création, elle hérite automatiquement du classement par défaut de l’instance.
Il est cependant tout à fait possible d’avoir une base de données avec un classement par défaut différent de celui de l’instance. Cela ne pose à priori pas de problème, sauf si certaines applications créent et utilisent des tables temporaires sans spécifier explicitement leur classement : la base tempdb (et donc les tables temporaires créées sans rien spécifier) ayant hérité du classement de l’instance, une jointure entre deux colonnes de classements différents provoquera une erreur.

Comment modifier le classement des colonnes ?

La modification du classement d’une base de données implique donc d’abord de faire ce changement au niveau de chaque colonne de type caractère. Si cette opération peut sembler simple avec une série d’instructions ALTER TABLE … ALTER COLUMN, il n’en n’est rien : pour pouvoir modifier chacune de ces colonnes, il faut s’assurer :

  • Qu’elle ne fasse pas l’objet d’une contrainte CHECK
  • Qu’elle ne soit pas une clé primaire ou une clé étrangère
  • Qu’elle ne fasse partie d’aucun index, clustered ou non
  • Qu’elle n’est utilisée pour aucune statistique personnalisée
  • Qu'aucune vue indexée ne s'appuie dessus
  • Qu'il n'y ait pas de réplication
  • Qu'il n'y ait pas d'index Full Text sur cette colonne

Il faudra donc supprimer tous ces éléments pour les recréer après changement du classement. Accessoirement, il faudra aussi supprimer les tables de diagramme (sysdiagram, dtproperties) ou de journal SSIS (sysssislog). Tout ceci n’est pas nécessairement exhaustif et on peut avoir des surprises !
Il peut donc apparaître plus simple à certains DBA de créer une nouvelle base de données avec le classement cible et d’y importer toutes les données : l’opération est très lourde aussi, personnellement ce n’est pas la procédure que je préfère…
Personnellement, je préfère la procédure de modification « en place » avec des ALTER TABLE … ALTER COLUMN … Ayant réalisé cette opération à plusieurs reprises, je vous soumets un petit squelette de script en 20 étapes : bon courage… !

/********* Modification de classement - template ************/

-- Step 1 - Drop System Tables : sysdiagrams, dtproperties, sysssislogs
-- Step 2 - Remove replication
-- Step 3 - Remove full text indexes
-- Step 4 - Drop Check Constraints on character columns
-- Step 5 - Drop FK on character columns
-- Step 6 - Drop nonclustered index on character columns
-- Step 7 - Drop clustered index on character columns
-- Step 8 - Drop user statistics on character columns
-- Step 9 - Drop indexed views on tables with character columns
-- Step 10 - Alter Columns to change collation
-- Step 11 - Drop and recreate calculated character columns
-- Step 12 - Recreate indexed views
-- Step 13 - Rereate clustered index
-- Step 14 - Recreate nonclustered index
-- Step 15 - Recreate statistics
-- Step 16 - Recreate FK
-- Step 17 - Recreate Check Constraints
-- Step 18 - Change database collation
-- Step 19 - Recreate full text indexes
-- Step 20 - Reconfigure replication

Attention, ça ne marche pas toujours !

Tout cela n’empêche pas un autre problème, quelle que soit la manière dont on réalise l'opération : le changement de classement peut générer des doublons. Imaginons que nous passons d’un classement sensible à un classement insensible aux accents : le risque existe de découvrir de nouveaux doublons, qui nous empêcheront de recréer les clés primaires et index uniques ou d’y insérer des données !
Vous voyez donc que l’opération risque d’être complexe.

Bonus : le script

Pour vous aider à effectuer tout cela, j'ai mis en ligne ici un script qui génère les commandes nécessaires : à tester en ayant soin d'avoir une bonne sauvegarde de côté !