#Table or @Table ?

Depuis qu’elles sont apparus, j’ai très souvent entendu dire que les variables de types tables étaient « meilleures » que les tables temporaires parce qu’elles seraient « en mémoire uniquement » et ne généreraient pas d’IO : c’est absolument faux. A l’inverse, certaines personnes « Old school » (désolé ;)) ne jurent que par les tables temporaires sous prétexte qu’elles offrent plus de souplesse et de possibilités dans leurs usages, et que par conséquent, elles offriront systématiquement de meilleures performances. C’est également faux.

Alors #Table or @Table ?

Je ne prendrai pas directement part au débat dans cet article, pour conclure sur l’une ou l’autre, mais je vais exposer les possibilités offertes par les deux méthodes et comparer leurs performances.

Les possibilités

Commençons par un petit rappel sur les possibilités de chacune.

Les tables temporaires

Une table temporaire va agir en tout point comme une table classique. En cela, elle va supporter toutes les instructions DDL ou DML valable sur les tables classiques. Il sera dès
lors possible (entre autre) :

• De modifier sa définition pour ajouter, supprimer ou modifier une colonne.

• D’ajouter des contraintes (check, foreign key, valeur par défaut etc.).

• D’ajouter des index (cluster et non cluster).

• Et très important comme vous le verrez plus bas dans cet article, le moteur de base de données va maintenir des statistiques de distribution, tout en nous laissant la possibilité d’en créer manuellement.

Les variables de type table

Une variable de type table est gérée par le moteur comme une variable classique et non pas comme un objet. Elle sera donc plus restrictive dans ces possibilités, permettant néanmoins :

• De créer une clé primaire, contrainte unique ou nulle.

• De créer un index cluster.

• D’être utilisée dans une fonction de type table.

• En revanche, le moteur ne maintiendra aucune statistique de distribution sur une variable de type table, et il nous sera impossible d’en créer.

La portée

Une table temporaire sera visible dans la session de l’utilisateur qui la crée. Première conséquence : plusieurs utilisateurs pourront donc simultanément créer la même table temporaire dans leurs sessions respectives, avec exactement le même nom, sans problème. Pour pouvoir gérer plusieurs objets portant le même nom, le moteur va ajouter un identifiant unique rattaché à la session au nom interne de la table temporaire. Pour l’utilisateur, le nom de la table temporaire sera donc limité à 116 caractères ‘seulement’, contre 128 pour les tables classiques (ce qui ne devrait pas être trop restrictif, n’est-ce-pas ?).

Deuxième conséquence : dans la mesure où la table temporaire est visible dans la session, il sera tout à fait possible de l’utiliser au travers de procédures stockées imbriquées. La table temporaire créée dans une procédure stockée pourra être adressée dans toutes les procédures appelées par celle-ci.

Enfin, il sera également possible d’utiliser les syntaxes INSERT / EXEC et SELECT INTO pour créer la table temporaire, comme on le ferait pour une table classique.

À noter qu’il est possible de créer des tables temporaires de portées globales, visibles depuis toutes les sessions connectées, en préfixant le nom de la table par un double # au moment de sa création. Dans ce cas, une seule table de ce nom pourra être créée à un instant donné.

Les variables de type tables quant à elles ne sont visibles que dans le contexte de la transaction dans laquelle elles sont créées, et je parle là de transactions internes au moteur et non pas de transactions utilisateurs, comme nous le verrons plus loin. Cela implique qu’il sera impossible de les utiliser dans des procédures stockées imbriquées et d’utiliser les syntaxes INSERT / EXEC ou SELECT INTO @Table.

En revanche, à l’inverse d’une table temporaire, une variable de type table peut être utilisée dans une fonction de type table (TVF : Table Valuable Function).

La fonction suivante sera acceptée sans problème par le moteur :

CREATE FUNCTION dbo.F_VariableTable ()
RETURNS INT
AS

BEGIN
DECLARE @VariableTable TABLE (col1 INT)
DECLARE @max INT
INSERT @ VariableTable VALUES (1)
SELECT @max = MAX(col1) FROM @VariableTable
RETURN @max
END
GO

En revanche si l’on tente :

CREATE FUNCTION dbo. F_TemporaryTable ()
RETURNS INT
AS
BEGIN
CREATE TABLE #TemporaryTable (col1 INT)
DECLARE @max INT
INSERT #TemporaryTable VALUES (1)
SELECT @max = MAX(col1) FROM #TemporaryTable
RETURN @max
END
GO

On obtiendra alors :

Server: Msg 2772, Level 16, State 1, Procedure example2,
Line 7
Cannot access temporary tables from within a function.

Nous avons bien déblayé le terrain en termes de capacité de l’une ou l’autre, et nous avons maintenant une meilleure idée du contexte dans lequel nous les utiliserons. Nous allons continuer notre étude en comparant maintenant les performances des deux possibilités.