Knowing Tempdb - SQL Server 2012 Internals and Troubleshooting - page 213

➤

Tempdb is always set to “Simple” recovery mode, which, if you remember from Chapter 1,
means that transaction log records for committed transactions are marked for reuse after
every checkpoint. This means you don't need to back up the transaction log for tempdb, and
in fact, you can't back up tempdb at all.

➤

Tempdb can only have one i legroup (the PRIMARY i legroup); you can't add more.

➤

Tempdb is used to store three types of objects: user objects, internal objects, and the version
store.

User Temporary Objects
All the code in this section uses the Ch8_1TempDBTempObjects.sql code i le.

To store data temporarily you can use local temporary tables, global temporary tables, or table
variables, all of which are stored in tempdb (you can't change where they're stored). A local
temporary table is dei ned by giving it a prei x of # and it is scoped to the session in which you
created it. This means no one can see it; and when you disconnect, or your session is reset with
connection pooling, the table is dropped. The following example creates a local temporary table,
populates it with one row, and then selects from it:

CREATE TABLE #TempTable ( ID INT, NAME CHAR(3) ) ;
INSERT INTO #TempTable ( ID, NAME )
VALUES ( 1, 'abc' ) ;
GO
SELECT *
FROM #TempTable ;
GO
DROP TABLE #TempTable ;

Global temporary tables can be seen by all sessions connected to the server and are dei ned by a
prei x of ##. They are used in exactly the same way as local temporary tables, the only difference
being that everyone can see them. They are not used very often because if you had a requirement for
multiple users to use the same table, you're more likely to implement a normal table in a user
database, rather than a global temporary table. Here is exactly the same code just shown but
implemented as a global temporary table:

CREATE TABLE ##TempTable ( ID INT, NAME CHAR(3) ) ;
INSERT INTO ##TempTable ( ID, NAME )
VALUES ( 1, 'abc' ) ;
GO
SELECT *
FROM ##TempTable ;
GO
DROP TABLE ##TempTable ;

As you can see, the only difference is the prei x; both local temporary tables and global temporary
tables are dropped when the session that created them is closed. This means it is not possible to
create a global temporary table in one session, close the session, and then use it in another.