Datasheet

dbo schema is called dbo.Employee. This table would be different from HumanResource.Employee, if you
had that table in the database. It is a best practice always to refer to a database object by its two-part name,
like this from the
AdventureWorks database:
SELECT EmployeeID, Salary
FROM HumanResource.Employee
Schemas have been around since earlier releases of SQL Server but were not used in the same manner.
Previously, schemas were tied to your user name. If a DBA were to leave the company, you could not
remove that DBA’s account from SQL Server until you ensured that all the objects inside the DBA’s
schema were also moved. That typically created additional development, as you were now pointing all
your application to new stored procedure names. This is no longer a problem in SQL Server 2005.
Synonyms
A synonym creates an abstraction layer between the database object and the client. It essentially creates
a secondary logical name for a database object. This abstraction comes in handy when you use linked
servers; with linked servers, you have to refer to the four-part qualifier, like the following code:
SELECT Column1, Column2
FROM LinkedServerName.DatabaseName.SchemaName.TableName
This long a name creates a usability issue for developers, who at a minimum will receive a massive hand
cramp after typing that long an object name all day long. With synonyms, you can create what equates
to a redirector so that anytime someone types
SchemaName.SynonymName, they’re redirected to
LinkedServerName.DatabaseName.SchemaName.Tablename.
As an abstraction layer, synonyms are also useful also if you think you may want to redirect that query to
a new table or server some day. For example, you may have a table named
Sales2004, and your syn-
onym name could be
Sales. When 2005 arrives, you can point the synonym to the new Sales2005 table.
A synonym cannot reference another synonym.
Dynamic Management Views
Dynamic management views (DMVs) and functions return information about your SQL Server instance
and the operating system. Much of the information you would use very elaborate scripts for in SQL Server
2000 to view operational data is now available through simple queries in SQL Server 2005 using DMVs. In
most cases, you could not see the type of operational data available in DMVs at all in SQL Server 2000.
DMVs can provide you with various types of information, from data about the I/O subsystem and RAM
to information about Service Broker.
Whenever you start an instance, SQL Server begins saving server-state and diagnostic information into
DMVs. When you stop and start the instance, the information is flushed from the views and fresh data
begins to be loaded. You can query the views just like any other table in SQL Server with the two-part
qualifier. For example, the following query uses the
sys.dm_exec_sessions DMV to retrieve the num-
ber of sessions connected to the instance, grouped by login name.
8
Chapter 1
04_055200 ch01_2.qxp 8/8/07 9:21 AM Page 8