Temporary Tables - Pro SQL Server Internals - page 246

■ We will discuss recovery models, and the differences in logging between tempdb and user databases in
Chapter 29, “transaction log Internals.”

Note

Tempdb could be a choice as the staging area for ETL processes, where you need to load and process a large
amount of data as fast as possible with minimum logging overhead. You can use temporary tables when the process is
done as a single session, however you need to use regular tables in more complex cases.

■ While tempdb can help with staging area performance, client applications need to handle the situation
when tempdb is recreated and the tables with the data are gone. this may occur if sQl server restarts or fails over to
another node.

Important

to make the situation even worse, this can happen transparently to the client applications in some cases. applications
need to handle those situations either by checking the existence of the staging tables or, if you are creating tables
automatically, the state information needs to be persisted somewhere else.

Let's assume that we have a table called ETLStatuses , which contains information about the ETL process
statuses. There are a couple ways that you can create such a table. One is using a model database. All objects created
in a model database are copied to tempdb during SQL Server startup.

■ Caution all objects created in a model database will be copied into the user databases that are created afterward.

Alternatively, you can create objects in tempdb using a stored procedure that executes upon SQL Server startup.
Listing 12-21 shows such an example.

Listing 12-21. Creating a table in tempdb with a startup stored procedure

use master;
GO

-- Enable scan for startup procs
exec sp_configure 'show advanced option', '1';
reconfigure;
exec sp_configure 'scan for startup procs', '1';
reconfigure;
go

create proc dbo.CreateETLStatusesTable
as
create table tempdb.dbo.ETLStatuses
(
ProcessId int not null,
ActivityTime datetime not null,
StageNo smallint not null,
[Status] varchar(16) not null,