Comment créer et gérer une table temporaire avec SQL Server ?
Définitions des tables temporaires SQL Server
Les tables temporaires SQL Server permettent de stocker temporairement des données. La commande CREATE TABLE en T-SQL permet de créer les tables temporaires. Dans ce tutoriel MS SQL, nous allons apprendre à connaître les tables temporaires SQL Server et à quoi elles servent. Il existe deux types de tables temporaires, les tables temporaires locales et les tables temporaires globales. Nous verrons également comment les créer, y insérer des données, les lister et enfin les supprimer.
SQL est un langage de programmation conçu pour la gestion des données dans les systèmes de gestion de bases de données relationnelles. Utiliser les instructions SQL pour créer, lire, mettre à jour et supprimer des données dans la base de données, et le principal objet utilisé est la table. Une table temporaire permet de stocker des données temporaires qui utilisées uniquement pendant la durée d’une seule session.
Le système supprime les tables temporaires lorsqu’une session est terminée. Une session peut-être un onglet connecté sur SQL Server Management Studio ou encore une connexion depuis un logiciel tiers est fermée ou lorsque vous exécutez une requête DROP TABLE sur elles.
Différence entre une table SQL Server temporaire et une table permanente
Quelle est la différence entre une table permanente et une table temporaire dans SQL Server ?
Une table permanente est créée lorsqu’un utilisateur la crée pour la première fois et n’est supprimée que de manière explicite. Elle reste donc dans la base de données. Elle est stockée sur le disque, et elle est sauvegardée si les sauvegardes sont actives sur la base de données du serveur SQL.
Une simple instruction SQL CREATE TABLE permet de créer es tables temporaires. Et elles sont supprimées après la fin d’une procédure stockée, ou après la fermeture de la session ou après la fermeture de toutes les tâches faisant référence à la table (pour les tables temporaires globales).
Avantages des tables temporaires dans un projet T-SQL
Le principal objectif et avantage des tables temporaires est de stocker les ensembles de résultats intermédiaires des requêtes. Ces ensembles de résultats ne sont pas stockés sur le disque et en mémoire. Les tables temporaires peuvent être créées pour stocker les données d’une seule ou de plusieurs requêtes.
Par exemple, vous devez créer une transformation très complexe en utilisant des données de plusieurs tables et vues, et vous avez seulement besoin de stocker le résultat. Par exemple, vous devez faire pivoter toutes les données d’une table, puis calculer une moyenne et les additionner, et enfin répartir les données à l’aide d’un ratio à différentes dates. Il est alors plus facile d’utiliser des tables intermédiaires temporaires plutôt qu’une procédure stockée longue et complexe codée en T-SQL en une seule fois.
Nous recommandons de diviser le code en étapes à l’intérieur des procédures stockées. Par exemple :
- #temp_1_pivot
- #temp_2_moyenne
- #temp_3_group_by_year
- #temp_4_ratio
Toutefois, n’utilisez pas les tables temporaires pour le stockage à long terme ou pour les données fréquemment consultées.
Les tables temporaires locales SQL Server
Une procédure stockée peut créer et appeler une table temporaire locale. Dans ce cas, il est possible d’appeler la même procédure stockée en même temps sans interférence. Le moteur de base de données MS SQL distingue les tables en ajoutant un suffixe numérique à la fin du nom de la table, ce suffixe numérique est répertorié dans la table système sys.sysobjects.
Créer une table temporaire locale
Les tables temporaires locales sont stockées dans la base de données système tempdb sur la même instance que la base de données qui les a créées. Toutes les tables temporaires sont créées dans le schéma dbo, il est donc inutile d’indiquer un schéma spécifique. Pour créer une table temporaire locale, il suffit d’exécuter une requête comme celle-ci:
-- Créer une table temporaire locale -- avec deux colonnes CREATE TABLE #Sales_Temp ( [MonthName] nvarchar(20), [Amount] numeric(8) ); GO -- Insérer des données pour les trois -- premiers mois de l'année INSERT INTO #Sales_Temp (MonthName, Amount) VALUES ( N'January', 1000); INSERT INTO #Sales_Temp (MonthName, Amount) VALUES ( N'February', 2000); INSERT INTO #Sales_Temp (MonthName, Amount) VALUES ( N'March', 3000); GO -- fourni par https://expert-only.com
Cependant, il est également possible de créer une table temporaire en dehors d’une procédure stockée, comme dans une simple instruction T-SQL. Il existe alors un moyen d’éviter les erreurs si une procédure stockée a exactement le même nom à l’intérieur d’une procédure.
Il est recommandé de vérifier et de supprimer toute table temporaire existante dans la base de données, même si elle n’est pas permanente.
Supprimer une table SQL Server temporaire locale
Pour supprimer une table temporaire locale, utilisez la syntaxe suivante :
DROP TABLE IF EXISTS #Sales_Temp; GO
Supprimer une table temporaire locale sur une version MS SQL avant 2016
Pour toutes les versions SQL server avant la version 2016, c’est à dire 2008, 2012, 2014, etc., utiliser ce script :
IF OBJECT_ID('#Sales_Temp', 'U') IS NOT NULL
DROP TABLE #Sales_Temp;
GO
Voici un autre tutoriel informatique pour gérer les tables SQL Server classiques cette fois, c’est à dire les tables permanentes.
Les tables temporaires globales
D’autre part, le système de supprime les tables temporaires globales qu’une fois toutes les tâches sur la table terminées. En d’autres termes, le système la supprime après avoir terminé la dernière requête T-SQL dans la même session qui a créé la table.
Créer une table SQL Server temporaire globale
Ce script permet de créer une table temporaire globale simple :
-- Créer une table temporaire globale -- avec deux colonnes CREATE TABLE ##Sales_Global ( [MonthName] nvarchar(20), [Amount] numeric(8) ); GO -- Insérer des données pour les trois -- premiers mois de l'année INSERT INTO ##Sales_Global (MonthName, Amount) VALUES ( N'January', 1000); INSERT INTO ##Sales_Global (MonthName, Amount) VALUES ( N'February', 2000); INSERT INTO ##Sales_Global (MonthName, Amount) VALUES ( N'March', 3000); GO -- fournit par https://expert-only.com
Supprimer une table temporaire globale
Utiliser donc ce script SQL pour supprimer une table temporaire globale avec une requête exécutée depuis un onglet SSMS.
DROP TABLE IF EXISTS ##Sales_Global ; GO
Supprimer une table temporaire globale avec une version SQL Server avant 2016
Par contre, supprimer une table temp globale avec ce script.
IF OBJECT_ID('##Sales_Global','U') IS NOT NULL
DROP TABLE #Sales_Global;
GO
Lister toutes les tables temporaires de l’instance
Premièrement, pour lister toutes les tables temporaires créées sur l’instance de SQL server, utilisez cette requête T-SQL :
SELECT * FROM tempdb.sys.sysobjects WHERE [name] like '#%' ORDER BY [name];
Deuxièmement, pour répertorier uniquement la table temporaire locale créée sur l’instance du serveur SQL, utilisez cette instruction T-SQL :
SELECT * FROM tempdb.sys.sysobjects WHERE [name] like '#%' AND [name] not like '##%' ORDER BY [name];
Troisièmement, pour répertorier uniquement la table temporaire globale créée sur l’instance actuelle, utilisez cet exemple T-SQL :
SELECT * FROM tempdb.sys.sysobjects WHERE [name] like '##%' ORDER BY [name];
Les avantages des tables temporaires dans les requêtes T-SQL
Ceci est une liste courte, non exhaustive des avantages apportés par les tables temporaires :
- Stocker des résultats intermédiaires pour des opérations de données complexes.
- Stocker les résultats d’une requête exécutée de façon répétée. Mettre en cache les données permet de calculer et stocker une seule fois et d’augmenter les performances. C’est donc un espace de travail idéal pour traiter de grandes quantités de données.
- Les tables temporaires locales sont créées dans la base de données système tempdb et ne sont pas visibles en dehors de la session dans laquelle elles ont été créées.
- Le moteur de base de données gère de façon automatique les tables temporaires.
Limitations des tables temporaires
Les tables temporaires de SQL Server ne prennent pas en charge les clés étrangères. Si le script de création de la table utilise une clé étrangère, l’exécution n’échouera pas mais la clé ne sera pas créée et le moteur de base de données affichera un message d’avertissement.
Ce tutoriel sur les tables temporaire, locales ou globales, explique donc comment les créer, insérer des données, supprimer et les lister depuis les tables systèmes. Voici un autre tutoriel pour lister toutes les tables d’une base de données SQL Server.
Soyez le premier à commenter