Modifier la Collation d’une base SQL 2005

La collation (ou classement en français) d’une base de données indique le jeu de caractères et l’ordre de tri des données stockées dans la base de données. De nombreux articles expliquent le rôle et traitent des problèmes de collation. L’objectif de celui-ci est de présenter une méthode pour changer la collation d’une base de données.

La collation peut être fixée:

1. Au niveau de l’instance: à l’installation de SQL Server 2005.
2. Au niveau de la base:

    CREATE DATABASE ... COLLATE ou ALTER DATABASE ... COLLATE.

3. Au niveau de la colonne:

   CREATE TABLE maTable ... COL1 NVARCHAR(100) COLLATE French_CI_AS

4. Au niveau de l’expression SQL:

   SELECT A, B from T1, T2 where T1.A = T2.B collate French_CI_AS

On peut parfois se retrouver dans la situation suivante : la base de données a été créée ou restaurée dans une collation qui pose problème à l’application et il faut la modifier.

Attention, gare alors aux problèmes lorsque l’on utilise des bases de collations différentes sur une même instance : La collation de tempdb est héritée de model, qui elle-même hérite de celle de l’instance. Lors de jointures avec des tables temporaires sur des colonnes contenant du texte, des erreurs de comparaison peuvent survenir:

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

Pour changer la collation d’une base de données, il existe plusieurs techniques :

Technique 1 :

1. Modifier la collation de la base : ALTER DATABASE … COLLATE
   Toutes les nouvelles colonnes de type caractère créées dans la base hériteront de la nouvelle collation mais pas les colonnes caractère déjà créées .
2. Modifier la collation de chaque colonne à condition quelle ne soit pas référencée dans un index, une contrainte Foreign key ou CHECK, une statistique de distribution ou une colonne calculée
   … pas évident

Technique 2 : celle que je vous propose

1. Générer le script de création de la base et de tous ses objets
2. Modifier ce script, pour créer une nouvelle base vide avec la bonne collation
3. Importer dans la nouvelle base les données de l’ancienne base à l’aide de l’assistant de Management Studio
4. Vérifier si l’application fonctionne correctement sur la nouvelle base
5. Intervertir l’ancienne base et la nouvelle
   On peut renommer une base ( commande ALTER DATABASE … MODIFY NAME). Attention de bien récupérer les comptes utilisateurs de l’ancienne base et le mapping sur les logins.

Exemple : Changer la collation de la base AdventureWorks Latin1_General_CS_AS en En French_CI_AS

Avant de commencer:

— Vérifier la collation du serveur

sp_helpsort

Server default collation
---------------------------------------------------------------------------
French, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

— Vérifier la collation de la base

select DATABASEPROPERTYEX('AdventureWorks', 'Collation')
-----------------------------------------------------------------------
Latin1_General_CS_AS

— Vérifier la collation des colonnes

SELECT B.table_name, column_name, collation_name, data_type FROM INFORMATION_SCHEMA.COLUMNS A,
INFORMATION_SCHEMA.TABLES B
WHERE A.table_name = B.table_name and collation_name is not null
ORDER BY B.table_name

table_name    column_name   collation_name             data_type
-----------------------------------------------------------------------------------
Address       AddressLine1  Latin1_General_CS_AS        nvarchar
Address       AddressLine2  Latin1_General_CS_AS        nvarchar
...

Etape 1 : Générer le script de création de la base et de tous ses objets

A Partir de Management Studio, cliquer sur “Generate Scripts”

—
Choisir la base AdventureWorks,
Cocher l’option “Script All Objects”
Next
—
Choose script Options
Modifier les options suivantes :
Include Descriptive Headers: True
Script Database Create: True
Script Object-Level Permissions : True
Script USE DATABASE : False
Script Full-Text Indexes : True
—
Next
Script to New Query Window
—
Next
—
Finish
—
Close

Etape 2 : Modifier ce script, pour créer une nouvelle base vide avec la bonne collatio

Avec l’outil Find/Replace Remplacer le mot AdventureWorks par AdventureWorks_2
(nom de la nouvelle base)

Modifier la Collation de la base de données avec la valeur voulue en ajoutant la clause collate

CREATE DATABASE
…
( NAME =
N'AdventureWorks_2_Log', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\AdventureWorks_2_Log.ldf' , SIZE = 916352KB , MAXSIZE
= UNLIMITED, FILEGROWTH = 10%)
COLLATE FRENCH_CI_AS
END

Modifier les chemins des fichiers de données en cas de besoin (pour ne pas utiliser les mêmes que la base d’origine).

=> Exécuter le script

Vérifier qu’il n’y a pas d’erreur problématique lors de la création de la base et de ses objetts.
Vérifier la collation de la nouvelle base et de ses colonnes.

Etape 3 : Importer dans la nouvelle base les données les données de l’ancienne

A l’aide SSMS depuis la base AdventureWorks_2, lancer l’assistant d’import de données :

—
Choose a Datasource: AdventureWorks
Next
—
Choose a destination : AdventureWorks_2
Next
—
Copy data from one or more tables or views
Next
—
Ne sélectionner que les tables , pas les vues
Next
—
Execute immediately
Next
Finish
—-

Les lignes de chaque table ont été ajoutées aux tables vides avec la nouvelle collation pour les données type Caractère

L.Hochberg

Continuez votre lecture sur le blog :

• Jeux de caractères, Unicode et Base de données (Benjamin VESAN) [GénéralMySQLOracleSQL ServerSybase]
• Le chiffrement et SQL Server – Episode 3 : Always Encrypted (Capdata team) [AzureSQL Server]
• Oracle Text pour DBA Oracle : Partie 1 (Capdata team) [Oracle]
• Le chiffrement et SQL Server – Episode 2 : Mise en oeuvre de TDE (Capdata team) [SQL Server]
• Retrouver la requête à l’origine d’une erreur 8623 “The query processor ran out of internal resources and could not produce a query plan” (David Baffaleuf) [SQL Server]