Stockage des données décimales sous forme de colonne de longueur variable
Les types de données decimal et numeric sont généralement stockés sur le disque sous forme de données de longueur fixe. Le type de données numeric est équivalent, d'un point de vue fonctionnel, au type de données decimal. Dans SQL Server 2005 Service Pack 2 (SP2) et les versions ultérieures, les types de données decimal et numeric peuvent être stockés sous forme de colonne de longueur variable à l'aide du format de stockage vardecimal. Le format de stockage vardecimal est disponible uniquement dans les éditions SQL Server Enterprise, Developer et Evaluation.
[!REMARQUE]
Cette fonctionnalité sera supprimée dans une prochaine version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité. Utilisez plutôt la compression ROW et PAGE. Pour plus d'informations, consultez Création de tables et d'index compressés.
[!REMARQUE]
vardecimal n'est pas un type de données mais un format de stockage.
Le format de stockage vardecimal peut réduire considérablement la taille de stockage des données, mais il augmente un peu la charge qui pèse sur le processeur. Le format de stockage vardecimal est appliqué au niveau table. Vous ne pouvez donc pas stocker certaines colonnes decimal d'une table au format de stockage vardecimal sans stocker les autres colonnes. Le type de données decimal reste un type de données exact.
Lorsque le format de stockage vardecimal est activé sur une table, les données decimal sont stockées dans les pages de données, d'index et de journal au format de stockage vardecimal. La modification du format de stockage constitue une opération hors connexion. La table qui est modifiée est verrouillée de façon exclusive pendant la durée de l'opération, et elle est indisponible pour l'accès simultané en lecture ou en écriture.
Implémentation du format de stockage vardecimal
En fonction de la précision de la colonne (1 à 38), le stockage d'une valeur decimal consomme entre 5 et 17 octets. Lorsqu'une table n'utilise pas le format de stockage vardecimal, toutes les entrées de la table consomment le même nombre d'octets pour chaque colonne décimale définie, même si la valeur d'une ligne est 0, NULL ou une autre valeur qui pourrait être exprimée dans un plus petit nombre d'octets (tel que le nombre 3). Lorsqu'une table est stockée au format de stockage vardecimal, les colonnes decimal consomment uniquement l'espace nécessaire pour contenir le nombre fourni, plus 2 octets de charge de traitement. Le résultat est toujours compris entre 5 et 20 octets. Ceci inclut les 2 octets de surcharge requis pour stocker le décalage de la valeur. Cependant, les valeurs Null et zéro sont traitées de manière particulière et utilisent uniquement 2 octets.
Si la table ne contient aucune colonne de longueur variable, il existe une surcharge supplémentaire de 2 octets par ligne pour le stockage du nombre de colonnes de longueur variable. Si la table contient déjà au moins une colonne de longueur variable, il n'existe aucune surcharge supplémentaire.
La table suivante indique le nombre d'octets requis pour stocker les données décimales au format fixe standard, ainsi que le nombre maximal d'octets requis pour stocker les données décimales au format de stockage vardecimal. Lorsqu'une table est stockée au format fixe, la valeur qui est répertoriée sera utilisée pour chaque ligne. Lorsqu'une table est stockée au format de stockage vardecimal, de nombreuses valeurs nécessiteront un nombre d'octets inférieur à celui qui est répertorié.
Précision de la colonne |
Taille décimale fixe d'origine (octets) |
Zone de données vardecimal maximale (octets) |
Surcharge pour stocker le décalage (octets) |
Stockage vardecimal maximal utilisé (octets) |
|---|---|---|---|---|
1-3 |
5 |
3 |
2 |
5 |
4-6 |
5 |
4 |
2 |
6 |
7-9 |
5 |
5 |
2 |
7 |
10-12 |
9 |
6 |
2 |
8 |
13-15 |
9 |
8 |
2 |
10 |
16-18 |
9 |
9 |
2 |
11 |
19 |
9 |
10 |
2 |
12 |
20-21 |
13 |
10 |
2 |
12 |
22-24 |
13 |
11 |
2 |
13 |
25-27 |
13 |
13 |
2 |
15 |
28 |
13 |
14 |
2 |
16 |
29-30 |
17 |
14 |
2 |
16 |
31-33 |
17 |
15 |
2 |
17 |
34-36 |
17 |
16 |
2 |
18 |
37-38 |
17 |
18 |
2 |
20 |
Utilisation du format de stockage vardecimal
Vous pouvez utiliser le format de stockage vardecimal lorsque vous tentez de résoudre les problèmes suivants :
lorsque l'espace disque est insuffisant ;
lorsque l'accès au disque (E/S) est un goulot d'étranglement pour les performances du système ;
lorsque vous avez besoin d'un degré de précision élevé pour certaines données, même si de nombreuses valeurs sont petites, NULL ou 0 (par exemple, une table d'un entrepôt de données incluant une colonne decimal qui possède un grand nombre de lignes contenant des valeurs 0 ou entières).
Lorsque vous modifiez le format de stockage d'une table, vous devez régénérer le stockage de la table (segment de mémoire ou index cluster). Si l'index cluster d'une table contient une colonne decimal, tous les index non-cluster doivent également être régénérés, car ils contiennent les valeurs de clés cluster. Si l'index cluster ne comporte pas de colonne decimal, les index non-cluster sont régénérés s'ils contiennent une colonne decimal. Si la table est un segment de mémoire (c'est-à-dire qu'elle est dépourvue d'index cluster), tous les index non-cluster doivent être régénérés pour qu'ils pointent vers les nouveaux emplacements de lignes dans le segment de mémoire.
Si vous recréez une table pour activer ou désactiver le format de stockage vardecimal, l'espace de stockage total requis pourra être plus du double de celui de la table d'origine. Si la table ne contient pas de colonne decimal ou numeric, l'activation du format de stockage vardecimal constitue une opération sur les métadonnées uniquement. Sachez que l'activité du journal sera intense lorsque la table et les index seront régénérés.
Le format de stockage vardecimal peut être utilisé aussi bien pour les tables en lecture seule que pour les tables en lecture/écriture. Vous devez comparer les économies en termes de stockage par rapport à l'utilisation complémentaire de l'UC qui est requise pour convertir le format de stockage des lignes lors de chaque accès à ces dernières. Qui plus est, l'écriture dans une table qui utilise le format de stockage vardecimal peut ralentir les performances en raison du nombre accru de fractionnements de pages.
Limitations du format de stockage vardecimal
Les restrictions suivantes s'appliquent :
Nécessite SQL Server 2005 SP2 ou versions ultérieures.
Le format de stockage vardecimal ne peut pas être activé dans les bases de données système : MASTER, model, msdb, tempdb ou distribution. Lorsqu'une requête trie des données stockées dans un format vardecimal, les données sont triées dans la base tempdb dans un état décimal fixe. En règle générale, les données de la base tempdb nécessiteront beaucoup plus d'espace que celui occupé par la table source au format de stockage vardecimal dans la base de données source.
Le format de stockage vardecimal ne peut pas être appliqué aux vues, aux vues indexées, aux index XML et aux index de texte intégral. Les tables sous-jacentes à ces objets peuvent cependant utiliser le format de stockage vardecimal.
Les tables internes (telles que les tables de notification et de métadonnées) ne peuvent pas utiliser le format de stockage vardecimal.
Les fonctions table ne peuvent pas utiliser le format de stockage vardecimal.
La colonne numeric qui est stockée dans un format de stockage vardecimal ne peut pas être chiffrée.
Les partitions hétérogènes (partitions au format décimal fixe et partitions au format de stockage vardecimal) ne sont pas prises en charge.
Les tables qui sont créées à partir d'une table au format de stockage vardecimal à l'aide de la syntaxe Transact-SQL SELECT … INTO… n'héritent pas du format de stockage vardecimal.
L'état du format de stockage vardecimal d'une base de données activée pour la mise en miroir de bases de données ne peut pas être modifié. Vous devez supprimer la mise en miroir de bases de données pour activer le format de stockage vardecimal sur la base de données. Cependant, il n'est pas nécessaire de supprimer la mise en miroir de bases de données lorsque certaines tables sont activées ou désactivées pour le format de stockage vardecimal.
SQL Server doit être en mesure de garantir que toutes les mises à jour aboutiront et que le format décimal fixe de la table pourra systématiquement être rétabli. Par conséquent, il n'est pas possible d'appliquer le format de stockage vardecimal à une table si, en raison de la surcharge supplémentaire, une ligne existante excède 8 060 octets ou une valeur d'index existante excède 900 octets.
[!REMARQUE]
Le format de stockage vardecimal diffère du stockage de données de type texte variable (varchar) car SQL Server vous permet de créer une ligne susceptible d'excéder 8 060 octets si toutes les colonnes variables ont la taille maximale. SQL Server applique la limite de 8 060 octets lorsque des données de type texte sont insérées ou mises à jour. SQL Server ne vous permet pas de créer un jeu de colonnes décimales susceptibles d'excéder la limite de 8 060 octets pour une ligne. La limite de 8 060 octets est appliquée lorsque vous modifiez le format d'une table pour lui appliquer le format vardecimal.
Lorsque vous transférez une base de données via la méthode de détachement et d'attachement de l'Assistant Copie de base de données, l'opération d'attachement échoue si le Moteur de base de données de destination ne possède pas la version SQL Server 2005 SP2 (ou ultérieure). La méthode SQL Server Management Objects crée alors la nouvelle base de données et les nouvelles tables sans utiliser le format de stockage vardecimal. Il est possible d'appliquer le format vardecimal à la base de données et aux tables après le transfert si le Moteur de base de données possède la version SQL Server 2005 SP2 ou une version ultérieure.
Sauvegarde et restauration, mise en miroir de base de données, sp_attach_db et copie des journaux de transaction
Les opérations de sauvegarde et de récupération, la mise en miroir de bases de données, sp_attach_db et la copie des journaux de transaction fonctionnent correctement avec le format de stockage vardecimal ; cependant, pour inclure une base de données qui utilise le format de stockage vardecimal, vous devez mettre à niveau chaque instance de SQL Server vers SQL Server 2005 SP2 (ou version ultérieure) au minimum. Par exemple, vous ne pouvez pas restaurer une sauvegarde des journaux d'une base de données activée pour le format de stockage vardecimal vers une base de données qui n'est pas activée, ou effectuer une mise en miroir à partir d'une base de données activée pour le format de stockage vardecimal vers une base de données qui n'est pas activée, ni attacher une base de données activée pour le format de stockage vardecimal à partir de SQL Server 2005 SP2 vers une version antérieure de SQL Server. Si vous restaurez une sauvegarde complète d'une base de données activée pour le format de stockage vardecimal vers une base de données qui n'est pas activée pour ce dernier, la base de données deviendra activée pour le format de stockage vardecimal.
Lorsque vous modifiez le format d'une table pour lui appliquer le format de stockage vardecimal, la séquence de sauvegardes de journaux reste valide, et la base de données peut être restaurée en appliquant la dernière sauvegarde complète plus une séquence de journaux valide. Pour empêcher la création de sauvegardes non valides, vous devez appliquer le mode de récupération simple à la base de données avant de modifier une table pour supprimer le format de stockage vardecimal. Une fois que vous avez supprimé le format de stockage vardecimal d'une table, vous devez créer une sauvegarde de base de données complète.
Utilisation du format de stockage vardecimal avec la mise en miroir de bases de données
Les procédures suivantes indiquent pas à pas comment utiliser le format de stockage vardecimal avec la mise en miroir de bases de données.
Pour utiliser le format de stockage vardecimal avec la mise en miroir de bases de données
Mettez à niveau les instances de la base de données principale et du partenaire de mise en miroir vers la version SQL Server 2005 SP2, au minimum.
Si vous utilisez actuellement la mise en miroir de bases de données, supprimez-la et retirez le partenaire de mise en miroir. Pour plus d'informations, consultez Procédure : supprimer une mise en miroir de bases de données (Transact-SQL).
Activez le format de stockage vardecimal sur la base de données principale (si la base de données se trouve sur SQL Server 2005) et assurez-vous que cette dernière est en mode de restauration complète.
Établissez la mise en miroir de la base de données à l'aide des sauvegardes des journaux et complètes de la base de données principale. Pour plus d'informations, consultez Procédure : établir une session de mise en miroir de bases de données au moyen de l'authentification Windows (Transact-SQL).
Modifiez les différentes tables pour qu'elles utilisent le format de stockage vardecimal.
[!REMARQUE]
Il n'est pas nécessaire de supprimer la mise en miroir de base de données pour modifier le format de stockage des différentes tables.
Pour supprimer le format de stockage vardecimal
Modifiez les tables de la base de données principale pour supprimer le format de stockage vardecimal.
Supprimez la mise en miroir de la base de données.
Configurez la base de données principale pour qu'elle adopte le mode de récupération simple. Cette opération provoque une rupture de la séquence de journaux.
Si la base de données se trouve sur SQL Server 2005, désactivez le format de stockage vardecimal sur la base de données principale.
Supprimez la base de données partenaire miroir.
Réaffectez à la base de données principale le mode de récupération complet.
Sauvegardez la base de données principale et rétablissez la mise en miroir de base de données.
Incidence du format de stockage vardecimal sur les opérations de réplication
La réplication fonctionne normalement sur les tables qui utilisent le format de stockage vardecimal, mais prenez en compte les considérations suivantes :
Les types de données decimal stockés au format de stockage vardecimal sont convertis au format décimal fixe pour le transfert durant la réplication. La base de données de distribution ne peut pas être activée pour le format de stockage vardecimal. Par conséquent, les données ne sont pas stockées au format vardecimal lorsqu'elles sont stockées dans les tables de réplication de la base de données de distribution. Au niveau de l'Abonné, les enregistrements de journal sont appliqués normalement.
Une table au format de stockage vardecimal peut être répliquée vers une table au format décimal fixe, et une table au format décimal fixe peut être répliquée vers une table au format de stockage vardecimal.
Le processus de création de table lié à un nouvel abonnement ne crée pas de table à l'aide du format de stockage vardecimal. Ceci permet à la réplication d'aboutir, indépendamment du niveau du Service Pack du Moteur de base de données ou de l'état activé du format de stockage vardecimal de la base de données d'abonnement. La table d'abonnement peut être activée pour le format de stockage vardecimal sur l'Abonné une fois que la table a été créée, ou en modifiant les scripts de création avant qu'ils ne soient appliqués.
Le tableau suivant décrit les exigences en matière de script pour différents abonnés.
Abonné |
Script |
|---|---|
SQL Server 2000 ou SQL Server version 7.0 |
Les scripts de création de table peuvent être utilisés sans modification. |
SQL Server 2005, la base de données n'est pas marquée pour le format de stockage vardecimal. |
Les scripts de création de table peuvent être utilisés sans modification. |
SQL Server 2005, la base de données est marquée pour le format de stockage vardecimal, mais vous ne voulez pas que le format de stockage vardecimal soit activé pour la table de l'Abonné. |
Les scripts de création de table peuvent être utilisés sans modification. |
SQL Server 2005, la base de données est marquée pour le format de stockage vardecimal et vous voulez que le format de stockage vardecimal soit activé pour la table de l'Abonné SQL Server 2005. |
Vous pouvez modifier les scripts de création de table afin d'activer le format de stockage vardecimal dans la base de données et les tables. Vous pouvez également activer la base de données et les tables de l'Abonné à l'aide des procédures stockées qui sont décrites dans la section « Activation du format de stockage vardecimal » ci-dessous. |
Considérations supplémentaires
La liste suivante contient des points supplémentaires à prendre en compte lorsque vous utilisez le format de stockage vardecimal :
Le format de stockage vardecimal n'a aucune incidence sur les opérations d'importation et d'exportation en bloc (bcp).
La fonction DATALENGTH ne détecte pas le format de stockage vardecimal et retourne le nombre d'octets qui seraient stockés au format décimal fixe.
Très rarement, le format de stockage vardecimal empêchera SQL Server d'utiliser un plan de requête qui était optimal pour les données décimales fixes.
Le format de stockage vardecimal peut être utilisé avec n'importe quel niveau de compatibilité de base de données.
Si une table ne comporte aucune colonne du type de données decimal ou numeric lorsque sp_tableoption est exécuté, les métadonnées de la table sont modifiées pour indiquer que la table utilise le format de stockage vardecimal. Lorsque de nouvelles colonnes decimal seront ajoutées par la suite, elles seront stockées au format de stockage vardecimal. Aucune technique particulière n'est requise pour ajouter ou supprimer des colonnes dans une table qui utilise le format de stockage vardecimal.
Activation du format de stockage vardecimal
L'activation ou la modification du format de stockage vardecimal nécessite les autorisations suivantes :
L'activation du format de stockage vardecimal dans une base de données implique obligatoirement l'autorisation ALTER DATABASE sur le serveur.
Le remplacement du format d'une table par le format de stockage vardecimal implique obligatoirement l'autorisation ALTER sur la table.
Avant d'activer le format de stockage vardecimal, vous devez tout d'abord vérifier que la table sera réduite lorsque le format de stockage sera activé. Lorsque la précision de la colonne définie est requise pour la plupart des lignes, la surcharge associée au format de stockage vardecimal peut être plus importante que les avantages en termes d'espace et peut entraîner une table plus volumineuse. Pour estimer la réduction de la taille de ligne avant de modifier la table, utilisez la procédure stockée sp_estimated_rowsize_reduction_for_vardecimal. Si vous décidez de modifier le format de stockage de la table, activez la base de données pour le format de stockage vardecimal, puis activez les différentes tables pour ce format de stockage. Il n'est pas nécessaire d'activer le format de stockage vardecimal pour les bases de données SQL Server 2008.
Dans une base de données SQL Server 2008, vous pouvez activer le format de stockage vardecimal pour les types de données decimal à l'aide de procédures stockées ou de SQL Server Management Studio :
Exécutez sp_db_vardecimal_storage_format pour activer le format de stockage vardecimal dans la base de données (si l'instance de SQL Server est SQL Server 2005 Service Pack 2), puis exécutez sp_tableoption pour activer le format de stockage vardecimal dans les tables appropriées.
Dans Management Studio, utilisez Database Properties Options Page pour activer le format de stockage vardecimal dans la base de données. Vous devez utiliser sp_tableoption pour affecter le format de stockage vardecimal à une table.
[!REMARQUE]
À partir de SQL Server 2008, toutes les bases de données sont activées pour le format de stockage vardecimal.
Identification des tables au format de stockage vardecimal
Pour déterminer quelles sont les tables d'une base de données qui utilisent le format de stockage vardecimal, utilisez la fonction OBJECTPROPERTY et recherchez la propriété TableHasVarDecimalStorageFormat.
L'exemple suivant retourne 1 si la table Production.WorkOrderRouting utilise le format de stockage vardecimal, et 0 si elle ne l'utilise pas.
USE AdventureWorks ;
GO
SELECT OBJECTPROPERTY(OBJECT_ID('Production.WorkOrderRouting'),
'TableHasVarDecimalStorageFormat') ;
GO
L'exemple suivant recherche dans la base de données AdventureWorks toutes les tables qui utilisent le format de stockage vardecimal.
USE AdventureWorks ;
GO
SELECT name, object_id, type_desc
FROM sys.objects
WHERE OBJECTPROPERTY(object_id,
N'TableHasVarDecimalStorageFormat') = 1 ;
GO
Problèmes lors de la suppression du format de stockage vardecimal
Pour supprimer le format de stockage vardecimal d'une table, cette dernière doit être recréée avec le format décimal fixe. Ceci peut entraîner une augmentation considérable de l'espace disque utilisé par la table. Si l'espace disque disponible n'est pas suffisant, l'opération échouera. Dans ce cas, pour désactiver le format de stockage vardecimal, vous devez vous assurer que SQL Server dispose de suffisamment d'espace disque. L'opération d'expansion nécessite également de l'espace temporaire pour stocker les données au format vardecimal et au format standard. Si la table étendue est susceptible de tenir dans l'espace disque disponible après l'expansion mais qu'elle ne parvient pas à s'étendre uniquement par manque d'espace disque temporaire, vous pouvez développer les données de manière incrémentielle en copiant les lignes de la table dans une nouvelle table non étendue.
Pour supprimer le format de stockage vardecimal d'une base de données immédiatement après sa modification, supprimez la base de données, puis restaurez cette dernière à partir d'une sauvegarde qui a été effectuée avant l'activation du format de stockage vardecimal pour la base de données.
Lorsque vous déplacez une base de données d'une édition SQL Server 2005 Enterprise, Developer ou Evaluation vers une autre édition ou vers une version antérieure de SQL Server, vous devez tout d'abord ouvrir la base de données en utilisant l'une des éditions requises, supprimer le format de stockage vardecimal, puis migrer la base de données. L'attachement d'une base de données qui contient le format de stockage vardecimal à un serveur inéligible échouera.