Datasheet

Now, take a look at a different scenario. The user Fred is created and assigned the default schema of
Production. Fred wants to retrieve the contents of a table called dbo.HourlyWage so he executes the
following:
SELECT * FROM HourlyWage
SQL Server first resolves this query as AUGHTFIVE.AdventureWorks.Production.HourlyWage
because Fred’s default schema is Production and he did not explicitly tell SQL Server what schema to
work with. Because the
HourlyWage table does not exist in the Production schema, the initial resolu-
tion fails, but SQL Server then falls back to the
dbo schema and resolves the name as AUGHTFIVE
.AdventureWorks.dbo.HourlyWage
. The resolution succeeds and Fred is returned the data he wanted.
SQL Server will always search the assigned schema first, then the
dbo schema if the initial resolution fails.
Care must be taken when creating objects so that the proper namespace is referenced. It is completely pos-
sible to create a table with the same name in two different schemas (for example, a
dbo.HourlyWage and
a
HumanResources.HourlyWage). When this happens and an application is created to expose the con-
tents of the
HourlyWage table, the possibilities for inconsistencies and confusion are endless. If the
schema is not referenced in the applications query, some users will invariably get their results from the
table in the
dbo schema, whereas others will end up getting results from the HumanResources version of
the table. As a best practice, all objects should be referenced by a two-part name to avoid this confusion.
SQL Server 2005 Databases
There are two types of databases in SQL Server: system databases and user databases. The system
databases are used to store system-wide data and metadata. User databases are created by users who have
the appropriate level of permissions to store application data.
System Databases
The system databases are comprised of Master, Model, MSDB, TempDB, and the hidden Resource
database. If the server is configured to be a replication distributor, there will also be at least one system
distribution database that is named during the replication configuration process.
The Master Database
The Master database is used to record all server-level objects in SQL Server 2005. This includes Server
Logon accounts, Linked Server definitions, and EndPoints. The
Master database also records informa-
tion about all the other databases on the server (such as their file locations and names). Unlike its prede-
cessors, SQL Server 2005 does not store system information in the
Master database, but rather in the
Resource database. However, system information is logically presented as the SYS schema in the
Master database.
The Model Database
The Model database is a template database. Whenever a new database is created (including the system
database
TempDB), a copy of the Model database is created and renamed with the name of the database
being created. The advantage of this behavior is that objects can be placed in the
Model database prior to
the creation of any new database and, when the database is created, the objects will appear in the new
14
Chapter 1
04_047046 ch01.qxp 10/18/06 12:18 AM Page 14