T-SQL Tuesday #201 – Temp Tables, Friend or Foe?

Words: 1100
Time to read: ~ 6 minutes

Nothing

Welcome to T-SQL Tuesday, the monthly blog party started by Adam Machanic and maintained by Steve Jones.

Each month, a different host chooses a topic for discussion. This month, we have Jeff Taylor asking us about “temp tables, friend or foe?“

Clicka-de-bait

Ages ago, I broke one of the stored procedures in Brent Ozar‘s First Responder Kit.

I filed that feat under the heading “Cool; Good to Know“, and then promptly forgot about it. Part of me thinks that I forgot about it because I didn’t understand how I accomplished it. While another part is sure I forgot about it cause “good to know” wasn’t on the list of tasks with looming deadlines.

I’m a man of many parts, but of more deadlines. Now, let’s understand it together!

First, there was the ooze

It’s a tactic of dealing with volatile amounts of data to call different stored procedures based on the number of records coming into it. I swear, I didn’t make this up!

Small numbers of records may be fine, but an odd, massive number may trigger a new plan. One that frequent executions and resources on the machine may not agree with.

So, normal, small, fast, very frequent executions get the “happy” path, but the big guy can go his own way. Free to roam, hog, and frizzle away everything he has!

We encountered this scenario once and decided to implement this tactic.

However, while reviewing this new stored procedure volatility countermeasure, I came across a temp table that wasn’t created anywhere in the procedure.

Stored Procedures are isolated. Aren’t they?

Or was it the sludge?

Let’s talk about Dynamic SQL for a moment.

I’m sure, although I won’t make up statistics, that you’ve used Dynamic SQL to query over different databases and collated their results.

Here’s a completely made-up example (Don’t do this. I’m sure it’s stupid, but it’s also very late right now)

DROP TABLE IF EXISTS #tables;
CREATE TABLE #tables
(
	tables_ID		int				NOT NULL	IDENTITY(1,1),
	[database_name]	nvarchar(255)	NOT NULL,
	table_count		int					NULL
);
INSERT INTO #tables ([database_name]) 
SELECT [name] FROM sys.databases;
SELECT * FROM #tables;
GO
DECLARE @i				int = 1;
DECLARE @max			int;
DECLARE @context		nvarchar(MAX);
DECLARE @context_stub	nvarchar(MAX) = N'.sys.sp_executesql';
DECLARE @sql			nvarchar(MAX) = N'WITH
	table_counts AS
	(
		SELECT COUNT(*) AS database_table_count FROM sys.tables
	)
	UPDATE #tables 
	SET table_count = database_table_count
	FROM table_counts
	WHERE tables_ID = @ID';
SELECT @max = MAX(tables_ID) FROM #tables;
WHILE @i <= @max
BEGIN
	SET @context = CONCAT(
			QUOTENAME(
				(
					SELECT TOP (1) [database_name] FROM #tables WHERE tables_ID = @i
				)
			),
			@context_stub
		);
	EXEC @context @sql, N'@ID int', @ID = @i
	SET @i += 1;
END;
SELECT * FROM #tables

I mean, look at line 25 in the code above. We didn’t create any temp table in that dynamic sql, but we’re updating it fine!

There was something I was forgetting: Scope!

Scopé (the é is pronounced)

I had never fully thought about this before.

I thought Dynamic SQL was just strings being executed (and it still kinda is), but it operates under a nested scope of the session.

So, when we say EXEC [sys].[sp_executesql] @stmt = N'INSERT INTO #temp…, for a temp table we’re created in the parent session, we’re ignoring scope boundaries.

The same thing happens with Stored Procedures.

Table Variables? No.

CREATE OR ALTER PROC dbo.TableVar2
AS
BEGIN
	INSERT INTO @table_variable (ID) VALUES (1);
END;
GO
CREATE OR ALTER PROC dbo.TableVar1
AS
BEGIN
	DECLARE @table_variable TABLE (ID int);
	EXEC dbo.TableVar2;
END;
GO
EXEC dbo.TableVar1;

Variables? No.

CREATE OR ALTER PROC dbo.Var2
AS
BEGIN
	SET @i = 2;
END;
GO
CREATE OR ALTER PROC dbo.Var1
AS
BEGIN
	DECLARE @i int;
	EXEC dbo.Var2;
END;
GO
EXEC dbo.Var1;

Temp Tables? Yes!

CREATE OR ALTER PROC dbo.Proc1
AS
BEGIN
	CREATE TABLE #t1
	(
		ID int NOT NULL
	);
	INSERT INTO #t1 (ID) VALUES (1);
	SELECT ID FROM #t1;
END;
GO
EXEC dbo.Proc1;
GO
CREATE OR ALTER PROC dbo.Proc2
AS
BEGIN
	CREATE TABLE #t2
	(
		ID int NOT NULL
	);
	EXEC dbo.Proc3;
	SELECT ID FROM #t2;
END;
GO
CREATE OR ALTER PROC dbo.Proc3
AS
BEGIN
	INSERT INTO #t2 (ID) VALUES (3);
END;
GO
EXEC dbo.Proc2;

That’s what the temp table and the stored procedures were doing. Boundary violations don’t really count when it’s my nested scope!

It’s Actually a Donut

Now that that tangent is all squared away, let’s return to how I broke a First Responder Kit stored procedure.

 CREATE OR ALTER PROC dbo.NormalProc
AS
BEGIN
	CREATE TABLE #BlitzFirstResults
	(
		ID int NOT NULL
	);

	INSERT INTO #BlitzFirstResults VALUES (1);

	SELECT ID FROM #BlitzFirstResults;
END;
GO

EXEC dbo.NormalProc;
GO

/*
DROP PROC dbo.NormalProc;
DROP TABLE IF EXISTS #BlitzFirstResults
*/

 /*
DROP TABLE IF EXISTS #BlitzFirstResults
*/
IF OBJECT_ID(N'tempdb..#BlitzFirstResults') IS NULL
BEGIN
	CREATE TABLE #BlitzFirstResults
	(
		BlitzFirstResults_ID int NOT NULL
	);
END
SELECT * FROM #BlitzFirstResults;
EXEC dbo.NormalProc

So, yeah … in truth, I didn’t so much break the stored procedure as I interfered with it…but that just doesn’t sound great when I say it like that.

Now, I know that my temp table in the parent session …got in the way… of the stored procedure’s inner temp table.

Anyway, things happened

What does this have to do with T-SQL Tuesday? Oh! The barest of passing similarities, but I’m team temp tables. They’re weird and (more) wonderful than I realised, but I’ll leave it to smarter people than myself to give you the raw numbers.

All I know is that I still have billion-row tables, procedures with multiple WHERE col1 = @param1 OR @param1 IS NULL) […] filters, and uptime requirements that make finding an outage window for UPDATE STATS more difficult than a greyscale Where’s Wally book.

I’ll take every trick in the book.

[Speaking of books, all this is wonderfully documented in the Microsoft Docs page, but someone didn’t think to read that part… – Editor Shane]