“Permanent” Temp Tables

Brent Ozar shows two ways of creating “permanent” temp tables:

The first one disappears when my session is over, but the latter two persist until the SQL Server is restarted.

Why would you ever do the latter two? Say you need to share data between sessions, or between different applications, or staging tables for a data warehouse, or just faster tables that live on local SSDs in a cluster (as opposed to slower shared storage), or you wanna build a really crappy caching tier.

Brent also talks about stored procedures.

Related Posts

Diagnosing TempDB Growth

Ginger Keys has a few tips around managing tempdb: So what happened to cause this inconsistent growth and how should I fix it? Tempdb is used (and can grow) when users explicitly create objects like temp tables, variables, cursors, or stored procedures. Tempdb is also used when the database engine creates work tables for sorts […]

Read More

On Global Temp Tables

Denis Gobo riffs on global temp tables: SQL Azure has added something called database scoped global temporary tables. Azure SQL Database supports global temporary tables that are also stored in tempdb and scoped to the database level. This means that global temporary tables are shared for all users’ sessions within the same Azure SQL database. […]

Read More


May 2016
« Apr Jun »