Le changement de colonnes de CHAR en VARCHAR augmente le nombre de pages d'une table

**Etanne** · 12/06/2014, 11h32

Bonjour,

Je reste bloqué sur un problème de compréhension concernant le nombre de pages d'une table.

En modifiant le type d'une colonne de CHAR à VARCHAR le nombre de pages augmente au lieu de diminuer (avec des textes d'une longueur bien en deçà de la taille de la colonne).

J'ai fait un petit script pour reproduire mon problème de compréhension :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
/*
 * Creation de la table
*/
CREATE TABLE USERS 
(
	ID INT NOT NULL IDENTITY(0, 1) PRIMARY KEY, 
	PRENOM CHAR(100),
	NOM CHAR(50),
	ADRESSE CHAR(200),
	VILLE CHAR(50)
)
GO
 
/* 
 * Insertion massive de données
*/
DECLARE @index int = 0
WHILE @index < 50000 BEGIN
    SET @index = @index + 1
	INSERT INTO USERS (PRENOM, NOM, ADRESSE, VILLE) VALUES (NEWID(), NEWID(), NEWID(), NEWID());
END
GO
 
/*
 * On affiche les infos
*/
DBCC showcontig ('USERS') WITH TABLERESULTS
GO
 
/*
 * Changement de type CHAR => VARCHAR
*/
ALTER TABLE USERS ALTER COLUMN PRENOM VARCHAR(100);
ALTER TABLE USERS ALTER COLUMN NOM VARCHAR(50);
ALTER TABLE USERS ALTER COLUMN ADRESSE VARCHAR(200);
ALTER TABLE USERS ALTER COLUMN VILLE VARCHAR(50);
GO
 
/*
 * On affiche les infos
*/
DBCC showcontig ('USERS') WITH TABLERESULTS;
GO
 
 
/*
 * On affiche les infos
*/
DROP TABLE USERS
GO
/*
 * Creation de la table avec des VARCHAR cette fois-ci
*/
CREATE TABLE USERS 
(
	ID INT NOT NULL IDENTITY(0, 1) PRIMARY KEY, 
	PRENOM VARCHAR(100),
	NOM VARCHAR(50),
	ADRESSE VARCHAR(200),
	VILLE VARCHAR(50)
)
GO
 
/* 
 * Insertion massive
*/
DECLARE @index int = 0
WHILE @index < 50000 BEGIN
    SET @index = @index + 1
	INSERT INTO USERS (PRENOM, NOM, ADRESSE, VILLE) VALUES (NEWID(), NEWID(), NEWID(), NEWID());
END
GO
 
/*
 * On affiche les infos
*/
DBCC showcontig ('USERS') WITH TABLERESULTS
GO

Ce script effectue ceci :

Création d'une table
Insertion de 50000 élements
showcontig pour connaitre le nombre de pages
Modification des colonnes de CHAR en VARCHAR
showcontig pour connaitre le nombre de pages
Suppression de la table
Recréation de la table mais cette fois ci avec des VARCHAR
insertion massive de 50000 élements
showcontig pour connaitre le nombre de pages

Je regarde le nombre de pages avec la colonne "Pages" avec l'instruction DBCC showcontig ('USERS') WITH TABLERESULTS
Les résultats obtenus sont les suivants :

La table avec des colonnes typées CHAR : 2 632 pages
Après modification du type CHAR en VARCHAR : 7 895 pages
Et lorsque la table a été construite avec des VARCHAR : 1 042 pages

Et là où est mon incompréhension : pourquoi n'y a t'il pas eu de réduction de pages après mon avoir effectuer la modification de CHAR en VARCHAR ? Est-ce du à une fragmentation ?

Merci pour vos lumières !

**iberserk** · 12/06/2014, 13h35

Faites un rebuild et vous verrez...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER INDEX ALL ON USERS  REBUILD

Le ALTER COLUMN ne libère pas l'espace et provoque de la fragmentation...

Un REBUILD corrige tout ca...

**Etanne** · 12/06/2014, 14h14

Merci iberserk

Effectivement après la conversion j'obtiens ceci :

Table avec CHAR : 2 632 pages
Modification du type CHAR en VARCHAR + Reconstruction des indexes : 2 632 pages

Que que le nombre de pages n'ai pas augmenté, je me retrouve avec même nombre de pages qu'avec des CHAR. Normalement le nombre de pages devrait être inférieur dans mon cas avec les VARCHAR ?!

J'ai comme l'impression que ALTER INDEX ALL ON USERS REBUILD ne supprime pas les espaces réservés du type CHAR.

Par folie j'ai tenté l'expérience suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
/* 
 * Réduction à 36 car les textes d'exemple sont avec 36 caractères max
 */
ALTER TABLE USERS ALTER COLUMN PRENOM CHAR(36);
ALTER TABLE USERS ALTER COLUMN NOM CHAR(36);
ALTER TABLE USERS ALTER COLUMN ADRESSE CHAR(36);
ALTER TABLE USERS ALTER COLUMN VILLE CHAR(36);
GO
 
ALTER TABLE USERS ALTER COLUMN PRENOM VARCHAR(100);
ALTER TABLE USERS ALTER COLUMN NOM VARCHAR(50);
ALTER TABLE USERS ALTER COLUMN ADRESSE VARCHAR(200);
ALTER TABLE USERS ALTER COLUMN VILLE VARCHAR(50);
GO
 
ALTER INDEX ALL ON USERS  REBUILD
GO
 
DBCC showcontig ('USERS') WITH TABLERESULTS;
GO

Et là j'obtiens 1 042 pages.

Cette barbarie pour réduire le nombre de pages me semble pas très orthodoxe. Quels conseils avez-vous dans le cas d'un changement de type de CHAR en VARCHAR ?

Merci

**iberserk** · 12/06/2014, 16h38

c'est parfaitement normal vous utilisez un CHAR (50) (exemple) SQL SERVER a donc complété les 36 caractère pour arriver à 50...

Quand vous faites un ALTER en varchar(50) la colonne ne fait plus 36 comme vous le pensez mais 50... et même plus pusqu'il faut stocker a longeur réelle.

Que donnes un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT LENGTH(NOM),DATALENGTH(NOM ) FROM USERS

**Etanne** · 12/06/2014, 17h33

Après la création de la table (avec CHAR) et le remplissage des données, voici les résultats de la requête SELECT LEN(NOM) len, DATALENGTH(NOM ) datalength FROM USERS :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
len         datalength
----------- -----------
36          50
...

Je fait un ALTER ... VARCHAR(50) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
len         datalength
----------- -----------
36          50
...

Et effectivement, les espaces du CHAR sont restés.

Et si je fais à la place la méthode barbare précédente : UPDATE USER SET NOM = RTRIM(NOM), j'obtiens :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
len         datalength
----------- -----------
36          36
...

Les espaces blancs ne sont plus présents.

Donc conclusion (corrigez moi si je me trompe) : Modifier un CHAR en VARCHAR fragmente les données et ne supprime les espaces blancs provenant du type CHAR.
Le fait de faire un UPDATE ... SET col = RTRIM(col) permet de supprimer les espaces blancs, et finir par ALTER INDEX ALL ON USERS REBUILD permet des réorganiser les données fragmentées suite à l'ALTER et le UPDATE.

Merci pour les éclaircissements, tout est maintenant plus clair !

**mikedavem** · 12/06/2014, 21h53

Hello,

Ici ce n'est pas un problème de fragmentation mais de la façon dont SQL Server gère le stockage des lignes de données en interne dans les pages SQL Server.
Lorsqu'un type de données est changé SQL Server ne remplace pas l'ancienne colonne mais en ajoute une autre.

Si on prend un exemple simplifié de ton problème :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
create table t
(
	id int,
	col1 char(100)
)
 
insert t values (1, REPLICATE('t', 60))

Si on fait une rapide inspection de la page de données SQL Server voici ce que l'on peut constater :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
...
0000000000000000:   10006c00 01000000 74747474 74747474 74747474  ..l.....tttttttttttt
0000000000000014:   74747474 74747474 74747474 74747474 74747474  tttttttttttttttttttt
0000000000000028:   74747474 74747474 74747474 74747474 74747474  tttttttttttttttttttt
000000000000003C:   74747474 74747474 20202020 20202020 20202020  tttttttt            
0000000000000050:   20202020 20202020 20202020 20202020 20202020                      
0000000000000064:   20202020 20202020 020000                              ...
 
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
 
id = 1                              
 
Slot 0 Column 2 Offset 0x8 Length 100 Length (physical) 100
 
col1 = tttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt 
...

Une rapide traduction hexadécimale du dump donne ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
-- 10                                                       --> status bit A
-- 00                                                                  --> status bit B
-- 6c00                                                               --> 108 (fixed length)
-- 01000000                                                       --> 1 (id column)
-- 74747474 74747474 74747474 ... 74747474    --> t x 60 (col1 column) (t = 74 hex ASCII) 
-- 20202020 20202020 20202020 ... 20202020    --> [space] x 40 ([space] = 20 hex ASCII)
-- 0200                                                              --> 2 (number of fixed columns)
-- 00                                                                  --> null bitmap (aucune colonne qui accepte les null)

Maintenant si on modifie le type de données de la col "col1" de char(100) à varchar(100) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

alter table t alter column col1 varchar(100);

Le dump de la page de données allouée à la table t donne ceci maintenant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
0000000000000000:   30006c00 01000000 74747474 74747474 74747474  0.l.....tttttttttttt
0000000000000014:   74747474 74747474 74747474 74747474 74747474  tttttttttttttttttttt
0000000000000028:   74747474 74747474 74747474 74747474 74747474  tttttttttttttttttttt
000000000000003C:   74747474 74747474 20202020 20202020 20202020  tttttttt            
0000000000000050:   20202020 20202020 20202020 20202020 20202020                      
0000000000000064:   20202020 20202020 03000001 00d70074 74747474          .....×.ttttt
0000000000000078:   74747474 74747474 74747474 74747474 74747474  tttttttttttttttttttt
000000000000008C:   74747474 74747474 74747474 74747474 74747474  tttttttttttttttttttt
00000000000000A0:   74747474 74747474 74747474 74747420 20202020  ttttttttttttttt     
00000000000000B4:   20202020 20202020 20202020 20202020 20202020                      
00000000000000C8:   20202020 20202020 20202020 202020                            

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 1                              

Slot 0 Column 67108865 Offset 0x8 Length 0 Length (physical) 100

DROPPED = NULL                      

Slot 0 Column 2 Offset 0x73 Length 100 Length (physical) 100

col1 = tttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt

... avec une rapide traduction :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 30					   --> status bit A
-- 00				                 --> status bit B
-- 6c00					   --> 108 (fixed length)
-- 01000000				   --> 1 (id column)
-- 74747474 74747474 74747474 ... 74747474    --> t x 60 (old col1 column) (t = 74 hex ASCII) 
-- 20202020 20202020 20202020 ... 20202020    --> [space] x 40 ([space] = 20 hex ASCII)
-- 0300                                                              --> 3 (number of fixed columns)
-- 00					   --> null bitmap (aucune colonne qui accepte les null)

-- 0100					   --> 1 (number of variable columns)
-- d700					   --> 215 (offset of first variable column)
-- 74747474 74747474 74747474 ... 74747474    --> t x 60 (new col1 column) (t = 74 hex ASCII) 
-- 20202020 20202020 20202020 ... 20202020    --> [space] x 40 ([space] = 20 hex ASCII)

J'ai mis en évidence ici les changements en rouge .. On constate ici que la partie fixe du stockage de la ligne de données n'a pas changé et qu'une partie variable a été ajoutée avec les données de l'ancienne colonne vers la nouvelle ... ce qui explique pourquoi tu as vu ton nombre de pages augmenter dans ton cas.

++

**Etanne** · 13/06/2014, 16h36

Merci pour ces explications détaillées !

Envoyé par mikedavem

une partie variable a été ajoutée avec les données de l'ancienne colonne vers la nouvelle

Il est étonnant ce choix de dupliquer une partie des données, je m'attendais à avoir simplement de reste d'espace blanc du CHAR. On sait pourquoi SQL-Server fait ce choix ?

Merci !