Les tables temporaires SQL Server sont un type spécial de tables écrites dans la base de données TempDB et agissent comme des tables régulières, fournissant un lieu de travail approprié pour le traitement des données intermédiaires avant d’enregistrer le résultat dans une table régulière, car elles ne peuvent vivre que pendant l’âge de la connexion à la base de données.,

Les tables SQL temp peuvent être utilisées pour améliorer les performances des procédures stockées en raccourcissant le temps de transaction, ce qui vous permet de préparer des enregistrements que vous modifierez dans la table SQL Server temp, puis d’ouvrir une transaction et d’effectuer les modifications.

Il existe quatre types principaux pour les tables SQL temp:

• une table SQL Server temp locale, qui est nommée en commençant par un symbole # (par exemple #TempShipments), qui ne peut être référencée que par la session de base de données en cours et supprimée par sa déconnexion,

• une table SQL Temp globale, qui, ## TempShipments), qui peut être référencé par n’importe quel processus de la base de données actuelle et supprimé lorsque la session de base de données d’origine qui a créé cette table temporaire est déconnectée ou jusqu’à ce que la dernière instruction qui référençait la table temporaire ne l’utilise plus, car toute personne ayant accès à la base de données tempdb système lorsque cette table temporaire SQL globale est créée pourra utiliser cette table,

• une table temporaire SQL Server persistante nommée commençant par un préfixe TempDB tel que TempDB.DBO.TempShipments

• et une Variable de Table qui commence par un préfixe @ (par exemple, @ TempShipments)

le moteur de base de données SQL Server peut distinguer les mêmes tables temporaires SQL créées lors de l’exécution de la même procédure stockée plusieurs fois simultanément en ajoutant un suffixe numérique généré par le système au nom de la table temporaire SQL Server. C’est pourquoi le nom de table SQL temp local ne peut pas dépasser 116 caractères.,

bien que les tables temporaires SQL Server et les variables de table soient stockées dans la base de données TempDB, il existe de nombreuses différences entre elles, telles que:

• Les tables temporaires SQL sont créées à l’aide de L’instruction CREATE TABLE T-SQL, mais les variables de table sont créées à l’aide de

• Vous pouvez modifier les tables temporaires SQL Server après l’avoir créées, mais les variables de table ne prennent en charge aucune instruction DDL comme ALTER statement.

• Les tables SQL temp ne peuvent pas être utilisées dans les fonctions définies par l’utilisateur, mais les variables de table peuvent l’être.,

• Les tables temporaires SQL Server honorent les transactions explicites définies par l’utilisateur, mais les variables de table ne peuvent pas participer à de telles transactions.

• Les tables SQL temp prennent en charge l’ajout d’index en cluster et non en cluster après la création de la table SQL Server temp et implicitement en définissant une contrainte de clé primaire ou une contrainte de clé Unique pendant la création des tables, mais les variables de table ne prennent en charge que l’ajout,

• Les tables SQL temp peuvent être supprimées explicitement, mais les variables de table ne peuvent pas être supprimées explicitement, en tenant compte du fait que les deux types sont supprimés automatiquement lorsque la session dans laquelle ils sont créés est déconnectée.

• Les tables temporaires SQL Server peuvent être des tables temporelles locales au niveau de la procédure batch ou stockée dans laquelle la table a été déclarée ou des tables temporelles globales où elle peut être appelée en dehors de la portée de la procédure batch ou stockée, mais les variables de table ne peuvent être appelées que dans,

de plus, les statistiques au niveau des colonnes SQL Server sont générées automatiquement par rapport aux tables SQL temp, ce qui aide L’Optimiseur de requêtes SQL Server à générer le meilleur plan d’exécution et à obtenir les meilleures performances lors de l’interrogation de cette table SQL Server temp. Mais vous devez prendre en considération que la modification de tables temporaires SQL plusieurs fois dans votre code peut entraîner des statistiques obsolètes. Cela nécessiterait une mise à jour manuelle de ces statistiques ou l’activation de L’indicateur de Trace 2371., Dans cet article, nous verrons comment nous pouvons bénéficier de la possibilité d’ajouter des index en cluster et non en cluster dans les tables temporaires SQL Server.

La définition des contraintes de clé primaire et de clé UNIQUE lors de la création de la table temporaire SQL permettra à SQL Server Query Optimizer de toujours pouvoir utiliser ces index. Néanmoins, ces index empêchent d’insérer des valeurs non uniques dans ces colonnes, ce qui n’est pas le meilleur cas dans tous les scénarios, qui peuvent nécessiter des valeurs non uniques. Dans ce cas, il est préférable de définir explicitement un index clusterisé ou non clusterisé qui pourrait être configuré comme un index non unique., L’ajout d’index aux tables temporaires SQL améliorera ses performances si l’index est choisi correctement, sinon cela peut entraîner une dégradation des performances. En outre, toutes les tables temporaires SQL Server n’ont pas besoin d’ajouter d’index, car cela dépend de beaucoup de choses telles que la façon dont cette table temporaire SQL sera appelée, jointe à d’autres tables énormes ou si elle fera partie d’une procédure stockée complexe.,

commençons notre démo, dans laquelle nous allons tester les performances de remplissage et de récupération de données à partir de tables temporaires SQL Server contenant 100 000 enregistrements, sans index, avec un index non clusterisé et avec un index clusterisé. Nous nous concentrerons sur la vérification du temps consommé par chaque cas et du plan d’exécution généré. Pour évaluer le temps consommé, nous déclarerons la variable @StartTime avant chaque exécution, définirons sa valeur sur GETDATE () et à la fin de chaque exécution, nous imprimerons la différence de date (en ms) entre l’heure actuelle et l’Heure de début.,

le script ci-dessous va créer les trois tables SQL temp mentionnées précédemment; table temp sans index, table temp avec un index non clusterisé et table temp avec index clusterisé et le remplir avec 100k enregistrements de la table de test CountryInfo puis récupérer ces enregistrements des tables:

en exécutant le script précédent, le résultat nous montrera que dans notre cas, ajouter un index non clusterisé est pire que d’avoir la table sans index par 1.,e Temps dans notre cas comme dans la comparaison de synchronisation ci-dessous dans ms:

vérification du plan d’exécution généré à L’aide de L’application ApexSQL Plan après l’exécution, nous verrons que, comme nous n’avons pas de jointures avec des tables énormes ou des requêtes complexes, la récupération des données des trois tables consomme les mêmes ressources (1%) et diffère dans l’opérateur utilisé pour récupérer les données;, Recherche d’index dans le cas d’une table temporaire avec index non clusterisé et recherche d’index clusterisé dans le cas d’une table temporaire avec index clusterisé.,

Vous pouvez également déduire du plan d’exécution que la table avec index non clusterisé a pris le plus de temps (1063 ms) et de ressources (47% de l’exécution globale) pendant le processus d’insertion de table opposé à la table avec insertion d’index clusterisé qui a pris moins de temps (827 ms) et de ressources (32% De index non clusterisé après avoir rempli la table temporaire et l’index clusterisé avant de remplir la table temporaire., Mais est-ce différent lorsque nous créons l’index avant ou après avoir rempli la table temporaire?, Pour répondre à cette question, effectuons le test suivant dans lequel nous vérifierons le temps consommé dans tous les cas; ajout d’un index non clusterisé avant de remplir la table temporaire, ajout d’un index non clusterisé après avoir rempli la table temporaire, ajout d’un index clusterisé avant de remplir la table temporaire et ajout d’un index clusterisé après avoir rempli la table temporaire:

comme c’est 2.,5% plus rapide, en raison du mécanisme utilisé pour remplir les tables et créer les index:

vérification du plan d’exécution, le résultat nous montrera que la création de l’index clusterisé avant l’insertion consomme 15,7% de l’exécution globale, où la création après l’insertion consommera 22% de l’exécution globale., D’autre part, la création des index non clusterisés après l’insertion consomme 23% des ressources par rapport aux 25% qui sont consommés en les créant avant le processus d’insertion:

Conclusion

dans SQL Server, les tables SQL temp, qui sont stockées dans la base de données TempDB, sont largement utilisées pour fournir un endroit approprié pour le traitement des données intermédiaires avant d’enregistrer le résultat dans une table de base., Il a également utilisé pour raccourcir la durée des transactions de longue durée avec un verrouillage minimum de la table de base en prenant les données, en les traitant et en ouvrant enfin une transaction pour effectuer le changement sur la table de base. Cette approche est applicable pour ajouter des index non-clustered et clustered aux tables temporaires SQL Server, qui peuvent toutes deux améliorer les performances de récupération des données de ces tables si les index sont choisis correctement.,

• Author
• Recent Posts

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.,
En outre, il contribue avec ses conseils SQL dans de nombreux blogs.
Afficher tous les articles par Ahmad Yaseen

Derniers messages par Ahmad Yaseen (voir tous)

• Comment faire pour surveiller Azure Data Factory – 15 janvier 2021
• Utilisation de la Source de Contrôle dans Azure Data Factory – 12 janvier 2021
• à l’Aide de Modèles dans Azure Data Factory – 8 janvier 2021