Quand dois-je utiliser une variable table vs table temporaire dans sql server?

votre question montre que vous avez succombé à certaines des idées fausses communes entourant les variables de table et les tables temporaires.

j'ai écrit réponse assez complète sur le site DBA en regardant les différences entre les deux types d'objet. Cela répond aussi à votre question sur le disque vs la mémoire (je n'ai pas vu de différence significative dans le comportement entre les deux).

au sujet de la question de la titre cependant pour savoir quand utiliser une variable de table par rapport à une table temporaire locale vous n'avez pas toujours le choix. Dans les fonctions, par exemple, il est seulement possible d'utiliser une variable de table et si vous avez besoin d'écrire à la table d'un enfant alors qu'un #temp table ne (les paramètres évalués dans le tableau permettent accès en lecture seule ).

où vous avez un choix quelques suggestions sont ci-dessous (bien que la méthode la plus fiable est de tester simplement les deux avec votre charge de travail spécifique).

1. si vous avez besoin d'un indice qui ne peut pas être créé implicitement par une contrainte UNIQUE ou PRIMARY KEY alors vous avez besoin d'une table #temporary car il n'est pas possible de créer ces variables sur la table. (Par exemple, des index non uniques, des index filtrés ou des index avec des colonnes INCLUDE d). NB: SQL Server 2014 permettra aux index non uniques d'être déclarés en ligne pour les variables de table.
2. si vous ajouterez et supprimerez à plusieurs reprises un grand nombre de lignes de la table, puis vous utiliserez une table #temporary . Qui supporte TRUNCATE (qui est plus efficace que DELETE pour les grandes tables) et en outre inserts suivants après un TRUNCATE peut avoir de meilleures performances que ceux qui suivent un DELETE comme illustré ici .
3. si vous supprimez ou mettez à jour un grand nombre de lignes, alors la table temp peut bien effectuer beaucoup mieux qu'un tableau de variable si elle est en mesure d'utiliser l'ensemble de lignes de partage (voir "Effets de l'ensemble de lignes de partage" ci-dessous pour un exemple).
4. si le plan optimal utilisant la table varie en fonction des données, utilisez alors une table #temporary . Cela soutient la création de statistiques qui permet au plan d'être dynamiquement recompilé selon les données (bien que pour les tableaux temporaires mis en cache dans les procédures stockées le comportement de recompilation doit être compris séparément).
5. si le plan optimal pour la requête à l'aide de la table est peu susceptible de changer jamais, alors vous pouvez considérer une variable de table pour sauter les frais généraux de la création de statistiques et recompiler (nécessiterait éventuellement des conseils pour fixer le plan que vous voulez).
6. si la source des données insérées dans la table provient d'une déclaration SELECT potentiellement coûteuse, alors considérer que l'utilisation d'une variable de table bloquera la possibilité de cette en utilisant un plan parallèle.
7. si vous avez besoin des données dans la table pour survivre à un rollback d'une transaction d'utilisateur externe puis utiliser une variable de table. Un cas d'utilisation possible pour cela pourrait être la journalisation de la progression de différentes étapes dans un lot SQL long.
8. lors de l'utilisation d'une table #temp à l'intérieur d'une transaction utilisateur, les serrures peuvent être conservées plus longtemps que pour les variables de table (potentiellement jusqu'à la fin de la transaction vs fin de l'énoncé dépendant du type de serrure et de plus, il peut empêcher la troncature du journal des transactions tempdb jusqu'à la fin de la transaction utilisateur. Cela pourrait donc favoriser l'utilisation de variables de tableau.
9. dans les routines stockées, les variables de table et les tables temporaires peuvent être mises en cache. La maintenance des métadonnées pour les variables de table en cache est inférieure à celle des tables #temporary . Bob Ward souligne dans son tempdb présentation que cela peut causer des contestation sur les tables de système dans des conditions de haute concurrence. De plus, lorsqu'on traite de petites quantités de données, cela peut faire de une différence mesurable par rapport à la performance .

Effets de l'ensemble de lignes de partage

DECLARE @T TABLE(id INT PRIMARY KEY, Flag BIT);

CREATE TABLE #T (id INT PRIMARY KEY, Flag BIT);

INSERT INTO @T 
output inserted.* into #T
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), 0
FROM master..spt_values v1, master..spt_values v2

SET STATISTICS TIME ON

/*CPU time = 7016 ms,  elapsed time = 7860 ms.*/
UPDATE @T SET Flag=1;

/*CPU time = 6234 ms,  elapsed time = 7236 ms.*/
DELETE FROM @T

/* CPU time = 828 ms,  elapsed time = 1120 ms.*/
UPDATE #T SET Flag=1;

/*CPU time = 672 ms,  elapsed time = 980 ms.*/
DELETE FROM #T

DROP TABLE #T