Construire des tables efficaces

  • 8 minutes

Une conception de table efficace constitue la base de n’importe quelle base de données. Les tables structurent vos données et déterminent l’efficacité de vos requêtes pour accéder aux informations et les modifier.

Concevoir et créer des tables

Les tables sont les blocs de construction fondamentaux des bases de données relationnelles, l’organisation des données dans des lignes et des colonnes qui représentent des entités et leurs attributs. Dans les systèmes relationnels, les tables définissent la structure pour le stockage des données transactionnelles, appliquent les relations via des clés étrangères et fournissent la base des requêtes et des rapports.

Pour l’analytique multidimensionnelle, les tables servent de tables de faits stockant des événements mesurables et des tables de dimension fournissant un contexte pour l’analyse. Les décisions de conception que vous prenez lors de la création de tables ( types de données, dimensionnement de colonnes, contraintes et relations) ont un impact direct sur l’efficacité du stockage, les performances des requêtes, l’intégrité des données et l’extensibilité entre les charges de travail opérationnelles et analytiques.

Choisir les types de données appropriés

Les types de données sont des décisions fondamentales qui affectent votre base de données. Le mauvais choix peut entraîner un stockage gaspille, des performances médiocres, une perte de données ou des erreurs d’application. Contrairement au code d’application que vous pouvez refactoriser facilement, la modification des types de données de colonne dans les bases de données de production nécessite souvent des reconstructions de tables, ce qui peut signifier des heures d’arrêt pour les tables volumineuses.

Sélectionnez les types de données appropriés lorsque vous concevez le schéma initial, car il s’agit du moment le plus simple pour l’obtenir correctement. Prenez également en compte attentivement les types de données quand :

  • Vous stockez des données où la précision est importante
  • Vous travaillez avec des tables à volume élevé où les coûts de stockage se multiplient
  • Vous définissez des colonnes souvent interrogées qui fonctionnent plus rapidement avec des types de données plus petits.

Explorer les types de données courants

Les types de données appropriés affectent le stockage, les performances et les opérations :

Catégorie de type Types de données Taille du stockage Instructions d’utilisation Example
Numeric INT, BIGINT, DECIMAL, FLOAT 4 octets, 8 octets, variable Choisir en fonction des exigences d'étendue et de précision Quantity INT, Revenue DECIMAL(10,2), Population BIGINT
Chaîne VARCHAR, CHAR, NVARCHAR 1 octet/char, fixe, 2 octets/char Utiliser VARCHAR pour les données de longueur variable, CHAR pour la longueur fixe, NVARCHAR pour Unicode Email VARCHAR(100), CountryCode CHAR(2), ProductName NVARCHAR(100)
Date/Heure DATE, DATETIME2, DATETIMEOFFSET 3 octets, 6 à 8 octets, 10 octets DATETIME2 offre une meilleure précision que DATETIME BirthDate DATE, OrderTimestamp DATETIME2, EventTime DATETIMEOFFSET
Binaire VARBINARY, IMAGE varies Pour stocker des données binaires telles que des images ou des documents ProfilePhoto VARBINARY(MAX), DocumentContent VARBINARY(MAX)
Spécial UNIQUEIDENTIFIER, XML, JSON 16 octets, varie, binaire natif UNIQUEIDENTIFIER pour les GUID, XML pour les documents XML, JSON (SQL 2025+) pour les documents JSON au format binaire natif RowGUID UNIQUEIDENTIFIER, Config XML, Settings JSON

Les nuances de type de données nécessitent une attention particulière. Par exemple, utiliser FLOAT pour les données financières au lieu de DECIMAL peut introduire des erreurs d'arrondi qui ne peuvent pas être corrigées sans recalculer chaque valeur dépendante. Utiliser une clé primaire UNIQUEIDENTIFIER alors qu’un INT suffit triple la taille de votre index et ralentit chaque opération JOIN. La plupart de ces décisions affectent les performances de la base de données et peuvent déterminer si les requêtes s’exécutent en millisecondes ou en minutes.

Estimer les exigences de taille de table

La taille des tables n’est pas seulement à propos des coûts de stockage ; elle a un impact direct sur vos opérations de base de données. La taille de la table affecte les temps de sauvegarde et de restauration, les durées de reconstruction d’index et les performances des requêtes.

Important

Une table mal conçue qui stocke 200 octets par ligne au lieu de 100 octets double vos besoins de stockage, les temps de sauvegarde et les exigences d’E/S.

Un autre scénario de planification du dimensionnement de table consiste à calculer les coûts de stockage des bases de données cloud, à concevoir un espace disque limité ou à planifier des stratégies d’archivage. Ces scénarios nécessitent toutes des estimations précises de taille pour prendre des décisions éclairées sur les ressources et les opérations.

Par exemple, une entreprise de vente au détail stockant 100 millions de transactions quotidiennement avec un supplément de 50 octets par ligne gaspille 5 Go par jour, soit 1,8 To de stockage inutile, plus des augmentations proportionnelles du temps de sauvegarde et des coûts.

L’exemple suivant montre comment estimer la taille de la Employee table :

-- Estimate row size for a table
-- Fixed-length columns: sum of column sizes
-- Variable-length: estimate average size
-- Example row calculation:
CREATE TABLE Employee (
    EmployeeID INT,              -- 4 bytes
    FirstName NVARCHAR(50),      -- ~2-100 bytes (avg 40)
    LastName NVARCHAR(50),       -- ~2-100 bytes (avg 40)
    HireDate DATE,               -- 3 bytes
    Salary DECIMAL(10,2)         -- 5 bytes
);  
-- Estimated row size: 4 + 40 + 40 + 3 + 5 = ~92 bytes
-- Plus row overhead (~7 bytes) = ~99 bytes per row
-- 1 million rows ≈ 94 MB

Conseil / Astuce

Vous pouvez utiliser Copilot pour vous aider à générer l’estimation de la taille de la table.

Concevoir des colonnes effectives

L’exemple suivant illustre une table bien conçue Product qui applique les principes abordés dans cette unité :

CREATE TABLE Product (
    ProductID INT PRIMARY KEY IDENTITY(1,1),       -- Auto-incrementing surrogate key (4 bytes)
    ProductName NVARCHAR(100) NOT NULL,             -- Unicode support, appropriate length, enforced
    Category NVARCHAR(50) NOT NULL,                 -- Smaller than ProductName (categorization needs less space)
    Price DECIMAL(10,2) NOT NULL,                   -- Exact precision for financial data
    StockQuantity INT NOT NULL DEFAULT 0,           -- Integer sufficient for inventory, default prevents nulls
    LastRestocked DATETIME2 DEFAULT GETUTCDATE()    -- Modern date type with automatic timestamp
);

Ce tableau présente plusieurs bonnes pratiques :

  • Types de données appropriés : INT pour la clé primaire (inférieure BIGINT ou UNIQUEIDENTIFIER), DECIMAL(10,2) pour des calculs financiers précis au lieu de FLOAT, DATETIME2 pour une meilleure précision que l’ancienne DATETIME
  • Colonnes de taille appropriée : NVARCHAR(100) pour les noms de produits et NVARCHAR(50) pour les catégories en fonction de la longueur des données attendue
  • Contraintes : NOT NULL garantit la qualité des données en empêchant les valeurs critiques manquantes
  • Valeurs par défaut : Les valeurs automatiques pour StockQuantity (0) et LastRestocked (heure UTC actuelle) réduisent la complexité du code d’application
  • Clé primaire efficace : IDENTITY génère des clés séquentielles qui se regroupent efficacement et utilisent un stockage minimal (4 octets contre 16 octets pour GUID)

Note

Cet exemple utilise NVARCHAR (2 octets par caractère) pour la prise en charge Unicode. Si vos données sont ASCII uniquement, VARCHAR (1 octet par caractère) réduit le stockage de chaînes en moitié. A ProductName VARCHAR(100) utilise ~30 octets et ~60 octets pour NVARCHAR(100) un nom de 30 caractères. Sur 10 millions de lignes, cela permet d’économiser environ 300 Mo. Utilisez NVARCHAR pour les données internationales ; utilisez VARCHAR lorsque l’efficacité du stockage importe et que les données restent ASCII seulement.

Meilleures pratiques en matière de conception

Appliquez ces principes clés lors de la conception et de l’implémentation de tables pour garantir les performances et la facilité de maintenance :

  • Utiliser les types de données appropriés - Les types de données plus petits réduisent le stockage et améliorent les performances
  • Prendre en compte la taille de la table au début - Estimer la taille de ligne et la taille totale de la table pour planifier le stockage et l’indexation
  • Implémenter des contraintes significatives - Garantir la qualité des données au niveau de la base de données
  • Planifier la croissance - Concevoir des tables pour gérer le volume de données futur
  • Indexer de manière stratégique - Colonnes d’index utilisées dans les clauses WHERE, JOIN et ORDER BY
  • Choisir columnstore pour l’analytique - Utiliser des index columnstore pour les tables volumineuses avec des requêtes analytiques
  • Normaliser le cas échéant - Équilibrer la normalisation avec les besoins en matière de performances des requêtes
  • Surveiller la compression des lignes et des pages - Activer la compression pour les tables volumineuses afin d’enregistrer le stockage

La plupart des problèmes de performances de base de données découlent de décisions de conception médiocres prises au début du développement. Les types de données surdimensionnés gaspillent le stockage et les requêtes lentes. Les types d’index manquants ou incorrects créent des goulots d’étranglement que les mises à niveau des ressources ne peuvent pas résoudre. Empêchez ces problèmes en investissant du temps dans une conception d’objet appropriée avant de créer ou de modifier des tables. Les décisions que vous prenez lors de la conception ( choix des types de données appropriés, estimation des tailles de table, sélection des types d’index appropriés) ont beaucoup plus d’effet sur les performances et les coûts à long terme que toute optimisation que vous pouvez appliquer ultérieurement.