Utilisation de colonnes fragmentées
Les colonnes fragmentées sont des colonnes ordinaires qui ont un stockage optimisé pour les valeurs NULL. Les colonnes fragmentées réduisent l'espace requis pour les valeurs NULL, en échange d'une augmentation du coût d'extraction des valeurs autres que NULL. Envisagez d'utiliser des colonnes fragmentées lorsque l'espace économisé est d'au moins 20 à 40 pour cent. Les colonnes fragmentées et les jeux de colonnes sont définis à l'aide des instructions CREATE TABLE ou ALTER TABLE.
Les colonnes fragmentées peuvent être utilisées avec des jeux de colonnes et des index filtrés :
Jeux de colonnes
Les instructions INSERT, UPDATE et DELETE peuvent faire référence aux colonnes fragmentées par nom. Toutefois, vous pouvez également afficher et utiliser toutes les colonnes fragmentées d'une table qui sont combinées dans une colonne XML unique. Cette colonne porte le nom de jeu de colonnes. Pour plus d'informations sur les jeux de colonnes, consultez Utilisation de jeux de colonnes.
Index filtrés
Les colonnes fragmentées ayant de nombreuses lignes évaluées à NULL, elles sont particulièrement appropriées pour les index filtrés. Un index filtré sur une colonne fragmentée peut indexer uniquement les lignes qui ont des valeurs remplies. Cela crée un index plus petit et plus efficace. Pour plus d'informations, consultez Règles de conception d'index filtrés.
Les colonnes fragmentées et les index filtrés permettent aux applications, telles que Windows SharePoint Services, de stocker efficacement et d'accéder à un grand nombre de propriétés définies par l'utilisateur à l'aide de SQL Server 2008.
Propriétés des colonnes fragmentées
Les colonnes fragmentées présentent les caractéristiques suivantes :
Le Moteur de base de données SQL Server utilise le mot clé SPARSE dans une définition de colonne pour optimiser le stockage des valeurs dans cette colonne. Par conséquent, lorsque la valeur de colonne est NULL pour toute ligne dans la table, les valeurs ne requièrent pas de stockage.
Les affichages catalogue pour une table qui a des colonnes fragmentées sont les mêmes que pour une table ordinaire. L'affichage catalogue sys.columns contient une ligne pour chaque colonne de la table et inclut un jeu de colonnes s'il y en a un de défini.
Les colonnes fragmentées sont une propriété de la couche de stockage, plutôt que la table logique. Par conséquent, une instruction SELECT…INTO ne copie pas sur la propriété de colonne fragmentée dans une nouvelle table.
La fonction COLUMNS_UPDATED renvoie une valeur varbinary pour indiquer toutes les colonnes qui ont été mises à jour pendant une action DML. Les bits retournés par la fonction COLUMNS_UPDATED sont les suivants :
Lorsqu'une colonne fragmentée est mise à jour de manière explicite, le bit correspondant pour cette colonne fragmentée est défini sur 1 et le bit pour le jeu de colonnes est défini sur 1.
Lorsqu'un jeu de colonnes est mis à jour de manière explicite, le bit pour le jeu de colonnes est défini sur 1 et les bits pour toutes les colonnes fragmentées dans cette table sont définis sur 1.
Pour les opérations d'insertion, tous les bits sont définis sur 1.
Pour plus d'informations sur les jeux de colonnes, consultez Utilisation de jeux de colonnes.
Les types de données suivants ne peuvent pas être spécifiés comme SPARSE :
geography |
text |
geometry |
timestamp |
image |
user-defined data types |
ntext |
Évaluation des économies d'espace par type de données
Les colonnes fragmentées requièrent davantage d'espace de stockage pour les valeurs autres que Null, comparé à l'espace requis pour les données identiques qui ne sont pas marquées SPARSE. Les tableaux suivants indiquent l'utilisation d'espace pour chaque type de données. La colonne Pourcentage NULL indique le pourcentage des données qui doivent être NULL pour une économie d'espace nette de 40 pour cent.
Types de données de longueur fixe
Type de données |
Octets non fragmentés |
Octets fragmentés |
Pourcentage NULL |
|---|---|---|---|
bit |
0.125 |
4.125 |
98% |
tinyint |
1 |
5 |
86% |
smallint |
2 |
6 |
76% |
int |
4 |
8 |
64% |
bigint |
8 |
12 |
52% |
real |
4 |
8 |
64% |
float |
8 |
12 |
52% |
smallmoney |
4 |
8 |
64% |
money |
8 |
12 |
52% |
smalldatetime |
4 |
8 |
64% |
datetime |
8 |
12 |
52% |
uniqueidentifier |
16 |
20 |
43% |
date |
3 |
7 |
69% |
Types de données dont la longueur dépend de la précision
Type de données |
Octets non fragmentés |
Octets fragmentés |
Pourcentage NULL |
|---|---|---|---|
datetime2(0) |
6 |
10 |
57% |
datetime2(7) |
8 |
12 |
52% |
time(0) |
3 |
7 |
69% |
time(7) |
5 |
9 |
60% |
datetimetoffset(0) |
8 |
12 |
52% |
datetimetoffset (7) |
10 |
14 |
49% |
decimal/numeric(1,s) |
5 |
9 |
60% |
decimal/numeric(38,s) |
17 |
21 |
42% |
vardecimal(p,s) |
Utilisez le type decimal comme évaluation classique. |
Types de données dont la longueur dépend des données
Type de données |
Octets non fragmentés |
Octets fragmentés |
Pourcentage NULL |
|---|---|---|---|
sql_variant |
Varie selon le type de données sous-jacent |
||
varchar ou char |
2* |
4* |
60% |
nvarchar ou nchar |
2* |
4*+ |
60% |
varbinary ou binary |
2* |
4* |
60% |
xml |
2* |
4* |
60% |
hierarchyid |
2* |
4* |
60% |
*La longueur est égale à la moyenne des données contenues dans le type, plus 2 ou 4 octets.
Restrictions relatives à l'utilisation des colonnes fragmentées
Les colonnes fragmentées peuvent être de n'importe quel type de données SQL Server ; en outre, elles se comportent comme n'importe quelle autre colonne avec les restrictions suivantes :
Une colonne fragmentée doit être nullable et ne peut pas avoir les propriétés ROWGUIDCOL ou IDENTITY. Une colonne fragmentée ne peut pas être des types de données suivants : text, ntext, image, timestamp, type de données défini par l'utilisateur, geometry ou geography ; ni avoir l'attribut FILESTREAM.
Une colonne fragmentée ne peut pas avoir de valeur par défaut.
Une colonne fragmentée ne peut pas être liée à une règle.
Bien qu'une colonne calculée puisse contenir une colonne fragmentée, une colonne calculée ne peut pas être marquée comme SPARSE.
Une colonne fragmentée ne peut pas faire partie d'un index cluster ou d'un index de clé primaire unique. Toutefois, les colonnes calculées persistantes et non persistantes définies sur des colonnes fragmentées peuvent faire partie d'une clé cluster.
Une colonne fragmentée ne peut pas être utilisée comme clé de partition d'un index cluster ou d'un segment de mémoire. Toutefois, une colonne fragmentée peut être utilisée comme clé de partition d'un index non-cluster.
Une colonne fragmentée ne peut pas faire partie d'un type de table défini par l'utilisateur, qui est utilisé dans des variables de table et des paramètres table.
La compression de données est incompatible avec les colonnes fragmentées. Par conséquent, les tables qui contiennent des colonnes fragmentées ne peuvent pas être compressées et les colonnes fragmentées ne peuvent pas être ajoutées à des tables compressées.
Le changement d'une colonne fragmentée en colonne non fragmentée ou d'une colonne non fragmentée en colonne fragmentée requiert la modification du format de stockage. Le moteur de base de données SQL Server effectue cette modification en procédant comme suit :
Il ajoute une nouvelle colonne à la table en fonction de la nouvelle taille et du nouveau format de stockage.
Pour chaque ligne de la table, il met à jour et copie la valeur stockée dans l'ancienne colonne vers la nouvelle colonne.
Il supprime l'ancienne colonne du schéma de la table.
Il reconstruit la table pour libérer l'espace utilisé par l'ancienne colonne.
[!REMARQUE]
L'étape 2 peut échouer lorsque la taille des données de la ligne dépasse la taille de ligne maximale autorisée. Cette taille inclut la taille des données stockées dans l'ancienne colonne et celle des données mises à jour stockées dans la nouvelle colonne. Cette limite est de 8 060 octets pour les tables qui ne contiennent pas de colonnes fragmentées ou de 8 018 octets pour les tables qui contiennent des colonnes fragmentées. Cette erreur peut se produire même si toutes les colonnes éligibles ont été déplacées hors des lignes. Pour plus d'informations, consultez Données de dépassement de ligne de plus de 8 Ko.
Lorsque vous modifiez une colonne non fragmentée en colonne fragmentée, la colonne fragmentée consomme davantage d'espace pour les valeurs non Null. Lorsqu'une ligne est proche de la limite de taille de ligne maximale, l'opération peut échouer.
Technologies SQL Server qui prennent en charge les colonnes fragmentées
Cette section décrit comment les colonnes fragmentées sont prises en charge dans les technologies SQL Server suivantes :
Réplication transactionnelle
La réplication transactionnelle prend en charge les colonnes fragmentées, mais pas les jeux de colonnes, qui peuvent être utilisés avec les colonnes fragmentées. Pour plus d'informations sur les jeux de colonnes, consultez Utilisation de jeux de colonnes.
La réplication de l'attribut SPARSE est déterminée par une option de schéma spécifiée à l'aide de sp_addarticle ou de la boîte de dialogue Propriétés de l'article dans SQL Server Management Studio. Les versions antérieures de SQL Server ne prennent pas en charge les colonnes fragmentées. Si vous devez répliquer des données vers une version antérieure, spécifiez que l'attribut SPARSE ne doit pas être répliqué.
Pour les tables publiées, vous ne pouvez pas ajouter de nouvelles colonnes fragmentées à une table ni modifier la propriété de fragmentation d'une colonne existante. Si une telle opération est requise, supprimez et recréez la publication.
Réplication de fusion
La réplication de fusion ne prend pas en charge les colonnes fragmentées ni les jeux de colonnes.
Suivi des modifications
Le suivi des modifications prend en charge les colonnes fragmentées et les jeux de colonnes. Lorsqu'un jeu de colonnes est mis à jour dans une table, le suivi des modifications traite cela comme une mise à jour de la ligne entière. Aucun suivi des modifications détaillé n'est fourni pour obtenir le jeu exact des colonnes fragmentées mises à jour par le biais de l'opération de mise à jour de jeu de colonnes. Si les colonnes fragmentées sont mises à jour de manière explicite par le biais d'une instruction DML, le suivi des modifications sur ces colonnes fonctionne de façon ordinaire et peut identifier le jeu exact de colonnes modifiées.
Capture des données modifiées
La capture de données modifiées prend en charge les colonnes fragmentées, mais pas les jeux de colonnes.
Exemples
Dans cet exemple, une table de documents contient un jeu commun qui a les colonnes DocID et Title. Le groupe Production souhaite avoir une colonne ProductionSpecification et ProductionLocation pour tous les documents de production. Le groupe Marketing souhaite avoir une colonne MarketingSurveyGroup pour les documents de marketing. Le code dans cet exemple crée une table qui utilise des colonnes fragmentées, insère deux lignes dans la table, puis sélectionne des données de la table.
[!REMARQUE]
Cette table ne possède que cinq colonnes, de manière à simplifier son affichage et sa lecture. La déclaration des colonnes fragmentées comme nullables est facultative si l'option ANSI_NULL_DFLT_ON est définie.
USE AdventureWorks
GO
CREATE TABLE DocumentStore
(DocID int PRIMARY KEY,
Title varchar(200) NOT NULL,
ProductionSpecification varchar(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup varchar(20) SPARSE NULL ) ;
GO
INSERT DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27)
GO
INSERT DocumentStore(DocID, Title, MarketingSurveyGroup)
VALUES (2, 'Survey 2142', 'Men 25 - 35')
GO
La sélection de toutes les colonnes de la table retourne un jeu de résultats ordinaire.
SELECT * FROM DocumentStore ;
Voici l'ensemble des résultats.
DocID Title ProductionSpecification ProductionLocation MarketingSurveyGroup
1 Tire Spec 1 AXZZ217 27 NULL
2 Survey 2142 NULL NULL Men 25-35
Le département Production ne s'intéressant pas aux données de marketing, il souhaite utiliser une liste de colonnes qui retourne uniquement les colonnes pertinentes, comme illustré dans la requête suivante.
SELECT DocID, Title, ProductionSpecification, ProductionLocation
FROM DocumentStore
WHERE ProductionSpecification IS NOT NULL ;
Voici l'ensemble des résultats.
DocID Title ProductionSpecification ProductionLocation
1 Tire Spec 1 AXZZ217 27
Voir aussi
Référence
Concepts
Historique des modifications
Mise à jour du contenu |
|---|
Correction des tailles de données listées dans le tableau des types dépendants des données. |