Différences entre tables temporaires et variable table : ##temp, #temp, @temp... ou CTE ?

Différences entre tables temporaires et variable table : ##temp, #temp, @temp… ou CTE ?

10 décembre 2017

Commentaires fermés

Question récurrente : Quelles différences entre une table temporaire ##temp, #temp (et non, ça n’a rien à voir avec un hashtag), une variable table @temp et une CTE (même si c’est un peu à part) ? C’est un article que je songeais à écrire depuis longtemps car à chaque audit SQL, le sujet revient sur le tapis. Pour vulgariser, cela revient à se demander quelle variété de pomme utiliser pour différents desserts. Typiquement pour faire une tarte aux pommes, on choisira plutôt la Granny-Smith; on évitera soigneusement la Boskoop et la Canada plus adaptées à la compote.

Pour commencer, voici quelques différences illustrées par des exemples :

Stockage

Une bonne fois pour toute, une variable table n’est pas stockée en mémoire, pour preuve, démarrons une session dans ProcessMonitor et appliquons deux filtres :

– Process Name = sqlservr.exe
– Operation = WriteFile

Créons une table dans laquelle on injecte 1000 lignes.

DECLARE @t AS TABLE (C1 INT IDENTITY(1,1), C2 UNIQUEIDENTIFIER)

INSERT	INTO @t 
SELECT  TOP 5000
		C2 = NEWID()
FROM  
Master.dbo.SysColumns t1, 
Master.dbo.SysColumns t2

DECLARE @t AS TABLE (C1 INT IDENTITY(1,1), C2 UNIQUEIDENTIFIER)

INSERT INTO @t

SELECT TOP 5000

C2 = NEWID()

FROM

Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2

1) Quand on lance une recherche sur la localisation physique des pages dans la table @temp, la requête renvoie bien un résultat :

SELECT	TOP 10 sys.fn_PhysLocFormatter(%%physloc%%) AS [FileID:PageID:SlotID], *
FROM	@t

1 2	SELECT TOP 10 sys.fn_PhysLocFormatter(%%physloc%%) AS [FileID:PageID:SlotID], * FROM @t

2) La requête permettant de retourner l’allocation de pages retourne elle aussi des données :

SELECT t.object_id,
       t.name,
       p.rows,
       a.type_desc,
       a.total_pages,
       a.used_pages,
       a.data_pages,
       p.data_compression_desc
FROM   tempdb.sys.partitions AS p
       INNER JOIN tempdb.sys.system_internals_allocation_units AS a
         ON p.hobt_id = a.container_id
       INNER JOIN tempdb.sys.tables AS t
         ON t.object_id = p.object_id
       INNER JOIN tempdb.sys.columns AS c
         ON c.object_id = p.object_id
WHERE  c.name = 'C1'

SELECT t.object_id,

t.name,

p.rows,

a.type_desc,

a.total_pages,

a.used_pages,

a.data_pages,

p.data_compression_desc

FROM tempdb.sys.partitions AS p

INNER JOIN tempdb.sys.system_internals_allocation_units AS a

ON p.hobt_id = a.container_id

INNER JOIN tempdb.sys.tables AS t

ON t.object_id = p.object_id

INNER JOIN tempdb.sys.columns AS c

ON c.object_id = p.object_id

WHERE c.name = 'C1'

3) Et enfin, la session ProcessMonitor fait bien état d’un processus d’écriture dans la base de données tempdb :

Transaction

Dans la démo ci-dessous, on utilise une table @temp pour conserver un jeu d’enregistrements qui a fait l’objet d’un rollback, alors que l’ajout de données dans la table #temp a bien été annulé. Cette possibilité de soustraire un jeu de données à un rollback peut être utile à des fins de traçabilité. Vous l’aurez donc compris, une variable table n’est pas transactionnelle.

IF OBJECT_ID('tempdb..#t') IS NOT NULL
	DROP TABLE #t

DECLARE @t TABLE(C1 INT, C2 CHAR(1))
CREATE TABLE #t (C1 INT IDENTITY, C2 CHAR(1))

BEGIN TRY  

    BEGIN TRAN

		INSERT #t (C2)
		OUTPUT INSERTED.C1, INSERTED.C2 INTO @t
		VALUES('A'),('B'),('C')

		SELECT '#t & @t : Begin Transaction' AS Evt
		SELECT * FROM #t
		SELECT * FROM @t

		;THROW 51000, 'Custom error', 1;  

	COMMIT TRAN

END TRY  
BEGIN CATCH  
    ROLLBACK
END CATCH  

SELECT '#t : Rollback Transaction' AS Evt
SELECT * FROM #t
SELECT * FROM @t

IF OBJECT_ID('tempdb..#t') IS NOT NULL

DROP TABLE #t

DECLARE @t TABLE(C1 INT, C2 CHAR(1))

CREATE TABLE #t (C1 INT IDENTITY, C2 CHAR(1))

BEGIN TRY

BEGIN TRAN

INSERT #t (C2)

OUTPUT INSERTED.C1, INSERTED.C2 INTO @t

VALUES('A'),('B'),('C')

SELECT '#t & @t : Begin Transaction' AS Evt

SELECT * FROM #t

SELECT * FROM @t

;THROW 51000, 'Custom error', 1;

COMMIT TRAN

END TRY

BEGIN CATCH

ROLLBACK

END CATCH

SELECT '#t : Rollback Transaction' AS Evt

SELECT * FROM #t

SELECT * FROM @t

Cardinalité

Un autre point à prendre en considération pour le choix d’une table temporaire est la volumétrie. Typiquement une variable table ne possède pas de statistiques. Sur un nombre de lignes important, cela devient problématique. Néanmoins, il y a plusieurs manières de forcer l’estimation de cardinalité telle que l’option RECOMPILE :

DECLARE @t TABLE (id INT, name SYSNAME NOT NULL);

INSERT	INTO @t
SELECT	TOP (1000) [object_id], name
FROM	sys.all_objects;

SET STATISTICS PROFILE ON

SELECT	TOP (100) t.id, t.name
FROM	@t AS t
OPTION (RECOMPILE);

SET STATISTICS PROFILE OFF

DECLARE @t TABLE (id INT, name SYSNAME NOT NULL);

INSERT INTO @t

SELECT TOP (1000) [object_id], name

FROM sys.all_objects;

SET STATISTICS PROFILE ON

SELECT TOP (100) t.id, t.name

FROM @t AS t

OPTION (RECOMPILE);

SET STATISTICS PROFILE OFF

Cela étant, la méthode RECOMPILE peut s’avérer extrêmement coûteuse en temps d’exécution et CPU, particulièrement au sein d’une fonction scalaire qui sera appelée pour chaque ligne.
Depuis SQL Server 2012 SP2, il est possible d’utiliser une méthode intermédiaire, avec le traceflg 2453 dont la recompilation n’est pas systémique. Reste à savoir que l’estimation de cardinalité n’opère que si le plan associé à la requête n’est pas trivial !

Requête avec un plan trivial

DBCC TRACEON(2453);

DECLARE @t TABLE(id INT , name SYSNAME NOT NULL );
 
INSERT INTO	@t 
SELECT TOP (1000) [object_id], name 
FROM	sys.all_objects;

SET STATISTICS PROFILE ON

-- PLAN TRIVIAL : estimation KO
SELECT	TOP (100) t.id, t.name
FROM	@t AS t 

DBCC TRACEOFF(2453);

DBCC TRACEON(2453);

DECLARE @t TABLE(id INT , name SYSNAME NOT NULL );

INSERT INTO @t

SELECT TOP (1000) [object_id], name

FROM sys.all_objects;

SET STATISTICS PROFILE ON

-- PLAN TRIVIAL : estimation KO

SELECT TOP (100) t.id, t.name

FROM @t AS t

DBCC TRACEOFF(2453);

Le nombre de lignes estimées est toujours égal à 1 !

Requête 1 avec un plan full

DBCC TRACEON(2453);

DECLARE @t TABLE(id INT , name SYSNAME NOT NULL );
 
INSERT INTO	@t 
SELECT TOP (1000) [object_id], name 
FROM	sys.all_objects;

SET STATISTICS PROFILE ON

-- PLAN FULL : estimation OK
SELECT	TOP (100) t.id, t.name
FROM	@t AS t 
ORDER BY NEWID()

SET STATISTICS PROFILE OFF

DBCC TRACEOFF(2453);

DBCC TRACEON(2453);

DECLARE @t TABLE(id INT , name SYSNAME NOT NULL );

INSERT INTO @t

SELECT TOP (1000) [object_id], name

FROM sys.all_objects;

SET STATISTICS PROFILE ON

-- PLAN FULL : estimation OK

SELECT TOP (100) t.id, t.name

FROM @t AS t

ORDER BY NEWID()

SET STATISTICS PROFILE OFF

DBCC TRACEOFF(2453);

Le nombre de lignes estimées est réaliste.

Requête 2 avec un plan full

DBCC TRACEON(2453);

DECLARE @t TABLE(id INT , name SYSNAME NOT NULL );
 
INSERT INTO	@t 
SELECT TOP (1000) [object_id], name 
FROM	sys.all_objects;

SET STATISTICS PROFILE ON

-- PLAN FULL : estimation OK
SELECT	TOP (100) t.id, t.name
FROM	@t AS t 
LEFT	JOIN sys.all_columns AS c ON t.id = c.[object_id]

SET STATISTICS PROFILE OFF

DBCC TRACEOFF(2453);

DBCC TRACEON(2453);

DECLARE @t TABLE(id INT , name SYSNAME NOT NULL );

INSERT INTO @t

SELECT TOP (1000) [object_id], name

FROM sys.all_objects;

SET STATISTICS PROFILE ON

-- PLAN FULL : estimation OK

SELECT TOP (100) t.id, t.name

FROM @t AS t

LEFT JOIN sys.all_columns AS c ON t.id = c.[object_id]

SET STATISTICS PROFILE OFF

DBCC TRACEOFF(2453);

Le nombre de lignes estimées est encore une fois réaliste.

NB : La granularité du traceflag 2453 peut être le batch et l’instance.

https://support.microsoft.com/en-us/help/2952444/fix-poor-performance-when-you-use-table-variables-in-sql-server-2012-o

Recompilation

En plus de la mise à jour de données (INSERT, UPDATE, DELETE) de nature à modifier les statistiques, les tables #temp et ##temp peuvent entraîner la recompilation des procédures stockées, ces tables pouvant faire l’objet d’opérations DDL postérieures à leur création, telle que l’ajout de colonne comme dans l’exemple ci-dessous après l’injection de données :

USE tempdb
GO

CREATE PROCEDURE ProcRecompile

AS

	CREATE TABLE #T (
		C1 INT IDENTITY(1,1), 
		C2 UNIQUEIDENTIFIER
	)

	INSERT	INTO #T
	SELECT  TOP 5000
			C2 = NEWID()
	FROM  
	Master.dbo.SysColumns t1, 
	Master.dbo.SysColumns t2

	-- Add columns
	ALTER TABLE #T ADD C3 VARCHAR(50)

	UPDATE #T SET
	C3 = NEWID()

GO

USE tempdb

CREATE PROCEDURE ProcRecompile

CREATE TABLE #T (

C1 INT IDENTITY(1,1),

C2 UNIQUEIDENTIFIER

)

INSERT INTO #T

SELECT TOP 5000

C2 = NEWID()

FROM

Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2

-- Add columns

ALTER TABLE #T ADD C3 VARCHAR(50)

UPDATE #T SET

C3 = NEWID()

Avant l’exécution de la procédure stockée, une trace XEvent est créer pour consigner les recompilations. La définition et l’exploitation de la trace est consultable dans cet article : https://www.concatskills.com/2018/01/12/sql-server-compilation-et-recompilation/
Une fois la trace créée, on peut passer à l’exécution de la procédure stockée, 10 fois, comme suit :

EXEC ProcRecompile
GO 10

1 2	EXEC ProcRecompile GO 10

La trace XEvent a bien enregistré 10 recompilations dûes à un changement de schéma :

Parallélisme

Nativement, une variable table n’est pas éligible à la parallélisation contrairement aux tables #temp et ##temp. Néanmoins c’est un comportement qu’on peut modifier moyennant le recours à la recompilation ou au traceflag 8649 sur un SELECT uniquement. Dans cet exemple, en premier lieu on injecte une quantité de données conséquente dans la table temporaire #TI.

IF OBJECT_ID('tempdb..#TI') IS NOT NULL
	DROP TABLE #TI

CREATE TABLE #TI (C1 INT IDENTITY(1,1), C2 UNIQUEIDENTIFIER, C3 BIT) 

INSERT	INTO #TI (C2, C3)
SELECT  TOP 5000000
		NEWID(),
		ABS(CHECKSUM(NEWID()) % 10) % 2
FROM  
Master.dbo.SysColumns t1, 
Master.dbo.SysColumns t2

IF OBJECT_ID('tempdb..#TI') IS NOT NULL

DROP TABLE #TI

CREATE TABLE #TI (C1 INT IDENTITY(1,1), C2 UNIQUEIDENTIFIER, C3 BIT)

INSERT INTO #TI (C2, C3)

SELECT TOP 5000000

NEWID(),

ABS(CHECKSUM(NEWID()) % 10) % 2

FROM

Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2

Dans un second temps, on alimente la variable table @T1 et une table temporaire #T à partir de la table #TI, puis on lance un SELECT sur celles-ci pour observer la parallélisation sur ces deux opérations (INSERT et SELECT), avec différents query hint :

DECLARE @T1 AS TABLE (
	C1 INT ,
	C2 UNIQUEIDENTIFIER,
	C3 BIT
)

IF OBJECT_ID('tempdb..#T2') IS NOT NULL
	DROP TABLE #T2

INSERT	INTO @T1
SELECT	*
FROM	#TI (TABLOCK)
WHERE	C3 = 1
ORDER BY NEWID()

SELECT	*
INTO	#T2
FROM	#TI (TABLOCK)
WHERE	C3 = 1
ORDER BY NEWID()

SELECT	*
FROM	@T1
WHERE	C3 = 1
ORDER BY NEWID()

SELECT	*
FROM	@T1
WHERE	C3 = 1
ORDER BY NEWID()
OPTION (RECOMPILE)

SELECT	*
FROM	@T1
WHERE	C3 = 1
ORDER BY NEWID()
OPTION (QUERYTRACEON 8649) -- Force parallelism

SELECT	*
FROM	#T2
WHERE	C3 = 1
ORDER BY NEWID()

DECLARE @T1 AS TABLE (

C1 INT ,

C2 UNIQUEIDENTIFIER,

C3 BIT

)

IF OBJECT_ID('tempdb..#T2') IS NOT NULL

DROP TABLE #T2

INSERT INTO @T1

SELECT *

FROM #TI (TABLOCK)

WHERE C3 = 1

ORDER BY NEWID()

SELECT *

INTO #T2

FROM #TI (TABLOCK)

WHERE C3 = 1

ORDER BY NEWID()

SELECT *

FROM @T1

WHERE C3 = 1

ORDER BY NEWID()

SELECT *

FROM @T1

WHERE C3 = 1

ORDER BY NEWID()

OPTION (RECOMPILE)

SELECT *

FROM @T1

WHERE C3 = 1

ORDER BY NEWID()

OPTION (QUERYTRACEON 8649) -- Force parallelism

SELECT *

FROM #T2

WHERE C3 = 1

ORDER BY NEWID()

La parallélisation est effective sur les INSERT et SELECT de la table temporaire #T2 (requête 2 et 6). A noter que la parallélisation des INSERT n’est disponible que depuis SQL Server 2014. En revanche, concernant la variable table @T1, la parallélisation n’est effective que sur l’opération de SELECT à l’aide d’un RECOMPILE (on reste néanmoins tributaire du CTP) ou du traceflag 8649 (requête 4 et 5). Cela étant le RECOMPILE et le traceflag restent inopérant sur un INSERT. Plus globalement, l’intérêt du traceflag 8649 réside dans le fait de pouvoir forcer le parallélisme en s’affranchissant de la comparaison des coûts (plans sérialisés VS plans parallélisés) sur une instance où l’on a désactivé cette option par exemple mais ce traceflag requiert un niveau de droit élevé.

Contraintes et indexation

L’ajout de contraintes et d’index sur une variable table ne peut pas être postérieur à sa création. L’indexation n’était possible qu’indirectement, autrement dit à partir de la création de contrainte (ex : PRIMARY KEY, UNIQUE) pour les versions antérieures à SQL Server 2014. Petite nouveauté sur SQL Server 2016, on peut désormais ajouter des index filtrant.

DECLARE @T1 TABLE
(
C1 INT NULL UNIQUE CLUSTERED,
C2 INT NOT NULL PRIMARY KEY NONCLUSTERED
)

DECLARE @T2 TABLE
(
C1 INT NULL UNIQUE NONCLUSTERED,
C2 INT NOT NULL PRIMARY KEY NONCLUSTERED
)

DECLARE @T3 TABLE
(
C1 INT NULL UNIQUE NONCLUSTERED,
C2 INT NOT NULL PRIMARY KEY CLUSTERED
)

-- >= SQL Server 2014
DECLARE @T4 TABLE (
C1 INT INDEX IDX1 CLUSTERED,
C2 INT INDEX IDX2 NONCLUSTERED,
       INDEX IDX3 NONCLUSTERED(C1,C2) 
);

-- >= SQL Server 2016
DECLARE @T5 TABLE
(
C1 INT NULL INDEX IDX1 UNIQUE WHERE C1 IS NOT NULL
)

DECLARE @T1 TABLE

(

C1 INT NULL UNIQUE CLUSTERED,

C2 INT NOT NULL PRIMARY KEY NONCLUSTERED

)

DECLARE @T2 TABLE

(

C1 INT NULL UNIQUE NONCLUSTERED,

C2 INT NOT NULL PRIMARY KEY NONCLUSTERED

)

DECLARE @T3 TABLE

(

C1 INT NULL UNIQUE NONCLUSTERED,

C2 INT NOT NULL PRIMARY KEY CLUSTERED

)

-- >= SQL Server 2014

DECLARE @T4 TABLE (

C1 INT INDEX IDX1 CLUSTERED,

C2 INT INDEX IDX2 NONCLUSTERED,

INDEX IDX3 NONCLUSTERED(C1,C2)

);

-- >= SQL Server 2016

DECLARE @T5 TABLE

(

C1 INT NULL INDEX IDX1 UNIQUE WHERE C1 IS NOT NULL

)

Locking allégé

On ne peut pas parler d’absence de locking sur une variable table. Cela étant, à l’aide du traceflag 1200 qui permet d’afficher les informations sur le locking, la sortie de l’exemple ci-dessous indique qu’il est considérablement réduit.

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#T') IS NOT NULL
	DROP TABLE #T

PRINT '-- LOCKING ON #T --'

CREATE TABLE #T  (C1 INT, C2 VARCHAR(50))

DBCC TRACEON(1200,-1,3604)  WITH NO_INFOMSGS

INSERT INTO #T (C1, C2)
VALUES (1, REPLICATE('A',50)), (2, REPLICATE('A',50))

DBCC TRACEOFF(1200,-1,3604)  WITH NO_INFOMSGS

PRINT '-- LOCKING ON @T --'

DECLARE @T TABLE (C1 INT, C2 VARCHAR(50))

DBCC TRACEON(1200,-1,3604)  WITH NO_INFOMSGS

INSERT INTO @T (C1, C2)
VALUES (1, REPLICATE('A',50)), (2, REPLICATE('A',50))

DBCC TRACEOFF(1200,-1,3604)  WITH NO_INFOMSGS

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#T') IS NOT NULL

DROP TABLE #T

PRINT '-- LOCKING ON #T --'

CREATE TABLE #T (C1 INT, C2 VARCHAR(50))

DBCC TRACEON(1200,-1,3604) WITH NO_INFOMSGS

INSERT INTO #T (C1, C2)

VALUES (1, REPLICATE('A',50)), (2, REPLICATE('A',50))

DBCC TRACEOFF(1200,-1,3604) WITH NO_INFOMSGS

PRINT '-- LOCKING ON @T --'

DECLARE @T TABLE (C1 INT, C2 VARCHAR(50))

DBCC TRACEON(1200,-1,3604) WITH NO_INFOMSGS

INSERT INTO @T (C1, C2)

VALUES (1, REPLICATE('A',50)), (2, REPLICATE('A',50))

DBCC TRACEOFF(1200,-1,3604) WITH NO_INFOMSGS

Sortie :

-- LOCKING ON #T --
Process 81 acquiring S lock on DATABASE: 2:0 [PLANGUIDE] (class bit0 ref1) result: OK

Process 81 acquiring Sch-S lock on OBJECT: 2:-1157720094:0  (class bit0 ref1) result: OK

Process 81 acquiring Sch-S lock on OBJECT: 2:74:0  (class bit0 ref1) result: OK

Process 81 acquiring Sch-S lock on METADATA: database_id = 2 INDEXSTATS(object_id = -1157720094, index_id or stats_id = 0), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 81 acquiring Sch-S lock on OBJECT: 2:74:0  (class bit0 ref1) result: OK

Process 81 acquiring Sch-S lock on METADATA: database_id = 2 INDEXSTATS(object_id = -1157720094, index_id or stats_id = 0), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 81 releasing lock reference on METADATA: database_id = 2 INDEXSTATS(object_id = -1157720094, index_id or stats_id = 0), lockPartitionId = 0

Process 81 acquiring S lock on DATABASE: 2:0 [QDS] (class bit0 ref1) result: OK

Process 81 acquiring Sch-S lock on OBJECT: 2:49:0  (class bit0 ref1) result: OK

Process 81 acquiring Sch-S lock on METADATA: database_id = 2 INDEXSTATS(object_id = -1157720094, index_id or stats_id = 0), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 81 releasing lock reference on METADATA: database_id = 2 INDEXSTATS(object_id = -1157720094, index_id or stats_id = 0), lockPartitionId = 0

Process 81 acquiring Sch-S lock on OBJECT: 2:75:0  (class bit0 ref1) result: OK

Process 81 acquiring Sch-S lock on OBJECT: 2:74:0  (class bit0 ref1) result: OK

Process 81 releasing lock on METADATA: database_id = 2 INDEXSTATS(object_id = -1157720094, index_id or stats_id = 0), lockPartitionId = 0

Process 81 releasing lock on OBJECT: 2:-1157720094:0 

Process 81 releasing lock on DATABASE: 2:0 [PLANGUIDE]

Process 81 acquiring IX lock on OBJECT: 2:-1157720094:0  (class bit2000000 ref1) result: OK

Process 81 acquiring X lock on OBJECT: 2:-1157720094:0  (class bit2000000 ref1) result: OK

Process 81 acquiring IX lock on OBJECT: 2:-1157720094:0  (class bit2000000 ref1) result: OK

Process 81 acquiring X lock on EXTENT: 2:6:24424 (class bit4000000 ref1) result: OK

Process 81 releasing lock on EXTENT: 2:6:24424

Process 81 acquiring U lock on EXTENT: 2:1:72 (class bit0 ref1) result: OK

Process 81 acquiring X lock on PAGE: 2:1:79  (class bit4000000 ref0) result: OK

Process 81 releasing lock on EXTENT: 2:1:72

Process 81 acquiring Sch-S lock on OBJECT: 2:7:0  (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:7:0 

Process 81 acquiring Sch-S lock on OBJECT: 2:7:0  (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:7:0 

Process 81 acquiring Sch-S lock on OBJECT: 2:7:0  (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:7:0 

-- LOCKING ON @T --
Process 81 acquiring Sch-S lock on OBJECT: 2:-1173720151:0  (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:-1173720151:0 

Process 81 acquiring Sch-S lock on OBJECT: 2:-1173720151:0  (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:-1173720151:0 

Process 81 acquiring Sch-S lock on OBJECT: 2:-1173720151:0  (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:-1173720151:0 

Process 81 acquiring Sch-S lock on OBJECT: 2:-1173720151:0  (class bit0 ref1) result: OK

Process 81 acquiring X lock on OBJECT: 2:-1173720151:0  (class bit2000000 ref1) result: OK

Process 81 acquiring IX lock on OBJECT: 2:-1173720151:0  (class bit2000000 ref1) result: OK

Process 81 acquiring X lock on EXTENT: 2:5:33280 (class bit4000000 ref1) result: OK

Process 81 releasing lock on EXTENT: 2:5:33280

Process 81 acquiring X lock on EXTENT: 2:4:48 (class bit4000000 ref1) result: OK

Process 81 acquiring U lock on EXTENT: 2:4:48 (class bit0 ref1) result: OK

Process 81 acquiring X lock on PAGE: 2:4:48  (class bit4000000 ref0) result: OK

Process 81 releasing lock on EXTENT: 2:4:48

Process 81 acquiring Sch-S lock on OBJECT: 2:7:0  (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:7:0 

Process 81 acquiring Sch-S lock on OBJECT: 2:7:0  (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:7:0 

Process 81 acquiring Sch-S lock on OBJECT: 2:7:0  (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:7:0

100

101

102

103

-- LOCKING ON #T --

Process 81 acquiring S lock on DATABASE: 2:0 [PLANGUIDE] (class bit0 ref1) result: OK

Process 81 acquiring Sch-S lock on OBJECT: 2:-1157720094:0 (class bit0 ref1) result: OK

Process 81 acquiring Sch-S lock on OBJECT: 2:74:0 (class bit0 ref1) result: OK

Process 81 acquiring Sch-S lock on METADATA: database_id = 2 INDEXSTATS(object_id = -1157720094, index_id or stats_id = 0), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 81 acquiring Sch-S lock on OBJECT: 2:74:0 (class bit0 ref1) result: OK

Process 81 acquiring Sch-S lock on METADATA: database_id = 2 INDEXSTATS(object_id = -1157720094, index_id or stats_id = 0), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 81 releasing lock reference on METADATA: database_id = 2 INDEXSTATS(object_id = -1157720094, index_id or stats_id = 0), lockPartitionId = 0

Process 81 acquiring S lock on DATABASE: 2:0 [QDS] (class bit0 ref1) result: OK

Process 81 acquiring Sch-S lock on OBJECT: 2:49:0 (class bit0 ref1) result: OK

Process 81 acquiring Sch-S lock on METADATA: database_id = 2 INDEXSTATS(object_id = -1157720094, index_id or stats_id = 0), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 81 releasing lock reference on METADATA: database_id = 2 INDEXSTATS(object_id = -1157720094, index_id or stats_id = 0), lockPartitionId = 0

Process 81 acquiring Sch-S lock on OBJECT: 2:75:0 (class bit0 ref1) result: OK

Process 81 acquiring Sch-S lock on OBJECT: 2:74:0 (class bit0 ref1) result: OK

Process 81 releasing lock on METADATA: database_id = 2 INDEXSTATS(object_id = -1157720094, index_id or stats_id = 0), lockPartitionId = 0

Process 81 releasing lock on OBJECT: 2:-1157720094:0

Process 81 releasing lock on DATABASE: 2:0 [PLANGUIDE]

Process 81 acquiring IX lock on OBJECT: 2:-1157720094:0 (class bit2000000 ref1) result: OK

Process 81 acquiring X lock on OBJECT: 2:-1157720094:0 (class bit2000000 ref1) result: OK

Process 81 acquiring IX lock on OBJECT: 2:-1157720094:0 (class bit2000000 ref1) result: OK

Process 81 acquiring X lock on EXTENT: 2:6:24424 (class bit4000000 ref1) result: OK

Process 81 releasing lock on EXTENT: 2:6:24424

Process 81 acquiring U lock on EXTENT: 2:1:72 (class bit0 ref1) result: OK

Process 81 acquiring X lock on PAGE: 2:1:79 (class bit4000000 ref0) result: OK

Process 81 releasing lock on EXTENT: 2:1:72

Process 81 acquiring Sch-S lock on OBJECT: 2:7:0 (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:7:0

Process 81 acquiring Sch-S lock on OBJECT: 2:7:0 (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:7:0

Process 81 acquiring Sch-S lock on OBJECT: 2:7:0 (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:7:0

-- LOCKING ON @T --

Process 81 acquiring Sch-S lock on OBJECT: 2:-1173720151:0 (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:-1173720151:0

Process 81 acquiring Sch-S lock on OBJECT: 2:-1173720151:0 (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:-1173720151:0

Process 81 acquiring Sch-S lock on OBJECT: 2:-1173720151:0 (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:-1173720151:0

Process 81 acquiring Sch-S lock on OBJECT: 2:-1173720151:0 (class bit0 ref1) result: OK

Process 81 acquiring X lock on OBJECT: 2:-1173720151:0 (class bit2000000 ref1) result: OK

Process 81 acquiring IX lock on OBJECT: 2:-1173720151:0 (class bit2000000 ref1) result: OK

Process 81 acquiring X lock on EXTENT: 2:5:33280 (class bit4000000 ref1) result: OK

Process 81 releasing lock on EXTENT: 2:5:33280

Process 81 acquiring X lock on EXTENT: 2:4:48 (class bit4000000 ref1) result: OK

Process 81 acquiring U lock on EXTENT: 2:4:48 (class bit0 ref1) result: OK

Process 81 acquiring X lock on PAGE: 2:4:48 (class bit4000000 ref0) result: OK

Process 81 releasing lock on EXTENT: 2:4:48

Process 81 acquiring Sch-S lock on OBJECT: 2:7:0 (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:7:0

Process 81 acquiring Sch-S lock on OBJECT: 2:7:0 (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:7:0

Process 81 acquiring Sch-S lock on OBJECT: 2:7:0 (class bit0 ref1) result: OK

Process 81 releasing lock on OBJECT: 2:7:0

Logging

Pour ce qui est du logging, il se fait de la même façon que ce soit pour une table temporaire ou une variable table dans le journal de transaction. Pour s’en rendre compte, il suffit d’interroger le fichier de transaction via la fonction fn_dblog ,après avoir lancer les mêmes mises à jour sur une table temporaire et une variable table. On obtient un nombre/type d’opérations et quantité de log similaires :

USE tempdb 
GO   

IF OBJECT_ID('tempdb..#T') IS NOT NULL
	DROP TABLE #T

CHECKPOINT
GO

DECLARE @T TABLE (
	[7E0D03E3-4F91-4436-A7DD-93140A42122F] INT, 
	Label CHAR(100)
)

INSERT INTO @T VALUES (1, REPLICATE('A', 100)), (2, REPLICATE('A', 100))

UPDATE @T SET    
Label = REPLICATE ('B', 100)

CREATE TABLE #T (
	[C317B476-7559-4215-A6F0-F8F32C885206] INT, 
	Label CHAR(100)
)

INSERT INTO #T VALUES (1, REPLICATE('A', 100)), (2, REPLICATE('A', 100))

UPDATE #T SET
Label = REPLICATE ('B', 100)

DECLARE @AllocUnitIds AS TABLE (AllocUnitId BIGINT, TableType VARCHAR(50))

INSERT	INTO @AllocUnitIds
SELECT	a.allocation_unit_id,
		IIF(c.name = '7E0D03E3-4F91-4436-A7DD-93140A42122F', '@T', '#T')
FROM	tempdb.sys.partitions AS p
INNER	JOIN tempdb.sys.system_internals_allocation_units AS a ON p.hobt_id = a.container_id
INNER	JOIN tempdb.sys.tables AS t ON t.object_id = p.object_id
INNER	JOIN tempdb.sys.columns AS c ON c.object_id = p.object_id
WHERE	c.name IN ('7E0D03E3-4F91-4436-A7DD-93140A42122F', 'C317B476-7559-4215-A6F0-F8F32C885206')

SELECT  Operation,
        Context,
		a.TableType,
        AllocUnitName,
        [Log Record Length]
FROM	fn_dblog(NULL, NULL) l
INNER	JOIN @AllocUnitIds a ON l.AllocUnitId = a.AllocUnitId

SELECT  a.TableType,
        [Total Log Record Length] = SUM([Log Record Length])
FROM	fn_dblog(NULL, NULL) l
INNER	JOIN @AllocUnitIds a ON l.AllocUnitId = a.AllocUnitId
GROUP BY a.TableType

USE tempdb

IF OBJECT_ID('tempdb..#T') IS NOT NULL

DROP TABLE #T

CHECKPOINT

DECLARE @T TABLE (

[7E0D03E3-4F91-4436-A7DD-93140A42122F] INT,

Label CHAR(100)

)

INSERT INTO @T VALUES (1, REPLICATE('A', 100)), (2, REPLICATE('A', 100))

UPDATE @T SET

Label = REPLICATE ('B', 100)

CREATE TABLE #T (

[C317B476-7559-4215-A6F0-F8F32C885206] INT,

Label CHAR(100)

)

INSERT INTO #T VALUES (1, REPLICATE('A', 100)), (2, REPLICATE('A', 100))

UPDATE #T SET

Label = REPLICATE ('B', 100)

DECLARE @AllocUnitIds AS TABLE (AllocUnitId BIGINT, TableType VARCHAR(50))

INSERT INTO @AllocUnitIds

SELECT a.allocation_unit_id,

IIF(c.name = '7E0D03E3-4F91-4436-A7DD-93140A42122F', '@T', '#T')

FROM tempdb.sys.partitions AS p

INNER JOIN tempdb.sys.system_internals_allocation_units AS a ON p.hobt_id = a.container_id

INNER JOIN tempdb.sys.tables AS t ON t.object_id = p.object_id

INNER JOIN tempdb.sys.columns AS c ON c.object_id = p.object_id

WHERE c.name IN ('7E0D03E3-4F91-4436-A7DD-93140A42122F', 'C317B476-7559-4215-A6F0-F8F32C885206')

SELECT Operation,

Context,

a.TableType,

AllocUnitName,

[Log Record Length]

FROM fn_dblog(NULL, NULL) l

INNER JOIN @AllocUnitIds a ON l.AllocUnitId = a.AllocUnitId

SELECT a.TableType,

[Total Log Record Length] = SUM([Log Record Length])

FROM fn_dblog(NULL, NULL) l

INNER JOIN @AllocUnitIds a ON l.AllocUnitId = a.AllocUnitId

GROUP BY a.TableType

Et la CTE dans tout ça ?

Ce n’est pas une table en sois mais une vue calculée à la volée, non matérialisée et sans metadonnées. L’appel à une CTE peut même faire partie d’une vue contrairement aux tables temporaires ##temp et #temp. Voici quelques exemples d’implémentation d’une CTE :

Mise à jour basée sur une agrégation

Au préalable, création d’une table contenant des montants de commandes associés à des produits : on souhaite remonter sur chaque ligne le résultat d’agrégation à savoir le montant minimum et maximum pour chaque produit.

IF OBJECT_ID('tempdb..#Orders') IS NOT NULL
	DROP TABLE #Orders

CREATE TABLE #Orders
(
  ProductID    INT NOT NULL,
  TotalOrder    DECIMAL(10,2),
  MaxOrder  DECIMAL(10,2),
  MinOrder DECIMAL(10,2)
);
 
INSERT #Orders(ProductID, TotalOrder) 
VALUES	(1, 78.10),  
		(2, 8.20),
		(1, 9.40),  
		(2, 20.60),
		(1, 55.80),
		(2, 90.70);

IF OBJECT_ID('tempdb..#Orders') IS NOT NULL

DROP TABLE #Orders

CREATE TABLE #Orders

(

ProductID INT NOT NULL,

TotalOrder DECIMAL(10,2),

MaxOrder DECIMAL(10,2),

MinOrder DECIMAL(10,2)

);

INSERT #Orders(ProductID, TotalOrder)

VALUES (1, 78.10),

(2, 8.20),

(1, 9.40),

(2, 20.60),

(1, 55.80),

(2, 90.70);

On pourrait passer par une sous-requête mais le recours à une CTE est aussi valable :

;WITH OrderCTE AS
(
  SELECT 
    ProductID,
	MaxOrder,
	MinOrder, 
    MaxOrderCalc = MAX(TotalOrder) OVER (PARTITION BY ProductID),
    MinOrderCalc = MIN(TotalOrder) OVER (PARTITION BY ProductID)
  FROM #Orders
)
UPDATE OrderCTE SET 
MaxOrder = MaxOrderCalc, 
MinOrder = MinOrderCalc

SELECT	*
FROM	#Orders

;WITH OrderCTE AS

(

SELECT

ProductID,

MaxOrder,

MinOrder,

MaxOrderCalc = MAX(TotalOrder) OVER (PARTITION BY ProductID),

MinOrderCalc = MIN(TotalOrder) OVER (PARTITION BY ProductID)

FROM #Orders

)

UPDATE OrderCTE SET

MaxOrder = MaxOrderCalc,

MinOrder = MinOrderCalc

SELECT *

FROM #Orders

Récursivité

Dans le cas présent, on souhaite générer un calendrier pour l’année en cours :

DECLARE	@DateStart DATETIME = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0),
		@DateEnd DATETIME = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1)

;WITH CteDimTime AS (SELECT CAST(@DateStart AS DATETIME) MyDate
UNION ALL 
SELECT	DATEADD(HOUR,1,MyDate)
FROM	CteDimTime 
WHERE	DATEADD(HOUR,1,MyDate) <= CAST(@DateEnd AS DATETIME)) 

SELECT	MyDate
FROM	CteDimTime
OPTION (MAXRECURSION 0);

DECLARE @DateStart DATETIME = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0),

@DateEnd DATETIME = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1)

;WITH CteDimTime AS (SELECT CAST(@DateStart AS DATETIME) MyDate

UNION ALL

SELECT DATEADD(HOUR,1,MyDate)

FROM CteDimTime

WHERE DATEADD(HOUR,1,MyDate) <= CAST(@DateEnd AS DATETIME))

SELECT MyDate

FROM CteDimTime

OPTION (MAXRECURSION 0);

On peut tout aussi bien générer des incréments comme suit :

;WITH x AS
(
  SELECT y = 1
  UNION ALL
  SELECT y + 1 
  FROM x 
  WHERE y < 200
)
SELECT y FROM x
OPTION (MAXRECURSION 200);

;WITH x AS

(

SELECT y = 1

UNION ALL

SELECT y + 1

FROM x

WHERE y < 200

)

SELECT y FROM x

OPTION (MAXRECURSION 200);

Pratique pour la récursivité mais dans certains cas un WHILE sera beaucoup plus performant.

Synthèse

Pour conclure, voici une matrice permettant d’orienter votre choix pour l’utilisation d’une table temporaire, multi-sessions ou pas, d’une variable table ou d’une CTE :

Auteur

Sarah Béquet

Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.

Derniers articles

2019.12.02BI & Big Data Etude des contextes en DAX (4/4) : Transition de contexte
2019.11.25BI & Big Data Etude des contextes en DAX (3/4) : Contexte de requête
2019.11.17BI & Big Data Etude des contextes en DAX (2/4) : Contexte de filtre
2019.11.11BI & Big Data Etude des contextes en DAX (1/4) : Contexte de ligne

Articles liés

Purger le fichier de transaction d'une base en AlwaysOn ou en Miroir

sp_help_revlogin : Copie des logins d’une instance à l’autre

Optimiseur de requête

SQL Server : Compilation et recompilation

Blog