Schemas in AdventureWorks

Article
12/03/2008

In the AdventureWorks sample OLTP database, objects such as tables, views, and procedures are contained in schemas. Schemas change the way in which these objects can be accessed. This topic provides a brief overview of schemas, describes how schemas are used in the AdventureWorks database, and provides methods and alternatives for accessing objects that are contained in schemas.

Schemas in AdventureWorks

In Microsoft SQL Server 2005, schemas are separate from users: As database principals, users own schemas, and objects are contained in the schemas. For more information, see User-Schema Separation.

The following table describes the schemas that are used in AdventureWorks and lists representative tables in each schema.

Schema	Contains objects related to	Examples
HumanResources	Employees of Adventure Works Cycles.	Employee Table Department Table
Person	Names and addresses of individual customers, vendors, and employees.	Contact Table Address Table StateProvince Table
Production	Products manufactured and sold by Adventure Works Cycles.	BillOfMaterials Table Product Table WorkOrder Table
Purchasing	Vendors from who parts and products are purchased.	PurchaseOrderDetail Table PurchaseOrderHeader Table Vendor Table
Sales	Customers and sales-related data.	Customer Table SalesOrderDetail Table SalesOrderHeader Table

Accessing Objects Contained in Schemas

Users always have a default schema. The default schema is the schema that is searched by the server when it resolves the names of unqualified objects that are specified in a DML or DDL statement. Therefore, the schema name does not have to be specified when referencing objects that are contained in the default schema For example, the statement SELECT * FROM table_name successfully executes if table_name is in the default schema.

Note

If a default schema is not specified when the user is created, the default schema is dbo. For more information, see CREATE USER (Transact-SQL).

To access objects in a schema other than the default schema, at a minimum, a two-part identifier (schema_name**.**object_name) must be specified. This is true for all DDL and DML statements that reference schema-scoped objects.

The following example uses two SELECT statements to show referencing objects outside the default schema. Assuming the default schema is not HumanResources, the first statement fails because the Department table is not contained in the default schema. The second statement succeeds because it specifies the schema in which the object is contained.

USE AdventureWorks;
GO
--This statement fails.
SELECT Name, GroupName
FROM Department;
GO
--This statement succeeds.
SELECT Name, GroupName
FROM HumanResources.Department;
GO

Alternatives to Schemas in AdventureWorks

The code examples in SQL Server Books Online and the additional samples that can be installed with SQL Server 2005 specify at least the two-part identifier for all schema-scoped objects. These examples can be run successfully regardless of the user default schema.

Users who want to use AdventureWorks for their own samples and testing may want an alternative to specifying the two-part identifier. SQL Server 2005 provides scripts in the SQLServerEngineSamples.msi that provide two alternatives to using the schemas in AdventureWorks.

Transferring Objects to the DBO Schema

The script AlterSchemaToDbo.sql transfers every schema-scoped object in AdventureWorks to the dbo schema. After this script has been run, users with a default schema of dbo do not have to use the two-part identifier when referencing AdventureWorks objects in DDL and DML statements.

Important

The code examples and samples provided with SQL Server 2005 will not run after the AlterSchemaToDbo.sql is executed unless the schema names specified in the code are either replaced with dbo or removed.

The script AlterSchemaFromDbo.sql transfers, or returns, the objects from the dbo schema to the schemas that they were in before running the AlterSchemaToDbo.sql script.

Using Synonyms

A synonym is another name that is given to a schema-scoped object. The synonym is specified instead of the base object in DDL and DML statements.

The script CreateSynonymsDbo.sql creates a synonym for each schema-scoped object in AdventureWorks The synonym name is the same as the base object name, but uses the dbo schema. For example, the synonym for HumanResources.Department is dbo.Department. This has the following advantages:

If dbo is the default schema, a two-part identifier is not needed to specify these objects in DDL and DML statements.
The code examples and samples provided with SQL Server 2005 can be used without modification.

The script DropSynonymsDbo.sql drops the synonyms created by the CreateSynonymsDbo.sql script. For more information about synonyms, see Using Synonyms.

For more information about installing these scripts, see Readme_AdventureWorksScripts.

Share via

Schemas in AdventureWorks