SQL – Etienne ZINZINDOHOUE

Un article de zinzineti    1 Commentaire

Les tables temporaires et les variables tables sont toutes créées dans la base tempdb. La durée de vie d’une table temporaire est celle de la session tandis que pour une variable table la portée est celle du lot d’instruction donc plus limitée que la session. La variable table présente beaucoup de limitations contrairement à une table temporaire. Par contre les variables tables sont très utiles pour les fonctions définies par utiliseur (UDF) retournant une table. Côté performance, difficile de dire si la variable table est plus performante qu’une table temporaire dans la mesure où il est possible de poser des index Non-Cluster sur une table temporaire contrairement à une variable table

=>1. Impossible de faire un ROLLBACK dans une transaction avec une variable table

DECLARE @PAYS TABLE(  
   id int NOT NULL,  
   nom varchar(50)  
)    
BEGIN TRANSACTION
INSERT INTO @PAYS (id, nom)  VALUES (1,'BENIN')  
ROLLBACK TRANSACTION
SELECT id, nom FROM @PAYS
 
-- Alors que ça roule avec une table temporaire
CREATE TABLE #PAYS (  
   id int NOT NULL,  
   nom varchar(50)  
)    
BEGIN TRANSACTION
INSERT INTO #PAYS (id, nom) VALUES (1,'BENIN')  
ROLLBACK TRANSACTION
SELECT id, nom FROM #PAYS
DROP TABLE #PAYS

=>2.Impossible de créer un index Non-Cluster sur une variable table

DECLARE @PAYS TABLE(  
   id int NOT NULL,  
   nom varchar(50)  
)    
--Création d'index non cluster
CREATE INDEX IX_NC_PAYS ON @PAYS (id)
 
/*  
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '@PAYS'.
*/
--Lors des imports/exports/archivages de données poser des index non-cluster sur la table temporaire est bien utile
CREATE TABLE #PAYS(  
   id int NOT NULL,  
   nom varchar(50)  
)
--Création d'index non cluster
CREATE INDEX IX_NC_PAYS ON #PAYS (id)
DROP TABLE #PAYS

=>3.Impossible de faire un ALTER sur une variable table

DECLARE TABLE @PAYS(  
   id int NOT NULL,  
   nom varchar(50)  
)
ALTER TABLE @PAYS ADD CONSTRAINT nom_pays DEFAULT('Unknown')FOR nom
 
 
/*
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'TABLE'.
*/
 
--Alors qu'avec une table temporaire ça roule  
CREATE TABLE #PAYS(  
   id int NOT NULL,  
   nom varchar(50)  
)
ALTER TABLE #PAYS ADD CONSTRAINT nom_pays DEFAULT('Unknown')FOR nom
DROP TABLE #PAYS

=> 4.Impossible de faire directement du SQL dynamique avec une variable table

DECLARE TABLE @PAYS(  
   id int NOT NULL,  
   nom varchar(50)  
)
INSERT INTO @PAYS VALUES (1,'BENIN')  
EXEC sp_executesql N'SELECT * FROM @PAYS'
 
/*
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TABLE'.
*/
 
-- Avec une table temporaire ça marche nickel  
CREATE TABLE #PAYS(  
   id int NOT NULL,  
   nom varchar(50)  
)
INSERT INTO #PAYS VALUES (1,'BENIN')  
EXEC sp_executesql N'SELECT * FROM #PAYS'
DROP TABLE #PAYS

=> 5. La déclaration de la variable table doit être dans le même lot d’instruction que la requête sinon il y a erreur. Vérifions cela avec la commande « interpreteur » GO (qui n’est pas une commande SQL). la commande « interpreteur » GO force l’envoi d’instruction SQL au serveur

DECLARE @PAYS TABLE(  
   id int NOT NULL,  
   nom varchar(50)  
)  
GO
INSERT INTO @PAYS (id, nom) VALUES (1,'BENIN')
SELECT id, nom FROM @PAYS
 
-- SQL Server renvoie cette erreur :
/*
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@PAYS".
*/
 
--Alors qu'avec la table temporaire ça roule nickel ...
CREATE TABLE #PAYS(  
   id int NOT NULL,  
   nom varchar(50)  
)
GO  
INSERT INTO #PAYS (id, nom)  
VALUES (1,'BENIN')  
SELECT id, nom FROM #PAYS
DROP TABLE #PAYS

=> 6. La variable table est très utile pour les UDFs retournant une table

CREATE FUNCTION dbo.usp_AllBasesName ( @idBase int )  
RETURNS  
   @BaseTab TABLE (  
       [BaseId] int,  
       [BaseName] varchar (50),  
       [compatibility_level] varchar (50),  
       [recovery_model] varchar (50)
   )  
AS  
BEGIN  
       INSERT INTO @BaseTab  
       SELECT  database_id,name,[compatibility_level],[recovery_model]  
       FROM sys.databases  
       WHERE database_id = @idBase
           
   RETURN  
END  
GO
 
 
--Test
SELECT * FROM dbo.usp_AllBasesName(1)
 
 
--Resultat
BaseId  BaseName  compatibility_level    recovery_model
  1          master    100                               3

Alors que choisir ? variable table ou table temporaire ?

Tout dépend du besoin et du contexte…

Et le choix intelligent sera fait si et seulement si on comprends comment chaque table(variable ou temporaire) fonctionne.

———
Etienne ZINZINDOHOUE