Utilisation de jeux de colonnes

Indications pour utiliser des jeux de colonnes

Lorsque vous utilisez des jeux de colonnes, considérez les indications suivantes :

• Les colonnes fragmentées et un jeu de colonnes peuvent être ajoutés dans le cadre de la même instruction.

• Le jeu de colonnes ne peut pas être modifié. Pour modifier un jeu de colonnes, vous devez le supprimer et le recréer.

• Un jeu de colonnes ne peut pas être ajouté à une table si celle-ci contient déjà des colonnes fragmentées.

• Un jeu de colonnes peut être ajouté à une table qui n'inclut pas de colonnes fragmentées. Si des colonnes fragmentées sont ajoutées ultérieurement à la table, elles apparaîtront dans le jeu de colonnes.

• Un seul jeu de colonnes est autorisé par table.

• Un jeu de colonnes est facultatif et n'est pas nécessaire pour pouvoir utiliser des colonnes fragmentées.

• Il est impossible de définir des contraintes ou des valeurs par défaut sur un jeu de colonnes.

• Les colonnes calculées ne peuvent pas contenir de colonnes de jeu de colonnes.

• Les requêtes distribuées ne sont pas prises en charge sur les tables qui contiennent des jeux de colonnes.

• La réplication ne prend pas en charge les jeux de colonnes.

• La capture de données modifiées ne prend pas en charge les jeux de colonnes.

• Un jeu de colonnes ne peut faire partie d'aucun type d'index. Cela inclut les index XML, les index de recherche en texte intégral et les vues indexées. Un jeu de colonnes ne peut pas être ajouté en tant que colonne incluse dans un index.

• Un jeu de colonnes ne peut pas être utilisé dans l'expression de filtre d'un index filtré ou de statistiques filtrées.

• Lorsqu'une vue inclut un jeu de colonnes, celui-ci apparaît dans la vue comme une colonne XML.

• Un jeu de colonnes ne peut pas être inclus dans une définition de vue indexée.

• Les vues partitionnées qui incluent des tables qui contiennent des jeux de colonnes peuvent être mises à jour lorsque la vue partitionnée spécifie les colonnes fragmentées par nom. Une vue partitionnée ne peut pas être mise à jour lorsqu'elle fait référence au jeu de colonnes.

• Les notifications de requêtes qui renvoient à des jeux de colonnes ne sont pas autorisées.

• Les données XML ont une limite de taille de 2 Go. Si les données combinées de toutes les colonnes fragmentées non nulles dans une ligne dépassent cette limite, la requête ou opération DML génère une erreur.

• Pour plus d'informations sur les données retournées par la fonction COLUMNS_UPDATED, consultez Utilisation de colonnes fragmentées.

Indications pour la sélection de données dans un jeu de colonnes

Considérez les indications suivantes lors de la sélection de données dans un jeu de colonnes :

• D'un point de vue conceptuel, un jeu de colonnes est un type de colonne XML calculé pouvant être mis à jour, qui regroupe un jeu de colonnes relationnelles sous-jacentes dans une représentation XML unique. Le jeu de colonnes prend en charge uniquement la propriété ALL_SPARSE_COLUMNS. Cette propriété est utilisée pour regrouper toutes les valeurs non nulles de toutes les colonnes fragmentées pour une ligne particulière.

• Dans l'éditeur de tables SQL Server Management Studio, les jeux de colonnes sont affichés sous forme de champ XML modifiable. Définissez les jeux de colonnes au format suivant :

  <column_name_1>value1</column_name_1><column_name_2>value2</column_name_2>...
  

  Voici quelques exemples de valeurs de jeu de colonnes :

  • <sparseProp1>10</sparseProp1><sparseProp3>20</sparseProp3>

  • <DocTitle>Bicycle Parts List</DocTitle><Region>West</Region>

• Les colonnes fragmentées qui contiennent des valeurs NULL sont omises de la représentation XML pour le jeu de colonnes.

Attention
L'ajout d'un jeu de colonnes modifie le comportement des requêtes SELECT *. La requête retournera le jeu de colonnes sous forme de colonne XML et ne retournera pas les colonnes fragmentées individuelles. Les concepteurs de schémas et développeurs de logiciels doivent prendre soin de ne pas arrêter les applications existantes.

Insertion ou modification de données dans un jeu de colonnes

La manipulation des données d'une colonne fragmentée peut s'effectuer en utilisant le nom de chaque colonne ou en faisant référence au nom du jeu de colonnes et en spécifiant les valeurs du jeu de colonnes à l'aide du format XML du jeu de colonnes. Les colonnes fragmentées peuvent apparaître dans n'importe quel ordre dans la colonne XML.

Lorsque des valeurs de colonnes fragmentées sont insérées ou mises à jour à l'aide du jeu de colonnes XML, les valeurs insérées dans les colonnes fragmentées sous-jacentes sont converties implicitement à partir du type de données xml. Dans le cas des colonnes numériques, une valeur vierge dans le XML pour la colonne numérique est convertie en chaîne vide. Cela provoque l'insertion d'un zéro dans la colonne numérique, comme illustré dans l'exemple suivant.

CREATE TABLE t (i int SPARSE, cs xml column_set FOR ALL_SPARSE_COLUMNS)
GO
INSERT t(cs) VALUES ('<i/>')
GO
SELECT i FROM t
GO

Dans cet exemple, aucune valeur n'a été spécifiée pour le i de colonne, mais la valeur 0 a été insérée.

Utilisation du Type de données sql_variant

Le type de données sql_variant peut stocker plusieurs types de données différents, tels que int, char et date. Les jeux de colonnes retournent les informations de type de données telles que l'échelle, la précision et les informations relatives aux paramètres régionaux associées à une valeur sql_variant sous la forme d'attributs dans la colonne XML générée. Si vous essayez de fournir ces attributs dans une instruction XML générée de manière personnalisée en tant qu'entrée pour une opération d'insertion ou de mise à jour sur un jeu de colonnes, certains de ces attributs sont requis et une valeur par défaut est assignée à certains d'entre eux. Le tableau suivant répertorie les types de données et les valeurs par défaut générées par le serveur lorsque la valeur n'est pas fournie.

* localeID -1 correspond aux paramètres régionaux par défaut. Les paramètres régionaux pour l'anglais sont 1033.

** Non applicable = Aucune valeur n'est sortie pour ces attributs durant une opération de sélection sur le jeu de colonnes. Génère une erreur lorsqu'une valeur est spécifiée pour cet attribut par l'appelant dans la représentation XML fournie pour un jeu de colonnes dans une opération d'insertion ou de mise à jour.

Sécurité

Le modèle de sécurité pour un jeu de colonnes fonctionne de manière semblable au modèle de sécurité qui existe entre la table et les colonnes. Les jeux de colonnes peuvent être visualisés comme une minitable et une opération de sélection est semblable à uneopération SELECT * sur cette minitable. Mais la relation entre le jeu de colonnes et les colonnes fragmentées est une relation de regroupement plutôt qu'un conteneur strict. Le modèle de sécurité vérifie la sécurité sur la colonne de jeu de colonnes et honore les opérations DENY sur les colonnes fragmentées sous-jacentes. Les caractéristiques supplémentaires du modèle de sécurité sont les suivantes :

• Des autorisations de sécurité peuvent être accordées et révoquées pour la colonne de jeu de colonnes, comme pour toute autre colonne dans la table.

• Une valeur GRANT ou REVOKE d'autorisation SELECT, INSERT, UPDATE, DELETE et REFERENCES sur une colonne de jeu de colonnes n'est pas propagée aux colonnes membres sous-jacentes de ce jeu. Elle s'applique uniquement à l'utilisation de la colonne de jeu de colonnes. L'autorisation DENY sur un jeu de colonnes est propagée aux colonnes fragmentées sous-jacentes de la table.

• L'exécution d'instructions SELECT, INSERT, UPDATE et DELETE sur la colonne de jeu de colonnes requiert que l'utilisateur ait des autorisations correspondantes sur la colonne de jeu de colonnes et également l'autorisation correspondante sur toutes les colonnes fragmentées dans la table. Étant donné que le jeu de colonnes représente toutes les colonnes fragmentées dans la table, vous devez avoir l'autorisation sur toutes les colonnes fragmentées, y compris celles que vous ne modifierez peut-être pas.

• L'exécution d'une instruction REVOKE sur une colonne fragmentée ou un jeu de colonnes provoque l'application par défaut de la sécurité à leur objet parent.

Exemples

Dans les exemples suivants, une table de documents contient le jeu de colonnes commun 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.

A. Création d'une table qui a un jeu de colonnes

USE AdventureWorks2008R2;
GO

CREATE TABLE DocumentStoreWithColumnSet
(DocID int PRIMARY KEY,
Title varchar(200) NOT NULL,
ProductionSpecification varchar(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup varchar(20) SPARSE NULL,
MarketingProgramID int SPARSE NULL,
SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);
GO

B. Insertion de données dans une table en utilisant les noms des colonnes fragmentées

INSERT DocumentStoreWithColumnSet (DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27)
GO

INSERT DocumentStoreWithColumnSet (DocID, Title, MarketingSurveyGroup)
VALUES (2, 'Survey 2142', 'Men 25 - 35')
GO

C. Insertion de données dans une table en utilisant le nom du jeu de colonnes

INSERT DocumentStoreWithColumnSet (DocID, Title, SpecialPurposeColumns)
VALUES (3, 'Tire Spec 2', '<ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>')
GO

D. Observation des résultats d'un jeu de colonnes lorsque SELECT * est utilisé

SELECT * FROM DocumentStoreWithColumnSet ;

E. Observation des résultats de la sélection du jeu de colonnes par nom

SELECT DocID, Title, SpecialPurposeColumns
FROM DocumentStoreWithColumnSet
WHERE ProductionSpecification IS NOT NULL ;

F. Observation des résultats de la sélection des colonnes fragmentées par nom

SELECT DocID, Title, ProductionSpecification, ProductionLocation 
FROM DocumentStoreWithColumnSet
WHERE ProductionSpecification IS NOT NULL ;

G. Mise à jour d'une table en utilisant un jeu de colonnes

UPDATE DocumentStoreWithColumnSet
SET SpecialPurposeColumns = '<ProductionSpecification>ZZ285W</ProductionSpecification><ProductionLocation>38</ProductionLocation>'
WHERE DocID = 3 ;
GO

UPDATE DocumentStoreWithColumnSet
SET SpecialPurposeColumns = '<ProductionSpecification>ZZ285W</ProductionSpecification>'
WHERE DocID = 3 ;
GO

Voir aussi

Concepts