Souvenez-vous : j’avais écrit un article qui comparait les variables de type table (@table) avec les tables temporaires (#table). (www.itpro.fr/a/table-or-table).
Tables temporaires et variables de type table, débat en cours…

En résumé notre étude a montré entre autres que les variables de types tables sont pratiques car elles peuvent être créées au sein de fonctions utilisateurs, par contre en terme de performances pures, les tables temporaires s’avéraient plus puissantes de par la possibilité de leur ajouter index et statistiques de distribution.
Qu’en est-il maintenant, alors que nous pouvons créer des index sur les variables tables grâce à cette nouvelle syntaxe ?
Les variables table indexées
Allons-y : créons notre première variable table indexée :
DECLARE @VarTable_IDX TABLE
(
Col1 int,
Col2 Varchar(255),
INDEX IDX_NCL_Col1Col2 NONCLUSTERED(col1)
)
Super ! Pas d’erreur : notre table et son index associés sont bien créés. Voyons voir maintenant ce qu’il en est des performances. Nous allons comparer les performances entre deux variables table, l’une étant indexée, l’autre non : voir listing 2.
• Création d’une variable table indexé
DECLARE @VarTable_IDX TABLE
(
Col1 int,
Col2 Varchar(255),
INDEX IDX_NCL_Col1 NONCLUSTERED(col1)
)
• Création de la même variable table, mais sans l’index
DECLARE @VarTable TABLE
(
Col1 int,
Col2 Varchar(255)
)
• Ajout de 1000 lignes dans les deux tables
DECLARE @i INT = 0
WHILE @i < 1000
BEGIN
INSERT INTO @VarTable_IDX VALUES(@i, replicate(‘a’, 255))
INSERT INTO @VarTable VALUES(@i, replicate(‘b’, 255))
SET @i=@i+1
END
• Comparaison des perfs entre les deux : select sur la colonne indexée
set statistics IO ON
set statistics TIME ON
SELECT * FROM @VarTable_IDX where col1>= 25 and col1 <= 35
SELECT * FROM @VarTable where col1>= 25 and col1 <= 35
set statistics IO OFF
set statistics TIME OFF
Et voici le résultat :
Table ‘#A989C52B’. Scan count 1, logical reads 35, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table ‘#AA7DE964’. Scan count 1, logical reads 35, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Aucune différence ! Et si l’on compare les plans d’exécution, ce sont les mêmes dans les deux cas : voir listing 3.
(((IMG7604)))
Nous avons un scan de table pour les deux requêtes, avec un nombre de lignes estimé à 1. Et oui ! S’il est effectivement possible de créer des index sur notre variable table, le moteur ne crée ni ne maintient aucune statistique de distribution dessus, car les statistiques sont créées au moment de la compilation, le moteur n’a aucune idée du nombre de lignes contenu dans la variable table à ce moment-là !
D’où une estimation toujours à 1.
Mais je n’ai pas dit mon dernier mot. Faisons le même test, mais avec un index couvrant sur notre variable table :
• Création d’une variable table indexée
DECLARE @VarTable_IDX TABLE
(
Col1 int,
Col2 Varchar(255),
INDEX IDX_NCL_Col1Col2 NONCLUSTERED(col1, col2)
)
Voici le résultat :
Table ‘#B8CC08BB’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table ‘#B9C02CF4’. Scan count 1, logical reads 35, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
4 logical read pour la variable table indexée contre 35 pour la non indexée.
Et le plan d’exécution de notre variable table indexée a changé : nous sommes passés sur un index Seek :
(((IMG7605)))
Effectivement, dans le cadre d’un index couvrant, le moteur privilégiera le parcours par l’index plutôt que le scan de la table, économisant ainsi des lectures.
Autrement dit, si l’on veut tirer avantages d’un index sur une variable table, il faut que cet index soit couvrant, autrement dit, contienne l’ensemble des colonnes de la requête sous-jacente. Sachant que l’on ne peut ajouter de clause INCLUDE sur ce type d’index, la question du coût de l’index jouera dans la décision de son implémentation.
Téléchargez cette ressource

Rapport Forrester sur les solutions de sécurité des charges de travail cloud (CWS)
Dans cette évaluation, basée sur 21 critères, Forrester Consulting étudie, analyse et note les fournisseurs de solutions de sécurité des charges de travail cloud (CWS). Ce rapport détaille le positionnement de chacun de ces fournisseurs pour aider les professionnels de la sécurité et de la gestion des risques (S&R) à adopter les solutions adaptées à leurs besoins.
Les articles les plus consultés
Les plus consultés sur iTPro.fr
- Les risques liés à l’essor fulgurant de l’IA générative
- Pourquoi est-il temps de repenser la gestion des vulnérabilités ?
- Reporting RSE : un levier d’innovation !
- De la 5G à la 6G : la France se positionne pour dominer les réseaux du futur
- Datanexions, acteur clé de la transformation numérique data-centric
