71
4.1 Modélisation de la base de données
taille des autres colonnes. Une solution à ce problème pourrait être d’effectuer un
partitionnement verticale, en créant une autre table contenant une colonne
VARCHAR(8000) et l’identifiant, pour une relation un-à-un sur la première table. À
partir de SQL Server 2005, ce n’est plus nécessaire, un mécanisme automatique
permet aux colonnes de longueur variable de dépasser la limite de la page. Cette
fonctionnalité est appelée dépassement de ligne (row overflow), elle ne s’applique
qu’aux types variables (N)VARCHAR et VARBINARY, ainsi qu’aux types de données .NET.
Prenons un exemple simple :
USE tempdb
GO
CREATE TABLE dbo.longcontact (
longcontactId int NOT NULL PRIMARY KEY,
nom varchar(5000),
prenom varchar(5000)
)
GO
INSERT INTO dbo.longcontact (longcontactId, nom, prenom)
SELECT 1, REPLICATE('N', 5000), REPLICATE('P', 5000)
GO
Avant SQL Server 2005, la création de cette table aurait été acceptée, mais avec
l’affichage d’un avertissement disant que l’insertion d’une ligne dépassant
8 060 octets ne pourrait aboutir. Et dans les faits, l’INSERT aurait déclenché une
erreur. En SQL Server 2005, tout fonctionne comme un charme. Observons ce qui a
été stocké :
SELECT p.index_id, au.type_desc, au.used_pages
FROM sys.partitions p
JOIN sys.allocation_units au ON p.partition_id = au.container_id
WHERE p.object_id = OBJECT_ID('dbo.longcontact');
L’insertion s’est donc déroulée sans problème pour une ligne d’une taille totale de
10 004 octets (2 × 5 000, plus 4 octets d’INTEGER). À l’insertion, SQL Server a automatiquement déplacé une partie de la ligne dans une autre page, créée à la volée, qui
sert uniquement à continuer la ligne. Cette page est d’un type spécial, comme nous
l’avons vu dans la requête sur sys.allocation_units. Alors que la ligne est normalement dans une page de type In-row data, le reste doit être placé dans une page de
type Row-overflow data.
Nous voyons qu’il y a quatre pages en tout, dont deux pages de row overflow.
Pourquoi deux pages, alors que nous n’avons ajouté que de quoi remplir une seule
page de dépassement ? Vérifions à l’aide de DBCC IND :
DBCC IND ('tempdb', 'longcontact', 1);
Nous voyons le résultat sur la figure 4.8. Pour chaque type de page, nous avons
une page de type 10, c’est-à-dire une page IAM (Index Allocation Map), en quelque
sorte la table des matières du stockage. Tout s’explique.
4.1 Modélisation de la base de données
taille des autres colonnes. Une solution à ce problème pourrait être d’effectuer un
partitionnement verticale, en créant une autre table contenant une colonne
VARCHAR(8000) et l’identifiant, pour une relation un-à-un sur la première table. À
partir de SQL Server 2005, ce n’est plus nécessaire, un mécanisme automatique
permet aux colonnes de longueur variable de dépasser la limite de la page. Cette
fonctionnalité est appelée dépassement de ligne (row overflow), elle ne s’applique
qu’aux types variables (N)VARCHAR et VARBINARY, ainsi qu’aux types de données .NET.
Prenons un exemple simple :
USE tempdb
GO
CREATE TABLE dbo.longcontact (
longcontactId int NOT NULL PRIMARY KEY,
nom varchar(5000),
prenom varchar(5000)
)
GO
INSERT INTO dbo.longcontact (longcontactId, nom, prenom)
SELECT 1, REPLICATE('N', 5000), REPLICATE('P', 5000)
GO
Avant SQL Server 2005, la création de cette table aurait été acceptée, mais avec
l’affichage d’un avertissement disant que l’insertion d’une ligne dépassant
8 060 octets ne pourrait aboutir. Et dans les faits, l’INSERT aurait déclenché une
erreur. En SQL Server 2005, tout fonctionne comme un charme. Observons ce qui a
été stocké :
SELECT p.index_id, au.type_desc, au.used_pages
FROM sys.partitions p
JOIN sys.allocation_units au ON p.partition_id = au.container_id
WHERE p.object_id = OBJECT_ID('dbo.longcontact');
L’insertion s’est donc déroulée sans problème pour une ligne d’une taille totale de
10 004 octets (2 × 5 000, plus 4 octets d’INTEGER). À l’insertion, SQL Server a automatiquement déplacé une partie de la ligne dans une autre page, créée à la volée, qui
sert uniquement à continuer la ligne. Cette page est d’un type spécial, comme nous
l’avons vu dans la requête sur sys.allocation_units. Alors que la ligne est normalement dans une page de type In-row data, le reste doit être placé dans une page de
type Row-overflow data.
Nous voyons qu’il y a quatre pages en tout, dont deux pages de row overflow.
Pourquoi deux pages, alors que nous n’avons ajouté que de quoi remplir une seule
page de dépassement ? Vérifions à l’aide de DBCC IND :
DBCC IND ('tempdb', 'longcontact', 1);
Nous voyons le résultat sur la figure 4.8. Pour chaque type de page, nous avons
une page de type 10, c’est-à-dire une page IAM (Index Allocation Map), en quelque
sorte la table des matières du stockage. Tout s’explique.