• Article

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 nécessaire 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.

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 de la table, la valeur ne requiert 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 de la table logique. Par conséquent, une instruction SELECT…INTO ne copie pas la 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 de valeurs 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 éparses

Octets éparses

Pourcentage NULL

bit

0.125

5

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 éparses

Octets éparses

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 pessimiste.

Types de données dont la longueur dépend des données

Type de données

Octets non éparses

Octets éparses

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.

Charge en mémoire requise pour les mises à jour de colonnes fragmentées

Lorsque vous concevez des tables comportant des colonnes fragmentées, gardez à l'esprit qu'une charge supplémentaire de 2 octets est requise pour chaque colonne fragmentée non Null dans la table lorsqu'une ligne est mise à jour. Conséquemment à cette exigence de mémoire supplémentaire, les mises à jour peuvent échouer de façon inattendue avec l'erreur 576 lorsque la taille totale de la ligne, y compris sa charge de mémoire, dépasse 8019 et qu'aucune colonne ne peut être sortie de la ligne.

Prenons l'exemple d'une table contenant 600 colonnes fragmentées de type bigint. S'il y a 571 colonnes non Null, alors la taille totale sur le disque est de 571 * 12 = 6852 octets. Après l'ajout de la charge de ligne supplémentaire et de l'en-tête de colonne fragmentée, ce chiffre augmente pour atteindre 6895 octets environ. La page dispose toujours d'environ 1124 octets disponibles sur le disque. Cela peut donner l'impression que des colonnes supplémentaires peuvent être mises à jour sans problème. Cependant, pendant la mise à jour, une charge supplémentaire en mémoire équivalente à 2 fois le nombre de colonnes fragmentées non Null est requise. Dans cet exemple, en incluant la charge supplémentaire (2 * 571 = 1142 octets) la taille de la ligne sur le disque atteint environ 8037 octets. Cette valeur dépasse la taille de ligne autorisée maximale de 8019 octets. Étant donné que toutes les colonnes ont un type de données de longueur fixe, elles ne peuvent pas être sorties de la ligne. En conséquence, la mise à jour échoue avec l'erreur 576.

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.

  • Les colonnes fragmentées ne sont pas compatibles avec la compression de données. Par conséquent, les colonnes fragmentées ne peuvent pas être ajoutées aux tables compressées et les tables contenant des colonnes fragmentées ne peuvent pas être compressées.

  • Le changement d'une colonne fragmentée en colonne non éparse ou d'une colonne non éparse en colonne fragmentée requiert la modification du format de stockage. Le moteur de base de données SQL Server utilise la procédure suivante pour effectuer cette modification :

    1. Il ajoute une nouvelle colonne à la table en fonction de la nouvelle taille et du nouveau format de stockage.

    2. Pour chaque ligne de la table, il met à jour et copie la valeur stockée dans l'ancienne colonne vers la nouvelle colonne.

    3. Il supprime l'ancienne colonne du schéma de la table.

    4. Il reconstruit la table pour libérer l'espace utilisé par l'ancienne colonne.

    Notes

    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 éparse 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é SPARSE 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 au moyen de l'opération de mise à jour de jeu de colonnes. Si les colonnes fragmentées sont mises à jour de manière explicite au moyen 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.

  • La propriété Sparse d'une colonne n'est pas conservée lorsque la table est copiée.

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.

Notes

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 AdventureWorks2008R2;
GO

CREATE TABLE dbo.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 dbo.DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);
GO

INSERT dbo.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 DocID, Title, ProductionSpecification, ProductionLocation, MarketingSurveyGroup
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