Table SQL

Composants de base de la structure d'une table SQL

Colonnes, types de données et contraintes

• CHAR : chaîne de caractères de longueur fixe
• VARCHAR : Chaîne de caractères de longueur variable
• INT : Valeur numérique entière
• DECIMAL : Valeur numérique à virgule fixe
• DATE : Valeur de la date
• TIME : Valeur de l'heure

• NOT NULL : garantit qu'une colonne contient toujours une valeur.
• UNIQUE : garantit que chaque valeur d'une colonne est unique.
• CLÉ PRIMAIRE : permet d'identifier de façon unique chaque ligne d'une table.
• FOREIGN KEY : Relie deux tables entre elles.
• CHECK : Valide si une condition spécifiée est vraie

Clés primaires et étrangères

Index et optimisation des performances

• Index en grappe : Détermine l'ordre physique des données dans une table.
• Index non groupé : Maintient une structure d'index séparée, avec des pointeurs vers les données réelles.
• Index unique : Assure l'unicité des colonnes indexées
• Index plein texte : Permet de rechercher des mots ou des phrases dans les colonnes de texte.

Types de tables SQL et leur utilisation

Les types de tables SQL ont diverses classifications basées sur leur utilisation et leur fonctionnalité. Parmi les plus courantes, on trouve les tables temporaires, les tables système et les tables partitionnées. Elles servent à différentes fins pour le stockage efficace des données, le stockage des résultats intermédiaires et la gestion de la base de données.

Tables temporaires pour les résultats intermédiaires

• Table temporaire locale : Créées avec le préfixe #, elles ne sont visibles que dans la session de l'utilisateur en cours et sont automatiquement abandonnées lorsque la session se termine.
• Table temporaire globale : Préfixées par ##, elles peuvent être partagées entre différentes sessions d'utilisateurs mais sont abandonnées automatiquement lorsque la dernière session faisant référence à la table est fermée.

• Manipulation de données complexes : Lorsqu'il s'agit d'ensembles de données ou de calculs volumineux, les tables temporaires peuvent aider à décomposer les opérations en parties gérables.
• Récapitulation des données : Elles facilitent les calculs sur des données agrégées et peuvent affiner les résultats à des fins de rapport ou d'analyse.
• Filtrage des données : Les tables temporaires peuvent être utilisées pour filtrer les données ou stocker des résultats préliminaires en vue d'un traitement ultérieur par des requêtes ultérieures.

Tables système pour la gestion des bases de données

Tables partitionnées pour un stockage efficace des données

• Performance des requêtes : En raison de la localité des données, les requêtes qui filtrent les données en fonction de la colonne de partitionnement peuvent accéder efficacement aux seules partitions pertinentes, ce qui réduit la nécessité d'effectuer des balayages complets de la table.
• Gestion des données : Les tables partitionnées permettent des opérations efficaces de maintenance des données telles que l'archivage, la suppression ou le chargement des données à l'aide de techniques de commutation de partitions.
• Optimisation du stockage : Les partitions peuvent être stockées sur différents groupes de fichiers, ce qui permet une meilleure gestion du stockage et une meilleure utilisation des ressources.
• Parallélisme : Les requêtes impliquant l'agrégation de données ou des calculs complexes peuvent être exécutées en parallèle sur plusieurs partitions, ce qui permet d'obtenir des temps de réponse plus rapides.

Exploration d'exemples de tables SQL

Création de tables SQL avec la syntaxe de base

En SQL, l'instruction CREATE TABLE est utilisée pour créer et définir des tables. Pour créer une table SQL, il est nécessaire de définir les colonnes, les types de données associés à chaque colonne et, éventuellement, des contraintes pour assurer l'intégrité des données. Voici un exemple d'instruction CREATE TABLE de base :

 CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, DateOfBirth DATE, Email VARCHAR(100) UNIQUE, Position VARCHAR(50), Salary DECIMAL(10, 2) ) ;

Dans cet exemple, nous créons une table Employés avec les colonnes suivantes :

• EmployeeID : Type de données entier et sert de clé primaire identifiant de manière unique chaque enregistrement.
• Prénom : Chaîne de caractères de longueur variable avec un maximum de 50 caractères et ne peut pas être nulle.
• Nom : Chaîne de caractères de longueur variable comprenant un maximum de 50 caractères et ne pouvant être nulle.
• Date de naissance : Type de données de type date pour stocker la date de naissance
• Email : Chaîne de caractères de longueur variable comprenant un maximum de 100 caractères et imposant l'unicité.
• Position : Chaîne de caractères de longueur variable avec un maximum de 50 caractères
• Salaire : Type de données décimal avec 10 chiffres au total et deux décimales.

Modification des tables SQL : ALTER et UPDATE

  ALTER TABLE Employés ADD Département VARCHAR(50) ; 

  ALTER TABLE Employees ALTER COLUMN Salary DECIMAL(12, 2) ; 

  ALTER TABLE Employees DROP COLUMN DateOfBirth ; 

Récupérer des données dans des tables SQL : SELECT et JOIN

 SELECT EmployeeID, FirstName, LastName, Salary FROM Employees ORDER BY LastName ; 

SELECT E.EmployeeID, E.FirstName, E.LastName, E.Salary, D.DepartmentName FROM Employees E INNER JOIN Departments D ON E.DepartmentID = D.DepartmentID ORDER BY D.DepartmentName, E.LastName ; 

Variables de table SQL : Stockage de données temporaires

Définition et utilisation des variables de table SQL

Les variables de table SQL sont déclarées à l'aide de l'instruction DECLARE avec la structure de la table et sont remplies de données à l'aide d'instructions INSERT. Elles constituent un moyen flexible de stocker des résultats intermédiaires pour des tâches de manipulation de données complexes et des processus de synthèse de données. Voici un exemple de création d'une variable de table et de son remplissage avec des données :

DECLARE @EmployeeSales TABLE ( EmployeeID INT, LastName VARCHAR(50), SalesAmount DECIMAL(10, 2) ) ; INSERT INTO @EmployeeSales (EmployeeID, LastName, SalesAmount) SELECT EmployeeID, LastName, SUM(SalesAmount) FROM Sales GROUP BY EmployeeID, LastName ;

Dans cet exemple, une variable de table nommée @EmployeeSales est déclarée avec les colonnes EmployeeID, LastName et SalesAmount. L'instruction INSERT remplit ensuite la variable de table avec les données de vente agrégées pour chaque employé.

Les variables de table présentent certains avantages par rapport à leurs homologues temporaires, notamment :

• L'utilisation du journal des transactions : Les opérations des variables de table sont moins souvent enregistrées, ce qui réduit l'utilisation de l'espace du journal des transactions et, par conséquent, améliore les performances.
• Portée et durée de vie : Les variables de table ont une portée bien définie et sont automatiquement nettoyées lorsqu'elles sortent de leur portée, ce qui simplifie la gestion de la mémoire.
• Recompilations : Les modifications des variables de table n'entraînent pas de recompilation de la procédure stockée ou de la requête qui les contient, ce qui conduit à des performances de requête plus prévisibles.

Comparaison entre les tables temporaires et les variables de table

• Portée et durée de vie : Les tables temporaires ont une portée plus large et existent jusqu'à la fin de la session de l'utilisateur actuel ou jusqu'à ce qu'elles soient explicitement abandonnées. Les variables de tableau ont une portée locale, limitée au contexte dans lequel elles ont été définies, et sont automatiquement nettoyées lorsqu'elles sortent de leur portée.
• Performance : Les variables de tableau ont généralement de meilleures performances en raison de la réduction de la journalisation et de l'absence de recompilation en cas de modification. Toutefois, cet avantage peut devenir insignifiant pour les grands ensembles de données ou les opérations complexes avec de multiples modifications.
• Index : Les tables temporaires prennent en charge la création d'index, alors que les variables de table ne permettent que la création de contraintes de clé primaire et d'unique dans leur déclaration.
• Statistiques : SQL Server maintient des statistiques pour les tables temporaires, ce qui peut conduire à une meilleure optimisation des requêtes, alors que les variables de table ne disposent pas de statistiques.
• Parallélisme : Les tables temporaires permettent des plans d'exécution parallèles, alors que les variables de table sont limitées à des plans d'exécution en série.
• Contraintes : Les tables temporaires prennent en charge l'ajout de contraintes, tandis que les variables de table ne prennent en charge que les contraintes PRIMARY KEY et UNIQUE au moment de la déclaration.
• Sécurité des transactions : Les modifications des tables temporaires font partie des transactions et peuvent être annulées, alors que les opérations sur les variables de table ne sont pas soumises aux annulations de transactions.

SQL Table vs View : Comprendre les différences

Avantages des vues SQL par rapport aux tables SQL

• Sécurité des données : Les vues peuvent limiter l'accès à un sous-ensemble d'enregistrements ou de colonnes, ce qui constitue une couche supplémentaire de protection des données et de contrôle d'accès. Par exemple, une vue peut masquer des colonnes sensibles, comme le salaire ou le numéro de sécurité sociale, tout en affichant les informations pertinentes aux utilisateurs.
• Abstraction et simplification : Les vues peuvent simplifier les requêtes complexes impliquant plusieurs tables et jointures, en fournissant une interface conviviale pour accéder et manipuler des données connexes. Les utilisateurs peuvent interagir avec la vue comme s'il s'agissait d'une table unique, sans avoir besoin de comprendre la structure ou les relations de la table sous-jacente.
• Agrégation des données : Les vues peuvent stocker des données agrégées, ce qui facilite l'utilisation d'informations récapitulatives, la récupération de résultats précalculés et l'exécution de tâches de reporting ou d'analyse.
• Représentation cohérente des données : Les vues peuvent maintenir une représentation cohérente des données face aux changements de schéma ou de table. Les applications ou les requêtes qui s'appuient sur une vue ne nécessiteront que des mises à jour minimales, voire aucune, lorsque les tables sous-jacentes sont modifiées.
• Optimisation des requêtes : Les vues peuvent optimiser les performances des requêtes en stockant des résultats de requête précalculés ou en appliquant des filtres spécifiques, ce qui réduit la surcharge de recherche de données et améliore les temps de réponse.

Cas d'utilisation des tables et vues SQL

• Stocker les informations sur les clients dans une plateforme de commerce électronique.
• Conserver une liste de produits et leurs niveaux de stock dans un système de gestion des stocks.
• Enregistrer les activités des utilisateurs dans une application Web.

• Restreindre l'accès aux données confidentielles, comme les salaires ou les courriels personnels, en créant des vues qui excluent ces colonnes.
• Créer une vue qui combine les informations sur les clients et les commandes pour faciliter les tâches de reporting ou d'analyse dans une plateforme de commerce électronique.
• Définir des vues avec des métriques précalculées, comme les ventes totales par produit, pour accélérer la récupération des données dans un système de gestion des stocks.