What is Temp Stored Procedures? – Interview Question of the Week #294

Question: What is a temporary stored procedure? A SQL Server procedure named with a leading # is local to its creating connection. It lives in TempDB and disappears when that connection closes, without waiting for a server restart.

During a Comprehensive Database Performance Health Check, I often compare alternative versions of a procedure. Naming permanent copies Temp, Temp2 and TempFinal quickly leaves a database full of experiments. A local temporary procedure keeps a short, single-session comparison out of the application’s permanent procedure list.

Create and Execute It in the Same Connection

CREATE PROCEDURE #TempSP
AS
    SELECT 1 AS SPName;
GO
EXEC #TempSP;
GO

The result is one column named SPName, containing 1. CREATE PROCEDURE starts its own batch; GO is the client batch separator. Reconnecting the query window creates a new connection, so the earlier local temporary procedure won’t be there.

What Changes With Two Hash Signs?

-- Concept only: use a unique name in a shared instance.
CREATE PROCEDURE ##GlobalSP
AS
    SELECT 1 AS SPName;
GO

A global temporary procedure can be accessed from other connections. Its lifecycle depends on the sessions using it, so don’t promise that it remains until the server restarts. Shared names also mean collisions and unintended callers. For a private tuning experiment, I prefer the local form.

Neither form makes a test representative automatically. Keep parameters, data and session SET options comparable when evaluating plans. Once an alternative is ready for application use, deploy a reviewed permanent procedure through the normal process; a temporary object is not an application deployment strategy.

A temporary procedure is not a shortcut to production, it is a scratch pad, and it vanishes with your connection.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.