Datasheet
The TempDB database is used by the Database Engine to store temporary objects (such as temporary
tables, views, cursors, and table-valued variables) that are explicitly created by database programmers.
In addition, the
TempDB database is used by the SQL Server database engine to store work tables contain-
ing intermediate results of a query prior to a sort operation or other data manipulation. For example, if
you wrote a query that returned 100,000 rows and you wanted the results sorted by a date value in the
results, SQL Server could send the unsorted results to a temporary work table where it would perform
the sorting operation and then return the sorted results to you. The
TempDB database is also used exten-
sively to support new connection options such as
SNAPSHOT ISOLATION or Multiple Active Result Sets
(MARS). If online index operations are performed, the
TempDB database will hold the index during the
build or rebuild process.
Another important aspect to keep in mind about the
TempDB database is that all database users have
access to it and have the ability to create and populate temporary objects. This access can potentially cre-
ate locking and size limitation issues on SQL Server, so it is important to monitor the
TempDB database
just like any other database on SQL Server.
The Resource Database
The last system database is the Resource database. The Resource database is a read-only database that
contains all the system objects used by an instance of SQL Server. The
Resource database is not accessi-
ble during normal database operations. It is logically presented as the
SYS schema in every database. It
contains no user data or metadata. Instead, it contains the structure and description of all system objects.
This design enables the fast application of service packs by just replacing the existing
Resource
database with a new one. As an added bonus, to roll back a service pack installation, all you have to do
is replace the new
Resource database with the old one. This very elegant design replaces the older
method of running many scripts that progressively dropped and added new system objects.
User Databases
User databases are simply that: databases created by users. They are created to store data used by data
applications and are the primary purpose of having a database server. During installation, you have the
option of installing two sample user databases:
AdventureWorks and AdventureWorksDW.
The
AdventureWorks database is an OLTP database used by the fictitious Adventure-Works Cycles
Company, which sells mountain bikes and mountain-biking-related merchandise.
The
AdventureWorksDW database is an OLAP database used for data analysis of historical Adventure-
Works Cycles data. Most of the sample code and examples provided in Books Online use these two sam-
ple databases.
Distribution Databases
One or more distribution databases can be configured to support replication. Some SQL Server profes-
sionals describe the distribution databases as system databases, and yet others describe them as user
databases. I don’t think it makes much difference. What is important is what the database or databases do.
The distribution database stores metadata and transactional history to support all types of replication on
a SQL Server. Typically, one distribution database is created when configuring a SQL Server as a replica-
tion Distributor. However, if needed, multiple distribution databases can be configured.
16
Chapter 1
04_047046 ch01.qxp 10/18/06 12:18 AM Page 16