Redgate Hub (Français)

Les tables temporaires ne sont que cela. Ils sont le plus souvent utilisés pour fournir un espace de travail pour les résultats intermédiaires lors du traitement des données au sein dun lot ou dune procédure. Ils sont également utilisés pour transmettre une table à partir dune fonction table, pour passer des données basées sur une table entre des procédures stockées ou, plus récemment sous la forme de paramètres table, pour envoyer des tables entières en lecture seule des applications aux routines SQL Server ou transmettez des tables temporaires en lecture seule comme paramètres. Une fois leur utilisation terminée, elles sont automatiquement supprimées.

Avant de nous plonger dans la technologie, je vous conseille dutiliser des variables de table lorsque cela est possible. Ils sont faciles et SQL Server fait le travail pour vous. Ils ont également tendance à causer moins de problèmes à un système OLTP qui travaille dur. Juste de temps en temps, vous devrez peut-être les affiner pour obtenir de bonnes performances dans les jointures, mais je vous lexpliquerai dans un instant, cependant, si vous effectuez un traitement plus complexe sur des données temporaires ou si vous utilisez probablement plus que raisonnablement petit les quantités de données quils contiennent, les tables temporaires locales seront probablement un meilleur choix.

Variables de table

Les variables de table sont utilisées dans le cadre de la routine ou du lot dans lequel elles sont définies et ont été créées à lorigine pour rendre possibles les fonctions table. Cependant, ils conviennent à de nombreuses utilisations de la table temporaire traditionnelle. Ils se comportent comme les autres variables dans leurs règles de portée. Une fois hors de portée, ils sont éliminés. Celles-ci sont beaucoup plus faciles à utiliser et assez sécurisées, et elles déclenchent également moins de recompilations dans les routines où elles sont utilisées que si vous utilisiez des tables temporaires. Les variables de table nécessitent moins de ressources de verrouillage car elles sont «privées» pour le processus qui les a créées. Les annulations de transaction ne les affectent pas car les variables de table ont une portée limitée et ne font pas partie de la base de données persistante, elles sont donc pratiques pour créer ou stocker des données qui devraient survivre aux annulations telles que les entrées de journal. Linconvénient des variables de table est quelles sont souvent éliminées avant que vous puissiez étudier leur contenu pour le débogage, ou les utiliser pour essayer différentes expressions SQL de manière interactive.

Si votre application est prudente et que vos volumes de données vous éclairent Je ne veux jamais rien dautre. Cependant, vous pouvez rencontrer des problèmes. Une difficulté est que les variables de table ne peuvent être référencées que dans leur portée locale, vous ne pouvez donc pas les traiter à laide de SQL dynamique comme vous le feriez avec une table temporaire ou un paramètre table. En effet, vous ne pouvez pas faire référence à une variable de table définie en externe dans le SQL dynamique que vous exécutez ensuite via linstruction EXEC ou la procédure stockée sp_ExecuteSQL car le SQL dynamique est exécuté en dehors de la portée de la variable de table. Vous pouvez, bien sûr, créer, puis utiliser, la variable de table à lintérieur du SQL dynamique car la variable de table serait dans la portée. Cependant, une fois le SQL dynamique exécuté, il ny aurait pas de variable de table

Il y a aussi quelques anomalies à prendre en compte. Vous ne pouvez pas, par exemple, modifier la définition de la table après linstruction DECLARE initiale. Dans SQL Server 2000, une variable de table ne peut pas être la destination dune instruction SELECT INTO ou dune INSERT EXEC (désormais corrigée); Vous ne pouvez pas appeler des fonctions définies par lutilisateur à partir des contraintes CHECK, des valeurs DEFAULT et des colonnes calculées dans la variable de table. Les seules contraintes autorisées au-delà des contraintes CHECK sont PRIMARY KEY, UNIQUE KEY et NULL / NOT NULL

Les problèmes les plus délicats, cependant, viennent avec laugmentation de la taille des tables, car, avant SQL Server 2016 , vous ne pouviez pas déclarer un index explicitement et les index qui appliquaient les contraintes UNIQUE et PRIMARY KEY navaient pas dindex de distribution maintenu sur eux. Vous pouvez désormais créer certains types dindex en ligne avec la définition de table, mais les statistiques de distribution ne sont toujours pas gérées sur eux. LOptimiseur de requête suppose quil ny a quune seule ligne dans le tableau. Vous ne pouvez pas non plus générer de plans de requête parallèles pour une expression SQL qui modifie le contenu de la table. Pour contourner partiellement la restriction dindex, vous pouvez utiliser des contraintes pour faire la même chose. Le plus essentiel est la contrainte de clé primaire qui vous permet dimposer un index clusterisé, mais les contraintes uniques sont utiles pour les performances. L’optimiseur de requêtes les utilisera volontiers s’ils existent.

Le plus gros problème avec les variables de table est que les statistiques ne sont pas gérées sur les colonnes. Cela signifie que loptimiseur de requêtes doit faire une estimation de la taille et de la distribution des données et sil se trompe, vous allez voir des performances médiocres sur les jointures: si cela se produit, vous ne pouvez pas faire grand-chose dautre que de revenir à lutilisation des tables temporaires locales classiques. À partir de SQL Server 2019, Microsoft a introduit une nouvelle fonctionnalité appelée Compilation différée des variables de table qui résout ce problème. Pour en savoir plus, lisez cet article de Greg Larsen.

Si vous nutilisez pas SQL Server 2019, une chose que vous pouvez essayer est dajouter OPTION (RECOMPILE) à linstruction qui implique la jointure de la variable de table avec dautres tables. En faisant cela, SQL Server sera en mesure de détecter le nombre de lignes lors de la recompilation, car les lignes auront déjà été remplies. Cela ne résout pas entièrement le problème car l’optimiseur n’a toujours pas de statistiques de distribution et peut, généralement lorsque la distribution est biaisée, produire un mauvais plan. Dans cette démo, la jointure a été réduite dans le temps de trois quarts simplement en ajoutant lOPTION (RECOMPILE)

Maintenant, si vous pouvez rendre ce qui entre dans les tables unique, vous pouvez alors utiliser une contrainte de clé primaire sur ces les tables. Cela a permis à loptimiseur dutiliser une recherche dindex en cluster au lieu dune analyse de table et le temps dexécution était trop rapide à mesurer.

Commencez avec des variables de table, mais revenez à lutilisation de tables temporaires locales si vous rencontrez des problèmes de performances. Certaines personnes sont assez audacieuses pour donner des conseils en termes de nombre de lignes dans un tableau, et jai vu 100 ou 1 000 proposés au maximum; mais j’ai vu des variables de table beaucoup plus grandes fonctionner de manière parfaitement satisfaisante dans le temps, et des variables beaucoup plus petites posent problème. Cependant, dans les petites tables, le problème est moins détectable!

Paramètres de table

Le paramètre de table (TVP) est un type spécial de variable de table qui étend son utilisation. Lorsque les variables de table sont passées en tant que paramètres, la table est matérialisée dans la base de données système TempDB en tant que variable de table et transmise par référence, un pointeur vers la table dans TempDB.

Les paramètres de table ont été utilisés depuis SQL Server 2008 pour envoyer plusieurs lignes de données à une routine Transact-SQL ou à un batch via sp_ExecuteSQL .. Leur valeur particulière pour le programmeur est quelles peuvent être utilisées dans le code TSQL comme ainsi que dans lapplication client, ils sont donc utiles pour envoyer des tables client au serveur. À partir de TSQL, vous pouvez déclarer des variables table, y insérer des données et transmettre ces variables en tant que paramètres table aux procédures et fonctions stockées. Leur utilité plus générale est limitée par le fait quelles ne sont transmises quen lecture seule. Vous ne pouvez pas effectuer dinstructions UPDATE, DELETE ou INSERT sur une table dans le corps dune routine.

Vous devez créer un type de table défini par lutilisateur et définir une structure de table pour les utiliser. Voici un exemple simple de leur utilisation dans TSQL

Comme pour les variables de table, le paramètre table cesse dexister une fois quil est hors de portée mais la définition de type reste jusquà ce quelle soit explicitement supprimée.Comme les variables de table, elles nacquièrent pas de verrous lorsque les données sont remplies à partir dun client, et les statistiques ne sont pas conservées sur les colonnes de paramètres table. Vous ne pouvez pas utiliser un paramètre table comme cible dune instruction SELECT INTO ou INSERT EXEC. Comme vous vous en doutez, un paramètre table peut être dans la clause FROM de SELECT INTO ou dans le INSERT EXEC chaîne ou procédure stockée.

Le TVP résout le problème courant de vouloir passer une variable locale au SQL dynamique qui est ensuite exécuté par un sp_ExecuteSQL. Il est mal documenté par Microsoft, donc je vais vous montrer un exemple concret pour vous aider à démarrer

Avant de passer à la description des tables temporaires plus traditionnelles et de leur utilisation, nous devrons nous plonger dans la endroit où les tables temporaires sont tenues. TempDB.

TempDB

Les tables temporaires et les variables de table sont créées dans la base de données TempDB, qui nest en réalité quune autre base de données avec une récupération simple: avec TempDB, seule une journalisation «  minimale suffisante est effectuée pour permettre la restauration et dautres subtilités ACID. La différence particulière de TempDB est que tous les objets tels que les tables sont effacés au démarrage. Étant donné que TempDB utilise toujours le modèle de récupération simple, la transaction terminée est effacée du journal du journal au prochain point de contrôle TempDB et seules les transactions actives sont conservées. Tout cela signifie que les tables temporaires se comportent comme nimporte quel autre type de table de base en ce sens quelles sont journalisées et stockées comme elles. En pratique, les tables temporaires sont susceptibles de rester en mémoire cache, mais uniquement si elles sont fréquemment utilisées: comme avec une table de base. TempDB exploite un système appelé réutilisation dobjets temporaires, qui mettra en cache une partie des objets temporaires avec le plan, si la mémoire est suffisante. Cela peut expliquer la légende selon laquelle les objets temporaires nexistent quen mémoire. La vérité est que « ça dépend… ».

Beaucoup dautres choses se passent dans TempDB: le moteur de base de données peut lutiliser pour placer des tables de travail pour les vérifications DBCC, pour créer ou reconstruire des index, des curseurs, par exemple. Les tables intermédiaires dans les requêtes décrites comme des «hachages», des «tris» et des «spools» sont matérialisées dans TempDB, par exemple, avec celles requises pour plusieurs opérations «physiques» lors de l’exécution des instructions SQL. Il est également utilisé comme magasin de versions pour lisolation des instantanés, les ensembles de résultats actifs multiples (MARS), les déclencheurs et la construction dindex en ligne.

Comme les tables temporaires sont stockées comme les tables de base, il y en a une ou deux des choses dont vous devez vous méfier. Vous devez, par exemple, disposer de lautorisation CREATE TABLE dans TempDB pour créer une table normale. Pour vous éviter les problèmes, cela est attribué par défaut au rôle DBO (propriétaire de la base de données), mais vous devrez peut-être le faire explicitement pour les utilisateurs qui ne sont pas affectés au rôle DBO. Tous les utilisateurs sont autorisés à créer des tables temporaires locales ou globales dans TempDB car elles leur sont attribuées via le contexte de sécurité utilisateur GUEST.

La table temporaire classique est disponible en deux versions, la table temporaire globale ou partageable, préfixée par ##, et la table temporaire locale, dont le nom est préfixé par #. Les tables temporaires locales ressemblent moins aux tables normales que les tables temporaires globales: vous ne peut pas créer de vues sur eux ou leur associer des déclencheurs. Il est un peu difficile de déterminer quel processus, session ou procédure les a créés. Nous vous aiderons un peu plus tard. Plus important encore, elles sont plus sécurisées quune table temporaire globale car seul le processus propriétaire peut la voir.

Une autre bizarrerie de la table temporaire locale (et de la procédure stockée temporaire locale) est quelle a un nom différent dans les métadonnées à celle que vous lui donnez dans votre routine ou lot. Si la même routine est exécutée simultanément par plusieurs processus, le moteur de base de données doit être capable de distinguer les tables temporaires locales de même nom créées par les différents processus. Pour ce faire, il ajoute une chaîne numérique à chaque nom de table temporaire local complété à gauche par des caractères de soulignement. Bien que vous spécifiiez le nom court tel que #MyTempTable, ce qui est réellement stocké dans TempDB est constitué du nom de table spécifié dans linstruction CREATE TABLE et le suffixe. En raison de ce suffixe, les noms de table temporaires locaux doivent contenir 116 caractères ou moins.

Si vous souhaitez voir ce qui se passe, vous pouvez afficher les tables dans TempDB de la même manière que vous le feriez pour nimporte quel autre table. Vous pouvez même utiliser le travail sp_help sur des tables temporaires uniquement si vous les appelez depuis TempDB.

ou vous pouvez les trouver dans les vues système de TempDB sans changer de base de données.

Ou le schéma dinformation

Encore mieux, vous pouvez découvrir quel processus, et quel utilisateur, tient sur dénormes tables temporaires dans TempDB et refuse de céder de lespace

Vous ne pouvez pas utiliser de types de données définis par lutilisateur dans des tables temporaires à moins que les types de données existent dans TempDB; cest-à-dire, à moins que les types de données naient été explicitement créés.

Tables utilisateur dans TempDB

En utilisation normale, vous allez créer des tables temporaires ou des variables de table sans trop y réfléchir. Cependant, il est intéressant de noter que TempDB est là pour toute sorte dactivité sandbox. Vous pouvez créer des tables de base ordinaires, des vues ou tout ce que vous voulez. Vous pouvez créer des schémas, des procédures stockées, etc. Vous ne voudrez probablement pas faire cela, mais cest certainement possible car TempDB nest quune autre base de données. Je viens de redémarrer mon développement SQL Server après me le prouver en installant AdventureWorks dessus. Cela signifie quil est possible de créer une table de base dans TempDB, une sorte de ..er… table permanente temporaire. Contrairement à la table temporaire globale, vous devez faire tout votre propre ménage: vous êtes seul. La même chose est vraie des routines. Lavantage de faire cela est que tout traitement que vous effectuez utilise la récupération simple de TempDB de sorte que, si vous ne parvenez pas à nettoyer, SQL Server agit comme mère au prochain démarrage: bien que cela puisse être très long. L’étape suivante consiste à créer ce que j’appelle une table «temporaire persistante». Dans ce tableau, les données elles-mêmes sont volatiles lorsque le serveur redémarre, mais la table elle-même persiste. Le moyen le plus courant de créer une table temporaire persistante consiste probablement à recréer au démarrage une table temporaire globale. Cette opération peut être effectuée automatiquement lorsque toutes les bases de données sont récupérées et que le message « La récupération est terminée » est consigné. Même sil sagit dun « temporaire global », il nest pas supprimé lorsque toutes les connexions qui lutilisent ont disparu, car le processus qui lexécute ne disparaît jamais. On peut soutenir quil est préférable de créer ce type de table de travail dans la base de données qui lutilise, cependant, si vous utilisez la restauration complète, le travail temporaire restera dans le journal. Vous pouvez, bien sûr, simplement créer un fichier ordinaire table dans TempDB. Vous pouvez créer ces tables «persistantes» au démarrage en définissant une procédure stockée dans master qui crée la table temporaire globale

Pourquoi utiliser ce type de table hybride? Il y a, par exemple, un nombre de techniques permettant de passer des tables entre procédures via des tables «persistantes» de manière multiprocessus, de manière à effectuer une série de traitements sur les données. Celles-ci sont appelées tables à clé de processus (voir «Comment partager des données entre des procédures stockées : Table Process-Keyed par Erland Sommarskog ). Ils soulèveront initialement les sourcils de tout DBA chevronné, mais ils sont une solution efficace et sûre à un problème éternel, lorsquils sont faits correctement.

En plus des tables temporaires, il existe également un certain nombre de types de tables qui ne sont pas directement dérivées des tables de base, telles que les «fausses» tables et les tables dérivées: certaines dentre elles sont si éphémères quil vaut mieux les considérer comme éphémères plutôt que temporaires. Le CTE utilise des tables éphémères qui sont «en ligne» ou «dérivées» et ne sont pas matérialisées. BOL les appelle «ensembles de résultats nommés temporaires». Ils nexistent que dans le cadre de lexpression. Dans un CTE, ils ont lavantage sur les tables dérivées en ce sens quils sont accessibles plus dune fois.

Table temporaire locale

Avec la table temporaire locale (noms commençant par #), ce qui se passe sous le capot est étonnamment similaire aux variables de table. Comme pour les variables de table, les tables temporaires locales sont privées pour le processus qui les a créées. Ils ne peuvent donc pas être utilisés dans les vues et vous ne pouvez pas leur associer des déclencheurs.

Elles sont plus pratiques que les variables de table si vous aimez utiliser SELECT INTO pour les créer, mais je me méfie un peu de lutilisation de SELECT INTO dans un système susceptible de nécessiter des modifications, je préfère de loin créer mes tables temporaires explicitement, avec toutes les contraintes nécessaires.

Vous ne pouvez pas facilement dire quelle session ou procédure a été créée ces tableaux. En effet, si la même procédure stockée est exécutée simultanément par plusieurs processus, le moteur de base de données doit être en mesure de distinguer les mêmes tables créées par les différents processus. Le moteur de base de données le fait en ajoutant en interne un suffixe numérique à gauche à chaque nom de table temporaire locale. Le nom complet dune table temporaire tel quil est stocké dans la vue sys.objects dans TempDB est composé du nom de table spécifié dans linstruction CREATE TABLE et du suffixe numérique généré par le système. Pour permettre le suffixe, le nom de table spécifié pour un nom temporaire local doit comporter moins de 116 caractères.

Vous obtenez une gestion interne avec les tables temporaires locales; ils sont automatiquement supprimés lorsquils sont hors de portée, sauf sils sont explicitement supprimés à laide de DROP TABLE. Leur portée est plus généreuse quune variable de table, vous navez donc pas de problèmes à les référencer dans des lots ou en SQL dynamique. Les tables temporaires locales sont supprimées automatiquement à la fin de la session ou de la procédure en cours. Le laisser tomber à la fin de la procédure qui la créé peut causer des problèmes: une table temporaire locale créée dans une procédure stockée ou une session est supprimée lorsquelle est terminée, de sorte quelle ne peut pas être référencée par le processus qui a appelé la procédure stockée qui a créé la table. Il peut cependant être référencé par toutes les procédures stockées imbriquées exécutées par la procédure stockée qui a créé la table. Si la procédure imbriquée fait référence à une table temporaire et que deux tables temporaires avec le même nom existent à ce moment, quelle table la requête est-elle résolue?

Par curiosité, vous pouvez également créer des procédures stockées temporaires locales avec la même portée et la même durée de vie quune table temporaire locale. Vous ne pouvez pas faire la même chose pour d’autres routines.

Tables temporaires globales.

Comme les tables temporaires locales, les tables temporaires globales (elles commencent par ##) sont automatiquement supprimées à la fin de la session qui a créé la table: Cependant, parce que les tables globales ne sont pas privées pour le processus qui la créée, elles doivent persister par la suite jusquà ce que la dernière instruction Transact-SQL qui référençait activement la table au moment où la session de création sest terminée a terminé son exécution et que les verrous sont supprimés. Toute personne ayant accès à TempDB au moment où ces tables temporaires globales existent peut directement interroger, modifier ou supprimer ces objets temporaires.

Vous pouvez associer des règles, des valeurs par défaut et des index à des tables temporaires, mais vous ne pouvez pas créer de vues sur des tables temporaires ou associez-leur des déclencheurs. Vous pouvez utiliser un type de données défini par lutilisateur lors de la création dune table temporaire uniquement si le type de données existe dans TempDB.

Les procédures stockées peuvent référencer des tables temporaires créées pendant la session en cours. Dans une procédure stockée, vous ne pouvez pas créer une table temporaire, la supprimer, puis créer une nouvelle table temporaire avec le même nom.

Bien que cela fonctionne….

… cela ne fonctionne pas t

Vous pouvez, en utilisant des tables temporaires locales, forcer involontairement la recompilation de la procédure stockée à chaque fois quelle est utilisée. Ce n’est pas bon car il est peu probable que la procédure stockée fonctionne correctement. Pour éviter la recompilation, évitez de faire référence à une table temporaire créée dans une procédure stockée appelante ou appelée: si vous ne pouvez pas le faire, placez la référence dans une chaîne qui est ensuite exécutée à laide de EXECUTE ou sp_ExecuteSQL procédure stockée. Assurez-vous également que la table temporaire est créée dans la procédure stockée ou le déclencheur avant dêtre référencée et supprimée après ces références.Ne créez pas de table temporaire dans une instruction de contrôle de flux telle que IF... ELSE ou WHILE.

Vous êtes autorisé à créer des procédures stockées temporaires globales, mais je nai pas encore trouvé dutilisation pour elles. Les fonctions temporaires globales ne sont pas autorisées.

Conclusions

Dans tout terrain de jeu partagé, faites très attention à la façon dont vous balancez cette batte. Vous aurez réalisé, en lisant ceci, que beaucoup dactivité se déroule dans TempDB et que vous pouvez causer des ravages à lensemble du serveur SQL en utilisant des processus de longue durée qui remplissent les tables temporaires, quel que soit leur type, avec des quantités inutiles de Les données. En fait, je vous ai donné des indices dans cet article sur la façon de vraiment, vraiment, déranger votre DBA par une utilisation inconsidérée de cette précieuse ressource partagée, la TempDB. (Dans lancien temps avant la S2005, utiliser SELECT INTO avec une énorme table était la grande arme en V (Vergeltungswaffe).

Je me méfie toujours de fournir des sur- conseils généraux, mais je préfère toujours que mes bases de données utilisent des variables de table et des TVP dans la mesure du possible. Elles nécessitent moins de ressources et vous êtes moins susceptible de les conserver lorsque vous en avez terminé. Jaime les utiliser au maximum , avec des vérifications et des contraintes de colonne et de table. Vous pouvez trouver des moments où ils sessoufflent, en particulier lorsque la taille des tables augmente. Dans des cas comme celui-ci, ou lorsquil nest pas pratique dutiliser des variables de table en raison de leur portée restreinte, alors Jutiliserai des tables temporaires locales. Il faut beaucoup de lèvres pincées et de secousses de tête avant daccepter une table temporaire globale ou une table temporaire persistante. Elles ont quelques utilisations valides et parfaitement raisonnables, mais elles reposent sur la programmeur pour faire le ménage nécessaire