Pourquoi et quand devrais-je utiliser SPARSE COLUMN? (SQL SERVER 2008)

Question

Après en passant par quelques tutoriels sur SQL SERVER 2008 nouvelle fonction COLONNE FRAGMENTÉE, j'ai trouvé qu'il ne prend pas tout l'espace si la valeur de la colonne est 0 ou null mais quand il y a une valeur, il faut 4 fois en l'espace de façon régulière(non fragmentée) colonne contient.

Si ma compréhension est correcte, alors pourquoi je vais aller pour qu'au moment de la conception de base de données? Et si j'utilise, à quel situation je être?

Aussi par curiosité, comment viennent pas de l'espace obtenir de réserve lorsqu'une colonne est définie comme étant incomplet de la colonne(je veux dire, qu'est-ce que la mise en œuvre interne pour qu')

Merci d'avance

Answer 1

Une colonne fragmentée ne pas utiliser 4x le montant de l'espace pour stocker une valeur, il utilise un (fixe) 4 octets supplémentaires par valeur non null. (Comme vous l'avez mentionné, NULL prend 0 espace.)

• Si une valeur non null stockées dans un peu de la colonne sera de 1 bit + 4 octets = 4.125 octets. Mais si 99% de ces personnes sont NULLES, c'est toujours une épargne nette.

• Une valeur non nulle stockées dans un GUID (de type UniqueIdentifier) de la colonne est de 16 octets + 4 octets = 20 octets. Donc, si seulement 50% de ces derniers sont NULS, c'est encore un filet d'épargne.

De sorte que les "économies attendues" dépend fortement de ce type de colonne dont nous parlons, et que votre estimation de ce ratio sera nulle vs non-null. Variable de la largeur des colonnes (varchars) sont sans doute un peu plus difficile à prévoir avec précision.

Cette documentation en Ligne de la Page a un tableau indiquant quel est le pourcentage des différents types de données doivent être null pour vous retrouver avec un avantage.

Donc, quand devriez-vous utiliser une Colonne Fragmentée? Lorsque vous vous attendez à un pourcentage significatif de lignes pour avoir une valeur NULLE. Quelques exemples qui me viennent à l'esprit:

• Un "Ordre de Date de Retour" d'une colonne dans une table de l'ordre. Vous espérez qu'un très petit pourcentage de la vente d'un résultat dans les produits retournés.
• Un "4e Adresse" ligne dans une table d'Adresses. La plupart des adresses postales, même si vous avez besoin d'un nom de Département et de "Soins" n'est probablement pas besoin de 4 lignes distinctes.
• Un "Suffixe" colonne de la table client. Un assez faible pourcentage de personnes qui ont un "Jr" ou "III" ou "Esquire" après leur nom.

Answer 2

• Le stockage d'une valeur null dans une colonne fragmentée prend pas de place du tout.

• Pour toute application externe de la colonne se comportent de la même

• Les colonnes fragmentées marche vraiment bien, avec des index filtrés que vous ne souhaitez créer un index pour faire face à la non-attributs vides dans la colonne.

• Vous pouvez créer un jeu de colonnes sur les colonnes fragmentées qui renvoie un xml clip de tous les non-null données de colonnes couvertes par le jeu. La colonne ensemble se comporte comme une colonne elle-même. Remarque: vous pouvez seulement avoir un jeu de colonnes par table.

• La Capture de Données modifiées de la réplication Transactionnelle et les deux fonctionnent, mais pas la colonne définit la fonction.

Inconvénients

• Si une colonne fragmentée dispose de données, il passer de 4 octets de plus que normale de la colonne par exemple même un peu (0.125 octets normalement) est de 4,125 octets et l'identificateur unique s'élève formulaire de 16 octets 20 octets.

• Pas tous les type de données peuvent être dispersés: text, ntext, image, l'horodatage, le type de données utilisateur, la géométrie, la géographie ou varbinray (max) avec l'attribut FILESTREAM ne peut pas être rares. (Changed17/5/2009 merci Alex pour repérer la faute de frappe)

• les colonnes calculées peuvent pas être rares (bien que les colonnes fragmentées peuvent prendre part à un calcul dans une autre colonne calculée)

• Vous ne pouvez pas appliquer des règles ou des valeurs par défaut.

• Les colonnes fragmentées ne font pas partie d'un index cluster. Si vous avez besoin de faire usage d'une colonne calculée basée sur la colonne fragmentée et de créer l'index cluster (qui est en quelque sorte de défaites de l'objet).

• La réplication de fusion ne fonctionne pas.

• La compression de données ne fonctionne pas.

• Accès (lecture et écriture) pour les colonnes fragmentées est plus cher, mais je n'ai pas été en mesure de trouver des chiffres précis sur ce point.

Référence

Answer 3

Vous lisez mal - il ne faut jamais 4x l'espace.

Plus précisément, il est indiqué 4 * (4 octets, voir note de bas de page), et non 4x (multiplier par 4). Le seul cas où l'espace est exactement 4x est un caractère (4), ce qui permettrait des économies si les valeurs NULL existent plus de 64% du temps.

"* La longueur est égale à la moyenne des données contenues dans le type, plus 2 ou 4 octets."

Answer 4

Toutes les colonnes FRAGMENTÉES sont stockés en tant que colonne XML dans la base de données. Laissez-nous voir quelques-uns des avantages et des inconvénients de la colonne FRAGMENTÉE.

Avantages de la colonne FRAGMENTÉE sont:

• INSERT, UPDATE, et DELETE peuvent faire référence aux colonnes fragmentées par son nom. Colonne FRAGMENTÉE peut fonctionner comme une colonne XML.

• Colonne FRAGMENTÉE peut prendre avantage de l'Index filtrés, où les données sont remplis en ligne.

• Colonne FRAGMENTÉE permet d'économiser beaucoup d'espace de base de données quand il y a zéro ou des valeurs null dans la base de données.

Les inconvénients de la colonne FRAGMENTÉE sont:

• Colonne FRAGMENTÉE n'ont pas d'IDENTITÉ ou de la propriété ROWGUIDCOL.

• Colonne FRAGMENTÉE ne peut pas être appliqué sur des données text, ntext, image, timestamp, la géométrie, la géographie ou les types de données définis par l'utilisateur.

• Colonne FRAGMENTÉE ne peut pas avoir de valeur par défaut ou de la règle ou de la colonne calculée.

• Index cluster ou d'un index de clé primaire unique ne peut pas être appliquée colonne FRAGMENTÉE. Colonne FRAGMENTÉE peut ne pas être une partie de la clé d'index cluster.

• La Table contenant la colonne FRAGMENTÉE peut avoir au maximum la taille de 8 018 octets au lieu d'un 8060 octets. Une table d'opération qui consiste à colonne FRAGMENTÉE prend des performances plus régulières de la colonne.