[SQLServer] Tables temporaires et variables

Parmi les petites choses bien pratiques que l’on retrouve dans SQLServer (bon, pas que, mais le billet ne traitera que de SQLServer), il y a les tables temporaires ainsi que les variables tables (une variable qui est de type table).

Il existe deux types de tables temporaires, pour des usages spécifiques.
Les tables variables sont destinées à un troisième usage.

Dans ce billet, nous allons voir comment les manipuler.

Note : la version utilisée pour ce billet est SQL Server 2008 R2, il est possible que tout ne fonctionne pas comme attendu sur les versions précédentes.

 

Qu’est ce que c’est ?

 
Les tables temporaires sont préfixées par un #, ou deux 🙂

Les tables #table sont des tables locales temporaires.
Ce qui veut dire qu’elles ne sont visible uniquement dans la session qui les a créées.
La table temporaire va également être détruite à la fin du scope dans lequel est utilisé la table (généralement la procédure, en dernier ressort la session).
Les tables locales temporaires sont stockées avec un suffixe qui permet de différencier les noms. Ce qui veut dire que plusieurs sessions peuvent très bien créer des tables locales temporaires ayant le même nom, SQL Server fera très bien la différence (il y a un exemple plus bas).

Les tables ##table sont des tables globales temporaires.
La différence principale avec les tables locales temporaires, c’est qu’elles seront visibles sur toutes les sessions.
Une table globale va être détruite à l’instant même où plus aucune session ne référencera la table.
Comme la table est accessible à toutes les sessions, il est impossible de créer deux tables globales temporaires avec le même nom.

Dans les deux cas, les tables sont créées dans le tempdb. Les logs et locks sont plus légers que pour une table traditionnelle.
Il faut donc bien garder en mémoire que les tables ne sont pas stockées dans la même base de données que celle utilisée !

Enfin, les variables de type tables.
Comme leur nom l’indique, ce sont des variables. Elles sont donc limitées au scope dans lequel elles sont déclarées (déclarées, non créées).
Ce qui veut dire que la durée de vie des variables de type table sont potentiellement bien inférieure à la durée de vie des tables locales et encore plus globales temporaires.
Une autre différence majeure est que les variables sont toujours conservées en mémoire.
L’avantage est l’accès beaucoup plus rapide, l’inconvénient étant qu’il ne faut être attentif au volume de données dans les variables.
De plus, les variables sont insensibles aux transactions : les commit et rollback ne fonctionnent donc pas. Si l’on modifie une variable au sein d’une transaction, un rollback ne servira pas à revenir à la valeur précédente.

 

Pourquoi vouloir les utiliser ?

 
Une base de données, c’est quand même fait pour réaliser des traitements de masses.
Ce qui veut dire que dès que l’on veut avoir des traitements ensemblistes, on va (notamment pour des questions de performances) d’abord travailler les données dans la base plutôt que dans le code.

Et lorsque l’on veut traiter des données, on peut le faire directement sur les tables appartenant à la structure de la base de données, soit le faire dans un endroit plus « safe » avant d’insérer dans les tables « réelles » ou plus simplement pour traiter une masse importante de données avant de les remonter (exemple : consolidation de données).
Et cet endroit safe, cela peut être les tables temporaires.

 

Dans quel scénario utiliser chacune ?

 
Du fait de leur rapidité d’accès et quelles soient stockées en mémoire, les variables tables sont à utiliser pour traiter des faibles volumétries.
Il est aussi possible de les utiliser pour retourner des données.

Les tables temporaires sont à utiliser majoritairement lorsque l’on a une volumétrie importante, pour remplacer un curseur ou encore lorsque l’on doit faire des jointures.
Elles sont également utiles, je trouve, pour agréger des données avant de les remonter (pour les traitements ensemblistes, en résumé).

Exemple de scénario :
On doit faire des traitements très longs d’agrégation de données à partir de plusieurs tables avant d’insérer le résultat dans une table « ordinaire ». En cas de plantage, aucune opération ne doit avoir été réalisée.
Dans ce cas, on pourrait utiliser une transaction. Mais plus les mouvements sont importants, plus les logs le seront également.
Du coup, utiliser une table temporaire pour faire les opérations est une bonne idée : en cas de plantage, on vire la table et…c’est tout. Les données ne sont pas en vrac, il n’y a pas de rollback qui dure des plombes ou autres petites joyeusetés.
C’est un scénario très basique, mais l’idée est que l’on peut avantageusement remplacer transactions et curseurs avec les tables temporaires.

 

Dans le code

 

 

Tables temporaires

 
Ici, je ne prends que l’exemple des tables locales, mais pour les tables globales, on procédera de la même manière.

-- Création d'une table temporaire #TableTemporaire
CREATE TABLE #TableTemporaire(
		[STRING][varchar](64) NOT NULL,
		[DATE] datetime NOT NULL)

-- Insertion dans #TableTemporaire
INSERT INTO #TableTemporaire 
	VALUES ('Hier', DATEADD(day, -1, GETDATE()))
INSERT INTO #TableTemporaire 
	VALUES ('Maintenant', GETDATE())
INSERT INTO #TableTemporaire 
	VALUES ('Demain', DATEADD(day, 1, GETDATE()))

-- Sélection de #TableTemporaire
SELECT * 
	FROM #TableTemporaire

-- Création de #TableTemporaire2 et insertion de données
SELECT STRING, DATE 
	INTO #TableTemporaire2 
	FROM #TableTemporaire
	WHERE STRING = 'Hier'

-- Sélection de #TableTemporaire2
SELECT * 
	FROM #TableTemporaire2

-- Sélection des noms de tables dans le tempdb
SELECT name 
	FROM tempdb..sysobjects WHERE name LIKE '#%'

-- Suppression des tables
DROP TABLE #TableTemporaire
DROP TABLE #TableTemporaire2

A noter que le SELECT INTO ne fonctionne que la première fois, car à la seconde exécution, la table aura déjà été créée, ce qui occasionne une erreur.

Ce qui donne, comme résultat :

 

Tables variables

 

-- Création d'une table temporaire #TableTemporaire
DECLARE @TableTemporaire TABLE(
		[STRING][varchar](64) NOT NULL,
		[DATE] datetime NOT NULL)

-- Insertion dans @TableTemporaire
INSERT INTO @TableTemporaire 
	VALUES ('Hier', DATEADD(day, -1, GETDATE()))
INSERT INTO @TableTemporaire 
	VALUES ('Maintenant', GETDATE())
INSERT INTO @TableTemporaire 
	VALUES ('Demain', DATEADD(day, 1, GETDATE()))
	
-- Sélection dans @TableTemporaire
SELECT * FROM @TableTemporaire

Ce qui donne, comme résultat :

 
L’énorme différence entre les deux, c’est que je peux créer une table temporaire puis l’alimenter au fil de l’eau.
Par contre, il ne sera pas du tout possible de créer une table variable pour l’alimenter en différé, car la variable n’existera plus.
Le bon exemple pour comprendre est d’exécuter chaque instruction l’une après l’autre.

 
Un autre exemple de l’utilisation des tables variables :

CREATE FUNCTION dbo.GetSysObjects ( @initial numeric, @limit numeric ) 
RETURNS  
   @SysObjects TABLE ( 
       [ID] [numeric],  
       [NAME] [varchar] (30)
   ) 
AS 
BEGIN 

	INSERT INTO @SysObjects
	SELECT id, name
	FROM (
		SELECT ROW_NUMBER() OVER(ORDER BY id) RNUM, id, name
		FROM sysobjects
	) T
	WHERE RNUM BETWEEN @initial AND @limit

   RETURN 
END 
GO

Ainsi, le résultat de la requête suivante :

SELECT * FROM dbo.GetSysObjects(10, 20)

Serait :

 
Voilà, j’espère que ça aidera.
Maintenant, je vais me liquéfier à l’ombre…