Quelle est la différence entre une variable table temp et une variable table dans SQL Server?
dans SQL Server 2005, nous pouvons créer des tables temp de deux façons:
declare @tmp table (Col1 int, Col2 int);
ou
create table #tmp (Col1 int, Col2 int);
Quelles sont les différences entre ces deux? J'ai lu des opinions contradictoires sur le fait que @tmp utilise toujours tempdb, ou si tout se passe dans la mémoire.
dans quels scénarios les uns sont-ils plus performants que les autres?
11 réponses
il y a quelques différences entre les Tables temporaires (#tmp) et les Variables de Table (@tmp), bien que l'utilisation de tempdb n'en soit pas une, comme expliqué dans le lien MSDN ci-dessous.
en règle générale, pour des volumes de données petits à moyens et des scénarios d'utilisation simples, vous devez utiliser des variables de tableau. (Il s'agit d'une ligne directrice trop large avec, bien entendu, de nombreuses exceptions - voir ci-dessous et les articles suivants.)
quelques points à considérer lors du choix entre eux:
-
les Tables temporaires sont des tables réelles qui vous permettent de créer des index, etc. Si vous avez de grandes quantités de données pour lesquelles l'accès par index sera plus rapide alors tables temporaires sont une bonne option.
-
les variables de Table peuvent avoir des indices en utilisant la clé primaire ou des contraintes uniques. (Si vous voulez un index non unique suffit d'inclure la colonne de clé primaire de la dernière colonne dans l'unique contrainte. Si vous n'avez pas de colonne unique, vous pouvez utiliser une colonne d'identité.) SQL 2014 a non-index uniques trop .
-
les variables de Table ne participent pas aux transactions et
SELECTs correspondent implicitement àNOLOCK. Le comportement de la transaction peut être très utile, par exemple si vous voulez revenir en arrière à mi-chemin à travers une procédure alors les variables de table peuplées pendant cette transaction seront toujours peuplées!
Les tables de température -
pourraient entraîner la recompilation des procédures stockées, peut-être souvent. Les variables de Table ne le seront pas.
-
vous pouvez créer une table temp en utilisant SELECT INTO, qui peut être plus rapide à écrire (bon pour les questions ad-hoc) et peut vous permettre de faire face à l'évolution des types de données au fil du temps, puisque vous n'avez pas besoin de définir votre structure de table temp à l'avance.
-
Vous pouvez renvoyer les variables de table des fonctions, vous permettant d'encapsuler et de réutiliser la logique beaucoup plus facilement (par exemple faire une fonction pour diviser une chaîne en une table de valeurs sur un certain délimiteur arbitraire).
-
L'utilisation de Variables de tableau dans des fonctions définies par l'utilisateur permet d'utiliser ces fonctions plus largement (voir Créer de la documentation sur les fonctions pour plus de détails). Si vous écrivez une fonction vous devriez utiliser des variables de table au-dessus des tables de température à moins qu'il y ait un besoin impérieux autrement.
-
les variables de table et les tables de température sont stockées dans tempdb. Mais variables de table (depuis 2005) par défaut à la collation de la base de données actuelle par rapport aux tables de température qui prennent la collation par défaut de tempdb ( ref ). Cela signifie que vous devez être conscient des problèmes de collation si l'utilisation de tables temp et votre collation db est différente de tempdb, ce qui provoque des problèmes si vous voulez comparer des données dans le temp table avec des données dans votre base de données.
-
Global Temp Tables (##tmp) sont un autre type de table temp disponible pour toutes les sessions et les utilisateurs.
une autre lecture:
-
la grande réponse de Martin Smith sur dba.stackexchange.com
-
MSDN FAQ sur la différence entre les deux: https://support.microsoft.com/en-gb/kb/305977
-
article du blog MDSN: http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx
-
Article: http://searchsqlserver.techtarget.com/tip/0,289483, sid87_gci1267047,00.html#
-
comportements Inattendus et implications sur les performances de tables temporaires et variables temp: Paul Blanc sur SQLblog.com
simplement en regardant l'affirmation dans la réponse acceptée que les variables de table ne participent pas à l'enregistrement.
il semble généralement faux qu'il y ait une différence dans la quantité d'exploitation (au moins pour insert / update / delete opérations à la table elle-même bien que j'ai depuis trouvé qu'Il ya une petite différence à cet égard pour les objets temporaires mis en cache dans les procédures stockées en raison de la table système supplémentaire mettre.)
j'ai examiné le comportement d'exploitation contre un tableau @table_variable et un tableau #temp pour les opérations suivantes.
- Insertion Réussie
- Multi-lignes Insérer où la déclaration annulée en raison de violation de contrainte.
- mise à Jour
- Supprimer
- Libérer
le journal des transactions les enregistrements étaient presque identiques pour toutes les opérations.
la version de la variable de table a en fait quelques supplémentaires entrées de journal parce qu'il obtient une Entrée ajoutée à (et plus tard supprimée de) la table de base sys.syssingleobjrefs , mais dans l'ensemble avait un peu moins d'octets enregistré purement comme le Nom interne pour les variables de table consomme 236 octets de moins que pour les tables #temp (118 caractères de moins nvarchar ).
script complet pour reproduire (le meilleur de fonctionner sur une instance a démarré en mode utilisateur unique et à l'aide de sqlcmd mode
:setvar tablename "@T"
:setvar tablescript "DECLARE @T TABLE"
/*
--Uncomment this section to test a #temp table
:setvar tablename "#T"
:setvar tablescript "CREATE TABLE #T"
*/
USE tempdb
GO
CHECKPOINT
DECLARE @LSN NVARCHAR(25)
SELECT @LSN = MAX([Current LSN])
FROM fn_dblog(null, null)
EXEC(N'BEGIN TRAN StartBatch
SAVE TRAN StartBatch
COMMIT
$(tablescript)
(
[4CA996AC-C7E1-48B5-B48A-E721E7A435F0] INT PRIMARY KEY DEFAULT 0,
InRowFiller char(7000) DEFAULT ''A'',
OffRowFiller varchar(8000) DEFAULT REPLICATE(''B'',8000),
LOBFiller varchar(max) DEFAULT REPLICATE(cast(''C'' as varchar(max)),10000)
)
BEGIN TRAN InsertFirstRow
SAVE TRAN InsertFirstRow
COMMIT
INSERT INTO $(tablename)
DEFAULT VALUES
BEGIN TRAN Insert9Rows
SAVE TRAN Insert9Rows
COMMIT
INSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0])
SELECT TOP 9 ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM sys.all_columns
BEGIN TRAN InsertFailure
SAVE TRAN InsertFailure
COMMIT
/*Try and Insert 10 rows, the 10th one will cause a constraint violation*/
BEGIN TRY
INSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0])
SELECT TOP (10) (10 + ROW_NUMBER() OVER (ORDER BY (SELECT 0))) % 20
FROM sys.all_columns
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
BEGIN TRAN Update10Rows
SAVE TRAN Update10Rows
COMMIT
UPDATE $(tablename)
SET InRowFiller = LOWER(InRowFiller),
OffRowFiller =LOWER(OffRowFiller),
LOBFiller =LOWER(LOBFiller)
BEGIN TRAN Delete10Rows
SAVE TRAN Delete10Rows
COMMIT
DELETE FROM $(tablename)
BEGIN TRAN AfterDelete
SAVE TRAN AfterDelete
COMMIT
BEGIN TRAN EndBatch
SAVE TRAN EndBatch
COMMIT')
DECLARE @LSN_HEX NVARCHAR(25) =
CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 1, 8),2) AS INT) AS VARCHAR) + ':' +
CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 10, 8),2) AS INT) AS VARCHAR) + ':' +
CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 19, 4),2) AS INT) AS VARCHAR)
SELECT
[Operation],
[Context],
[AllocUnitName],
[Transaction Name],
[Description]
FROM fn_dblog(@LSN_HEX, null) AS D
WHERE [Current LSN] > @LSN
SELECT CASE
WHEN GROUPING(Operation) = 1 THEN 'Total'
ELSE Operation
END AS Operation,
Context,
AllocUnitName,
COALESCE(SUM([Log Record Length]), 0) AS [Size in Bytes],
COUNT(*) AS Cnt
FROM fn_dblog(@LSN_HEX, null) AS D
WHERE [Current LSN] > @LSN
GROUP BY GROUPING SETS((Operation, Context, AllocUnitName),())
résultats
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| | | | @TV | #TV | |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| Operation | Context | AllocUnitName | Size in Bytes | Cnt | Size in Bytes | Cnt | Difference Bytes |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| LOP_ABORT_XACT | LCX_NULL | | 52 | 1 | 52 | 1 | |
| LOP_BEGIN_XACT | LCX_NULL | | 6056 | 50 | 6056 | 50 | |
| LOP_COMMIT_XACT | LCX_NULL | | 2548 | 49 | 2548 | 49 | |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysallocunits.clust | 624 | 3 | 624 | 3 | |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrowsets.clust | 208 | 1 | 208 | 1 | |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrscols.clst | 832 | 4 | 832 | 4 | |
| LOP_CREATE_ALLOCCHAIN | LCX_NULL | | 120 | 3 | 120 | 3 | |
| LOP_DELETE_ROWS | LCX_INDEX_INTERIOR | Unknown Alloc Unit | 720 | 9 | 720 | 9 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysallocunits.clust | 444 | 3 | 444 | 3 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysallocunits.nc | 276 | 3 | 276 | 3 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.syscolpars.clst | 628 | 4 | 628 | 4 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.syscolpars.nc | 484 | 4 | 484 | 4 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysidxstats.clst | 176 | 1 | 176 | 1 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysidxstats.nc | 144 | 1 | 144 | 1 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysiscols.clst | 100 | 1 | 100 | 1 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysiscols.nc1 | 88 | 1 | 88 | 1 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysobjvalues.clst | 596 | 5 | 596 | 5 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysrowsets.clust | 132 | 1 | 132 | 1 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysrscols.clst | 528 | 4 | 528 | 4 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.clst | 1040 | 6 | 1276 | 6 | 236 |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc1 | 820 | 6 | 1060 | 6 | 240 |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc2 | 820 | 6 | 1060 | 6 | 240 |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc3 | 480 | 6 | 480 | 6 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.syssingleobjrefs.clst | 96 | 1 | | | -96 |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.syssingleobjrefs.nc1 | 88 | 1 | | | -88 |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | Unknown Alloc Unit | 72092 | 19 | 72092 | 19 | |
| LOP_DELETE_ROWS | LCX_TEXT_MIX | Unknown Alloc Unit | 16348 | 37 | 16348 | 37 | |
| LOP_FORMAT_PAGE | LCX_HEAP | Unknown Alloc Unit | 1596 | 19 | 1596 | 19 | |
| LOP_FORMAT_PAGE | LCX_IAM | Unknown Alloc Unit | 252 | 3 | 252 | 3 | |
| LOP_FORMAT_PAGE | LCX_INDEX_INTERIOR | Unknown Alloc Unit | 84 | 1 | 84 | 1 | |
| LOP_FORMAT_PAGE | LCX_TEXT_MIX | Unknown Alloc Unit | 4788 | 57 | 4788 | 57 | |
| LOP_HOBT_DDL | LCX_NULL | | 108 | 3 | 108 | 3 | |
| LOP_HOBT_DELTA | LCX_NULL | | 9600 | 150 | 9600 | 150 | |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysallocunits.clust | 456 | 3 | 456 | 3 | |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.syscolpars.clst | 644 | 4 | 644 | 4 | |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysidxstats.clst | 180 | 1 | 180 | 1 | |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysiscols.clst | 104 | 1 | 104 | 1 | |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysobjvalues.clst | 616 | 5 | 616 | 5 | |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysrowsets.clust | 136 | 1 | 136 | 1 | |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysrscols.clst | 544 | 4 | 544 | 4 | |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysschobjs.clst | 1064 | 6 | 1300 | 6 | 236 |
| LOP_INSERT_ROWS | LCX_CLUSTERED | sys.syssingleobjrefs.clst | 100 | 1 | | | -100 |
| LOP_INSERT_ROWS | LCX_CLUSTERED | Unknown Alloc Unit | 135888 | 19 | 135888 | 19 | |
| LOP_INSERT_ROWS | LCX_INDEX_INTERIOR | Unknown Alloc Unit | 1596 | 19 | 1596 | 19 | |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysallocunits.nc | 288 | 3 | 288 | 3 | |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.syscolpars.nc | 500 | 4 | 500 | 4 | |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysidxstats.nc | 148 | 1 | 148 | 1 | |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysiscols.nc1 | 92 | 1 | 92 | 1 | |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysschobjs.nc1 | 844 | 6 | 1084 | 6 | 240 |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysschobjs.nc2 | 844 | 6 | 1084 | 6 | 240 |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysschobjs.nc3 | 504 | 6 | 504 | 6 | |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.syssingleobjrefs.nc1 | 92 | 1 | | | -92 |
| LOP_INSERT_ROWS | LCX_TEXT_MIX | Unknown Alloc Unit | 5112 | 71 | 5112 | 71 | |
| LOP_MARK_SAVEPOINT | LCX_NULL | | 508 | 8 | 508 | 8 | |
| LOP_MODIFY_COLUMNS | LCX_CLUSTERED | Unknown Alloc Unit | 1560 | 10 | 1560 | 10 | |
| LOP_MODIFY_HEADER | LCX_HEAP | Unknown Alloc Unit | 3780 | 45 | 3780 | 45 | |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.syscolpars.clst | 384 | 4 | 384 | 4 | |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysidxstats.clst | 100 | 1 | 100 | 1 | |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysrowsets.clust | 92 | 1 | 92 | 1 | |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysschobjs.clst | 1144 | 13 | 1144 | 13 | |
| LOP_MODIFY_ROW | LCX_IAM | Unknown Alloc Unit | 4224 | 48 | 4224 | 48 | |
| LOP_MODIFY_ROW | LCX_PFS | Unknown Alloc Unit | 13632 | 169 | 13632 | 169 | |
| LOP_MODIFY_ROW | LCX_TEXT_MIX | Unknown Alloc Unit | 108640 | 120 | 108640 | 120 | |
| LOP_ROOT_CHANGE | LCX_CLUSTERED | sys.sysallocunits.clust | 960 | 10 | 960 | 10 | |
| LOP_SET_BITS | LCX_GAM | Unknown Alloc Unit | 1200 | 20 | 1200 | 20 | |
| LOP_SET_BITS | LCX_IAM | Unknown Alloc Unit | 1080 | 18 | 1080 | 18 | |
| LOP_SET_BITS | LCX_SGAM | Unknown Alloc Unit | 120 | 2 | 120 | 2 | |
| LOP_SHRINK_NOOP | LCX_NULL | | | | 32 | 1 | 32 |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| Total | | | 410144 | 1095 | 411232 | 1092 | 1088 |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
dans quels scénarios les uns sont-ils plus performants que les autres?
pour les tables plus petites (moins de 1000 lignes) utilisez une variable temp, sinon utilisez une table temp.
@wcm - en fait pour nit choisir la Variable de Table n'est pas seulement Ram-il peut être partiellement stocké sur le disque.
une table temp peut avoir des indices, alors qu'une variable table ne peut avoir qu'un indice primaire. Si la vitesse est une variable de table de discussion peut être plus rapide, mais évidemment s'il y a beaucoup d'enregistrements, ou la nécessité de rechercher la table de temp d'un indice groupé, alors une Table de Temp serait mieux.
-
table de température: une table de température est facile à créer et sauvegarder des données.
variable de Table: mais la variable de table implique l'effort quand nous créons habituellement les tables normales.
-
Temp table: résultat de la table Temp peut être utilisé par plusieurs utilisateurs.
variable de Table: mais la variable de table peut être utilisée par l'utilisateur courant seulement.
-
Table Temp: la table Temp sera stockée dans la tempdb. Il fera du trafic réseau. Quand nous avons de grandes données dans la table de température alors il doit fonctionner à travers la base de données. Il y aura un problème de rendement.
variable de Table: mais une variable de table stockera dans la mémoire physique pour certaines des données, puis plus tard quand la taille augmente, il sera déplacé à la tempdb.
-
table Temp: la table Temp peut effectuer toutes les opérations DDL. Il permet de créer les index, de les supprimer, de les modifier, etc..,
Tableau de variable: Tandis que la variable de table ne permet pas de faire les opérations DDL. Mais la variable table permet de créer l'indice groupé seulement.
-
table Temp: table Temp peut être utilisée pour la session en cours ou globale. Afin qu'une session d'utilisateur multiple puisse utiliser les résultats dans le tableau.
variable de Table: mais la variable de table peut être utilisée jusqu'à ce programme. (Procédure stockée)
-
Temp table: la variable Temp ne peut pas utiliser les transactions. Lorsque nous effectuons les opérations DML avec la table temp, il peut s'agir d'un retour en arrière ou d'une propagation des transactions.
variable de Table: mais nous ne pouvons pas le faire pour la variable de table.
-
Temp table: les fonctions ne peuvent pas utiliser la variable temp. Plus sur nous ne pouvons pas faire l'opération DML dans les fonctions .
variable de Table: mais la fonction nous permet d'utiliser la variable de table. Mais en utilisant la variable table, nous pouvons le faire.
-
table de température: la procédure stockée fera la recompilation (ne peut pas utiliser le même plan d'exécution) lorsque nous utilisons la variable de température pour chaque appel de sous-séquence.
variable de Table: alors que la variable de table ne fera pas comme cela.
Pour tous ceux qui croient au mythe que les variables temp sont dans la mémoire
Premièrement, la variable de table n'est pas nécessairement résident de mémoire. Sous la pression de la mémoire, les pages appartenant à une variable de table peuvent être repoussées à tempdb.
Lire l'article ici: la base de données TempDB:: variable de Table vs table temporaire locale
l'autre différence principale est que les variables de table n'ont pas de statistiques de colonne, où comme les tables de temp. Cela signifie que l'optimiseur de requête ne sait pas combien de lignes sont dans la variable table (il devine 1), ce qui peut conduire à des plans hautement non optimaux ont été générés si la variable table a effectivement un grand nombre de lignes.
citation tirée de; Professional SQL Server 2012 Internals and dépannage
statistiques La principale différence entre les tables de température et les variables de table est que les statistiques ne sont pas créées sur des variables de table. Cela a deux grandes conséquences, la première est que l'Optimiseur de Requête utilise une estimation fi xée du nombre de lignes d'une variable de tableau indépendamment des données il contient. De plus, ajouter ou supprimer les données ne changent pas l'estimation.
Indices Vous ne pouvez pas créer d'index sur la table des variables bien que vous pouvez créer des contraintes. Cela signifie qu'en créant des clés primaires ou uniques contraintes, vous pouvez avoir des index (car ils sont créés pour supporter contraintes) sur les variables de la table. Même si vous avez des contraintes, et par conséquent, les indices qui auront des statistiques, les indices ne seront pas utiliser quand la requête est compilée parce qu'ils n'existeront pas à la compilation le temps, et ils ne causeront pas de rétributions.
modifications de schéma des modifications de schéma sont possibles sur des tables mais pas sur les variables de table. Bien que les modifications de schéma soient possible sur les tables temporaires, éviter de les utiliser parce qu'ils provoquent recompilations des énoncés qui utilisent les tableaux.
LES VARIABLES DE TABLE NE SONT PAS CRÉÉES EN MÉMOIRE
Il ya une idée fausse commune que les variables de table sont des structures en mémoire et en tant que tel sera plus rapide que les tables temporaires . Grâce à un DMV appelé sys . dm _ db _ session _ Espace _ utilisation, qui montre l'utilisation de tempdb par session, vous pouvez prouver que ce n'est pas le cas . Après avoir redémarré le serveur SQL pour effacer DMV, exécutez le script suivant pour s'assurer que votre session _ id retourne 0 pour l'utilisateur _ objets d' _ alloc _ page _ comte :
SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;
maintenant, vous pouvez vérifier combien d'espace une table temporaire utilise en lançant ce qui suit script pour créer une table temporaire avec une colonne et la remplir avec une ligne:
CREATE TABLE #TempTable ( ID INT ) ;
INSERT INTO #TempTable ( ID )
VALUES ( 1 ) ;
GO
SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;
les résultats sur mon serveur indiquent que la table a été attribuée une page dans tempdb. Maintenant exécutez le même script mais utilisez une table variable cette fois:
DECLARE @TempTable TABLE ( ID INT ) ;
INSERT INTO @TempTable ( ID )
VALUES ( 1 ) ;
GO
SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;
lequel utiliser?
si vous utilisez ou non des tables temporaires ou des variables de table devrait être décidé par des tests approfondis, mais il est préférable de pencher vers temporaire tables comme la valeur par défaut parce qu'Il ya beaucoup moins de choses qui peuvent aller mauvais .
j'ai vu les clients développent le code en utilisant des variables de table parce qu'ils étaient confrontés à une petite quantité de lignes, et il était plus rapide que table temporaire, mais quelques années plus tard, il y avait des centaines de des milliers de lignes dans le tableau variable et la performance était terrible, essayez donc de permettre une planification de la capacité lorsque vous faites votre la décision!
autre différence:
une table var ne peut être accessible qu'à partir de déclarations dans la procédure qui la crée, et non à partir d'autres procédures appelées par cette procédure ou de SQL dynamique imbriqué (via exec ou sp_executesql).
la portée d'une table temp, d'autre part, inclut le code dans les procédures appelées et le SQL dynamique imbriqué.
si le tableau créé par votre procédure doit être accessible à partir d'autres procédures appelées ou dynamique SQL, vous devez utiliser une table temporaire. Cela peut être très pratique dans des situations complexes.
considérez aussi que vous pouvez souvent remplacer les deux avec des tables dérivées qui peuvent être plus rapides aussi bien. Comme avec tous les réglages de performance, cependant, seuls les tests réels par rapport à vos données réelles peuvent vous indiquer la meilleure approche pour votre requête particulière.
Tableau Temporaire
table temporaire se comporte comme une vraie table mais créée sur le temps d'exécution. Son travail similaire à la table réelle. Nous pouvons faire presque toutes les opérations possibles en de véritables tableaux. Nous pouvons utiliser des instructions DDL comme ALTER, CREATE, DROP sur les Tables temporaires.
tout changement dans la structure de la table temporaire est possible après la création. Table temporaire stockée dans la base de données" tempdb " des bases de données système.
table temporaire participer à des transactions, l'enregistrement ou le verrouillage. Pour cette raison, il est plus lent que la Variable de Table.
Variable De Table
"C'est Variable mais fonctionne comme une table. Il est aussi créé dans la base de données Tempdb pas dans la mémoire. Variable de tableau disponible uniquement dans le champ d'application de la procédure de traitement par lots ou stockée. Vous n'avez pas besoin de laisser tomber la Variable de Table , il est automatiquement abandonné lorsque le lot et la procédure de stockage processus d'exécution terminé
table variable support clé primaire, identité sur le temps de création. Mais il ne supporte pas non-clustered index. Après la déclaration clé primaire, identité vous ne pouvez pas les modifier.
Les variables de Tablene participent pas aux transactions, à la journalisation ou au verrouillage. Transactions, journalisation et verrouillage sans effet sur les Variables de Table.
Lire l'article pour plus de détails - http://goo.gl/GXtXqz