Skip to the content

Gebruik ik een variabele of local temp table in SQL Server

In opgeslagen procedures maak ik gebruik van tijdelijke tabellen. Deze zijn erg handig om complexere bewerkingen mee uit te voeren. In deze tabellen sla ik de data op uit de tabellen van de SQL Server database, pas ze aan en geef het resultaat terug als een query of lees de data terug in de SQL Server tabellen.

Hoe zit het ook alweer met tijdelijke tabellen

Local Temp Table kan tegelijkertijd worden gebruikt door meedere gebruikers en dus worden aangemaakt door verschillende gebruikers. Wanneer de opgeslagen procedure is verlaten zal de tijdelijke tabel niet langer bruikbaar zijn voor de gebruiker en worden verwijderd.

Global Temp Table verliest de toepasbaarheid nadat alle procedures die refereren naar deze tabel zijn uitgevoerd. Er kan dus vanuit een opgeslagen procedure een global temp table worden gemaakt die kan worden aangeroepen door andere procedures.

Variabele tabel kan ook door meerdere gebruikers worden uitgevoerd en leeft totdat de scope van de opgeslagen procedure wordt verlaten. De variabele tabel is snel en verbruikt vrijwel geen resources.

Mijn voorkeur gaat uit naar de variabele tabel (Variabele @). Enerzijds omdat deze het werk voor je doen en minder resources vragen dan de temp tables (local # of global ##)

Echter, bij grotere hoeveelheden of complexe data is het verstandiger en heel veel sneller om de local variant te gebruiken. Het verschil tussen de local en de global variant is dat je de global temp table kunt gebruiken in andere procedures. De levensduur is dus langer. Een local temp table leeft totdat de opgeslagen procedure is voltooid.

In onderstaande voorbeeld laat ik de verschillen zien tussen een local en een variabele tabel.

/***************** bog het gebruik van tijdelijke tabellen *********************/

--Tijdelijke tabellen van het type local en global eerst verwijderen
	IF OBJECT_ID(N'tempdb..#TABLE3', N'U') IS NOT NULL 
	DROP TABLE #TABLE3;

	IF OBJECT_ID(N'tempdb..#TABLE4', N'U') IS NOT NULL 
	DROP TABLE #TABLE4;
		
--Tijdelijke tabel 1 waarbij we de tabel als een variabele (@) opnemen
	DECLARE @TABLE1 AS TABLE(
		JpId INT,
		Datum DATETIME,
		Boekstuk VARCHAR(25)
	)
--Tijdelijke tabel 2 waarbij we de tabel als een variabele (@) opnemen
	DECLARE @TABLE2 AS TABLE(
		JpId INT,
		JpRegelId INT,
		Debet NUMERIC(28,10),
		Credit NUMERIC(28,10)
	)
	
--Nu de variant waarbij we de tabellen aanmaken als een local temp table (#)
	CREATE TABLE #TABLE3(
		JpId INT,
		Datum DATETIME,
		Boekstuk VARCHAR(25)
	)
--Op dit type tabellen kunnen we een index toevoegen
	CREATE INDEX IDX_Table3_id ON #TABLE3(JpId)
	
--Nu de variant, voor de tweede tabel, waarbij we de tabellen aanmaken als een local temp table (#)
	CREATE TABLE #TABLE4(
		JpId INT,
		JpRegelId INT,
		Debet NUMERIC(28,10),
		Credit NUMERIC(28,10)
	)

--Op dit type tabellen kunnen we een index toevoegen
	CREATE INDEX IDX_Table4_id ON #TABLE4(JpRegelId)
	
--Nu gaan we de tijdelijke tabellen vullen met data
	INSERT INTO @TABLE1(JpId, Datum, Boekstuk)
	SELECT 1, '20120101 00:00:00.000', N'' UNION ALL
	SELECT 3, '20120130 00:00:00.000', N'1' UNION ALL
	SELECT 4, '20120130 00:00:00.000', N'1' UNION ALL
	SELECT 10, '20120102 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 11, '20120102 00:00:00.000', N'Geldlade 2' UNION ALL
	SELECT 13, '20120102 00:00:00.000', N'1' UNION ALL
	SELECT 14, '20120108 00:00:00.000', N'2' UNION ALL
	SELECT 15, '20120116 00:00:00.000', N'3' UNION ALL
	SELECT 18, '20120128 00:00:00.000', N'6' UNION ALL
	SELECT 19, '20120129 00:00:00.000', N'7' UNION ALL
	SELECT 20, '20120131 00:00:00.000', N'8' UNION ALL
	SELECT 21, '20120131 00:00:00.000', N'9' UNION ALL
	SELECT 22, '20120102 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 23, '20120102 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 24, '20120102 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 25, '20120102 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 26, '20120102 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 27, '20120102 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 28, '20120103 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 29, '20120103 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 30, '20120103 00:00:00.000', N'Geldlade 1'
--Copy paste :)
	INSERT INTO #TABLE3(JpId, Datum, Boekstuk)
	SELECT 1, '20120101 00:00:00.000', N'' UNION ALL
	SELECT 3, '20120130 00:00:00.000', N'1' UNION ALL
	SELECT 4, '20120130 00:00:00.000', N'1' UNION ALL
	SELECT 10, '20120102 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 11, '20120102 00:00:00.000', N'Geldlade 2' UNION ALL
	SELECT 13, '20120102 00:00:00.000', N'1' UNION ALL
	SELECT 14, '20120108 00:00:00.000', N'2' UNION ALL
	SELECT 15, '20120116 00:00:00.000', N'3' UNION ALL
	SELECT 18, '20120128 00:00:00.000', N'6' UNION ALL
	SELECT 19, '20120129 00:00:00.000', N'7' UNION ALL
	SELECT 20, '20120131 00:00:00.000', N'8' UNION ALL
	SELECT 21, '20120131 00:00:00.000', N'9' UNION ALL
	SELECT 22, '20120102 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 23, '20120102 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 24, '20120102 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 25, '20120102 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 26, '20120102 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 27, '20120102 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 28, '20120103 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 29, '20120103 00:00:00.000', N'Geldlade 1' UNION ALL
	SELECT 30, '20120103 00:00:00.000', N'Geldlade 1'

--Om het blog leesbaar te houden Gebruik ik hier een paar regels. 
--U kunt deze actie uiteraard ook uitvoeren met een select uit de tabellen 
--INSERT INTO @TABLE2(JpId, JpRegelId, Debet, Credit)
--SELECT fldJournaalpostRegelID, fldJournaalpostID, fldDebet, fldCredit FROM dbo.tblJournaalpostRegel

	INSERT INTO @TABLE2(JpId, JpRegelId, Debet, Credit)
	SELECT 1, 1, 10.0000, 0.0000 UNION ALL
	SELECT 1, 2, 10.0000, 0.0000 UNION ALL
	SELECT 1, 3, 00.0000, 20.0000 UNION ALL
	SELECT 2, 4, 10.0000, 0.0000 UNION ALL
	SELECT 2, 5, 00.0000, 10.0000 UNION ALL
	SELECT 3, 6, 10.0000, 0.0000 UNION ALL
	SELECT 3, 7, 10.0000, 0.0000 UNION ALL
	SELECT 3, 8, 00.0000, 20.0000 UNION ALL
	SELECT 4, 9, 10.0000, 0.0000 UNION ALL
	SELECT 4, 10, 00.0000, 10.0000	
	
	INSERT INTO #TABLE4(JpId, JpRegelId, Debet, Credit)
	SELECT 1, 1, 10.0000, 0.0000 UNION ALL
	SELECT 1, 2, 10.0000, 0.0000 UNION ALL
	SELECT 1, 3, 00.0000, 20.0000 UNION ALL
	SELECT 2, 4, 10.0000, 0.0000 UNION ALL
	SELECT 2, 5, 00.0000, 10.0000 UNION ALL
	SELECT 3, 6, 10.0000, 0.0000 UNION ALL
	SELECT 3, 7, 10.0000, 0.0000 UNION ALL
	SELECT 3, 8, 00.0000, 20.0000 UNION ALL
	SELECT 4, 9, 10.0000, 0.0000 UNION ALL
	SELECT 4, 10, 00.0000, 10.0000

--Nu gaan we een query uitvoeren waarbij we een join toepassen
SELECT 	A.JpId,
		A.Datum,
		A.Boekstuk,
		B.Debet,
		B.Credit
FROM @TABLE1 A INNER JOIN @TABLE2 B ON A.JpId = B.JpId

--Doe nu eens hetzelfde bij de locale tabellen met de index en zie bij deze kleine hoeveelheid data al het verschil
SELECT 	A.JpId,
		A.Datum,
		A.Boekstuk,
		B.Debet,
		B.Credit
FROM #TABLE3 A INNER JOIN #TABLE4 B ON A.JpId = B.JpId

De SQL Server zoekopdracht uitgelegd

In de header van de sectie zie je dat de locale tabellen eerst worden verwijderd als deze nog bestaan. Zoals in het begin aangegeven leven lokale tabellen totdat de procedure de scope verliest. Je zou het voorbeeld script dan ook niet 2 keer uit kunnen voeren zonder eerst de tijdelijke tabellen te verwijderen. Of toch wel? Ja als je het script kopieert en opnieuw in een nieuwe query plakt kan je opnieuw de procedure uitvoeren. Dit geeft direct aan wat de levensduur is van de local temp table.

IF OBJECT_ID(N'tempdb..#TABLE3', N'U') IS NOT NULL

DROP TABLE #TABLE3;

Ik maak in het voorbeeld script 4 tabellen. Table1 en Table2 zijn de variabele varianten, Table3 en Table4 zijn de local temp table varianten. Bij de local variant bestaat de mogelijkheid om een index toe te voegen en dat is wat het verschil maakt bij grote hoeveelheden data. Geïndexeerde data zal sneller opgehaald kunnen worden. In een complexe situatie kan het verschil tussen beide varianten enorm zijn. Lees 3 uur voor de variabele en 28 seconden voor de local temp variant.

DECLARE @TABLE1 AS TABLE

CREATE TABLE #TABLE3

Tabel Table1 en Table3 zijn gelijk aan elkaar voor wat betreft de velden en veldtypes en datzelfde geldt ook voor de tabellen Table2 en Table4. Respectievelijk 1+3 wordt gevuld met dezelfde data en 2+4 worden eveneens gevuld met dezelfde data.

Reinder.eu | Variabele of local temp table in SQL server