“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

Using Temp Stored Procedures

Bert Wagner shows how you can perform testing with temporary stored procedures: A while back I learned that it’s possible to create temporary stored procedures in SQL Server. I never put that knowledge into practice however because I struggled to think of a good use case for when a temporary stored procedure would be preferable […]

Read More

Alleviating tempdb Contention

Pam Lahoud has some advice for those with tempdb-heavy workloads: TL;DR – Update to the latest CU, create multiple tempdb files, if you’re on SQL 2014 or earlier enable TF 1117 and 1118, if you’re on SQL 2016 enable TF 3427. And now it’s time for everyone’s favorite SQL Server topic – tempdb! In this […]

Read More

Categories

May 2016
MTWTFSS
« Apr Jun »
 1
2345678
9101112131415
16171819202122
23242526272829
3031