[SQL Server] TempDB : les tables variables peuvent-elles être indexées ? Démonstration…

Ce petit billet démontre que, contrairement à une idée reçue, il est possible de créer des indexes sur des tables variables… mais de manière implicite via une clé primaire et/ou une contrainte d’unicité.

Entrée dans le vif du sujet…

La création d’indexes au sein d’une table variable est possible, mais seulement de manière implicite lors de la déclaration de ladite table. Ainsi :

  • Pour créer des indexes, il suffit d’utiliser une clé primaire pour avoir un index en cluster et une contrainte d’unicité pour avoir un index non-cluster. Voici un exemple de création d’indexes (index en cluster sur Tab_ID et index non-cluster sur Tab_Name) :
DECLARE @Tab TABLE (Tab_ID INT PRIMARY KEY,
Tab_Name VARCHAR(20),
Tab_Owner VARCHAR(20),
UNIQUE (Tab_Name))
Notons que l’ajout du mot-clé CLUSTERED après PRIMARY KEY est, ici, facultatif vu que SQL Server crée implicitement un index en cluster sur la clé primaire lors de la déclaration de cette dernière.
  • Il est également possible de créer plusieurs indexes non-clusters, en plus d’un index en cluster :
DECLARE @Tab TABLE (Tab_ID INT PRIMARY KEY,
Tab_Name VARCHAR(20),
Tab_Owner VARCHAR(20),
UNIQUE (Tab_Name),
UNIQUE (Tab_Owner))
  • Sachant que les colonnes à indexer au sein d’une table variable doivent être uniques, si une colonne nécessitant d’être indexée n’est pas unique (supposons Tab_Name et Tab_Owner), il est possible de l’inclure dans une contrainte d’unicité composite avec la clé primaire :
DECLARE @Tab TABLE (Tab_ID INT PRIMARY KEY,
Tab_Name VARCHAR(20),
Tab_Owner VARCHAR(20),
UNIQUE (Tab_Name,Tab_Owner,Tab_ID))

Pour vérifier la présence des indexes au sein de tempDB (où ils sont stockés), vous pouvez exécuter la requête suivante (qui tient compte des indexes créés dans notre requête ci-dessus) :

SELECT t.name, i.name, i.index_id, i.type_desc
FROM tempdb.sys.tables t
 INNER JOIN tempdb.sys.indexes i ON t.object_id = i.object_id
GO

Résultat :


Pour aller plus loin…

Pour avoir une comparaison entre les tables temporaires et les tables variables, vous pouvez aller ici.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s