AdventureWorks database documentation

AdventureWorks database summary

AdventureWorks Sample OLTP Database

Database server:
Database size: 178.75 MB

70 tables
- 11 triggers
17 views
9 stored procedures
11 user defined functions
6 user defined data types
7 XML schemas
0 assemblies

User defined data types

AccountNumber ( nvarchar (15) , null )
Flag ( bit, not null )
Name ( nvarchar (50) , null )
NameStyle ( bit, not null )
OrderNumber ( nvarchar (25) , null )
Phone ( nvarchar (25) , null )

Generators

Users

User name	Login name	Created	Roles	User description
dbo	sa	08 Apr 2003	db_owner
TestUser	panel	03 Jun 2008	db_owner	zxcv

Roles

Role name	Is app role	Users with this role	Role description
db_accessadmin
db_backupoperator
db_datareader
db_datawriter
db_ddladmin
db_denydatareader
db_denydatawriter
db_owner		dbo, TestUser
db_securityadmin
public

Table: dbo.AWBuildVersion

Current version number of the AdventureWorks sample database.

Columns (4)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	SystemInformationID	tinyint				Primary key for AWBuildVersion records.
		Database Version	nvarchar	(25)			Version number of the database in 9.yy.mm.dd.00 format.
		VersionDate	datetime				Date and time the record was last updated.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_AWBuildVersion_SystemInformationID	SystemInformationID	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[AWBuildVersion](
    [SystemInformationID] [tinyint] IDENTITY(1,1) NOT NULL,
    [Database Version] [nvarchar](25) COLLATE Latin1_General_CS_AS NOT NULL,
    [VersionDate] [datetime] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: dbo.DatabaseLog

Audit table tracking all DDL changes made to the AdventureWorks database. Data is captured by the database trigger ddlDatabaseTriggerLog.

Columns (8)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	DatabaseLogID	int				Primary key for DatabaseLog records.
		PostTime	datetime				The date and time the DDL change occurred.
		DatabaseUser	nvarchar	(128)			The user who implemented the DDL change.
		Event	nvarchar	(128)			The type of DDL statement that was executed.
		Schema	nvarchar	(128)	Yes		The schema to which the changed object belongs.
		Object	nvarchar	(128)	Yes		The object that was changed by the DDL statment.
		TSQL	nvarchar				The exact Transact-SQL statement that was executed.
		XmlEvent	xml				The raw XML data generated by database trigger.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_DatabaseLog_DatabaseLogID	DatabaseLogID	ASC	Yes	NONCLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[DatabaseLog](
    [DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
    [PostTime] [datetime] NOT NULL,
    [DatabaseUser] [sysname] COLLATE Latin1_General_CS_AS NOT NULL,
    [Event] [sysname] COLLATE Latin1_General_CS_AS NOT NULL,
    [Schema] [sysname] COLLATE Latin1_General_CS_AS NULL,
    [Object] [sysname] COLLATE Latin1_General_CS_AS NULL,
    [TSQL] [nvarchar](max) COLLATE Latin1_General_CS_AS NOT NULL,
    [XmlEvent] [xml] NOT NULL
) ON [PRIMARY]

Table: dbo.ErrorLog

Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.

Columns (9)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	ErrorLogID	int				Primary key for ErrorLog records.
		ErrorTime	datetime			(getdate())	The date and time at which the error occurred.
		UserName	nvarchar	(128)			The user who executed the batch in which the error occurred.
		ErrorNumber	int				The error number of the error that occurred.
		ErrorSeverity	int		Yes		The severity of the error that occurred.
		ErrorState	int		Yes		The state number of the error that occurred.
		ErrorProcedure	nvarchar	(126)	Yes		The name of the stored procedure or trigger where the error occurred.
		ErrorLine	int		Yes		The line number at which the error occurred.
		ErrorMessage	nvarchar	(4000)			The message text of the error that occurred.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_ErrorLog_ErrorLogID	ErrorLogID	ASC	Yes	CLUSTERED

Objects that depend on dbo.ErrorLog
dbo.uspLogError

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[ErrorLog](
    [ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
    [ErrorTime] [datetime] NOT NULL,
    [UserName] [sysname] COLLATE Latin1_General_CS_AS NOT NULL,
    [ErrorNumber] [int] NOT NULL,
    [ErrorSeverity] [int] NULL,
    [ErrorState] [int] NULL,
    [ErrorProcedure] [nvarchar](126) COLLATE Latin1_General_CS_AS NULL,
    [ErrorLine] [int] NULL,
    [ErrorMessage] [nvarchar](4000) COLLATE Latin1_General_CS_AS NOT NULL
) ON [PRIMARY]

Table: HumanResources.Department

Lookup table containing the departments within the Adventure Works Cycles company.

Columns (4)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	DepartmentID	smallint				Primary key for Department records.
	IX	Name	Name				Name of the department.
		GroupName	Name				Name of the group to which the department belongs.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_Department_Name	Name	ASC	Yes	NONCLUSTERED
PK_Department_DepartmentID	DepartmentID	ASC	Yes	CLUSTERED

Objects that depend on HumanResources.Department
HumanResources.vEmployeeDepartment
HumanResources.vEmployeeDepartmentHistory

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[Department](
    [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [GroupName] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: HumanResources.Employee

Employee information such as salary, department, and title.

Columns (16)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	EmployeeID	int				Primary key for Employee records.
		IX	NationalIDNumber	nvarchar	(15)			Unique national identification number such as a social security number.
	FK		ContactID	int				Identifies the employee in the Contact table. Foreign key to Contact.ContactID.
		IX	LoginID	nvarchar	(256)			Network login.
	FK	IX	ManagerID	int		Yes		Manager to whom the employee is assigned. Foreign Key to Employee.M
			Title	nvarchar	(50)			Work title such as Buyer or Sales Representative.
			BirthDate	datetime				Date of birth.
			MaritalStatus	nchar	(1)			M = Married, S = Single
			Gender	nchar	(1)			M = Male, F = Female
			HireDate	datetime				Employee hired on this date.
			SalariedFlag	Flag			((1))	Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
			VacationHours	smallint			((0))	Number of available vacation hours.
			SickLeaveHours	smallint			((0))	Number of available sick leave hours.
			CurrentFlag	Flag			((1))	0 = Inactive, 1 = Active
		IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_Employee_LoginID	LoginID	ASC	Yes	NONCLUSTERED
AK_Employee_NationalIDNumber	NationalIDNumber	ASC	Yes	NONCLUSTERED
AK_Employee_rowguid	rowguid	ASC	Yes	NONCLUSTERED
IX_Employee_ManagerID	ManagerID	ASC		NONCLUSTERED
PK_Employee_EmployeeID	EmployeeID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_Employee_Contact_ContactID: ContactID relies upon remote PK_Contact_ContactID (Person.Contact)
FK_Employee_Employee_ManagerID: ManagerID relies upon remote PK_Employee_EmployeeID (HumanResources.Employee)

Check constraints:

Check name	Column name	Expresion
CK_Employee_BirthDate	BirthDate	([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate()))
CK_Employee_MaritalStatus	MaritalStatus	(upper([MaritalStatus])='S' OR upper([MaritalStatus])='M')
CK_Employee_HireDate	HireDate	([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate()))
CK_Employee_Gender	Gender	(upper([Gender])='F' OR upper([Gender])='M')
CK_Employee_VacationHours	VacationHours	([VacationHours]>=(-40) AND [VacationHours]<=(240))
CK_Employee_SickLeaveHours	SickLeaveHours	([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120))

Triggers:

Trigger name: HumanResources.dEmployee (Created: 26 Apr 2006)
Trigger type: INSTEAD OF DELETE
Trigger active: YesTrigger description: INSTEAD OF DELETE trigger which keeps Employees from being deleted.

Trigger definition:


CREATE TRIGGER [HumanResources].[dEmployee] ON [HumanResources].[Employee] 
INSTEAD OF DELETE NOT FOR REPLICATION AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN
        RAISERROR
            (N'Employees cannot be deleted. They can only be marked as not current.', -- Message
            10, -- Severity.
            1); -- State.

        -- Rollback any active or uncommittable transactions
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
    END;
END;

Objects that depend on HumanResources.Employee
dbo.uspGetEmployeeManagers
dbo.uspGetManagerEmployees
HumanResources.uspUpdateEmployeeHireInfo
HumanResources.uspUpdateEmployeeLogin
HumanResources.uspUpdateEmployeePersonalInfo
HumanResources.vEmployee
HumanResources.vEmployeeDepartment
HumanResources.vEmployeeDepartmentHistory
Sales.vSalesPerson
Sales.vSalesPersonSalesByFiscalYears

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[Employee](
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    [NationalIDNumber] [nvarchar](15) COLLATE Latin1_General_CS_AS NOT NULL,
    [ContactID] [int] NOT NULL,
    [LoginID] [nvarchar](256) COLLATE Latin1_General_CS_AS NOT NULL,
    [ManagerID] [int] NULL,
    [Title] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
    [BirthDate] [datetime] NOT NULL,
    [MaritalStatus] [nchar](1) COLLATE Latin1_General_CS_AS NOT NULL,
    [Gender] [nchar](1) COLLATE Latin1_General_CS_AS NOT NULL,
    [HireDate] [datetime] NOT NULL,
    [SalariedFlag] [dbo].[Flag] NOT NULL,
    [VacationHours] [smallint] NOT NULL,
    [SickLeaveHours] [smallint] NOT NULL,
    [CurrentFlag] [dbo].[Flag] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: HumanResources.EmployeeAddress

Cross-reference table mapping employees to their address(es).

Columns (4)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	EmployeeID	int				Primary key. Foreign key to Employee.EmployeeID.
PK	FK	IX	AddressID	int				Primary key. Foreign key to Address.AddressID.
		IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_EmployeeAddress_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_EmployeeAddress_EmployeeID_AddressID	EmployeeID	ASC	Yes	CLUSTERED
PK_EmployeeAddress_EmployeeID_AddressID	AddressID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_EmployeeAddress_Employee_EmployeeID: EmployeeID relies upon remote PK_Employee_EmployeeID (HumanResources.Employee)
FK_EmployeeAddress_Address_AddressID: AddressID relies upon remote PK_Address_AddressID (Person.Address)

Objects that depend on HumanResources.EmployeeAddress
HumanResources.vEmployee
Sales.vSalesPerson

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[EmployeeAddress](
    [EmployeeID] [int] NOT NULL,
    [AddressID] [int] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: HumanResources.EmployeeDepartmentHistory

Employee department transfers.

Columns (6)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	EmployeeID	int				Employee identification number. Foreign key to Employee.EmployeeID.
PK	FK	IX	DepartmentID	smallint				Department in which the employee worked including currently. Foreign key to Department.DepartmentID.
PK	FK	IX	ShiftID	tinyint				Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.
PK		IX	StartDate	datetime				Date the employee started work in the department.
			EndDate	datetime		Yes		Date the employee left the department. NULL = Current department.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
IX_EmployeeDepartmentHistory_DepartmentID	DepartmentID	ASC		NONCLUSTERED
IX_EmployeeDepartmentHistory_ShiftID	ShiftID	ASC		NONCLUSTERED
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	EmployeeID	ASC	Yes	CLUSTERED
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	DepartmentID	ASC	Yes	CLUSTERED
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	ShiftID	ASC	Yes	CLUSTERED
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	StartDate	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_EmployeeDepartmentHistory_Employee_EmployeeID: EmployeeID relies upon remote PK_Employee_EmployeeID (HumanResources.Employee)
FK_EmployeeDepartmentHistory_Department_DepartmentID: DepartmentID relies upon remote PK_Department_DepartmentID (HumanResources.Department)
FK_EmployeeDepartmentHistory_Shift_ShiftID: ShiftID relies upon remote PK_Shift_ShiftID (HumanResources.Shift)

Check constraints:

Check name	Column name	Expresion
CK_EmployeeDepartmentHistory_EndDate	StartDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)
CK_EmployeeDepartmentHistory_EndDate	EndDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)

Objects that depend on HumanResources.EmployeeDepartmentHistory
HumanResources.vEmployeeDepartment
HumanResources.vEmployeeDepartmentHistory

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[EmployeeDepartmentHistory](
    [EmployeeID] [int] NOT NULL,
    [DepartmentID] [smallint] NOT NULL,
    [ShiftID] [tinyint] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: HumanResources.EmployeePayHistory

Employee pay history.

Columns (5)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	EmployeeID	int				Employee identification number. Foreign key to Employee.EmployeeID.
PK		IX	RateChangeDate	datetime				Date the change in pay is effective
			Rate	money				Salary hourly rate.
			PayFrequency	tinyint				1 = Salary received monthly, 2 = Salary received biweekly
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_EmployeePayHistory_EmployeeID_RateChangeDate	EmployeeID	ASC	Yes	CLUSTERED
PK_EmployeePayHistory_EmployeeID_RateChangeDate	RateChangeDate	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_EmployeePayHistory_Employee_EmployeeID: EmployeeID relies upon remote PK_Employee_EmployeeID (HumanResources.Employee)

Check constraints:

Check name	Column name	Expresion
CK_EmployeePayHistory_PayFrequency	PayFrequency	([PayFrequency]=(2) OR [PayFrequency]=(1))
CK_EmployeePayHistory_Rate	Rate	([Rate]>=(6.50) AND [Rate]<=(200.00))

Objects that depend on HumanResources.EmployeePayHistory
HumanResources.uspUpdateEmployeeHireInfo

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[EmployeePayHistory](
    [EmployeeID] [int] NOT NULL,
    [RateChangeDate] [datetime] NOT NULL,
    [Rate] [money] NOT NULL,
    [PayFrequency] [tinyint] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: HumanResources.JobCandidate

Résumés submitted to Human Resources by job applicants.

Columns (4)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	JobCandidateID	int				Primary key for JobCandidate records.
	FK	IX	EmployeeID	int		Yes		Employee identification number if applicant was hired. Foreign key to Employee.EmployeeID.
			Resume	xml		Yes		Résumé in XML format.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
IX_JobCandidate_EmployeeID	EmployeeID	ASC		NONCLUSTERED
PK_JobCandidate_JobCandidateID	JobCandidateID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_JobCandidate_Employee_EmployeeID: EmployeeID relies upon remote PK_Employee_EmployeeID (HumanResources.Employee)

Objects that depend on HumanResources.JobCandidate
HumanResources.vJobCandidate
HumanResources.vJobCandidateEducation
HumanResources.vJobCandidateEmployment

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[JobCandidate](
    [JobCandidateID] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeID] [int] NULL,
    [Resume] [xml](CONTENT [HumanResources].[HRResumeSchemaCollection]) NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: HumanResources.Shift

Work shift lookup table.

Columns (5)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	ShiftID	tinyint				Primary key for Shift records.
	IX	Name	Name				Shift description.
	IX	StartTime	datetime				Shift start time.
	IX	EndTime	datetime				Shift end time.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_Shift_Name	Name	ASC	Yes	NONCLUSTERED
AK_Shift_StartTime_EndTime	StartTime	ASC	Yes	NONCLUSTERED
AK_Shift_StartTime_EndTime	EndTime	ASC	Yes	NONCLUSTERED
PK_Shift_ShiftID	ShiftID	ASC	Yes	CLUSTERED

Objects that depend on HumanResources.Shift
HumanResources.vEmployeeDepartmentHistory

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[Shift](
    [ShiftID] [tinyint] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [StartTime] [datetime] NOT NULL,
    [EndTime] [datetime] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Person.Address

Street address information for customers, employees, and vendors.

Columns (8)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	AddressID	int				Primary key for Address records.
		IX	AddressLine1	nvarchar	(60)			First street address line.
		IX	AddressLine2	nvarchar	(60)	Yes		Second street address line.
		IX	City	nvarchar	(30)			Name of the city.
	FK	IX	StateProvinceID	int				Unique identification number for the state or province. Foreign key to StateProvince table.
		IX	PostalCode	nvarchar	(15)			Postal code for the street address.
		IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_Address_rowguid	rowguid	ASC	Yes	NONCLUSTERED
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	AddressLine1	ASC	Yes	NONCLUSTERED
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	AddressLine2	ASC	Yes	NONCLUSTERED
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	City	ASC	Yes	NONCLUSTERED
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	StateProvinceID	ASC	Yes	NONCLUSTERED
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	PostalCode	ASC	Yes	NONCLUSTERED
IX_Address_StateProvinceID	StateProvinceID	ASC		NONCLUSTERED
PK_Address_AddressID	AddressID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_Address_StateProvince_StateProvinceID: StateProvinceID relies upon remote PK_StateProvince_StateProvinceID (Person.StateProvince)

Objects that depend on Person.Address
HumanResources.vEmployee
Purchasing.vVendor
Sales.vIndividualCustomer
Sales.vSalesPerson
Sales.vStoreWithDemographics

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[Address](
    [AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [AddressLine1] [nvarchar](60) COLLATE Latin1_General_CS_AS NOT NULL,
    [AddressLine2] [nvarchar](60) COLLATE Latin1_General_CS_AS NULL,
    [City] [nvarchar](30) COLLATE Latin1_General_CS_AS NOT NULL,
    [StateProvinceID] [int] NOT NULL,
    [PostalCode] [nvarchar](15) COLLATE Latin1_General_CS_AS NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Person.AddressType

Types of addresses stored in the Address table.

Columns (4)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	AddressTypeID	int				Primary key for AddressType records.
	IX	Name	Name				Address type description. For example, Billing, Home, or Shipping.
	IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_AddressType_Name	Name	ASC	Yes	NONCLUSTERED
AK_AddressType_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_AddressType_AddressTypeID	AddressTypeID	ASC	Yes	CLUSTERED

Objects that depend on Person.AddressType
Sales.vIndividualCustomer
Sales.vStoreWithDemographics

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[AddressType](
    [AddressTypeID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Person.Contact

Names of each employee, customer contact, and vendor contact.

Columns (15)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	ContactID	int				Primary key for Contact records.
		NameStyle	NameStyle			((0))	0 = The data in FirstName and LastName are stored in western style (first name, last name) order. 1 = Eastern style (last name, first name) order.
		Title	nvarchar	(8)	Yes		A courtesy title. For example, Mr. or Ms.
		FirstName	Name				First name of the person.
		MiddleName	Name		Yes		Middle name or middle initial of the person.
		LastName	Name				Last name of the person.
		Suffix	nvarchar	(10)	Yes		Surname suffix. For example, Sr. or Jr.
	IX	EmailAddress	nvarchar	(50)	Yes		E-mail address for the person.
		EmailPromotion	int			((0))	0 = Contact does not wish to receive e-mail promotions, 1 = Contact does wish to receive e-mail promotions from AdventureWorks, 2 = Contact does wish to receive e-mail promotions from AdventureWorks and selected partners.
		Phone	Phone		Yes		Phone number associated with the person.
		PasswordHash	varchar	(128)			Password for the e-mail account.
		PasswordSalt	varchar	(10)			Random value concatenated with the password string before the password is hashed.
	IX	AdditionalContactInfo	xml		Yes		Additional contact information about the person stored in xml format.
	IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_Contact_rowguid	rowguid	ASC	Yes	NONCLUSTERED
IX_Contact_EmailAddress	EmailAddress	ASC		NONCLUSTERED
PK_Contact_ContactID	ContactID	ASC	Yes	CLUSTERED
PXML_Contact_AddContact	AdditionalContactInfo	ASC		XML

Check constraints:

Check name	Column name	Expresion
CK_Contact_EmailPromotion	EmailPromotion	([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))

Objects that depend on Person.Contact
dbo.uspGetEmployeeManagers
dbo.uspGetManagerEmployees
HumanResources.vEmployee
HumanResources.vEmployeeDepartment
HumanResources.vEmployeeDepartmentHistory
Person.vAdditionalContactInfo
Purchasing.vVendor
Sales.vIndividualCustomer
Sales.vSalesPerson
Sales.vSalesPersonSalesByFiscalYears
Sales.vStoreWithDemographics

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[Contact](
    [ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [NameStyle] [dbo].[NameStyle] NOT NULL,
    [Title] [nvarchar](8) COLLATE Latin1_General_CS_AS NULL,
    [FirstName] [dbo].[Name] NOT NULL,
    [MiddleName] [dbo].[Name] NULL,
    [LastName] [dbo].[Name] NOT NULL,
    [Suffix] [nvarchar](10) COLLATE Latin1_General_CS_AS NULL,
    [EmailAddress] [nvarchar](50) COLLATE Latin1_General_CS_AS NULL,
    [EmailPromotion] [int] NOT NULL,
    [Phone] [dbo].[Phone] NULL,
    [PasswordHash] [varchar](128) COLLATE Latin1_General_CS_AS NOT NULL,
    [PasswordSalt] [varchar](10) COLLATE Latin1_General_CS_AS NOT NULL,
    [AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Person.ContactType

Lookup table containing the types of contacts stored in Contact.

Columns (3)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	ContactTypeID	int				Primary key for ContactType records.
	IX	Name	Name				Contact type description.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_ContactType_Name	Name	ASC	Yes	NONCLUSTERED
PK_ContactType_ContactTypeID	ContactTypeID	ASC	Yes	CLUSTERED

Objects that depend on Person.ContactType
Purchasing.vVendor
Sales.vStoreWithDemographics

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[ContactType](
    [ContactTypeID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Person.CountryRegion

Lookup table containing the ISO standard codes for countries and regions.

Columns (3)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	CountryRegionCode	nvarchar	(3)			ISO standard code for countries and regions.
	IX	Name	Name				Country or region name.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_CountryRegion_Name	Name	ASC	Yes	NONCLUSTERED
PK_CountryRegion_CountryRegionCode	CountryRegionCode	ASC	Yes	CLUSTERED

Objects that depend on Person.CountryRegion
HumanResources.vEmployee
Person.vStateProvinceCountryRegion
Purchasing.vVendor
Sales.vIndividualCustomer
Sales.vSalesPerson
Sales.vStoreWithDemographics

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[CountryRegion](
    [CountryRegionCode] [nvarchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Person.StateProvince

State and province lookup table.

Columns (8)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	StateProvinceID	int				Primary key for StateProvince records.
		IX	StateProvinceCode	nchar	(3)			ISO standard state or province code.
	FK	IX	CountryRegionCode	nvarchar	(3)			ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.
			IsOnlyStateProvinceFlag	Flag			((1))	0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode.
		IX	Name	Name				State or province description.
	FK		TerritoryID	int				ID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID.
		IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_StateProvince_Name	Name	ASC	Yes	NONCLUSTERED
AK_StateProvince_rowguid	rowguid	ASC	Yes	NONCLUSTERED
AK_StateProvince_StateProvinceCode_CountryRegionCode	StateProvinceCode	ASC	Yes	NONCLUSTERED
AK_StateProvince_StateProvinceCode_CountryRegionCode	CountryRegionCode	ASC	Yes	NONCLUSTERED
PK_StateProvince_StateProvinceID	StateProvinceID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_StateProvince_CountryRegion_CountryRegionCode: CountryRegionCode relies upon remote PK_CountryRegion_CountryRegionCode (Person.CountryRegion)
FK_StateProvince_SalesTerritory_TerritoryID: TerritoryID relies upon remote PK_SalesTerritory_TerritoryID (Sales.SalesTerritory)

Objects that depend on Person.StateProvince
HumanResources.vEmployee
Person.vStateProvinceCountryRegion
Purchasing.vVendor
Sales.vIndividualCustomer
Sales.vSalesPerson
Sales.vStoreWithDemographics

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[StateProvince](
    [StateProvinceID] [int] IDENTITY(1,1) NOT NULL,
    [StateProvinceCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [CountryRegionCode] [nvarchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [IsOnlyStateProvinceFlag] [dbo].[Flag] NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [TerritoryID] [int] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.BillOfMaterials

Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.

Columns (9)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	BillOfMaterialsID	int				Primary key for BillOfMaterials records.
	FK	IX	ProductAssemblyID	int		Yes		Parent product identification number. Foreign key to Product.ProductID.
	FK	IX	ComponentID	int				Component identification number. Foreign key to Product.ProductID.
		IX	StartDate	datetime			(getdate())	Date the component started being used in the assembly item.
			EndDate	datetime		Yes		Date the component stopped being used in the assembly item.
	FK	IX	UnitMeasureCode	nchar	(3)			Standard code identifying the unit of measure for the quantity.
			BOMLevel	smallint				Indicates the depth the component is from its parent (AssemblyID).
			PerAssemblyQty	decimal	(8.2)		((1.00))	Quantity of the component needed to create the assembly.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate	ProductAssemblyID	ASC	Yes	CLUSTERED
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate	ComponentID	ASC	Yes	CLUSTERED
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate	StartDate	ASC	Yes	CLUSTERED
IX_BillOfMaterials_UnitMeasureCode	UnitMeasureCode	ASC		NONCLUSTERED
PK_BillOfMaterials_BillOfMaterialsID	BillOfMaterialsID	ASC	Yes	NONCLUSTERED

Foreign keys dependencies:

FK_BillOfMaterials_Product_ProductAssemblyID: ProductAssemblyID relies upon remote PK_Product_ProductID (Production.Product)
FK_BillOfMaterials_Product_ComponentID: ComponentID relies upon remote PK_Product_ProductID (Production.Product)
FK_BillOfMaterials_UnitMeasure_UnitMeasureCode: UnitMeasureCode relies upon remote PK_UnitMeasure_UnitMeasureCode (Production.UnitMeasure)

Check constraints:

Check name	Column name	Expresion
CK_BillOfMaterials_EndDate	StartDate	([EndDate]>[StartDate] OR [EndDate] IS NULL)
CK_BillOfMaterials_EndDate	EndDate	([EndDate]>[StartDate] OR [EndDate] IS NULL)
CK_BillOfMaterials_ProductAssemblyID	ProductAssemblyID	([ProductAssemblyID]<>[ComponentID])
CK_BillOfMaterials_ProductAssemblyID	ComponentID	([ProductAssemblyID]<>[ComponentID])
CK_BillOfMaterials_BOMLevel	ProductAssemblyID	([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1))
CK_BillOfMaterials_BOMLevel	BOMLevel	([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1))
CK_BillOfMaterials_BOMLevel	PerAssemblyQty	([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1))
CK_BillOfMaterials_PerAssemblyQty	PerAssemblyQty	([PerAssemblyQty]>=(1.00))

Objects that depend on Production.BillOfMaterials
dbo.uspGetBillOfMaterials
dbo.uspGetWhereUsedProductID

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[BillOfMaterials](
    [BillOfMaterialsID] [int] IDENTITY(1,1) NOT NULL,
    [ProductAssemblyID] [int] NULL,
    [ComponentID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [UnitMeasureCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [BOMLevel] [smallint] NOT NULL,
    [PerAssemblyQty] [decimal](8, 2) NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.Culture

Lookup table containing the languages in which some AdventureWorks data is stored.

Columns (3)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	CultureID	nchar	(6)			Primary key for Culture records.
	IX	Name	Name				Culture description.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_Culture_Name	Name	ASC	Yes	NONCLUSTERED
PK_Culture_CultureID	CultureID	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[Culture](
    [CultureID] [nchar](6) COLLATE Latin1_General_CS_AS NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.Document

Product maintenance documents.

Columns (10)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	DocumentID	int				Primary key for Document records.
		Title	nvarchar	(50)			Title of the document.
	IX	FileName	nvarchar	(400)			Directory path and file name of the document
		FileExtension	nvarchar	(8)			File extension indicating the document type. For example, .doc or .txt.
	IX	Revision	nchar	(5)			Revision number of the document.
		ChangeNumber	int			((0))	Engineering change approval number.
		Status	tinyint				1 = Pending approval, 2 = Approved, 3 = Obsolete
		DocumentSummary	nvarchar		Yes		Document abstract.
		Document	varbinary		Yes		Complete document.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_Document_FileName_Revision	FileName	ASC	Yes	NONCLUSTERED
AK_Document_FileName_Revision	Revision	ASC	Yes	NONCLUSTERED
PK_Document_DocumentID	DocumentID	ASC	Yes	CLUSTERED

Check constraints:

Check name	Column name	Expresion
CK_Document_Status	Status	([Status]>=(1) AND [Status]<=(3))

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[Document](
    [DocumentID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
    [FileName] [nvarchar](400) COLLATE Latin1_General_CS_AS NOT NULL,
    [FileExtension] [nvarchar](8) COLLATE Latin1_General_CS_AS NOT NULL,
    [Revision] [nchar](5) COLLATE Latin1_General_CS_AS NOT NULL,
    [ChangeNumber] [int] NOT NULL,
    [Status] [tinyint] NOT NULL,
    [DocumentSummary] [nvarchar](max) COLLATE Latin1_General_CS_AS NULL,
    [Document] [varbinary](max) NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.Illustration

Bicycle assembly diagrams.

Columns (3)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	IllustrationID	int				Primary key for Illustration records.
		Diagram	xml		Yes		Illustrations used in manufacturing instructions. Stored as XML.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_Illustration_IllustrationID	IllustrationID	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[Illustration](
    [IllustrationID] [int] IDENTITY(1,1) NOT NULL,
    [Diagram] [xml] NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.Location

Product inventory and manufacturing locations.

Columns (5)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	LocationID	smallint				Primary key for Location records.
	IX	Name	Name				Location description.
		CostRate	smallmoney			((0.00))	Standard hourly cost of the manufacturing location.
		Availability	decimal	(8.2)		((0.00))	Work capacity (in hours) of the manufacturing location.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_Location_Name	Name	ASC	Yes	NONCLUSTERED
PK_Location_LocationID	LocationID	ASC	Yes	CLUSTERED

Check constraints:

Check name	Column name	Expresion
CK_Location_CostRate	CostRate	([CostRate]>=(0.00))
CK_Location_Availability	Availability	([Availability]>=(0.00))

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[Location](
    [LocationID] [smallint] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [CostRate] [smallmoney] NOT NULL,
    [Availability] [decimal](8, 2) NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.Product

Products sold or used in the manfacturing of sold products.

Columns (25)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	ProductID	int				Primary key for Product records.
		IX	Name	Name				Name of the product.
		IX	ProductNumber	nvarchar	(25)			Unique product identification number.
			MakeFlag	Flag			((1))	0 = Product is purchased, 1 = Product is manufactured in-house.
			FinishedGoodsFlag	Flag			((1))	0 = Product is not a salable item. 1 = Product is salable.
			Color	nvarchar	(15)	Yes		Product color.
			SafetyStockLevel	smallint				Minimum inventory quantity.
			ReorderPoint	smallint				Inventory level that triggers a purchase order or work order.
			StandardCost	money				Standard cost of the product.
			ListPrice	money				Selling price.
			Size	nvarchar	(5)	Yes		Product size.
	FK		SizeUnitMeasureCode	nchar	(3)	Yes		Unit of measure for Size column.
	FK		WeightUnitMeasureCode	nchar	(3)	Yes		Unit of measure for Weight column.
			Weight	decimal	(8.2)	Yes		Product weight.
			DaysToManufacture	int				Number of days required to manufacture the product.
			ProductLine	nchar	(2)	Yes		R = Road, M = Mountain, T = Touring, S = Standard
			Class	nchar	(2)	Yes		H = High, M = Medium, L = Low
			Style	nchar	(2)	Yes		W = Womens, M = Mens, U = Universal
	FK		ProductSubcategoryID	int		Yes		Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID.
	FK		ProductModelID	int		Yes		Product is a member of this product model. Foreign key to ProductModel.ProductModelID.
			SellStartDate	datetime				Date the product was available for sale.
			SellEndDate	datetime		Yes		Date the product was no longer available for sale.
			DiscontinuedDate	datetime		Yes		Date the product was discontinued.
		IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_Product_Name	Name	ASC	Yes	NONCLUSTERED
AK_Product_ProductNumber	ProductNumber	ASC	Yes	NONCLUSTERED
AK_Product_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_Product_ProductID	ProductID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_Product_UnitMeasure_SizeUnitMeasureCode: SizeUnitMeasureCode relies upon remote PK_UnitMeasure_UnitMeasureCode (Production.UnitMeasure)
FK_Product_UnitMeasure_WeightUnitMeasureCode: WeightUnitMeasureCode relies upon remote PK_UnitMeasure_UnitMeasureCode (Production.UnitMeasure)
FK_Product_ProductSubcategory_ProductSubcategoryID: ProductSubcategoryID relies upon remote PK_ProductSubcategory_ProductSubcategoryID (Production.ProductSubcategory)
FK_Product_ProductModel_ProductModelID: ProductModelID relies upon remote PK_ProductModel_ProductModelID (Production.ProductModel)

Check constraints:

Check name	Column name	Expresion
CK_Product_SafetyStockLevel	SafetyStockLevel	([SafetyStockLevel]>(0))
CK_Product_ReorderPoint	ReorderPoint	([ReorderPoint]>(0))
CK_Product_StandardCost	StandardCost	([StandardCost]>=(0.00))
CK_Product_ListPrice	ListPrice	([ListPrice]>=(0.00))
CK_Product_Weight	Weight	([Weight]>(0.00))
CK_Product_DaysToManufacture	DaysToManufacture	([DaysToManufacture]>=(0))
CK_Product_ProductLine	ProductLine	(upper([ProductLine])='R' OR upper([ProductLine])='M' OR upper([ProductLine])='T' OR upper([ProductLine])='S' OR [ProductLine] IS NULL)
CK_Product_Class	Class	(upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL)
CK_Product_Style	Style	(upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL)
CK_Product_SellEndDate	SellStartDate	([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL)
CK_Product_SellEndDate	SellEndDate	([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL)

Objects that depend on Production.Product
dbo.ufnGetProductDealerPrice
dbo.ufnGetProductListPrice
dbo.ufnGetProductStandardCost
dbo.uspGetBillOfMaterials
dbo.uspGetWhereUsedProductID
Production.vProductAndDescription

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[Product](
    [ProductID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ProductNumber] [nvarchar](25) COLLATE Latin1_General_CS_AS NOT NULL,
    [MakeFlag] [dbo].[Flag] NOT NULL,
    [FinishedGoodsFlag] [dbo].[Flag] NOT NULL,
    [Color] [nvarchar](15) COLLATE Latin1_General_CS_AS NULL,
    [SafetyStockLevel] [smallint] NOT NULL,
    [ReorderPoint] [smallint] NOT NULL,
    [StandardCost] [money] NOT NULL,
    [ListPrice] [money] NOT NULL,
    [Size] [nvarchar](5) COLLATE Latin1_General_CS_AS NULL,
    [SizeUnitMeasureCode] [nchar](3) COLLATE Latin1_General_CS_AS NULL,
    [WeightUnitMeasureCode] [nchar](3) COLLATE Latin1_General_CS_AS NULL,
    [Weight] [decimal](8, 2) NULL,
    [DaysToManufacture] [int] NOT NULL,
    [ProductLine] [nchar](2) COLLATE Latin1_General_CS_AS NULL,
    [Class] [nchar](2) COLLATE Latin1_General_CS_AS NULL,
    [Style] [nchar](2) COLLATE Latin1_General_CS_AS NULL,
    [ProductSubcategoryID] [int] NULL,
    [ProductModelID] [int] NULL,
    [SellStartDate] [datetime] NOT NULL,
    [SellEndDate] [datetime] NULL,
    [DiscontinuedDate] [datetime] NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductCategory

High-level product categorization.

Columns (4)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	ProductCategoryID	int				Primary key for ProductCategory records.
	IX	Name	Name				Category description.
	IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_ProductCategory_Name	Name	ASC	Yes	NONCLUSTERED
AK_ProductCategory_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_ProductCategory_ProductCategoryID	ProductCategoryID	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductCategory](
    [ProductCategoryID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductCostHistory

Changes in the cost of a product over time.

Columns (5)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	ProductID	int				Product identification number. Foreign key to Product.ProductID
PK		IX	StartDate	datetime				Product cost start date.
			EndDate	datetime		Yes		Product cost end date.
			StandardCost	money				Standard cost of the product.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_ProductCostHistory_ProductID_StartDate	ProductID	ASC	Yes	CLUSTERED
PK_ProductCostHistory_ProductID_StartDate	StartDate	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_ProductCostHistory_Product_ProductID: ProductID relies upon remote PK_Product_ProductID (Production.Product)

Check constraints:

Check name	Column name	Expresion
CK_ProductCostHistory_EndDate	StartDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)
CK_ProductCostHistory_EndDate	EndDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)
CK_ProductCostHistory_StandardCost	StandardCost	([StandardCost]>=(0.00))

Objects that depend on Production.ProductCostHistory
dbo.ufnGetProductStandardCost

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductCostHistory](
    [ProductID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [StandardCost] [money] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductDescription

Product descriptions in several languages.

Columns (4)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	ProductDescriptionID	int				Primary key for ProductDescription records.
		Description	nvarchar	(400)			Description of the product.
	IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_ProductDescription_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_ProductDescription_ProductDescriptionID	ProductDescriptionID	ASC	Yes	CLUSTERED

Objects that depend on Production.ProductDescription
Production.vProductAndDescription

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductDescription](
    [ProductDescriptionID] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](400) COLLATE Latin1_General_CS_AS NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductDocument

Cross-reference table mapping products to related product documents.

Columns (3)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	ProductID	int				Product identification number. Foreign key to Product.ProductID.
PK	FK	IX	DocumentID	int				Document identification number. Foreign key to Document.DocumentID.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_ProductDocument_ProductID_DocumentID	ProductID	ASC	Yes	CLUSTERED
PK_ProductDocument_ProductID_DocumentID	DocumentID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_ProductDocument_Product_ProductID: ProductID relies upon remote PK_Product_ProductID (Production.Product)
FK_ProductDocument_Document_DocumentID: DocumentID relies upon remote PK_Document_DocumentID (Production.Document)

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductDocument](
    [ProductID] [int] NOT NULL,
    [DocumentID] [int] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductInventory

Product inventory information.

Columns (7)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	ProductID	int				Product identification number. Foreign key to Product.ProductID.
PK	FK	IX	LocationID	smallint				Inventory location identification number. Foreign key to Location.LocationID.
			Shelf	nvarchar	(10)			Storage compartment within an inventory location.
			Bin	tinyint				Storage container on a shelf in an inventory location.
			Quantity	smallint			((0))	Quantity of products in the inventory location.
			rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_ProductInventory_ProductID_LocationID	ProductID	ASC	Yes	CLUSTERED
PK_ProductInventory_ProductID_LocationID	LocationID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_ProductInventory_Product_ProductID: ProductID relies upon remote PK_Product_ProductID (Production.Product)
FK_ProductInventory_Location_LocationID: LocationID relies upon remote PK_Location_LocationID (Production.Location)

Check constraints:

Check name	Column name	Expresion
CK_ProductInventory_Shelf	Shelf	([Shelf] like '[A-Za-z]' OR [Shelf]='N/A')
CK_ProductInventory_Bin	Bin	([Bin]>=(0) AND [Bin]<=(100))

Objects that depend on Production.ProductInventory
dbo.ufnGetStock

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductInventory](
    [ProductID] [int] NOT NULL,
    [LocationID] [smallint] NOT NULL,
    [Shelf] [nvarchar](10) COLLATE Latin1_General_CS_AS NOT NULL,
    [Bin] [tinyint] NOT NULL,
    [Quantity] [smallint] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductListPriceHistory

Changes in the list price of a product over time.

Columns (5)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	ProductID	int				Product identification number. Foreign key to Product.ProductID
PK		IX	StartDate	datetime				List price start date.
			EndDate	datetime		Yes		List price end date
			ListPrice	money				Product list price.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_ProductListPriceHistory_ProductID_StartDate	ProductID	ASC	Yes	CLUSTERED
PK_ProductListPriceHistory_ProductID_StartDate	StartDate	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_ProductListPriceHistory_Product_ProductID: ProductID relies upon remote PK_Product_ProductID (Production.Product)

Check constraints:

Check name	Column name	Expresion
CK_ProductListPriceHistory_EndDate	StartDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)
CK_ProductListPriceHistory_EndDate	EndDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)
CK_ProductListPriceHistory_ListPrice	ListPrice	([ListPrice]>(0.00))

Objects that depend on Production.ProductListPriceHistory
dbo.ufnGetProductDealerPrice
dbo.ufnGetProductListPrice

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductListPriceHistory](
    [ProductID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [ListPrice] [money] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductModel

Product model classification.

Columns (6)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	ProductModelID	int				Primary key for ProductModel records.
	IX	Name	Name				Product model description.
	IX	CatalogDescription	xml		Yes		Detailed product catalog information in xml format.
	IX	Instructions	xml		Yes		Manufacturing instructions in xml format.
	IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_ProductModel_Name	Name	ASC	Yes	NONCLUSTERED
AK_ProductModel_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_ProductModel_ProductModelID	ProductModelID	ASC	Yes	CLUSTERED
PXML_ProductModel_CatalogDescription	CatalogDescription	ASC		XML
PXML_ProductModel_Instructions	Instructions	ASC		XML

Objects that depend on Production.ProductModel
Production.vProductAndDescription
Production.vProductModelCatalogDescription
Production.vProductModelInstructions

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductModel](
    [ProductModelID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [CatalogDescription] [xml](CONTENT [Production].[ProductDescriptionSchemaCollection]) NULL,
    [Instructions] [xml](CONTENT [Production].[ManuInstructionsSchemaCollection]) NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductModelIllustration

Cross-reference table mapping product models and illustrations.

Columns (3)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	ProductModelID	int				Primary key. Foreign key to ProductModel.ProductModelID.
PK	FK	IX	IllustrationID	int				Primary key. Foreign key to Illustration.IllustrationID.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_ProductModelIllustration_ProductModelID_IllustrationID	ProductModelID	ASC	Yes	CLUSTERED
PK_ProductModelIllustration_ProductModelID_IllustrationID	IllustrationID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_ProductModelIllustration_ProductModel_ProductModelID: ProductModelID relies upon remote PK_ProductModel_ProductModelID (Production.ProductModel)
FK_ProductModelIllustration_Illustration_IllustrationID: IllustrationID relies upon remote PK_Illustration_IllustrationID (Production.Illustration)

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductModelIllustration](
    [ProductModelID] [int] NOT NULL,
    [IllustrationID] [int] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductModelProductDescriptionCulture

Cross-reference table mapping product descriptions and the language the description is written in.

Columns (4)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	ProductModelID	int				Primary key. Foreign key to ProductModel.ProductModelID.
PK	FK	IX	ProductDescriptionID	int				Primary key. Foreign key to ProductDescription.ProductDescriptionID.
PK	FK	IX	CultureID	nchar	(6)			Culture identification number. Foreign key to Culture.CultureID.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID	ProductModelID	ASC	Yes	CLUSTERED
PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID	ProductDescriptionID	ASC	Yes	CLUSTERED
PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID	CultureID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_ProductModelProductDescriptionCulture_ProductModel_ProductModelID: ProductModelID relies upon remote PK_ProductModel_ProductModelID (Production.ProductModel)
FK_ProductModelProductDescriptionCulture_ProductDescription_ProductDescriptionID: ProductDescriptionID relies upon remote PK_ProductDescription_ProductDescriptionID (Production.ProductDescription)
FK_ProductModelProductDescriptionCulture_Culture_CultureID: CultureID relies upon remote PK_Culture_CultureID (Production.Culture)

Objects that depend on Production.ProductModelProductDescriptionCulture
Production.vProductAndDescription

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductModelProductDescriptionCulture](
    [ProductModelID] [int] NOT NULL,
    [ProductDescriptionID] [int] NOT NULL,
    [CultureID] [nchar](6) COLLATE Latin1_General_CS_AS NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductPhoto

Product images.

Columns (6)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	ProductPhotoID	int				Primary key for ProductPhoto records.
		ThumbNailPhoto	varbinary		Yes		Small image of the product.
		ThumbnailPhotoFileName	nvarchar	(50)	Yes		Small image file name.
		LargePhoto	varbinary		Yes		Large image of the product.
		LargePhotoFileName	nvarchar	(50)	Yes		Large image file name.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_ProductPhoto_ProductPhotoID	ProductPhotoID	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductPhoto](
    [ProductPhotoID] [int] IDENTITY(1,1) NOT NULL,
    [ThumbNailPhoto] [varbinary](max) NULL,
    [ThumbnailPhotoFileName] [nvarchar](50) COLLATE Latin1_General_CS_AS NULL,
    [LargePhoto] [varbinary](max) NULL,
    [LargePhotoFileName] [nvarchar](50) COLLATE Latin1_General_CS_AS NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductProductPhoto

Cross-reference table mapping products and product photos.

Columns (4)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	ProductID	int				Product identification number. Foreign key to Product.ProductID.
PK	FK	IX	ProductPhotoID	int				Product photo identification number. Foreign key to ProductPhoto.ProductPhotoID.
			Primary	Flag			((0))	0 = Photo is not the principal image. 1 = Photo is the principal image.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_ProductProductPhoto_ProductID_ProductPhotoID	ProductID	ASC	Yes	NONCLUSTERED
PK_ProductProductPhoto_ProductID_ProductPhotoID	ProductPhotoID	ASC	Yes	NONCLUSTERED

Foreign keys dependencies:

FK_ProductProductPhoto_Product_ProductID: ProductID relies upon remote PK_Product_ProductID (Production.Product)
FK_ProductProductPhoto_ProductPhoto_ProductPhotoID: ProductPhotoID relies upon remote PK_ProductPhoto_ProductPhotoID (Production.ProductPhoto)

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductProductPhoto](
    [ProductID] [int] NOT NULL,
    [ProductPhotoID] [int] NOT NULL,
    [Primary] [dbo].[Flag] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductReview

Customer reviews of products they have purchased.

Columns (8)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	ProductReviewID	int				Primary key for ProductReview records.
	FK	IX	ProductID	int				Product identification number. Foreign key to Product.ProductID.
		IX	ReviewerName	Name				Name of the reviewer.
			ReviewDate	datetime			(getdate())	Date review was submitted.
			EmailAddress	nvarchar	(50)			Reviewer's e-mail address.
			Rating	int				Product rating given by the reviewer. Scale is 1 to 5 with 5 as the highest rating.
			Comments	nvarchar	(3850)	Yes		Reviewer's comments
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
IX_ProductReview_ProductID_Name	ProductID	ASC		NONCLUSTERED
IX_ProductReview_ProductID_Name	ReviewerName	ASC		NONCLUSTERED
PK_ProductReview_ProductReviewID	ProductReviewID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_ProductReview_Product_ProductID: ProductID relies upon remote PK_Product_ProductID (Production.Product)

Check constraints:

Check name	Column name	Expresion
CK_ProductReview_Rating	Rating	([Rating]>=(1) AND [Rating]<=(5))

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductReview](
    [ProductReviewID] [int] IDENTITY(1,1) NOT NULL,
    [ProductID] [int] NOT NULL,
    [ReviewerName] [dbo].[Name] NOT NULL,
    [ReviewDate] [datetime] NOT NULL,
    [EmailAddress] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
    [Rating] [int] NOT NULL,
    [Comments] [nvarchar](3850) COLLATE Latin1_General_CS_AS NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductSubcategory

Product subcategories. See ProductCategory table.

Columns (5)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	ProductSubcategoryID	int				Primary key for ProductSubcategory records.
	FK		ProductCategoryID	int				Product category identification number. Foreign key to ProductCategory.ProductCategoryID.
		IX	Name	Name				Subcategory description.
		IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_ProductSubcategory_Name	Name	ASC	Yes	NONCLUSTERED
AK_ProductSubcategory_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_ProductSubcategory_ProductSubcategoryID	ProductSubcategoryID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_ProductSubcategory_ProductCategory_ProductCategoryID: ProductCategoryID relies upon remote PK_ProductCategory_ProductCategoryID (Production.ProductCategory)

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductSubcategory](
    [ProductSubcategoryID] [int] IDENTITY(1,1) NOT NULL,
    [ProductCategoryID] [int] NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ScrapReason

Manufacturing failure reasons lookup table.

Columns (3)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	ScrapReasonID	smallint				Primary key for ScrapReason records.
	IX	Name	Name				Failure description.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_ScrapReason_Name	Name	ASC	Yes	NONCLUSTERED
PK_ScrapReason_ScrapReasonID	ScrapReasonID	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ScrapReason](
    [ScrapReasonID] [smallint] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.TransactionHistory

Record of each purchase order, sales order, or work order transaction year to date.

Columns (9)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	TransactionID	int				Primary key for TransactionHistory records.
	FK	IX	ProductID	int				Product identification number. Foreign key to Product.ProductID.
		IX	ReferenceOrderID	int				Purchase order, sales order, or work order identification number.
		IX	ReferenceOrderLineID	int			((0))	Line number associated with the purchase order, sales order, or work order.
			TransactionDate	datetime			(getdate())	Date and time of the transaction.
			TransactionType	nchar	(1)			W = WorkOrder, S = SalesOrder, P = PurchaseOrder
			Quantity	int				Product quantity.
			ActualCost	money				Product cost.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
IX_TransactionHistory_ProductID	ProductID	ASC		NONCLUSTERED
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID	ReferenceOrderID	ASC		NONCLUSTERED
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID	ReferenceOrderLineID	ASC		NONCLUSTERED
PK_TransactionHistory_TransactionID	TransactionID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_TransactionHistory_Product_ProductID: ProductID relies upon remote PK_Product_ProductID (Production.Product)

Check constraints:

Check name	Column name	Expresion
CK_TransactionHistory_TransactionType	TransactionType	(upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')

Objects that depend on Production.TransactionHistory
Production.iWorkOrder
Production.uWorkOrder
Purchasing.iPurchaseOrderDetail
Purchasing.uPurchaseOrderDetail
Sales.iduSalesOrderDetail

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[TransactionHistory](
    [TransactionID] [int] IDENTITY(100000,1) NOT NULL,
    [ProductID] [int] NOT NULL,
    [ReferenceOrderID] [int] NOT NULL,
    [ReferenceOrderLineID] [int] NOT NULL,
    [TransactionDate] [datetime] NOT NULL,
    [TransactionType] [nchar](1) COLLATE Latin1_General_CS_AS NOT NULL,
    [Quantity] [int] NOT NULL,
    [ActualCost] [money] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.TransactionHistoryArchive

Transactions for previous years.

Columns (9)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	TransactionID	int				Primary key for TransactionHistoryArchive records.
	IX	ProductID	int				Product identification number. Foreign key to Product.ProductID.
	IX	ReferenceOrderID	int				Purchase order, sales order, or work order identification number.
	IX	ReferenceOrderLineID	int			((0))	Line number associated with the purchase order, sales order, or work order.
		TransactionDate	datetime			(getdate())	Date and time of the transaction.
		TransactionType	nchar	(1)			W = Work Order, S = Sales Order, P = Purchase Order
		Quantity	int				Product quantity.
		ActualCost	money				Product cost.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
IX_TransactionHistoryArchive_ProductID	ProductID	ASC		NONCLUSTERED
IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID	ReferenceOrderID	ASC		NONCLUSTERED
IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID	ReferenceOrderLineID	ASC		NONCLUSTERED
PK_TransactionHistoryArchive_TransactionID	TransactionID	ASC	Yes	CLUSTERED

Check constraints:

Check name	Column name	Expresion
CK_TransactionHistoryArchive_TransactionType	TransactionType	(upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[TransactionHistoryArchive](
    [TransactionID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [ReferenceOrderID] [int] NOT NULL,
    [ReferenceOrderLineID] [int] NOT NULL,
    [TransactionDate] [datetime] NOT NULL,
    [TransactionType] [nchar](1) COLLATE Latin1_General_CS_AS NOT NULL,
    [Quantity] [int] NOT NULL,
    [ActualCost] [money] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.UnitMeasure

Unit of measure lookup table.

Columns (3)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	UnitMeasureCode	nchar	(3)			Primary key.
	IX	Name	Name				Unit of measure description.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_UnitMeasure_Name	Name	ASC	Yes	NONCLUSTERED
PK_UnitMeasure_UnitMeasureCode	UnitMeasureCode	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[UnitMeasure](
    [UnitMeasureCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.WorkOrder

Manufacturing work orders.

Columns (10)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	WorkOrderID	int				Primary key for WorkOrder records.
	FK	IX	ProductID	int				Product identification number. Foreign key to Product.ProductID.
			OrderQty	int				Product quantity to build.
			StockedQty	int				Quantity built and put in inventory.
			ScrappedQty	smallint				Quantity that failed inspection.
			StartDate	datetime				Work order start date.
			EndDate	datetime		Yes		Work order end date.
			DueDate	datetime				Work order due date.
	FK	IX	ScrapReasonID	smallint		Yes		Reason for inspection failure.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
IX_WorkOrder_ProductID	ProductID	ASC		NONCLUSTERED
IX_WorkOrder_ScrapReasonID	ScrapReasonID	ASC		NONCLUSTERED
PK_WorkOrder_WorkOrderID	WorkOrderID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_WorkOrder_Product_ProductID: ProductID relies upon remote PK_Product_ProductID (Production.Product)
FK_WorkOrder_ScrapReason_ScrapReasonID: ScrapReasonID relies upon remote PK_ScrapReason_ScrapReasonID (Production.ScrapReason)

Check constraints:

Check name	Column name	Expresion
CK_WorkOrder_OrderQty	OrderQty	([OrderQty]>(0))
CK_WorkOrder_ScrappedQty	ScrappedQty	([ScrappedQty]>=(0))
CK_WorkOrder_EndDate	StartDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)
CK_WorkOrder_EndDate	EndDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)

Triggers:

Trigger name: Production.iWorkOrder (Created: 26 Apr 2006)
Trigger type: INSERT
Trigger active: YesTrigger description: AFTER INSERT trigger that inserts a row in the TransactionHistory table.

Trigger definition:


CREATE TRIGGER [Production].[iWorkOrder] ON [Production].[WorkOrder] 
AFTER INSERT AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        INSERT INTO [Production].[TransactionHistory](
            [ProductID]
            ,[ReferenceOrderID]
            ,[TransactionType]
            ,[TransactionDate]
            ,[Quantity]
            ,[ActualCost])
        SELECT 
            inserted.[ProductID]
            ,inserted.[WorkOrderID]
            ,'W'
            ,GETDATE()
            ,inserted.[OrderQty]
            ,0
        FROM inserted;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

Trigger name: Production.uWorkOrder (Created: 26 Apr 2006)
Trigger type: UPDATE
Trigger active: YesTrigger description: AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.

Trigger definition:


CREATE TRIGGER [Production].[uWorkOrder] ON [Production].[WorkOrder] 
AFTER UPDATE AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        IF UPDATE([ProductID]) OR UPDATE([OrderQty])
        BEGIN
            INSERT INTO [Production].[TransactionHistory](
                [ProductID]
                ,[ReferenceOrderID]
                ,[TransactionType]
                ,[TransactionDate]
                ,[Quantity])
            SELECT 
                inserted.[ProductID]
                ,inserted.[WorkOrderID]
                ,'W'
                ,GETDATE()
                ,inserted.[OrderQty]
            FROM inserted;
        END;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

Objects that depend on Production.WorkOrder
Production.uWorkOrder
Production.WorkOrder

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[WorkOrder](
    [WorkOrderID] [int] IDENTITY(1,1) NOT NULL,
    [ProductID] [int] NOT NULL,
    [OrderQty] [int] NOT NULL,
    [StockedQty]  AS (isnull([OrderQty]-[ScrappedQty],(0))),
    [ScrappedQty] [smallint] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [DueDate] [datetime] NOT NULL,
    [ScrapReasonID] [smallint] NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.WorkOrderRouting

Work order details.

Columns (12)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	WorkOrderID	int				Primary key. Foreign key to WorkOrder.WorkOrderID.
PK		IX	ProductID	int				Primary key. Foreign key to Product.ProductID.
PK		IX	OperationSequence	smallint				Primary key. Indicates the manufacturing process sequence.
	FK		LocationID	smallint				Manufacturing location where the part is processed. Foreign key to Location.LocationID.
			ScheduledStartDate	datetime				Planned manufacturing start date.
			ScheduledEndDate	datetime				Planned manufacturing end date.
			ActualStartDate	datetime		Yes		Actual start date.
			ActualEndDate	datetime		Yes		Actual end date.
			ActualResourceHrs	decimal	(9.4)	Yes		Number of manufacturing hours used.
			PlannedCost	money				Estimated manufacturing cost.
			ActualCost	money		Yes		Actual manufacturing cost.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
IX_WorkOrderRouting_ProductID	ProductID	ASC		NONCLUSTERED
PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence	WorkOrderID	ASC	Yes	CLUSTERED
PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence	ProductID	ASC	Yes	CLUSTERED
PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence	OperationSequence	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_WorkOrderRouting_WorkOrder_WorkOrderID: WorkOrderID relies upon remote PK_WorkOrder_WorkOrderID (Production.WorkOrder)
FK_WorkOrderRouting_Location_LocationID: LocationID relies upon remote PK_Location_LocationID (Production.Location)

Check constraints:

Check name	Column name	Expresion
CK_WorkOrderRouting_ScheduledEndDate	ScheduledStartDate	([ScheduledEndDate]>=[ScheduledStartDate])
CK_WorkOrderRouting_ScheduledEndDate	ScheduledEndDate	([ScheduledEndDate]>=[ScheduledStartDate])
CK_WorkOrderRouting_ActualEndDate	ActualStartDate	([ActualEndDate]>=[ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL)
CK_WorkOrderRouting_ActualEndDate	ActualEndDate	([ActualEndDate]>=[ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL)
CK_WorkOrderRouting_ActualResourceHrs	ActualResourceHrs	([ActualResourceHrs]>=(0.0000))
CK_WorkOrderRouting_PlannedCost	PlannedCost	([PlannedCost]>(0.00))
CK_WorkOrderRouting_ActualCost	ActualCost	([ActualCost]>(0.00))

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[WorkOrderRouting](
    [WorkOrderID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [OperationSequence] [smallint] NOT NULL,
    [LocationID] [smallint] NOT NULL,
    [ScheduledStartDate] [datetime] NOT NULL,
    [ScheduledEndDate] [datetime] NOT NULL,
    [ActualStartDate] [datetime] NULL,
    [ActualEndDate] [datetime] NULL,
    [ActualResourceHrs] [decimal](9, 4) NULL,
    [PlannedCost] [money] NOT NULL,
    [ActualCost] [money] NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Purchasing.ProductVendor

Cross-reference table mapping vendors with the products they supply.

Columns (11)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	ProductID	int				Primary key. Foreign key to Product.ProductID.
PK	FK	IX	VendorID	int				Primary key. Foreign key to Vendor.VendorID.
			AverageLeadTime	int				The average span of time (in days) between placing an order with the vendor and receiving the purchased product.
			StandardPrice	money				The vendor's usual selling price.
			LastReceiptCost	money		Yes		The selling price when last purchased.
			LastReceiptDate	datetime		Yes		Date the product was last received by the vendor.
			MinOrderQty	int				The maximum quantity that should be ordered.
			MaxOrderQty	int				The minimum quantity that should be ordered.
			OnOrderQty	int		Yes		The quantity currently on order.
	FK	IX	UnitMeasureCode	nchar	(3)			The product's unit of measure.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
IX_ProductVendor_UnitMeasureCode	UnitMeasureCode	ASC		NONCLUSTERED
IX_ProductVendor_VendorID	VendorID	ASC		NONCLUSTERED
PK_ProductVendor_ProductID_VendorID	ProductID	ASC	Yes	CLUSTERED
PK_ProductVendor_ProductID_VendorID	VendorID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_ProductVendor_Product_ProductID: ProductID relies upon remote PK_Product_ProductID (Production.Product)
FK_ProductVendor_Vendor_VendorID: VendorID relies upon remote PK_Vendor_VendorID (Purchasing.Vendor)
FK_ProductVendor_UnitMeasure_UnitMeasureCode: UnitMeasureCode relies upon remote PK_UnitMeasure_UnitMeasureCode (Production.UnitMeasure)

Check constraints:

Check name	Column name	Expresion
CK_ProductVendor_AverageLeadTime	AverageLeadTime	([AverageLeadTime]>=(1))
CK_ProductVendor_StandardPrice	StandardPrice	([StandardPrice]>(0.00))
CK_ProductVendor_LastReceiptCost	LastReceiptCost	([LastReceiptCost]>(0.00))
CK_ProductVendor_MinOrderQty	MinOrderQty	([MinOrderQty]>=(1))
CK_ProductVendor_MaxOrderQty	MaxOrderQty	([MaxOrderQty]>=(1))
CK_ProductVendor_OnOrderQty	OnOrderQty	([OnOrderQty]>=(0))

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Purchasing].[ProductVendor](
    [ProductID] [int] NOT NULL,
    [VendorID] [int] NOT NULL,
    [AverageLeadTime] [int] NOT NULL,
    [StandardPrice] [money] NOT NULL,
    [LastReceiptCost] [money] NULL,
    [LastReceiptDate] [datetime] NULL,
    [MinOrderQty] [int] NOT NULL,
    [MaxOrderQty] [int] NOT NULL,
    [OnOrderQty] [int] NULL,
    [UnitMeasureCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Purchasing.PurchaseOrderDetail

Individual products associated with a specific purchase order. See PurchaseOrderHeader.

Columns (11)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	PurchaseOrderID	int				Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
PK		IX	PurchaseOrderDetailID	int				Primary key. One line number per purchased product.
			DueDate	datetime				Date the product is expected to be received.
			OrderQty	smallint				Quantity ordered.
	FK	IX	ProductID	int				Product identification number. Foreign key to Product.ProductID.
			UnitPrice	money				Vendor's selling price of a single product.
			LineTotal	money				Per product subtotal. Computed as OrderQty * UnitPrice.
			ReceivedQty	decimal	(8.2)			Quantity actually received from the vendor.
			RejectedQty	decimal	(8.2)			Quantity rejected during inspection.
			StockedQty	decimal	(9.2)			Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
IX_PurchaseOrderDetail_ProductID	ProductID	ASC		NONCLUSTERED
PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID	PurchaseOrderID	ASC	Yes	CLUSTERED
PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID	PurchaseOrderDetailID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID: PurchaseOrderID relies upon remote PK_PurchaseOrderHeader_PurchaseOrderID (Purchasing.PurchaseOrderHeader)
FK_PurchaseOrderDetail_Product_ProductID: ProductID relies upon remote PK_Product_ProductID (Production.Product)

Check constraints:

Check name	Column name	Expresion
CK_PurchaseOrderDetail_OrderQty	OrderQty	([OrderQty]>(0))
CK_PurchaseOrderDetail_UnitPrice	UnitPrice	([UnitPrice]>=(0.00))
CK_PurchaseOrderDetail_ReceivedQty	ReceivedQty	([ReceivedQty]>=(0.00))
CK_PurchaseOrderDetail_RejectedQty	RejectedQty	([RejectedQty]>=(0.00))

Triggers:

Trigger name: Purchasing.iPurchaseOrderDetail (Created: 26 Apr 2006)
Trigger type: INSERT
Trigger active: YesTrigger description: AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.

Trigger definition:


CREATE TRIGGER [Purchasing].[iPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail] 
AFTER INSERT AS
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        INSERT INTO [Production].[TransactionHistory]
            ([ProductID]
            ,[ReferenceOrderID]
            ,[ReferenceOrderLineID]
            ,[TransactionType]
            ,[TransactionDate]
            ,[Quantity]
            ,[ActualCost])
        SELECT 
            inserted.[ProductID]
            ,inserted.[PurchaseOrderID]
            ,inserted.[PurchaseOrderDetailID]
            ,'P'
            ,GETDATE()
            ,inserted.[OrderQty]
            ,inserted.[UnitPrice]
        FROM inserted 
            INNER JOIN [Purchasing].[PurchaseOrderHeader] 
            ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID];

        -- Update SubTotal in PurchaseOrderHeader record. Note that this causes the 
        -- PurchaseOrderHeader trigger to fire which will update the RevisionNumber.
        UPDATE [Purchasing].[PurchaseOrderHeader]
        SET [Purchasing].[PurchaseOrderHeader].[SubTotal] = 
            (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal])
                FROM [Purchasing].[PurchaseOrderDetail]
                WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID])
        WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN (SELECT inserted.[PurchaseOrderID] FROM inserted);
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

Trigger name: Purchasing.uPurchaseOrderDetail (Created: 26 Apr 2006)
Trigger type: UPDATE
Trigger active: YesTrigger description: AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.

Trigger definition:


CREATE TRIGGER [Purchasing].[uPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail] 
AFTER UPDATE AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice])
        -- Insert record into TransactionHistory 
        BEGIN
            INSERT INTO [Production].[TransactionHistory]
                ([ProductID]
                ,[ReferenceOrderID]
                ,[ReferenceOrderLineID]
                ,[TransactionType]
                ,[TransactionDate]
                ,[Quantity]
                ,[ActualCost])
            SELECT 
                inserted.[ProductID]
                ,inserted.[PurchaseOrderID]
                ,inserted.[PurchaseOrderDetailID]
                ,'P'
                ,GETDATE()
                ,inserted.[OrderQty]
                ,inserted.[UnitPrice]
            FROM inserted 
                INNER JOIN [Purchasing].[PurchaseOrderDetail] 
                ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID];

            -- Update SubTotal in PurchaseOrderHeader record. Note that this causes the 
            -- PurchaseOrderHeader trigger to fire which will update the RevisionNumber.
            UPDATE [Purchasing].[PurchaseOrderHeader]
            SET [Purchasing].[PurchaseOrderHeader].[SubTotal] = 
                (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal])
                    FROM [Purchasing].[PurchaseOrderDetail]
                    WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] 
                        = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID])
            WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] 
                IN (SELECT inserted.[PurchaseOrderID] FROM inserted);

            UPDATE [Purchasing].[PurchaseOrderDetail]
            SET [Purchasing].[PurchaseOrderDetail].[ModifiedDate] = GETDATE()
            FROM inserted
            WHERE inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]
                AND inserted.[PurchaseOrderDetailID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderDetailID];
        END;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

Objects that depend on Purchasing.PurchaseOrderDetail
Purchasing.iPurchaseOrderDetail
Purchasing.PurchaseOrderDetail
Purchasing.uPurchaseOrderDetail

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Purchasing].[PurchaseOrderDetail](
    [PurchaseOrderID] [int] NOT NULL,
    [PurchaseOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
    [DueDate] [datetime] NOT NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [LineTotal]  AS (isnull([OrderQty]*[UnitPrice],(0.00))),
    [ReceivedQty] [decimal](8, 2) NOT NULL,
    [RejectedQty] [decimal](8, 2) NOT NULL,
    [StockedQty]  AS (isnull([ReceivedQty]-[RejectedQty],(0.00))),
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Purchasing.PurchaseOrderHeader

General purchase order information. See PurchaseOrderDetail.

Columns (13)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	PurchaseOrderID	int				Primary key.
			RevisionNumber	tinyint			((0))	Incremental number to track changes to the purchase order over time.
			Status	tinyint			((1))	Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete
	FK	IX	EmployeeID	int				Employee who created the purchase order. Foreign key to Employee.EmployeeID.
	FK	IX	VendorID	int				Vendor with whom the purchase order is placed. Foreign key to Vendor.VendorID.
	FK		ShipMethodID	int				Shipping method. Foreign key to ShipMethod.ShipMethodID.
			OrderDate	datetime			(getdate())	Purchase order creation date.
			ShipDate	datetime		Yes		Estimated shipment date from the vendor.
			SubTotal	money			((0.00))	Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.
			TaxAmt	money			((0.00))	Tax amount.
			Freight	money			((0.00))	Shipping cost.
			TotalDue	money				Total due to vendor. Computed as Subtotal + TaxAmt + Freight.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
IX_PurchaseOrderHeader_EmployeeID	EmployeeID	ASC		NONCLUSTERED
IX_PurchaseOrderHeader_VendorID	VendorID	ASC		NONCLUSTERED
PK_PurchaseOrderHeader_PurchaseOrderID	PurchaseOrderID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_PurchaseOrderHeader_Employee_EmployeeID: EmployeeID relies upon remote PK_Employee_EmployeeID (HumanResources.Employee)
FK_PurchaseOrderHeader_Vendor_VendorID: VendorID relies upon remote PK_Vendor_VendorID (Purchasing.Vendor)
FK_PurchaseOrderHeader_ShipMethod_ShipMethodID: ShipMethodID relies upon remote PK_ShipMethod_ShipMethodID (Purchasing.ShipMethod)

Check constraints:

Check name	Column name	Expresion
CK_PurchaseOrderHeader_Status	Status	([Status]>=(1) AND [Status]<=(4))
CK_PurchaseOrderHeader_ShipDate	OrderDate	([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)
CK_PurchaseOrderHeader_ShipDate	ShipDate	([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)
CK_PurchaseOrderHeader_SubTotal	SubTotal	([SubTotal]>=(0.00))
CK_PurchaseOrderHeader_TaxAmt	TaxAmt	([TaxAmt]>=(0.00))
CK_PurchaseOrderHeader_Freight	Freight	([Freight]>=(0.00))

Triggers:

Trigger name: Purchasing.uPurchaseOrderHeader (Created: 26 Apr 2006)
Trigger type: UPDATE
Trigger active: YesTrigger description: AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.

Trigger definition:


CREATE TRIGGER [Purchasing].[uPurchaseOrderHeader] ON [Purchasing].[PurchaseOrderHeader] 
AFTER UPDATE AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        -- Update RevisionNumber for modification of any field EXCEPT the Status.
        IF NOT UPDATE([Status])
        BEGIN
            UPDATE [Purchasing].[PurchaseOrderHeader]
            SET [Purchasing].[PurchaseOrderHeader].[RevisionNumber] = 
                [Purchasing].[PurchaseOrderHeader].[RevisionNumber] + 1
            WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN 
                (SELECT inserted.[PurchaseOrderID] FROM inserted);
        END;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

Objects that depend on Purchasing.PurchaseOrderHeader
Purchasing.iPurchaseOrderDetail
Purchasing.PurchaseOrderHeader
Purchasing.uPurchaseOrderDetail
Purchasing.uPurchaseOrderHeader

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Purchasing].[PurchaseOrderHeader](
    [PurchaseOrderID] [int] IDENTITY(1,1) NOT NULL,
    [RevisionNumber] [tinyint] NOT NULL,
    [Status] [tinyint] NOT NULL,
    [EmployeeID] [int] NOT NULL,
    [VendorID] [int] NOT NULL,
    [ShipMethodID] [int] NOT NULL,
    [OrderDate] [datetime] NOT NULL,
    [ShipDate] [datetime] NULL,
    [SubTotal] [money] NOT NULL,
    [TaxAmt] [money] NOT NULL,
    [Freight] [money] NOT NULL,
    [TotalDue]  AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))) PERSISTED NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Purchasing.ShipMethod

Shipping company lookup table.

Columns (6)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	ShipMethodID	int				Primary key for ShipMethod records.
	IX	Name	Name				Shipping company name.
		ShipBase	money			((0.00))	Minimum shipping charge.
		ShipRate	money			((0.00))	Shipping charge per pound.
	IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_ShipMethod_Name	Name	ASC	Yes	NONCLUSTERED
AK_ShipMethod_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_ShipMethod_ShipMethodID	ShipMethodID	ASC	Yes	CLUSTERED

Check constraints:

Check name	Column name	Expresion
CK_ShipMethod_ShipBase	ShipBase	([ShipBase]>(0.00))
CK_ShipMethod_ShipRate	ShipRate	([ShipRate]>(0.00))

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Purchasing].[ShipMethod](
    [ShipMethodID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ShipBase] [money] NOT NULL,
    [ShipRate] [money] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Purchasing.Vendor

Companies from whom Adventure Works Cycles purchases parts or other goods.

Columns (8)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	VendorID	int				Primary key for Vendor records.
	IX	AccountNumber	AccountNumber				Vendor account (identification) number.
		Name	Name				Company name.
		CreditRating	tinyint				1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average
		PreferredVendorStatus	Flag			((1))	0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.
		ActiveFlag	Flag			((1))	0 = Vendor no longer used. 1 = Vendor is actively used.
		PurchasingWebServiceURL	nvarchar	(1024)	Yes		Vendor URL.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_Vendor_AccountNumber	AccountNumber	ASC	Yes	NONCLUSTERED
PK_Vendor_VendorID	VendorID	ASC	Yes	CLUSTERED

Check constraints:

Check name	Column name	Expresion
CK_Vendor_CreditRating	CreditRating	([CreditRating]>=(1) AND [CreditRating]<=(5))

Triggers:

Trigger name: Purchasing.dVendor (Created: 26 Apr 2006)
Trigger type: INSTEAD OF DELETE
Trigger active: YesTrigger description: INSTEAD OF DELETE trigger which keeps Vendors from being deleted.

Trigger definition:


CREATE TRIGGER [Purchasing].[dVendor] ON [Purchasing].[Vendor] 
INSTEAD OF DELETE NOT FOR REPLICATION AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        DECLARE @DeleteCount int;

        SELECT @DeleteCount = COUNT(*) FROM deleted;
        IF @DeleteCount > 0 
        BEGIN
            RAISERROR
                (N'Vendors cannot be deleted. They can only be marked as not active.', -- Message
                10, -- Severity.
                1); -- State.

        -- Rollback any active or uncommittable transactions
            IF @@TRANCOUNT > 0
            BEGIN
                ROLLBACK TRANSACTION;
            END
        END;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

Objects that depend on Purchasing.Vendor
Purchasing.vVendor

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Purchasing].[Vendor](
    [VendorID] [int] IDENTITY(1,1) NOT NULL,
    [AccountNumber] [dbo].[AccountNumber] NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [CreditRating] [tinyint] NOT NULL,
    [PreferredVendorStatus] [dbo].[Flag] NOT NULL,
    [ActiveFlag] [dbo].[Flag] NOT NULL,
    [PurchasingWebServiceURL] [nvarchar](1024) COLLATE Latin1_General_CS_AS NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Purchasing.VendorAddress

Cross-reference mapping vendors and addresses.

Columns (4)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	VendorID	int				Primary key. Foreign key to Vendor.VendorID.
PK	FK	IX	AddressID	int				Primary key. Foreign key to Address.AddressID.
	FK		AddressTypeID	int				Address type. Foreign key to AddressType.AddressTypeID.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
IX_VendorAddress_AddressID	AddressID	ASC		NONCLUSTERED
PK_VendorAddress_VendorID_AddressID	VendorID	ASC	Yes	CLUSTERED
PK_VendorAddress_VendorID_AddressID	AddressID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_VendorAddress_Vendor_VendorID: VendorID relies upon remote PK_Vendor_VendorID (Purchasing.Vendor)
FK_VendorAddress_Address_AddressID: AddressID relies upon remote PK_Address_AddressID (Person.Address)
FK_VendorAddress_AddressType_AddressTypeID: AddressTypeID relies upon remote PK_AddressType_AddressTypeID (Person.AddressType)

Objects that depend on Purchasing.VendorAddress
Purchasing.vVendor

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Purchasing].[VendorAddress](
    [VendorID] [int] NOT NULL,
    [AddressID] [int] NOT NULL,
    [AddressTypeID] [int] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Purchasing.VendorContact

Cross-reference table mapping vendors and their employees.

Columns (4)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	VendorID	int				Primary key.
PK	FK	IX	ContactID	int				Contact (Vendor employee) identification number. Foreign key to Contact.ContactID.
	FK	IX	ContactTypeID	int				Contact type such as sales manager, or sales agent.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
IX_VendorContact_ContactID	ContactID	ASC		NONCLUSTERED
IX_VendorContact_ContactTypeID	ContactTypeID	ASC		NONCLUSTERED
PK_VendorContact_VendorID_ContactID	VendorID	ASC	Yes	CLUSTERED
PK_VendorContact_VendorID_ContactID	ContactID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_VendorContact_Vendor_VendorID: VendorID relies upon remote PK_Vendor_VendorID (Purchasing.Vendor)
FK_VendorContact_Contact_ContactID: ContactID relies upon remote PK_Contact_ContactID (Person.Contact)
FK_VendorContact_ContactType_ContactTypeID: ContactTypeID relies upon remote PK_ContactType_ContactTypeID (Person.ContactType)

Objects that depend on Purchasing.VendorContact
Purchasing.vVendor

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Purchasing].[VendorContact](
    [VendorID] [int] NOT NULL,
    [ContactID] [int] NOT NULL,
    [ContactTypeID] [int] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.ContactCreditCard

Cross-reference table mapping customers in the Contact table to their credit card information in the CreditCard table.

Columns (3)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	ContactID	int				Customer identification number. Foreign key to Contact.ContactID.
PK	FK	IX	CreditCardID	int				Credit card identification number. Foreign key to CreditCard.CreditCardID.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_ContactCreditCard_ContactID_CreditCardID	ContactID	ASC	Yes	CLUSTERED
PK_ContactCreditCard_ContactID_CreditCardID	CreditCardID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_ContactCreditCard_Contact_ContactID: ContactID relies upon remote PK_Contact_ContactID (Person.Contact)
FK_ContactCreditCard_CreditCard_CreditCardID: CreditCardID relies upon remote PK_CreditCard_CreditCardID (Sales.CreditCard)

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[ContactCreditCard](
    [ContactID] [int] NOT NULL,
    [CreditCardID] [int] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.CountryRegionCurrency

Cross-reference table mapping ISO currency codes to a country or region.

Columns (3)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	CountryRegionCode	nvarchar	(3)			ISO code for countries and regions. Foreign key to CountryRegion.CountryRegionCode.
PK	FK	IX	CurrencyCode	nchar	(3)			ISO standard currency code. Foreign key to Currency.CurrencyCode.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
IX_CountryRegionCurrency_CurrencyCode	CurrencyCode	ASC		NONCLUSTERED
PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode	CountryRegionCode	ASC	Yes	CLUSTERED
PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode	CurrencyCode	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_CountryRegionCurrency_CountryRegion_CountryRegionCode: CountryRegionCode relies upon remote PK_CountryRegion_CountryRegionCode (Person.CountryRegion)
FK_CountryRegionCurrency_Currency_CurrencyCode: CurrencyCode relies upon remote PK_Currency_CurrencyCode (Sales.Currency)

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[CountryRegionCurrency](
    [CountryRegionCode] [nvarchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [CurrencyCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.CreditCard

Customer credit card information.

Columns (6)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	CreditCardID	int				Primary key for CreditCard records.
		CardType	nvarchar	(50)			Credit card name.
	IX	CardNumber	nvarchar	(25)			Credit card number.
		ExpMonth	tinyint				Credit card expiration month.
		ExpYear	smallint				Credit card expiration year.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_CreditCard_CardNumber	CardNumber	ASC	Yes	NONCLUSTERED
PK_CreditCard_CreditCardID	CreditCardID	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[CreditCard](
    [CreditCardID] [int] IDENTITY(1,1) NOT NULL,
    [CardType] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
    [CardNumber] [nvarchar](25) COLLATE Latin1_General_CS_AS NOT NULL,
    [ExpMonth] [tinyint] NOT NULL,
    [ExpYear] [smallint] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.Currency

Lookup table containing standard ISO currencies.

Columns (3)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	CurrencyCode	nchar	(3)			The ISO code for the Currency.
	IX	Name	Name				Currency name.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_Currency_Name	Name	ASC	Yes	NONCLUSTERED
PK_Currency_CurrencyCode	CurrencyCode	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[Currency](
    [CurrencyCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.CurrencyRate

Currency exchange rates.

Columns (7)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	CurrencyRateID	int				Primary key for CurrencyRate records.
		IX	CurrencyRateDate	datetime				Date and time the exchange rate was obtained.
	FK	IX	FromCurrencyCode	nchar	(3)			Exchange rate was converted from this currency code.
	FK	IX	ToCurrencyCode	nchar	(3)			Exchange rate was converted to this currency code.
			AverageRate	money				Average exchange rate for the day.
			EndOfDayRate	money				Final exchange rate for the day.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode	CurrencyRateDate	ASC	Yes	NONCLUSTERED
AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode	FromCurrencyCode	ASC	Yes	NONCLUSTERED
AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode	ToCurrencyCode	ASC	Yes	NONCLUSTERED
PK_CurrencyRate_CurrencyRateID	CurrencyRateID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_CurrencyRate_Currency_FromCurrencyCode: FromCurrencyCode relies upon remote PK_Currency_CurrencyCode (Sales.Currency)
FK_CurrencyRate_Currency_ToCurrencyCode: ToCurrencyCode relies upon remote PK_Currency_CurrencyCode (Sales.Currency)

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[CurrencyRate](
    [CurrencyRateID] [int] IDENTITY(1,1) NOT NULL,
    [CurrencyRateDate] [datetime] NOT NULL,
    [FromCurrencyCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [ToCurrencyCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [AverageRate] [money] NOT NULL,
    [EndOfDayRate] [money] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.Customer

Current customer information. Also see the Individual and Store tables.

Columns (6)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	CustomerID	int				Primary key for Customer records.
	FK	IX	TerritoryID	int		Yes		ID of the territory in which the customer is located. Foreign key to SalesTerritory.SalesTerritoryID.
		IX	AccountNumber	varchar	(10)			Unique number identifying the customer assigned by the accounting system.
			CustomerType	nchar	(1)			Customer type: I = Individual, S = Store
		IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_Customer_AccountNumber	AccountNumber	ASC	Yes	NONCLUSTERED
AK_Customer_rowguid	rowguid	ASC	Yes	NONCLUSTERED
IX_Customer_TerritoryID	TerritoryID	ASC		NONCLUSTERED
PK_Customer_CustomerID	CustomerID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_Customer_SalesTerritory_TerritoryID: TerritoryID relies upon remote PK_SalesTerritory_TerritoryID (Sales.SalesTerritory)

Check constraints:

Check name	Column name	Expresion
CK_Customer_CustomerType	CustomerType	(upper([CustomerType])='I' OR upper([CustomerType])='S')

Objects that depend on Sales.Customer
Sales.Customer
Sales.vIndividualCustomer
Sales.vStoreWithDemographics

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
CREATE TABLE [Sales].[Customer](
    [CustomerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [TerritoryID] [int] NULL,
    [AccountNumber]  AS (isnull('AW'+[dbo].[ufnLeadingZeros]([CustomerID]),'')),
    [CustomerType] [nchar](1) COLLATE Latin1_General_CS_AS NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.CustomerAddress

Cross-reference table mapping customers to their address(es).

Columns (5)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	CustomerID	int				Primary key. Foreign key to Customer.CustomerID.
PK	FK	IX	AddressID	int				Primary key. Foreign key to Address.AddressID.
	FK		AddressTypeID	int				Address type. Foreign key to AddressType.AddressTypeID.
		IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_CustomerAddress_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_CustomerAddress_CustomerID_AddressID	CustomerID	ASC	Yes	CLUSTERED
PK_CustomerAddress_CustomerID_AddressID	AddressID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_CustomerAddress_Customer_CustomerID: CustomerID relies upon remote PK_Customer_CustomerID (Sales.Customer)
FK_CustomerAddress_Address_AddressID: AddressID relies upon remote PK_Address_AddressID (Person.Address)
FK_CustomerAddress_AddressType_AddressTypeID: AddressTypeID relies upon remote PK_AddressType_AddressTypeID (Person.AddressType)

Objects that depend on Sales.CustomerAddress
Sales.vIndividualCustomer
Sales.vStoreWithDemographics

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[CustomerAddress](
    [CustomerID] [int] NOT NULL,
    [AddressID] [int] NOT NULL,
    [AddressTypeID] [int] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.Individual

Demographic data about customers that purchase Adventure Works products online.

Columns (4)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	CustomerID	int				Unique customer identification number. Foreign key to Customer.CustomerID.
	FK		ContactID	int				Identifies the customer in the Contact table. Foreign key to Contact.ContactID.
		IX	Demographics	xml		Yes		Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_Individual_CustomerID	CustomerID	ASC	Yes	CLUSTERED
PXML_Individual_Demographics	Demographics	ASC		XML
XMLPATH_Individual_Demographics	Demographics	ASC		XML
XMLPROPERTY_Individual_Demographics	Demographics	ASC		XML
XMLVALUE_Individual_Demographics	Demographics	ASC		XML

Foreign keys dependencies:

FK_Individual_Customer_CustomerID: CustomerID relies upon remote PK_Customer_CustomerID (Sales.Customer)
FK_Individual_Contact_ContactID: ContactID relies upon remote PK_Contact_ContactID (Person.Contact)

Triggers:

Trigger name: Sales.iuIndividual (Created: 26 Apr 2006)
Trigger type: INSERT
Trigger active: YesTrigger description: AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Individual table to the current date.

Trigger definition:


CREATE TRIGGER [Sales].[iuIndividual] ON [Sales].[Individual] 
AFTER INSERT, UPDATE NOT FOR REPLICATION AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    -- Only allow the Customer to be a Store OR Individual
    IF EXISTS (SELECT * FROM inserted INNER JOIN [Sales].[Store] 
        ON inserted.[CustomerID] = [Sales].[Store].[CustomerID]) 
    BEGIN
        -- Rollback any active or uncommittable transactions
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
    END;

    IF UPDATE([CustomerID]) OR UPDATE([Demographics]) 
    BEGIN
        UPDATE [Sales].[Individual] 
        SET [Sales].[Individual].[Demographics] = N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"> 
            <TotalPurchaseYTD>0.00</TotalPurchaseYTD> 
            </IndividualSurvey>' 
        FROM inserted 
        WHERE [Sales].[Individual].[CustomerID] = inserted.[CustomerID] 
            AND inserted.[Demographics] IS NULL;
        
        UPDATE [Sales].[Individual] 
        SET [Demographics].modify(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
            insert <TotalPurchaseYTD>0.00</TotalPurchaseYTD> 
            as first 
            into (/IndividualSurvey)[1]') 
        FROM inserted 
        WHERE [Sales].[Individual].[CustomerID] = inserted.[CustomerID] 
            AND inserted.[Demographics] IS NOT NULL 
            AND inserted.[Demographics].exist(N'declare default element namespace 
                "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
                /IndividualSurvey/TotalPurchaseYTD') <> 1;
    END;
END;

CREATE TRIGGER [Sales].[iuIndividual] ON [Sales].[Individual] 
AFTER INSERT, UPDATE NOT FOR REPLICATION AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    -- Only allow the Customer to be a Store OR Individual
    IF EXISTS (SELECT * FROM inserted INNER JOIN [Sales].[Store] 
        ON inserted.[CustomerID] = [Sales].[Store].[CustomerID]) 
    BEGIN
        -- Rollback any active or uncommittable transactions
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
    END;

    IF UPDATE([CustomerID]) OR UPDATE([Demographics]) 
    BEGIN
        UPDATE [Sales].[Individual] 
        SET [Sales].[Individual].[Demographics] = N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"> 
            <TotalPurchaseYTD>0.00</TotalPurchaseYTD> 
            </IndividualSurvey>' 
        FROM inserted 
        WHERE [Sales].[Individual].[CustomerID] = inserted.[CustomerID] 
            AND inserted.[Demographics] IS NULL;
        
        UPDATE [Sales].[Individual] 
        SET [Demographics].modify(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
            insert <TotalPurchaseYTD>0.00</TotalPurchaseYTD> 
            as first 
            into (/IndividualSurvey)[1]') 
        FROM inserted 
        WHERE [Sales].[Individual].[CustomerID] = inserted.[CustomerID] 
            AND inserted.[Demographics] IS NOT NULL 
            AND inserted.[Demographics].exist(N'declare default element namespace 
                "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
                /IndividualSurvey/TotalPurchaseYTD') <> 1;
    END;
END;

Objects that depend on Sales.Individual
Sales.iduSalesOrderDetail
Sales.iStore
Sales.iuIndividual
Sales.vIndividualCustomer
Sales.vIndividualDemographics

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[Individual](
    [CustomerID] [int] NOT NULL,
    [ContactID] [int] NOT NULL,
    [Demographics] [xml](CONTENT [Sales].[IndividualSurveySchemaCollection]) NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.SalesOrderDetail

Individual products associated with a specific sales order. See SalesOrderHeader.

Columns (11)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	SalesOrderID	int				Primary key. Foreign key to SalesOrderHeader.SalesOrderID.
PK		IX	SalesOrderDetailID	int				Primary key. One incremental unique number per product sold.
			CarrierTrackingNumber	nvarchar	(25)	Yes		Shipment tracking number supplied by the shipper.
			OrderQty	smallint				Quantity ordered per product.
	FK	IX	ProductID	int				Product sold to customer. Foreign key to Product.ProductID.
	FK		SpecialOfferID	int				Promotional code. Foreign key to SpecialOffer.SpecialOfferID.
			UnitPrice	money				Selling price of a single product.
			UnitPriceDiscount	money			((0.0))	Discount amount.
			LineTotal	numeric	(38.6)			Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.
		IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_SalesOrderDetail_rowguid	rowguid	ASC	Yes	NONCLUSTERED
IX_SalesOrderDetail_ProductID	ProductID	ASC		NONCLUSTERED
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID	SalesOrderID	ASC	Yes	CLUSTERED
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID	SalesOrderDetailID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID: SalesOrderID relies upon remote PK_SalesOrderHeader_SalesOrderID (Sales.SalesOrderHeader)
FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID: ProductID relies upon remote PK_SpecialOfferProduct_SpecialOfferID_ProductID (Sales.SpecialOfferProduct)
FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID: SpecialOfferID relies upon remote PK_SpecialOfferProduct_SpecialOfferID_ProductID (Sales.SpecialOfferProduct)

Check constraints:

Check name	Column name	Expresion
CK_SalesOrderDetail_OrderQty	OrderQty	([OrderQty]>(0))
CK_SalesOrderDetail_UnitPrice	UnitPrice	([UnitPrice]>=(0.00))
CK_SalesOrderDetail_UnitPriceDiscount	UnitPriceDiscount	([UnitPriceDiscount]>=(0.00))

Triggers:

Trigger name: Sales.iduSalesOrderDetail (Created: 26 Apr 2006)
Trigger type: INSERT
Trigger active: YesTrigger description: AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.

Trigger definition:


CREATE TRIGGER [Sales].[iduSalesOrderDetail] ON [Sales].[SalesOrderDetail] 
AFTER INSERT, DELETE, UPDATE AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        -- If inserting or updating these columns
        IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) OR UPDATE([UnitPriceDiscount]) 
        -- Insert record into TransactionHistory
        BEGIN
            INSERT INTO [Production].[TransactionHistory]
                ([ProductID]
                ,[ReferenceOrderID]
                ,[ReferenceOrderLineID]
                ,[TransactionType]
                ,[TransactionDate]
                ,[Quantity]
                ,[ActualCost])
            SELECT 
                inserted.[ProductID]
                ,inserted.[SalesOrderID]
                ,inserted.[SalesOrderDetailID]
                ,'S'
                ,GETDATE()
                ,inserted.[OrderQty]
                ,inserted.[UnitPrice]
            FROM inserted 
                INNER JOIN [Sales].[SalesOrderHeader] 
                ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID];

            UPDATE [Sales].[Individual] 
            SET [Demographics].modify('declare default element namespace 
                "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
                replace value of (/IndividualSurvey/TotalPurchaseYTD)[1] 
                with data(/IndividualSurvey/TotalPurchaseYTD)[1] + sql:column ("inserted.LineTotal")') 
            FROM inserted 
                INNER JOIN [Sales].[SalesOrderHeader] 
                ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID] 
            WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID];
        END;

        -- Update SubTotal in SalesOrderHeader record. Note that this causes the 
        -- SalesOrderHeader trigger to fire which will update the RevisionNumber.
        UPDATE [Sales].[SalesOrderHeader]
        SET [Sales].[SalesOrderHeader].[SubTotal] = 
            (SELECT SUM([Sales].[SalesOrderDetail].[LineTotal])
                FROM [Sales].[SalesOrderDetail]
                WHERE [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID])
        WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN (SELECT inserted.[SalesOrderID] FROM inserted);

        UPDATE [Sales].[Individual] 
        SET [Demographics].modify('declare default element namespace 
            "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
            replace value of (/IndividualSurvey/TotalPurchaseYTD)[1] 
            with data(/IndividualSurvey/TotalPurchaseYTD)[1] - sql:column("deleted.LineTotal")') 
        FROM deleted 
            INNER JOIN [Sales].[SalesOrderHeader] 
            ON deleted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID] 
        WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID];
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

CREATE TRIGGER [Sales].[iduSalesOrderDetail] ON [Sales].[SalesOrderDetail] 
AFTER INSERT, DELETE, UPDATE AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        -- If inserting or updating these columns
        IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) OR UPDATE([UnitPriceDiscount]) 
        -- Insert record into TransactionHistory
        BEGIN
            INSERT INTO [Production].[TransactionHistory]
                ([ProductID]
                ,[ReferenceOrderID]
                ,[ReferenceOrderLineID]
                ,[TransactionType]
                ,[TransactionDate]
                ,[Quantity]
                ,[ActualCost])
            SELECT 
                inserted.[ProductID]
                ,inserted.[SalesOrderID]
                ,inserted.[SalesOrderDetailID]
                ,'S'
                ,GETDATE()
                ,inserted.[OrderQty]
                ,inserted.[UnitPrice]
            FROM inserted 
                INNER JOIN [Sales].[SalesOrderHeader] 
                ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID];

            UPDATE [Sales].[Individual] 
            SET [Demographics].modify('declare default element namespace 
                "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
                replace value of (/IndividualSurvey/TotalPurchaseYTD)[1] 
                with data(/IndividualSurvey/TotalPurchaseYTD)[1] + sql:column ("inserted.LineTotal")') 
            FROM inserted 
                INNER JOIN [Sales].[SalesOrderHeader] 
                ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID] 
            WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID];
        END;

        -- Update SubTotal in SalesOrderHeader record. Note that this causes the 
        -- SalesOrderHeader trigger to fire which will update the RevisionNumber.
        UPDATE [Sales].[SalesOrderHeader]
        SET [Sales].[SalesOrderHeader].[SubTotal] = 
            (SELECT SUM([Sales].[SalesOrderDetail].[LineTotal])
                FROM [Sales].[SalesOrderDetail]
                WHERE [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID])
        WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN (SELECT inserted.[SalesOrderID] FROM inserted);

        UPDATE [Sales].[Individual] 
        SET [Demographics].modify('declare default element namespace 
            "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
            replace value of (/IndividualSurvey/TotalPurchaseYTD)[1] 
            with data(/IndividualSurvey/TotalPurchaseYTD)[1] - sql:column("deleted.LineTotal")') 
        FROM deleted 
            INNER JOIN [Sales].[SalesOrderHeader] 
            ON deleted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID] 
        WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID];
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

CREATE TRIGGER [Sales].[iduSalesOrderDetail] ON [Sales].[SalesOrderDetail] 
AFTER INSERT, DELETE, UPDATE AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        -- If inserting or updating these columns
        IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) OR UPDATE([UnitPriceDiscount]) 
        -- Insert record into TransactionHistory
        BEGIN
            INSERT INTO [Production].[TransactionHistory]
                ([ProductID]
                ,[ReferenceOrderID]
                ,[ReferenceOrderLineID]
                ,[TransactionType]
                ,[TransactionDate]
                ,[Quantity]
                ,[ActualCost])
            SELECT 
                inserted.[ProductID]
                ,inserted.[SalesOrderID]
                ,inserted.[SalesOrderDetailID]
                ,'S'
                ,GETDATE()
                ,inserted.[OrderQty]
                ,inserted.[UnitPrice]
            FROM inserted 
                INNER JOIN [Sales].[SalesOrderHeader] 
                ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID];

            UPDATE [Sales].[Individual] 
            SET [Demographics].modify('declare default element namespace 
                "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
                replace value of (/IndividualSurvey/TotalPurchaseYTD)[1] 
                with data(/IndividualSurvey/TotalPurchaseYTD)[1] + sql:column ("inserted.LineTotal")') 
            FROM inserted 
                INNER JOIN [Sales].[SalesOrderHeader] 
                ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID] 
            WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID];
        END;

        -- Update SubTotal in SalesOrderHeader record. Note that this causes the 
        -- SalesOrderHeader trigger to fire which will update the RevisionNumber.
        UPDATE [Sales].[SalesOrderHeader]
        SET [Sales].[SalesOrderHeader].[SubTotal] = 
            (SELECT SUM([Sales].[SalesOrderDetail].[LineTotal])
                FROM [Sales].[SalesOrderDetail]
                WHERE [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID])
        WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN (SELECT inserted.[SalesOrderID] FROM inserted);

        UPDATE [Sales].[Individual] 
        SET [Demographics].modify('declare default element namespace 
            "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
            replace value of (/IndividualSurvey/TotalPurchaseYTD)[1] 
            with data(/IndividualSurvey/TotalPurchaseYTD)[1] - sql:column("deleted.LineTotal")') 
        FROM deleted 
            INNER JOIN [Sales].[SalesOrderHeader] 
            ON deleted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID] 
        WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID];
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

Objects that depend on Sales.SalesOrderDetail
Sales.iduSalesOrderDetail
Sales.SalesOrderDetail

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SalesOrderDetail](
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) COLLATE Latin1_General_CS_AS NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal]  AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.SalesOrderHeader

General sales order information.

Columns (27)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	SalesOrderID	int				Primary key.
			RevisionNumber	tinyint			((0))	Incremental number to track changes to the sales order over time.
			OrderDate	datetime			(getdate())	Dates the sales order was created.
			DueDate	datetime				Date the order is due to the customer.
			ShipDate	datetime		Yes		Date the order was shipped to the customer.
			Status	tinyint			((1))	Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled
			OnlineOrderFlag	Flag			((1))	0 = Order placed by sales person. 1 = Order placed online by customer.
		IX	SalesOrderNumber	nvarchar	(25)			Unique sales order identification number.
			PurchaseOrderNumber	OrderNumber		Yes		Customer purchase order number reference.
			AccountNumber	AccountNumber		Yes		Financial accounting number reference.
	FK	IX	CustomerID	int				Customer identification number. Foreign key to Customer.CustomerID.
	FK		ContactID	int				Customer contact identification number. Foreign key to Contact.ContactID.
	FK	IX	SalesPersonID	int		Yes		Sales person who created the sales order. Foreign key to SalesPerson.SalePersonID.
	FK		TerritoryID	int		Yes		Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID.
	FK		BillToAddressID	int				Customer billing address. Foreign key to Address.AddressID.
	FK		ShipToAddressID	int				Customer shipping address. Foreign key to Address.AddressID.
	FK		ShipMethodID	int				Shipping method. Foreign key to ShipMethod.ShipMethodID.
	FK		CreditCardID	int		Yes		Credit card identification number. Foreign key to CreditCard.CreditCardID.
			CreditCardApprovalCode	varchar	(15)	Yes		Approval code provided by the credit card company.
	FK		CurrencyRateID	int		Yes		Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID.
			SubTotal	money			((0.00))	Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.
			TaxAmt	money			((0.00))	Tax amount.
			Freight	money			((0.00))	Shipping cost.
			TotalDue	money				Total due from customer. Computed as Subtotal + TaxAmt + Freight.
			Comment	nvarchar	(128)	Yes		Sales representative comments.
		IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_SalesOrderHeader_rowguid	rowguid	ASC	Yes	NONCLUSTERED
AK_SalesOrderHeader_SalesOrderNumber	SalesOrderNumber	ASC	Yes	NONCLUSTERED
IX_SalesOrderHeader_CustomerID	CustomerID	ASC		NONCLUSTERED
IX_SalesOrderHeader_SalesPersonID	SalesPersonID	ASC		NONCLUSTERED
PK_SalesOrderHeader_SalesOrderID	SalesOrderID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_SalesOrderHeader_Customer_CustomerID: CustomerID relies upon remote PK_Customer_CustomerID (Sales.Customer)
FK_SalesOrderHeader_Contact_ContactID: ContactID relies upon remote PK_Contact_ContactID (Person.Contact)
FK_SalesOrderHeader_SalesPerson_SalesPersonID: SalesPersonID relies upon remote PK_SalesPerson_SalesPersonID (Sales.SalesPerson)
FK_SalesOrderHeader_SalesTerritory_TerritoryID: TerritoryID relies upon remote PK_SalesTerritory_TerritoryID (Sales.SalesTerritory)
FK_SalesOrderHeader_Address_BillToAddressID: BillToAddressID relies upon remote PK_Address_AddressID (Person.Address)
FK_SalesOrderHeader_Address_ShipToAddressID: ShipToAddressID relies upon remote PK_Address_AddressID (Person.Address)
FK_SalesOrderHeader_ShipMethod_ShipMethodID: ShipMethodID relies upon remote PK_ShipMethod_ShipMethodID (Purchasing.ShipMethod)
FK_SalesOrderHeader_CreditCard_CreditCardID: CreditCardID relies upon remote PK_CreditCard_CreditCardID (Sales.CreditCard)
FK_SalesOrderHeader_CurrencyRate_CurrencyRateID: CurrencyRateID relies upon remote PK_CurrencyRate_CurrencyRateID (Sales.CurrencyRate)

Check constraints:

Check name	Column name	Expresion
CK_SalesOrderHeader_Status	Status	([Status]>=(0) AND [Status]<=(8))
CK_SalesOrderHeader_DueDate	OrderDate	([DueDate]>=[OrderDate])
CK_SalesOrderHeader_DueDate	DueDate	([DueDate]>=[OrderDate])
CK_SalesOrderHeader_ShipDate	OrderDate	([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)
CK_SalesOrderHeader_ShipDate	ShipDate	([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)
CK_SalesOrderHeader_SubTotal	SubTotal	([SubTotal]>=(0.00))
CK_SalesOrderHeader_TaxAmt	TaxAmt	([TaxAmt]>=(0.00))
CK_SalesOrderHeader_Freight	Freight	([Freight]>=(0.00))

Triggers:

Trigger name: Sales.uSalesOrderHeader (Created: 26 Apr 2006)
Trigger type: UPDATE
Trigger active: YesTrigger description: AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the SalesOrderHeader table.Updates the SalesYTD column in the SalesPerson and SalesTerritory tables.

Trigger definition:


CREATE TRIGGER [Sales].[uSalesOrderHeader] ON [Sales].[SalesOrderHeader] 
AFTER UPDATE NOT FOR REPLICATION AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        -- Update RevisionNumber for modification of any field EXCEPT the Status.
        IF NOT UPDATE([Status])
        BEGIN
            UPDATE [Sales].[SalesOrderHeader]
            SET [Sales].[SalesOrderHeader].[RevisionNumber] = 
                [Sales].[SalesOrderHeader].[RevisionNumber] + 1
            WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN 
                (SELECT inserted.[SalesOrderID] FROM inserted);
        END;

        -- Update the SalesPerson SalesYTD when SubTotal is updated
        IF UPDATE([SubTotal])
        BEGIN
            DECLARE @StartDate datetime,
                    @EndDate datetime

            SET @StartDate = [dbo].[ufnGetAccountingStartDate]();
            SET @EndDate = [dbo].[ufnGetAccountingEndDate]();

            UPDATE [Sales].[SalesPerson]
            SET [Sales].[SalesPerson].[SalesYTD] = 
                (SELECT SUM([Sales].[SalesOrderHeader].[SubTotal])
                FROM [Sales].[SalesOrderHeader] 
                WHERE [Sales].[SalesPerson].[SalesPersonID] = [Sales].[SalesOrderHeader].[SalesPersonID]
                    AND ([Sales].[SalesOrderHeader].[Status] = 5) -- Shipped
                    AND [Sales].[SalesOrderHeader].[OrderDate] BETWEEN @StartDate AND @EndDate)
            WHERE [Sales].[SalesPerson].[SalesPersonID] 
                IN (SELECT DISTINCT inserted.[SalesPersonID] FROM inserted 
                    WHERE inserted.[OrderDate] BETWEEN @StartDate AND @EndDate);

            -- Update the SalesTerritory SalesYTD when SubTotal is updated
            UPDATE [Sales].[SalesTerritory]
            SET [Sales].[SalesTerritory].[SalesYTD] = 
                (SELECT SUM([Sales].[SalesOrderHeader].[SubTotal])
                FROM [Sales].[SalesOrderHeader] 
                WHERE [Sales].[SalesTerritory].[TerritoryID] = [Sales].[SalesOrderHeader].[TerritoryID]
                    AND ([Sales].[SalesOrderHeader].[Status] = 5) -- Shipped
                    AND [Sales].[SalesOrderHeader].[OrderDate] BETWEEN @StartDate AND @EndDate)
            WHERE [Sales].[SalesTerritory].[TerritoryID] 
                IN (SELECT DISTINCT inserted.[TerritoryID] FROM inserted 
                    WHERE inserted.[OrderDate] BETWEEN @StartDate AND @EndDate);
        END;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

Objects that depend on Sales.SalesOrderHeader
Sales.iduSalesOrderDetail
Sales.SalesOrderHeader
Sales.uSalesOrderHeader
Sales.vSalesPersonSalesByFiscalYears

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
CREATE TABLE [Sales].[SalesOrderHeader](
    [SalesOrderID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [RevisionNumber] [tinyint] NOT NULL,
    [OrderDate] [datetime] NOT NULL,
    [DueDate] [datetime] NOT NULL,
    [ShipDate] [datetime] NULL,
    [Status] [tinyint] NOT NULL,
    [OnlineOrderFlag] [dbo].[Flag] NOT NULL,
    [SalesOrderNumber]  AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID],0),N'*** ERROR ***')),
    [PurchaseOrderNumber] [dbo].[OrderNumber] NULL,
    [AccountNumber] [dbo].[AccountNumber] NULL,
    [CustomerID] [int] NOT NULL,
    [ContactID] [int] NOT NULL,
    [SalesPersonID] [int] NULL,
    [TerritoryID] [int] NULL,
    [BillToAddressID] [int] NOT NULL,
    [ShipToAddressID] [int] NOT NULL,
    [ShipMethodID] [int] NOT NULL,
    [CreditCardID] [int] NULL,
    [CreditCardApprovalCode] [varchar](15) COLLATE Latin1_General_CS_AS NULL,
    [CurrencyRateID] [int] NULL,
    [SubTotal] [money] NOT NULL,
    [TaxAmt] [money] NOT NULL,
    [Freight] [money] NOT NULL,
    [TotalDue]  AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))),
    [Comment] [nvarchar](128) COLLATE Latin1_General_CS_AS NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.SalesOrderHeaderSalesReason

Cross-reference table mapping sales orders to sales reason codes.

Columns (3)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	SalesOrderID	int				Primary key. Foreign key to SalesOrderHeader.SalesOrderID.
PK	FK	IX	SalesReasonID	int				Primary key. Foreign key to SalesReason.SalesReasonID.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID	SalesOrderID	ASC	Yes	CLUSTERED
PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID	SalesReasonID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID: SalesOrderID relies upon remote PK_SalesOrderHeader_SalesOrderID (Sales.SalesOrderHeader)
FK_SalesOrderHeaderSalesReason_SalesReason_SalesReasonID: SalesReasonID relies upon remote PK_SalesReason_SalesReasonID (Sales.SalesReason)

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SalesOrderHeaderSalesReason](
    [SalesOrderID] [int] NOT NULL,
    [SalesReasonID] [int] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.SalesPerson

Sales representative current information.

Columns (9)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	SalesPersonID	int				Primary key for SalesPerson records.
	FK		TerritoryID	int		Yes		Territory currently assigned to. Foreign key to SalesTerritory.SalesTerritoryID.
			SalesQuota	money		Yes		Projected yearly sales.
			Bonus	money			((0.00))	Bonus due if quota is met.
			CommissionPct	smallmoney			((0.00))	Commision percent received per sale.
			SalesYTD	money			((0.00))	Sales total year to date.
			SalesLastYear	money			((0.00))	Sales total of previous year.
		IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_SalesPerson_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_SalesPerson_SalesPersonID	SalesPersonID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_SalesPerson_Employee_SalesPersonID: SalesPersonID relies upon remote PK_Employee_EmployeeID (HumanResources.Employee)
FK_SalesPerson_SalesTerritory_TerritoryID: TerritoryID relies upon remote PK_SalesTerritory_TerritoryID (Sales.SalesTerritory)

Check constraints:

Check name	Column name	Expresion
CK_SalesPerson_SalesQuota	SalesQuota	([SalesQuota]>(0.00))
CK_SalesPerson_Bonus	Bonus	([Bonus]>=(0.00))
CK_SalesPerson_CommissionPct	CommissionPct	([CommissionPct]>=(0.00))
CK_SalesPerson_SalesYTD	SalesYTD	([SalesYTD]>=(0.00))
CK_SalesPerson_SalesLastYear	SalesLastYear	([SalesLastYear]>=(0.00))

Objects that depend on Sales.SalesPerson
Sales.uSalesOrderHeader
Sales.vSalesPerson
Sales.vSalesPersonSalesByFiscalYears

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SalesPerson](
    [SalesPersonID] [int] NOT NULL,
    [TerritoryID] [int] NULL,
    [SalesQuota] [money] NULL,
    [Bonus] [money] NOT NULL,
    [CommissionPct] [smallmoney] NOT NULL,
    [SalesYTD] [money] NOT NULL,
    [SalesLastYear] [money] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.SalesPersonQuotaHistory

Sales performance tracking.

Columns (5)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	SalesPersonID	int				Sales person identification number. Foreign key to SalesPerson.SalesPersonID.
PK		IX	QuotaDate	datetime				Sales quota date.
			SalesQuota	money				Sales quota amount.
		IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_SalesPersonQuotaHistory_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_SalesPersonQuotaHistory_SalesPersonID_QuotaDate	SalesPersonID	ASC	Yes	CLUSTERED
PK_SalesPersonQuotaHistory_SalesPersonID_QuotaDate	QuotaDate	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_SalesPersonQuotaHistory_SalesPerson_SalesPersonID: SalesPersonID relies upon remote PK_SalesPerson_SalesPersonID (Sales.SalesPerson)

Check constraints:

Check name	Column name	Expresion
CK_SalesPersonQuotaHistory_SalesQuota	SalesQuota	([SalesQuota]>(0.00))

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SalesPersonQuotaHistory](
    [SalesPersonID] [int] NOT NULL,
    [QuotaDate] [datetime] NOT NULL,
    [SalesQuota] [money] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.SalesReason

Lookup table of customer purchase reasons.

Columns (4)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	SalesReasonID	int				Primary key for SalesReason records.
		Name	Name				Sales reason description.
		ReasonType	Name				Category the sales reason belongs to.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
PK_SalesReason_SalesReasonID	SalesReasonID	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SalesReason](
    [SalesReasonID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ReasonType] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.SalesTaxRate

Tax rate lookup table.

Columns (7)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	SalesTaxRateID	int				Primary key for SalesTaxRate records.
	FK	IX	StateProvinceID	int				State, province, or country/region the sales tax applies to.
		IX	TaxType	tinyint				1 = Tax applied to retail transactions, 2 = Tax applied to wholesale transactions, 3 = Tax applied to all sales (retail and wholesale) transactions.
			TaxRate	smallmoney			((0.00))	Tax rate amount.
			Name	Name				Tax rate description.
		IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_SalesTaxRate_rowguid	rowguid	ASC	Yes	NONCLUSTERED
AK_SalesTaxRate_StateProvinceID_TaxType	StateProvinceID	ASC	Yes	NONCLUSTERED
AK_SalesTaxRate_StateProvinceID_TaxType	TaxType	ASC	Yes	NONCLUSTERED
PK_SalesTaxRate_SalesTaxRateID	SalesTaxRateID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_SalesTaxRate_StateProvince_StateProvinceID: StateProvinceID relies upon remote PK_StateProvince_StateProvinceID (Person.StateProvince)

Check constraints:

Check name	Column name	Expresion
CK_SalesTaxRate_TaxType	TaxType	([TaxType]>=(1) AND [TaxType]<=(3))

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SalesTaxRate](
    [SalesTaxRateID] [int] IDENTITY(1,1) NOT NULL,
    [StateProvinceID] [int] NOT NULL,
    [TaxType] [tinyint] NOT NULL,
    [TaxRate] [smallmoney] NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.SalesTerritory

Sales territory lookup table.

Columns (10)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	TerritoryID	int				Primary key for SalesTerritory records.
	IX	Name	Name				Sales territory description
		CountryRegionCode	nvarchar	(3)			ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.
		Group	nvarchar	(50)			Geographic area to which the sales territory belong.
		SalesYTD	money			((0.00))	Sales in the territory year to date.
		SalesLastYear	money			((0.00))	Sales in the territory the previous year.
		CostYTD	money			((0.00))	Business costs in the territory year to date.
		CostLastYear	money			((0.00))	Business costs in the territory the previous year.
	IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_SalesTerritory_Name	Name	ASC	Yes	NONCLUSTERED
AK_SalesTerritory_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_SalesTerritory_TerritoryID	TerritoryID	ASC	Yes	CLUSTERED

Check constraints:

Check name	Column name	Expresion
CK_SalesTerritory_SalesYTD	SalesYTD	([SalesYTD]>=(0.00))
CK_SalesTerritory_SalesLastYear	SalesLastYear	([SalesLastYear]>=(0.00))
CK_SalesTerritory_CostYTD	CostYTD	([CostYTD]>=(0.00))
CK_SalesTerritory_CostLastYear	CostLastYear	([CostLastYear]>=(0.00))

Objects that depend on Sales.SalesTerritory
Sales.uSalesOrderHeader
Sales.vSalesPerson
Sales.vSalesPersonSalesByFiscalYears

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SalesTerritory](
    [TerritoryID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [CountryRegionCode] [nvarchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [Group] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
    [SalesYTD] [money] NOT NULL,
    [SalesLastYear] [money] NOT NULL,
    [CostYTD] [money] NOT NULL,
    [CostLastYear] [money] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.SalesTerritoryHistory

Sales representative transfers to other sales territories.

Columns (6)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	SalesPersonID	int				Primary key for SalesTerritoryHistory records.
PK	FK	IX	TerritoryID	int				Territory identification number. Foreign key to SalesTerritory.SalesTerritoryID.
PK		IX	StartDate	datetime				Date the sales representive started work in the territory.
			EndDate	datetime		Yes		Date the sales representative left work in the territory.
		IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_SalesTerritoryHistory_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_SalesTerritoryHistory_SalesPersonID_StartDate_TerritoryID	SalesPersonID	ASC	Yes	CLUSTERED
PK_SalesTerritoryHistory_SalesPersonID_StartDate_TerritoryID	TerritoryID	ASC	Yes	CLUSTERED
PK_SalesTerritoryHistory_SalesPersonID_StartDate_TerritoryID	StartDate	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_SalesTerritoryHistory_SalesPerson_SalesPersonID: SalesPersonID relies upon remote PK_SalesPerson_SalesPersonID (Sales.SalesPerson)
FK_SalesTerritoryHistory_SalesTerritory_TerritoryID: TerritoryID relies upon remote PK_SalesTerritory_TerritoryID (Sales.SalesTerritory)

Check constraints:

Check name	Column name	Expresion
CK_SalesTerritoryHistory_EndDate	StartDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)
CK_SalesTerritoryHistory_EndDate	EndDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SalesTerritoryHistory](
    [SalesPersonID] [int] NOT NULL,
    [TerritoryID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.ShoppingCartItem

Contains online customer orders until the order is submitted or cancelled.

Columns (6)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	ShoppingCartItemID	int				Primary key for ShoppingCartItem records.
		IX	ShoppingCartID	nvarchar	(50)			Shopping cart identification number.
			Quantity	int			((1))	Product quantity ordered.
	FK	IX	ProductID	int				Product ordered. Foreign key to Product.ProductID.
			DateCreated	datetime			(getdate())	Date the time the record was created.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
IX_ShoppingCartItem_ShoppingCartID_ProductID	ShoppingCartID	ASC		NONCLUSTERED
IX_ShoppingCartItem_ShoppingCartID_ProductID	ProductID	ASC		NONCLUSTERED
PK_ShoppingCartItem_ShoppingCartItemID	ShoppingCartItemID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_ShoppingCartItem_Product_ProductID: ProductID relies upon remote PK_Product_ProductID (Production.Product)

Check constraints:

Check name	Column name	Expresion
CK_ShoppingCartItem_Quantity	Quantity	([Quantity]>=(1))

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[ShoppingCartItem](
    [ShoppingCartItemID] [int] IDENTITY(1,1) NOT NULL,
    [ShoppingCartID] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
    [Quantity] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [DateCreated] [datetime] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.SpecialOffer

Sale discounts lookup table.

Columns (11)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	SpecialOfferID	int				Primary key for SpecialOffer records.
		Description	nvarchar	(255)			Discount description.
		DiscountPct	smallmoney			((0.00))	Discount precentage.
		Type	nvarchar	(50)			Discount type category.
		Category	nvarchar	(50)			Group the discount applies to such as Reseller or Customer.
		StartDate	datetime				Discount start date.
		EndDate	datetime				Discount end date.
		MinQty	int			((0))	Minimum discount percent allowed.
		MaxQty	int		Yes		Maximum discount percent allowed.
	IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
		ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_SpecialOffer_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_SpecialOffer_SpecialOfferID	SpecialOfferID	ASC	Yes	CLUSTERED

Check constraints:

Check name	Column name	Expresion
CK_SpecialOffer_EndDate	StartDate	([EndDate]>=[StartDate])
CK_SpecialOffer_EndDate	EndDate	([EndDate]>=[StartDate])
CK_SpecialOffer_DiscountPct	DiscountPct	([DiscountPct]>=(0.00))
CK_SpecialOffer_MinQty	MinQty	([MinQty]>=(0))
CK_SpecialOffer_MaxQty	MaxQty	([MaxQty]>=(0))

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SpecialOffer](
    [SpecialOfferID] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](255) COLLATE Latin1_General_CS_AS NOT NULL,
    [DiscountPct] [smallmoney] NOT NULL,
    [Type] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
    [Category] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NOT NULL,
    [MinQty] [int] NOT NULL,
    [MaxQty] [int] NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.SpecialOfferProduct

Cross-reference table mapping products to special offer discounts.

Columns (4)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	SpecialOfferID	int				Primary key for SpecialOfferProduct records.
PK	FK	IX	ProductID	int				Product identification number. Foreign key to Product.ProductID.
		IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_SpecialOfferProduct_rowguid	rowguid	ASC	Yes	NONCLUSTERED
IX_SpecialOfferProduct_ProductID	ProductID	ASC		NONCLUSTERED
PK_SpecialOfferProduct_SpecialOfferID_ProductID	SpecialOfferID	ASC	Yes	CLUSTERED
PK_SpecialOfferProduct_SpecialOfferID_ProductID	ProductID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_SpecialOfferProduct_SpecialOffer_SpecialOfferID: SpecialOfferID relies upon remote PK_SpecialOffer_SpecialOfferID (Sales.SpecialOffer)
FK_SpecialOfferProduct_Product_ProductID: ProductID relies upon remote PK_Product_ProductID (Production.Product)

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SpecialOfferProduct](
    [SpecialOfferID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.Store

Customers (resellers) of Adventure Works products.

Columns (6)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	CustomerID	int				Primary key. Foreign key to Customer.CustomerID.
			Name	Name				Name of the store.
	FK	IX	SalesPersonID	int		Yes		ID of the sales person assigned to the customer. Foreign key to SalesPerson.SalesPersonID.
		IX	Demographics	xml		Yes		Demographic informationg about the store such as the number of employees, annual sales and store type.
		IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_Store_rowguid	rowguid	ASC	Yes	NONCLUSTERED
IX_Store_SalesPersonID	SalesPersonID	ASC		NONCLUSTERED
PK_Store_CustomerID	CustomerID	ASC	Yes	CLUSTERED
PXML_Store_Demographics	Demographics	ASC		XML

Foreign keys dependencies:

FK_Store_Customer_CustomerID: CustomerID relies upon remote PK_Customer_CustomerID (Sales.Customer)
FK_Store_SalesPerson_SalesPersonID: SalesPersonID relies upon remote PK_SalesPerson_SalesPersonID (Sales.SalesPerson)

Triggers:

Trigger name: Sales.iStore (Created: 26 Apr 2006)
Trigger type: INSERT
Trigger active: YesTrigger description: AFTER INSERT trigger inserting Store only if the Customer does not exist in the Individual table.

Trigger definition:


CREATE TRIGGER [Sales].[iStore] ON [Sales].[Store] 
AFTER INSERT AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        -- Only allow the Customer to be a Store OR Individual
        IF EXISTS (SELECT * FROM inserted INNER JOIN [Sales].[Individual] 
            ON inserted.[CustomerID] = [Sales].[Individual].[CustomerID]) 
        BEGIN
            -- Rollback any active or uncommittable transactions
            IF @@TRANCOUNT > 0
            BEGIN
                ROLLBACK TRANSACTION;
            END
        END;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

Objects that depend on Sales.Store
Sales.iuIndividual
Sales.vStoreWithDemographics

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[Store](
    [CustomerID] [int] NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [SalesPersonID] [int] NULL,
    [Demographics] [xml](CONTENT [Sales].[StoreSurveySchemaCollection]) NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.StoreContact

Cross-reference table mapping stores and their employees.

Columns (5)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	CustomerID	int				Store identification number. Foreign key to Customer.CustomerID.
PK	FK	IX	ContactID	int				Contact (store employee) identification number. Foreign key to Contact.ContactID.
	FK	IX	ContactTypeID	int				Contact type such as owner or purchasing agent. Foreign key to ContactType.ContactTypeID.
		IX	rowguid	uniqueidentifier			(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
			ModifiedDate	datetime			(getdate())	Date and time the record was last updated.

Indexes

Index name	Column name	Sort direction	Is unique	Index type
AK_StoreContact_rowguid	rowguid	ASC	Yes	NONCLUSTERED
IX_StoreContact_ContactID	ContactID	ASC		NONCLUSTERED
IX_StoreContact_ContactTypeID	ContactTypeID	ASC		NONCLUSTERED
PK_StoreContact_CustomerID_ContactID	CustomerID	ASC	Yes	CLUSTERED
PK_StoreContact_CustomerID_ContactID	ContactID	ASC	Yes	CLUSTERED

Foreign keys dependencies:

FK_StoreContact_Store_CustomerID: CustomerID relies upon remote PK_Store_CustomerID (Sales.Store)
FK_StoreContact_Contact_ContactID: ContactID relies upon remote PK_Contact_ContactID (Person.Contact)
FK_StoreContact_ContactType_ContactTypeID: ContactTypeID relies upon remote PK_ContactType_ContactTypeID (Person.ContactType)

Objects that depend on Sales.StoreContact
Sales.vStoreWithDemographics

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[StoreContact](
    [CustomerID] [int] NOT NULL,
    [ContactID] [int] NOT NULL,
    [ContactTypeID] [int] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

View: HumanResources.vEmployee ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Employee names and addresses.

Fields

Source	Field name	Data type	Nullable	Field description
HumanResources.Employee	EmployeeID	int
Person.Contact	Title	nvarchar (8)	Yes
Person.Contact	FirstName	Name
Person.Contact	MiddleName	Name	Yes
Person.Contact	LastName	Name
Person.Contact	Suffix	nvarchar (10)	Yes
	JobTitle	nvarchar (50)
Person.Contact	Phone	Phone	Yes
Person.Contact	EmailAddress	nvarchar (50)	Yes
Person.Contact	EmailPromotion	int
Person.Address	AddressLine1	nvarchar (60)
Person.Address	AddressLine2	nvarchar (60)	Yes
Person.Address	City	nvarchar (30)
	StateProvinceName	Name
Person.Address	PostalCode	nvarchar (15)
	CountryRegionName	Name
Person.Contact	AdditionalContactInfo	xml	Yes

View definition


CREATE VIEW [HumanResources].[vEmployee] 
AS 
SELECT 
    e.[EmployeeID]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,e.[Title] AS [JobTitle] 
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode]
    ,cr.[Name] AS [CountryRegionName] 
    ,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeAddress] ea 
    ON e.[EmployeeID] = ea.[EmployeeID] 
    INNER JOIN [Person].[Address] a 
    ON ea.[AddressID] = a.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];

View: HumanResources.vEmployeeDepartment ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Returns employee name, title, and current department.

Fields

Source	Field name	Data type	Nullable	Field description
HumanResources.Employee	EmployeeID	int
Person.Contact	Title	nvarchar (8)	Yes
Person.Contact	FirstName	Name
Person.Contact	MiddleName	Name	Yes
Person.Contact	LastName	Name
Person.Contact	Suffix	nvarchar (10)	Yes
	JobTitle	nvarchar (50)
	Department	Name
HumanResources.Department	GroupName	Name
HumanResources.EmployeeDepartmentHistory	StartDate	datetime

View definition


CREATE VIEW [HumanResources].[vEmployeeDepartment] 
AS 
SELECT 
    e.[EmployeeID] 
    ,c.[Title] 
    ,c.[FirstName] 
    ,c.[MiddleName] 
    ,c.[LastName] 
    ,c.[Suffix] 
    ,e.[Title] AS [JobTitle] 
    ,d.[Name] AS [Department] 
    ,d.[GroupName] 
    ,edh.[StartDate] 
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
    ON e.[EmployeeID] = edh.[EmployeeID] 
    INNER JOIN [HumanResources].[Department] d 
    ON edh.[DepartmentID] = d.[DepartmentID] 
WHERE GETDATE() BETWEEN edh.[StartDate] AND ISNULL(edh.[EndDate], GETDATE());

View: HumanResources.vEmployeeDepartmentHistory ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Returns employee name and current and previous departments.

Fields

Source	Field name	Data type	Nullable	Field description
HumanResources.Employee	EmployeeID	int
Person.Contact	Title	nvarchar (8)	Yes
Person.Contact	FirstName	Name
Person.Contact	MiddleName	Name	Yes
Person.Contact	LastName	Name
Person.Contact	Suffix	nvarchar (10)	Yes
	Shift	Name
	Department	Name
HumanResources.Department	GroupName	Name
HumanResources.EmployeeDepartmentHistory	StartDate	datetime
HumanResources.EmployeeDepartmentHistory	EndDate	datetime	Yes

View definition


CREATE VIEW [HumanResources].[vEmployeeDepartmentHistory] 
AS 
SELECT 
    e.[EmployeeID] 
    ,c.[Title] 
    ,c.[FirstName] 
    ,c.[MiddleName] 
    ,c.[LastName] 
    ,c.[Suffix] 
    ,s.[Name] AS [Shift]
    ,d.[Name] AS [Department] 
    ,d.[GroupName] 
    ,edh.[StartDate] 
    ,edh.[EndDate]
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
    ON e.[EmployeeID] = edh.[EmployeeID] 
    INNER JOIN [HumanResources].[Department] d 
    ON edh.[DepartmentID] = d.[DepartmentID] 
    INNER JOIN [HumanResources].[Shift] s
    ON s.[ShiftID] = edh.[ShiftID];

View: HumanResources.vJobCandidate ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Job candidate names and resumes.

Fields

Source	Field name	Data type	Nullable	Field description
HumanResources.JobCandidate	JobCandidateID	int
HumanResources.JobCandidate	EmployeeID	int	Yes
	Name.Prefix	nvarchar (30)	Yes
	Name.First	nvarchar (30)	Yes
	Name.Middle	nvarchar (30)	Yes
	Name.Last	nvarchar (30)	Yes
	Name.Suffix	nvarchar (30)	Yes
	Skills	nvarchar	Yes
	Addr.Type	nvarchar (30)	Yes
	Addr.Loc.CountryRegion	nvarchar (100)	Yes
	Addr.Loc.State	nvarchar (100)	Yes
	Addr.Loc.City	nvarchar (100)	Yes
	Addr.PostalCode	nvarchar (20)	Yes
	EMail	nvarchar	Yes
	WebSite	nvarchar	Yes
HumanResources.JobCandidate	ModifiedDate	datetime

View definition


CREATE VIEW [HumanResources].[vJobCandidate] 
AS 
SELECT 
    jc.[JobCandidateID] 
    ,jc.[EmployeeID] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Name/Name.Prefix)[1]', 'nvarchar(30)') AS [Name.Prefix] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
        (/Resume/Name/Name.First)[1]', 'nvarchar(30)') AS [Name.First] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Name/Name.Middle)[1]', 'nvarchar(30)') AS [Name.Middle] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Name/Name.Last)[1]', 'nvarchar(30)') AS [Name.Last] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Name/Name.Suffix)[1]', 'nvarchar(30)') AS [Name.Suffix] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Skills)[1]', 'nvarchar(max)') AS [Skills] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.Type)[1]', 'nvarchar(30)') AS [Addr.Type]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(100)') AS [Addr.Loc.CountryRegion]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.Location/Location/Loc.State)[1]', 'nvarchar(100)') AS [Addr.Loc.State]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.Location/Location/Loc.City)[1]', 'nvarchar(100)') AS [Addr.Loc.City]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.PostalCode)[1]', 'nvarchar(20)') AS [Addr.PostalCode]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/EMail)[1]', 'nvarchar(max)') AS [EMail] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/WebSite)[1]', 'nvarchar(max)') AS [WebSite] 
    ,jc.[ModifiedDate] 
FROM [HumanResources].[JobCandidate] jc 
CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
    /Resume') AS Resume(ref);

Related objects
HumanResources.JobCandidate

View: HumanResources.vJobCandidateEducation ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Displays the content from each education related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed.

Fields

Source	Field name	Data type	Nullable	Field description
HumanResources.JobCandidate	JobCandidateID	int
	Edu.Level	nvarchar	Yes
	Edu.StartDate	datetime	Yes
	Edu.EndDate	datetime	Yes
	Edu.Degree	nvarchar (50)	Yes
	Edu.Major	nvarchar (50)	Yes
	Edu.Minor	nvarchar (50)	Yes
	Edu.GPA	nvarchar (5)	Yes
	Edu.GPAScale	nvarchar (5)	Yes
	Edu.School	nvarchar (100)	Yes
	Edu.Loc.CountryRegion	nvarchar (100)	Yes
	Edu.Loc.State	nvarchar (100)	Yes
	Edu.Loc.City	nvarchar (100)	Yes

View definition


CREATE VIEW [HumanResources].[vJobCandidateEducation] 
AS 
SELECT 
    jc.[JobCandidateID] 
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Level)[1]', 'nvarchar(max)') AS [Edu.Level]
    ,CONVERT(datetime, REPLACE([Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.StartDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Edu.StartDate] 
    ,CONVERT(datetime, REPLACE([Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.EndDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Edu.EndDate] 
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Degree)[1]', 'nvarchar(50)') AS [Edu.Degree]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Major)[1]', 'nvarchar(50)') AS [Edu.Major]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Minor)[1]', 'nvarchar(50)') AS [Edu.Minor]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.GPA)[1]', 'nvarchar(5)') AS [Edu.GPA]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.GPAScale)[1]', 'nvarchar(5)') AS [Edu.GPAScale]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.School)[1]', 'nvarchar(100)') AS [Edu.School]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(100)') AS [Edu.Loc.CountryRegion]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Location/Location/Loc.State)[1]', 'nvarchar(100)') AS [Edu.Loc.State]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Location/Location/Loc.City)[1]', 'nvarchar(100)') AS [Edu.Loc.City]
FROM [HumanResources].[JobCandidate] jc 
CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
    /Resume/Education') AS [Education](ref);

Related objects
HumanResources.JobCandidate

View: HumanResources.vJobCandidateEmployment ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Displays the content from each employement history related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed.

Fields

Source	Field name	Data type	Nullable	Field description
HumanResources.JobCandidate	JobCandidateID	int
	Emp.StartDate	datetime	Yes
	Emp.EndDate	datetime	Yes
	Emp.OrgName	nvarchar (100)	Yes
	Emp.JobTitle	nvarchar (100)	Yes
	Emp.Responsibility	nvarchar	Yes
	Emp.FunctionCategory	nvarchar	Yes
	Emp.IndustryCategory	nvarchar	Yes
	Emp.Loc.CountryRegion	nvarchar	Yes
	Emp.Loc.State	nvarchar	Yes
	Emp.Loc.City	nvarchar	Yes

View definition


CREATE VIEW [HumanResources].[vJobCandidateEmployment] 
AS 
SELECT 
    jc.[JobCandidateID] 
    ,CONVERT(datetime, REPLACE([Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.StartDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Emp.StartDate] 
    ,CONVERT(datetime, REPLACE([Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.EndDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Emp.EndDate] 
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.OrgName)[1]', 'nvarchar(100)') AS [Emp.OrgName]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.JobTitle)[1]', 'nvarchar(100)') AS [Emp.JobTitle]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.Responsibility)[1]', 'nvarchar(max)') AS [Emp.Responsibility]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.FunctionCategory)[1]', 'nvarchar(max)') AS [Emp.FunctionCategory]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.IndustryCategory)[1]', 'nvarchar(max)') AS [Emp.IndustryCategory]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(max)') AS [Emp.Loc.CountryRegion]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.Location/Location/Loc.State)[1]', 'nvarchar(max)') AS [Emp.Loc.State]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.Location/Location/Loc.City)[1]', 'nvarchar(max)') AS [Emp.Loc.City]
FROM [HumanResources].[JobCandidate] jc 
CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
    /Resume/Employment') AS Employment(ref);

Related objects
HumanResources.JobCandidate

View: Person.vAdditionalContactInfo ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.

Fields

Source	Field name	Data type	Nullable	Field description
Person.Contact	ContactID	int
Person.Contact	FirstName	Name
Person.Contact	MiddleName	Name	Yes
Person.Contact	LastName	Name
	TelephoneNumber	nvarchar (50)	Yes
	TelephoneSpecialInstructions	nvarchar	Yes
	Street	nvarchar (50)	Yes
	City	nvarchar (50)	Yes
	StateProvince	nvarchar (50)	Yes
	PostalCode	nvarchar (50)	Yes
	CountryRegion	nvarchar (50)	Yes
	HomeAddressSpecialInstructions	nvarchar	Yes
	EMailAddress	nvarchar (128)	Yes
	EMailSpecialInstructions	nvarchar	Yes
	EMailTelephoneNumber	nvarchar (50)	Yes
Person.Contact	rowguid	uniqueidentifier
Person.Contact	ModifiedDate	datetime

View definition


CREATE VIEW [Person].[vAdditionalContactInfo] 
AS 
SELECT 
    [ContactID] 
    ,[FirstName]
    ,[MiddleName]
    ,[LastName]
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:telephoneNumber)[1]/act:number', 'nvarchar(50)') AS [TelephoneNumber] 
    ,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:telephoneNumber/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [TelephoneSpecialInstructions] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
        (act:homePostalAddress/act:Street)[1]', 'nvarchar(50)') AS [Street] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:City)[1]', 'nvarchar(50)') AS [City] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:StateProvince)[1]', 'nvarchar(50)') AS [StateProvince] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:PostalCode)[1]', 'nvarchar(50)') AS [PostalCode] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:CountryRegion)[1]', 'nvarchar(50)') AS [CountryRegion] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:SpecialInstructions/text())[1]', 'nvarchar(max)') AS [HomeAddressSpecialInstructions] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:eMail/act:eMailAddress)[1]', 'nvarchar(128)') AS [EMailAddress] 
    ,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:eMail/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [EMailSpecialInstructions] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:eMail/act:SpecialInstructions/act:telephoneNumber/act:number)[1]', 'nvarchar(50)') AS [EMailTelephoneNumber] 
    ,[rowguid] 
    ,[ModifiedDate]
FROM [Person].[Contact]
OUTER APPLY [AdditionalContactInfo].nodes(
    'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
    /ci:AdditionalContactInfo') AS ContactInfo(ref) 
WHERE [AdditionalContactInfo] IS NOT NULL;

Related objects
Person.Contact

View: Person.vStateProvinceCountryRegion ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Joins StateProvince table with CountryRegion table.

Fields

Source	Field name	Data type	Nullable	Field description
Person.StateProvince	StateProvinceID	int		Clustered index on the view vStateProvinceCountryRegion.
Person.StateProvince	StateProvinceCode	nchar (3)
Person.StateProvince	IsOnlyStateProvinceFlag	Flag
	StateProvinceName	Name
Person.StateProvince	TerritoryID	int
Person.CountryRegion	CountryRegionCode	nvarchar (3)
	CountryRegionName	Name

View definition


CREATE VIEW [Person].[vStateProvinceCountryRegion] 
WITH SCHEMABINDING 
AS 
SELECT 
    sp.[StateProvinceID] 
    ,sp.[StateProvinceCode] 
    ,sp.[IsOnlyStateProvinceFlag] 
    ,sp.[Name] AS [StateProvinceName] 
    ,sp.[TerritoryID] 
    ,cr.[CountryRegionCode] 
    ,cr.[Name] AS [CountryRegionName]
FROM [Person].[StateProvince] sp 
    INNER JOIN [Person].[CountryRegion] cr 
    ON sp.[CountryRegionCode] = cr.[CountryRegionCode];

Related objects
Person.CountryRegion
Person.StateProvince

View: Production.vProductAndDescription ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Product names and descriptions. Product descriptions are provided in multiple languages.

Fields

Source	Field name	Data type	Nullable	Field description
Production.Product	ProductID	int		Clustered index on the view vProductAndDescription.
Production.Product	Name	Name
	ProductModel	Name
Production.ProductModelProductDescriptionCulture	CultureID	nchar (6)
Production.ProductDescription	Description	nvarchar (400)

View definition


CREATE VIEW [Production].[vProductAndDescription] 
WITH SCHEMABINDING 
AS 
-- View (indexed or standard) to display products and product descriptions by language.
SELECT 
    p.[ProductID] 
    ,p.[Name] 
    ,pm.[Name] AS [ProductModel] 
    ,pmx.[CultureID] 
    ,pd.[Description] 
FROM [Production].[Product] p 
    INNER JOIN [Production].[ProductModel] pm 
    ON p.[ProductModelID] = pm.[ProductModelID] 
    INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx 
    ON pm.[ProductModelID] = pmx.[ProductModelID] 
    INNER JOIN [Production].[ProductDescription] pd 
    ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID];

View: Production.vProductModelCatalogDescription ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Displays the content from each element in the xml column CatalogDescription for each product in the Production.ProductModel table that has catalog data.

Fields

Source	Field name	Data type	Nullable	Field description
Production.ProductModel	ProductModelID	int
Production.ProductModel	Name	Name
	Summary	nvarchar	Yes
	Manufacturer	nvarchar	Yes
	Copyright	nvarchar (30)	Yes
	ProductURL	nvarchar (256)	Yes
	WarrantyPeriod	nvarchar (256)	Yes
	WarrantyDescription	nvarchar (256)	Yes
	NoOfYears	nvarchar (256)	Yes
	MaintenanceDescription	nvarchar (256)	Yes
	Wheel	nvarchar (256)	Yes
	Saddle	nvarchar (256)	Yes
	Pedal	nvarchar (256)	Yes
	BikeFrame	nvarchar	Yes
	Crankset	nvarchar (256)	Yes
	PictureAngle	nvarchar (256)	Yes
	PictureSize	nvarchar (256)	Yes
	ProductPhotoID	nvarchar (256)	Yes
	Material	nvarchar (256)	Yes
	Color	nvarchar (256)	Yes
	ProductLine	nvarchar (256)	Yes
	Style	nvarchar (256)	Yes
	RiderExperience	nvarchar (1024)	Yes
Production.ProductModel	rowguid	uniqueidentifier
Production.ProductModel	ModifiedDate	datetime

View definition


CREATE VIEW [Production].[vProductModelCatalogDescription] 
AS 
SELECT 
    [ProductModelID] 
    ,[Name] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace html="http://www.w3.org/1999/xhtml"; 
        (/p1:ProductDescription/p1:Summary/html:p)[1]', 'nvarchar(max)') AS [Summary] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Manufacturer/p1:Name)[1]', 'nvarchar(max)') AS [Manufacturer] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Manufacturer/p1:Copyright)[1]', 'nvarchar(30)') AS [Copyright] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Manufacturer/p1:ProductURL)[1]', 'nvarchar(256)') AS [ProductURL] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; 
        (/p1:ProductDescription/p1:Features/wm:Warranty/wm:WarrantyPeriod)[1]', 'nvarchar(256)') AS [WarrantyPeriod] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; 
        (/p1:ProductDescription/p1:Features/wm:Warranty/wm:Description)[1]', 'nvarchar(256)') AS [WarrantyDescription] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; 
        (/p1:ProductDescription/p1:Features/wm:Maintenance/wm:NoOfYears)[1]', 'nvarchar(256)') AS [NoOfYears] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; 
        (/p1:ProductDescription/p1:Features/wm:Maintenance/wm:Description)[1]', 'nvarchar(256)') AS [MaintenanceDescription] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:wheel)[1]', 'nvarchar(256)') AS [Wheel] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:saddle)[1]', 'nvarchar(256)') AS [Saddle] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:pedal)[1]', 'nvarchar(256)') AS [Pedal] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:BikeFrame)[1]', 'nvarchar(max)') AS [BikeFrame] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:crankset)[1]', 'nvarchar(256)') AS [Crankset] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Picture/p1:Angle)[1]', 'nvarchar(256)') AS [PictureAngle] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Picture/p1:Size)[1]', 'nvarchar(256)') AS [PictureSize] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Picture/p1:ProductPhotoID)[1]', 'nvarchar(256)') AS [ProductPhotoID] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/Material)[1]', 'nvarchar(256)') AS [Material] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/Color)[1]', 'nvarchar(256)') AS [Color] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/ProductLine)[1]', 'nvarchar(256)') AS [ProductLine] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/Style)[1]', 'nvarchar(256)') AS [Style] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/RiderExperience)[1]', 'nvarchar(1024)') AS [RiderExperience] 
    ,[rowguid] 
    ,[ModifiedDate]
FROM [Production].[ProductModel] 
WHERE [CatalogDescription] IS NOT NULL;

Related objects
Production.ProductModel

View: Production.vProductModelInstructions ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Displays the content from each element in the xml column Instructions for each product in the Production.ProductModel table that has manufacturing instructions.

Fields

Source	Field name	Data type	Nullable	Field description
Production.ProductModel	ProductModelID	int
Production.ProductModel	Name	Name
Production.ProductModel	Instructions	nvarchar	Yes
	LocationID	int	Yes
	SetupHours	decimal (9.4)	Yes
	MachineHours	decimal (9.4)	Yes
	LaborHours	decimal (9.4)	Yes
	LotSize	int	Yes
	Step	nvarchar (1024)	Yes
Production.ProductModel	rowguid	uniqueidentifier
Production.ProductModel	ModifiedDate	datetime

View definition


CREATE VIEW [Production].[vProductModelInstructions] 
AS 
SELECT 
    [ProductModelID] 
    ,[Name] 
    ,[Instructions].value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
        (/root/text())[1]', 'nvarchar(max)') AS [Instructions] 
    ,[MfgInstructions].ref.value('@LocationID[1]', 'int') AS [LocationID] 
    ,[MfgInstructions].ref.value('@SetupHours[1]', 'decimal(9, 4)') AS [SetupHours] 
    ,[MfgInstructions].ref.value('@MachineHours[1]', 'decimal(9, 4)') AS [MachineHours] 
    ,[MfgInstructions].ref.value('@LaborHours[1]', 'decimal(9, 4)') AS [LaborHours] 
    ,[MfgInstructions].ref.value('@LotSize[1]', 'int') AS [LotSize] 
    ,[Steps].ref.value('string(.)[1]', 'nvarchar(1024)') AS [Step] 
    ,[rowguid] 
    ,[ModifiedDate]
FROM [Production].[ProductModel] 
CROSS APPLY [Instructions].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
    /root/Location') MfgInstructions(ref)
CROSS APPLY [MfgInstructions].ref.nodes('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
    step') Steps(ref);

Related objects
Production.ProductModel

View: Purchasing.vVendor ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Vendor (company) names and addresses and the names of vendor employees to contact.

Fields

Source	Field name	Data type	Nullable	Field description
Purchasing.Vendor	VendorID	int
Purchasing.Vendor	Name	Name
	ContactType	Name
Person.Contact	Title	nvarchar (8)	Yes
Person.Contact	FirstName	Name
Person.Contact	MiddleName	Name	Yes
Person.Contact	LastName	Name
Person.Contact	Suffix	nvarchar (10)	Yes
Person.Contact	Phone	Phone	Yes
Person.Contact	EmailAddress	nvarchar (50)	Yes
Person.Contact	EmailPromotion	int
Person.Address	AddressLine1	nvarchar (60)
Person.Address	AddressLine2	nvarchar (60)	Yes
Person.Address	City	nvarchar (30)
	StateProvinceName	Name
Person.Address	PostalCode	nvarchar (15)
	CountryRegionName	Name

View definition


CREATE VIEW [Purchasing].[vVendor] AS 
SELECT 
    v.[VendorID]
    ,v.[Name]
    ,ct.[Name] AS [ContactType]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
FROM [Purchasing].[Vendor] v
    INNER JOIN [Purchasing].[VendorContact] vc 
    ON vc.[VendorID] = v.[VendorID]
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = vc.[ContactID]
    INNER JOIN [Person].[ContactType] ct 
    ON vc.[ContactTypeID] = ct.[ContactTypeID]
    INNER JOIN [Purchasing].[VendorAddress] va 
    ON va.[VendorID] = v.[VendorID]
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = va.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];

View: Sales.vIndividualCustomer ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Individual customers (names and addresses) that purchase Adventure Works Cycles products online.

Fields

Source	Field name	Data type	Nullable	Field description
Sales.Customer	CustomerID	int
Person.Contact	Title	nvarchar (8)	Yes
Person.Contact	FirstName	Name
Person.Contact	MiddleName	Name	Yes
Person.Contact	LastName	Name
Person.Contact	Suffix	nvarchar (10)	Yes
Person.Contact	Phone	Phone	Yes
Person.Contact	EmailAddress	nvarchar (50)	Yes
Person.Contact	EmailPromotion	int
	AddressType	Name
Person.Address	AddressLine1	nvarchar (60)
Person.Address	AddressLine2	nvarchar (60)	Yes
Person.Address	City	nvarchar (30)
	StateProvinceName	Name
Person.Address	PostalCode	nvarchar (15)
	CountryRegionName	Name
Sales.Individual	Demographics	xml	Yes

View definition


CREATE VIEW [Sales].[vIndividualCustomer] 
AS 
SELECT 
    i.[CustomerID]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,at.[Name] AS [AddressType]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
    ,i.[Demographics]
FROM [Sales].[Individual] i
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = i.[ContactID]
    INNER JOIN [Sales].[CustomerAddress] ca 
    ON ca.[CustomerID] = i.[CustomerID]
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = ca.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    INNER JOIN [Person].[AddressType] at 
    ON ca.[AddressTypeID] = at.[AddressTypeID]
WHERE i.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID] 
    FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'I');

View: Sales.vIndividualDemographics ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table.

Fields

Source	Field name	Data type	Nullable	Field description
Sales.Individual	CustomerID	int
	TotalPurchaseYTD	money	Yes
	DateFirstPurchase	datetime	Yes
	BirthDate	datetime	Yes
	MaritalStatus	nvarchar (1)	Yes
	YearlyIncome	nvarchar (30)	Yes
	Gender	nvarchar (1)	Yes
	TotalChildren	int	Yes
	NumberChildrenAtHome	int	Yes
	Education	nvarchar (30)	Yes
	Occupation	nvarchar (30)	Yes
	HomeOwnerFlag	bit	Yes
	NumberCarsOwned	int	Yes

View definition


CREATE VIEW [Sales].[vIndividualDemographics] 
AS 
SELECT 
    i.[CustomerID] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        TotalPurchaseYTD[1]', 'money') AS [TotalPurchaseYTD] 
    ,CONVERT(datetime, REPLACE([IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        DateFirstPurchase[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [DateFirstPurchase] 
    ,CONVERT(datetime, REPLACE([IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        BirthDate[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [BirthDate] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        MaritalStatus[1]', 'nvarchar(1)') AS [MaritalStatus] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        YearlyIncome[1]', 'nvarchar(30)') AS [YearlyIncome] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        Gender[1]', 'nvarchar(1)') AS [Gender] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        TotalChildren[1]', 'integer') AS [TotalChildren] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        NumberChildrenAtHome[1]', 'integer') AS [NumberChildrenAtHome] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        Education[1]', 'nvarchar(30)') AS [Education] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        Occupation[1]', 'nvarchar(30)') AS [Occupation] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        HomeOwnerFlag[1]', 'bit') AS [HomeOwnerFlag] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        NumberCarsOwned[1]', 'integer') AS [NumberCarsOwned] 
FROM [Sales].[Individual] i 
CROSS APPLY i.[Demographics].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
    /IndividualSurvey') AS [IndividualSurvey](ref) 
WHERE [Demographics] IS NOT NULL;

Related objects
Sales.Individual

View: Sales.vSalesPerson ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Sales representiatives (names and addresses) and their sales-related information.

Fields

Source	Field name	Data type	Nullable	Field description
Sales.SalesPerson	SalesPersonID	int
Person.Contact	Title	nvarchar (8)	Yes
Person.Contact	FirstName	Name
Person.Contact	MiddleName	Name	Yes
Person.Contact	LastName	Name
Person.Contact	Suffix	nvarchar (10)	Yes
	JobTitle	nvarchar (50)
Person.Contact	Phone	Phone	Yes
Person.Contact	EmailAddress	nvarchar (50)	Yes
Person.Contact	EmailPromotion	int
Person.Address	AddressLine1	nvarchar (60)
Person.Address	AddressLine2	nvarchar (60)	Yes
Person.Address	City	nvarchar (30)
	StateProvinceName	Name
Person.Address	PostalCode	nvarchar (15)
	CountryRegionName	Name
	TerritoryName	Name	Yes
	TerritoryGroup	nvarchar (50)	Yes
Sales.SalesPerson	SalesQuota	money	Yes
Sales.SalesPerson	SalesYTD	money
Sales.SalesPerson	SalesLastYear	money

View definition


CREATE VIEW [Sales].[vSalesPerson] 
AS 
SELECT 
    s.[SalesPersonID]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,[JobTitle] = e.[Title]
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
    ,[TerritoryName] = st.[Name]
    ,[TerritoryGroup] = st.[Group]
    ,s.[SalesQuota]
    ,s.[SalesYTD]
    ,s.[SalesLastYear]
FROM [Sales].[SalesPerson] s
    INNER JOIN [HumanResources].[Employee] e 
    ON e.[EmployeeID] = s.[SalesPersonID]
    LEFT OUTER JOIN [Sales].[SalesTerritory] st 
    ON st.[TerritoryID] = s.[TerritoryID]
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeAddress] ea 
    ON e.[EmployeeID] = ea.[EmployeeID] 
    INNER JOIN [Person].[Address] a 
    ON ea.[AddressID] = a.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];

View: Sales.vSalesPersonSalesByFiscalYears ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Uses PIVOT to return aggregated sales information for each sales representative.

Fields

Source	Field name	Data type	Nullable	Field description
Sales.SalesOrderHeader	SalesPersonID	int	Yes
	FullName	nvarchar (152)	Yes
HumanResources.Employee	Title	nvarchar (50)
	SalesTerritory	Name
	2002	money	Yes
	2003	money	Yes
	2004	money	Yes

View definition


CREATE VIEW [Sales].[vSalesPersonSalesByFiscalYears] 
AS 
SELECT 
    pvt.[SalesPersonID]
    ,pvt.[FullName]
    ,pvt.[Title]
    ,pvt.[SalesTerritory]
    ,pvt.[2002]
    ,pvt.[2003]
    ,pvt.[2004] 
FROM (SELECT 
        soh.[SalesPersonID]
        ,c.[FirstName] + ' ' + COALESCE(c.[MiddleName], '') + ' ' + c.[LastName] AS [FullName]
        ,e.[Title]
        ,st.[Name] AS [SalesTerritory]
        ,soh.[SubTotal]
        ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
    FROM [Sales].[SalesPerson] sp 
        INNER JOIN [Sales].[SalesOrderHeader] soh 
        ON sp.[SalesPersonID] = soh.[SalesPersonID]
        INNER JOIN [Sales].[SalesTerritory] st 
        ON sp.[TerritoryID] = st.[TerritoryID] 
        INNER JOIN [HumanResources].[Employee] e 
        ON soh.[SalesPersonID] = e.[EmployeeID] 
        INNER JOIN [Person].[Contact] c 
        ON e.[ContactID] = c.ContactID 
    ) AS soh 
PIVOT 
(
    SUM([SubTotal]) 
    FOR [FiscalYear] 
    IN ([2002], [2003], [2004])
) AS pvt;

View: Sales.vStoreWithDemographics ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Stores (names and addresses) that sell Adventure Works Cycles products to consumers.

Fields

Source	Field name	Data type	Nullable	Field description
Sales.StoreContact	CustomerID	int
Person.AddressType	Name	Name
	ContactType	Name
Person.Contact	Title	nvarchar (8)	Yes
Person.Contact	FirstName	Name
Person.Contact	MiddleName	Name	Yes
Person.Contact	LastName	Name
Person.Contact	Suffix	nvarchar (10)	Yes
Person.Contact	Phone	Phone	Yes
Person.Contact	EmailAddress	nvarchar (50)	Yes
Person.Contact	EmailPromotion	int
	AddressType	Name
Person.Address	AddressLine1	nvarchar (60)
Person.Address	AddressLine2	nvarchar (60)	Yes
Person.Address	City	nvarchar (30)
	StateProvinceName	Name
Person.Address	PostalCode	nvarchar (15)
	CountryRegionName	Name
	AnnualSales	money	Yes
	AnnualRevenue	money	Yes
	BankName	nvarchar (50)	Yes
	BusinessType	nvarchar (5)	Yes
	YearOpened	int	Yes
	Specialty	nvarchar (50)	Yes
	SquareFeet	int	Yes
	Brands	nvarchar (30)	Yes
	Internet	nvarchar (30)	Yes
	NumberEmployees	int	Yes

View definition


CREATE VIEW [Sales].[vStoreWithDemographics] AS 
SELECT 
    s.[CustomerID] 
    ,s.[Name] 
    ,ct.[Name] AS [ContactType] 
    ,c.[Title] 
    ,c.[FirstName] 
    ,c.[MiddleName] 
    ,c.[LastName] 
    ,c.[Suffix] 
    ,c.[Phone] 
    ,c.[EmailAddress] 
    ,c.[EmailPromotion] 
    ,at.[Name] AS [AddressType]
    ,a.[AddressLine1] 
    ,a.[AddressLine2] 
    ,a.[City] 
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode] 
    ,cr.[Name] AS [CountryRegionName] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/AnnualSales)[1]', 'money') AS [AnnualSales] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/AnnualRevenue)[1]', 'money') AS [AnnualRevenue] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/BankName)[1]', 'nvarchar(50)') AS [BankName] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/BusinessType)[1]', 'nvarchar(5)') AS [BusinessType] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/YearOpened)[1]', 'integer') AS [YearOpened] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Specialty)[1]', 'nvarchar(50)') AS [Specialty] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/SquareFeet)[1]', 'integer') AS [SquareFeet] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Brands)[1]', 'nvarchar(30)') AS [Brands] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Internet)[1]', 'nvarchar(30)') AS [Internet] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/NumberEmployees)[1]', 'integer') AS [NumberEmployees] 
FROM [Sales].[Store] s
    INNER JOIN [Sales].[StoreContact] sc 
    ON sc.[CustomerID] = s.[CustomerID]
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = sc.[ContactID]
    INNER JOIN [Person].[ContactType] ct 
    ON sc.[ContactTypeID] = ct.[ContactTypeID]
    INNER JOIN [Sales].[CustomerAddress] ca 
    ON ca.[CustomerID] = s.[CustomerID]
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = ca.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    INNER JOIN [Person].[AddressType] at 
    ON ca.[AddressTypeID] = at.[AddressTypeID]
WHERE s.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID] 
    FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'S');

Stored procedure: dbo.uspGetBillOfMaterials ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.

Parameters

Direction	Parameter name	Data type	Description
IN	@StartProductID	int	Input parameter for the stored procedure uspGetBillOfMaterials. Enter a valid ProductID from the Production.Product table.
IN	@CheckDate	datetime	Input parameter for the stored procedure uspGetBillOfMaterials used to eliminate components not used after that date. Enter a valid date.

Stored procedure definition


CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
    @StartProductID [int],
    @CheckDate [datetime]
AS
BEGIN
    SET NOCOUNT ON;

    -- Use recursive query to generate a multi-level Bill of Material (i.e. all level 1 
    -- components of a level 0 assembly, all level 2 components of a level 1 assembly)
    -- The CheckDate eliminates any components that are no longer used in the product on this date.
    WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
        FROM [Production].[BillOfMaterials] b
            INNER JOIN [Production].[Product] p 
            ON b.[ComponentID] = p.[ProductID] 
        WHERE b.[ProductAssemblyID] = @StartProductID 
            AND @CheckDate >= b.[StartDate] 
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        UNION ALL
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [BOM_cte] cte
            INNER JOIN [Production].[BillOfMaterials] b 
            ON b.[ProductAssemblyID] = cte.[ComponentID]
            INNER JOIN [Production].[Product] p 
            ON b.[ComponentID] = p.[ProductID] 
        WHERE @CheckDate >= b.[StartDate] 
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        )
    -- Outer select from the CTE
    SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
    FROM [BOM_cte] b
    GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
    ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
    OPTION (MAXRECURSION 25) 
END;

Related objects
Production.BillOfMaterials
Production.Product

Stored procedure: dbo.uspGetEmployeeManagers ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Stored procedure using a recursive query to return the direct and indirect managers of the specified employee.

Parameters

Direction	Parameter name	Data type	Description
IN	@EmployeeID	int	Input parameter for the stored procedure uspGetEmployeeManagers. Enter a valid EmployeeID from the HumanResources.Employee table.

Stored procedure definition


CREATE PROCEDURE [dbo].[uspGetEmployeeManagers]
    @EmployeeID [int]
AS
BEGIN
    SET NOCOUNT ON;

    -- Use recursive query to list out all Employees required for a particular Manager
    WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], [Title], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], 0 -- Get the initial Employee
        FROM [HumanResources].[Employee] e 
            INNER JOIN [Person].[Contact] c 
            ON e.[ContactID] = c.[ContactID]
        WHERE e.[EmployeeID] = @EmployeeID
        UNION ALL
        SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [HumanResources].[Employee] e 
            INNER JOIN [EMP_cte]
            ON e.[EmployeeID] = [EMP_cte].[ManagerID]
            INNER JOIN [Person].[Contact] c 
            ON e.[ContactID] = c.[ContactID]
    )
    -- Join back to Employee to return the manager name 
    SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName], 
        [EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName'  -- Outer select from the CTE
    FROM [EMP_cte] 
        INNER JOIN [HumanResources].[Employee] e 
        ON [EMP_cte].[ManagerID] = e.[EmployeeID]
        INNER JOIN [Person].[Contact] c 
        ON e.[ContactID] = c.[ContactID]
    ORDER BY [RecursionLevel], [ManagerID], [EmployeeID]
    OPTION (MAXRECURSION 25) 
END;

Related objects
Person.Contact
HumanResources.Employee

Stored procedure: dbo.uspGetManagerEmployees ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Stored procedure using a recursive query to return the direct and indirect employees of the specified manager.

Parameters

Direction	Parameter name	Data type	Description
IN	@ManagerID	int	Input parameter for the stored procedure uspGetManagerEmployees. Enter a valid ManagerID from the HumanResources.Employee table.

Stored procedure definition


CREATE PROCEDURE [dbo].[uspGetManagerEmployees]
    @ManagerID [int]
AS
BEGIN
    SET NOCOUNT ON;

    -- Use recursive query to list out all Employees required for a particular Manager
    WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], 0 -- Get the initial list of Employees for Manager n
        FROM [HumanResources].[Employee] e 
            INNER JOIN [Person].[Contact] c 
            ON e.[ContactID] = c.[ContactID]
        WHERE [ManagerID] = @ManagerID
        UNION ALL
        SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [HumanResources].[Employee] e 
            INNER JOIN [EMP_cte]
            ON e.[ManagerID] = [EMP_cte].[EmployeeID]
            INNER JOIN [Person].[Contact] c 
            ON e.[ContactID] = c.[ContactID]
        )
    -- Join back to Employee to return the manager name 
    SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName',
        [EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName] -- Outer select from the CTE
    FROM [EMP_cte] 
        INNER JOIN [HumanResources].[Employee] e 
        ON [EMP_cte].[ManagerID] = e.[EmployeeID]
        INNER JOIN [Person].[Contact] c 
        ON e.[ContactID] = c.[ContactID]
    ORDER BY [RecursionLevel], [ManagerID], [EmployeeID]
    OPTION (MAXRECURSION 25) 
END;

Related objects
Person.Contact
HumanResources.Employee

Stored procedure: dbo.uspGetWhereUsedProductID ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.

Parameters

Direction	Parameter name	Data type	Description
IN	@StartProductID	int	Input parameter for the stored procedure uspGetWhereUsedProductID. Enter a valid ProductID from the Production.Product table.
IN	@CheckDate	datetime	Input parameter for the stored procedure uspGetWhereUsedProductID used to eliminate components not used after that date. Enter a valid date.

Stored procedure definition


CREATE PROCEDURE [dbo].[uspGetWhereUsedProductID]
    @StartProductID [int],
    @CheckDate [datetime]
AS
BEGIN
    SET NOCOUNT ON;

    --Use recursive query to generate a multi-level Bill of Material (i.e. all level 1 components of a level 0 assembly, all level 2 components of a level 1 assembly)
    WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
        FROM [Production].[BillOfMaterials] b
            INNER JOIN [Production].[Product] p 
            ON b.[ProductAssemblyID] = p.[ProductID] 
        WHERE b.[ComponentID] = @StartProductID 
            AND @CheckDate >= b.[StartDate] 
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        UNION ALL
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [BOM_cte] cte
            INNER JOIN [Production].[BillOfMaterials] b 
            ON cte.[ProductAssemblyID] = b.[ComponentID]
            INNER JOIN [Production].[Product] p 
            ON b.[ProductAssemblyID] = p.[ProductID] 
        WHERE @CheckDate >= b.[StartDate] 
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        )
    -- Outer select from the CTE
    SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
    FROM [BOM_cte] b
    GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
    ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
    OPTION (MAXRECURSION 25) 
END;

Related objects
Production.BillOfMaterials
Production.Product

Stored procedure: dbo.uspLogError ( Created: 26 Apr 2006 Last altered: 26 Oct 2008 )

Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.

Parameters

Direction	Parameter name	Data type	Description
INOUT	@ErrorLogID	int	Output parameter for the stored procedure uspLogError. Contains the ErrorLogID value corresponding to the row inserted by uspLogError in the ErrorLog table.

Stored procedure definition


-- uspLogError logs error information in the ErrorLog table about the 
-- error that caused execution to jump to the CATCH block of a 
-- TRY...CATCH construct. This should be executed from within the scope 
-- of a CATCH block otherwise it will return without inserting error 
-- information. 
CREATE PROCEDURE [dbo].[uspLogError] 
    @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS                               -- by uspLogError in the ErrorLog table
BEGIN
    SET NOCOUNT ON;

    -- Output parameter value of 0 indicates that error 
    -- information was not logged
    SET @ErrorLogID = 0;

    BEGIN TRY
        -- Return if there is no error information to log
        IF ERROR_NUMBER() IS NULL
            RETURN;

        -- Return if inside an uncommittable transaction.
        -- Data insertion/modification is not allowed when 
        -- a transaction is in an uncommittable state.
        IF XACT_STATE() = -1
        BEGIN
            PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' 
                + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
            RETURN;
        END

        INSERT [dbo].[ErrorLog] 
            (
            [UserName], 
            [ErrorNumber], 
            [ErrorSeverity], 
            [ErrorState], 
            [ErrorProcedure], 
            [ErrorLine], 
            [ErrorMessage]
            ) 
        VALUES 
            (
            CONVERT(sysname, CURRENT_USER), 
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE()
            );

        -- Pass back the ErrorLogID of the row inserted
        SET @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred in stored procedure uspLogError: ';
        EXECUTE [dbo].[uspPrintError];
        RETURN -1;
    END CATCH
END;

Related objects
dbo.uspPrintError
dbo.ErrorLog

Objects that depend on dbo.uspLogError
HumanResources.uspUpdateEmployeeHireInfo
HumanResources.uspUpdateEmployeeLogin
HumanResources.uspUpdateEmployeePersonalInfo
Production.iWorkOrder
Production.uWorkOrder
Purchasing.dVendor
Purchasing.iPurchaseOrderDetail
Purchasing.uPurchaseOrderDetail
Purchasing.uPurchaseOrderHeader
Sales.iduSalesOrderDetail
Sales.iStore
Sales.uSalesOrderHeader

Stored procedure: dbo.uspPrintError ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information.

Stored procedure definition


-- uspPrintError prints error information about the error that caused 
-- execution to jump to the CATCH block of a TRY...CATCH construct. 
-- Should be executed from within the scope of a CATCH block otherwise 
-- it will return without printing any error information.
CREATE PROCEDURE [dbo].[uspPrintError] 
AS
BEGIN
    SET NOCOUNT ON;

    -- Print error information. 
    PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
          ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
          ', State ' + CONVERT(varchar(5), ERROR_STATE()) + 
          ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') + 
          ', Line ' + CONVERT(varchar(5), ERROR_LINE());
    PRINT ERROR_MESSAGE();
END;

Objects that depend on dbo.uspPrintError
dbo.ufnGetContactInformation
dbo.uspLogError
Production.iWorkOrder
Production.uWorkOrder
Purchasing.dVendor
Purchasing.iPurchaseOrderDetail
Purchasing.uPurchaseOrderDetail
Purchasing.uPurchaseOrderHeader
Sales.iduSalesOrderDetail
Sales.iStore
Sales.uSalesOrderHeader

Stored procedure: HumanResources.uspUpdateEmployeeHireInfo ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters.

Parameters

Direction	Parameter name	Data type	Description
IN	@EmployeeID	int	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a valid EmployeeID from the Employee table.
IN	@Title	nvarchar (50)	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a title for the employee.
IN	@HireDate	datetime	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a hire date for the employee.
IN	@RateChangeDate	datetime	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the date the rate changed for the employee.
IN	@Rate	money	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the new rate for the employee.
IN	@PayFrequency	tinyint	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the pay frequency for the employee.
IN	@CurrentFlag	bit	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the current flag for the employee.

Stored procedure definition


CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
    @EmployeeID [int], 
    @Title [nvarchar](50), 
    @HireDate [datetime], 
    @RateChangeDate [datetime], 
    @Rate [money], 
    @PayFrequency [tinyint], 
    @CurrentFlag [dbo].[Flag] 
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE [HumanResources].[Employee] 
        SET [Title] = @Title 
            ,[HireDate] = @HireDate 
            ,[CurrentFlag] = @CurrentFlag 
        WHERE [EmployeeID] = @EmployeeID;

        INSERT INTO [HumanResources].[EmployeePayHistory] 
            ([EmployeeID]
            ,[RateChangeDate]
            ,[Rate]
            ,[PayFrequency]) 
        VALUES (@EmployeeID, @RateChangeDate, @Rate, @PayFrequency);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

Stored procedure: HumanResources.uspUpdateEmployeeLogin ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Updates the Employee table with the values specified in the input parameters for the given EmployeeID.

Parameters

Direction	Parameter name	Data type	Description
IN	@EmployeeID	int	Input parameter for the stored procedure uspUpdateEmployeeLogin. Enter a valid EmployeeID from the Employee table.
IN	@ManagerID	int	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a valid ManagerID for the employee.
IN	@LoginID	nvarchar (256)	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a valid login for the employee.
IN	@Title	nvarchar (50)	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a title for the employee.
IN	@HireDate	datetime	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a hire date for the employee.
IN	@CurrentFlag	bit	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the current flag for the employee.

Stored procedure definition


CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeLogin]
    @EmployeeID [int], 
    @ManagerID [int],
    @LoginID [nvarchar](256),
    @Title [nvarchar](50),
    @HireDate [datetime],
    @CurrentFlag [dbo].[Flag]
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        UPDATE [HumanResources].[Employee] 
        SET [ManagerID] = @ManagerID 
            ,[LoginID] = @LoginID 
            ,[Title] = @Title 
            ,[HireDate] = @HireDate 
            ,[CurrentFlag] = @CurrentFlag 
        WHERE [EmployeeID] = @EmployeeID;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

Related objects
dbo.uspLogError
HumanResources.Employee

Stored procedure: HumanResources.uspUpdateEmployeePersonalInfo ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Updates the Employee table with the values specified in the input parameters for the given EmployeeID.

Parameters

Direction	Parameter name	Data type	Description
IN	@EmployeeID	int	Input parameter for the stored procedure uspUpdateEmployeePersonalInfo. Enter a valid EmployeeID from the HumanResources.Employee table.
IN	@NationalIDNumber	nvarchar (15)	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a national ID for the employee.
IN	@BirthDate	datetime	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a birth date for the employee.
IN	@MaritalStatus	nchar (1)	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a marital status for the employee.
IN	@Gender	nchar (1)	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a gender for the employee.

Stored procedure definition


CREATE PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo]
    @EmployeeID [int], 
    @NationalIDNumber [nvarchar](15), 
    @BirthDate [datetime], 
    @MaritalStatus [nchar](1), 
    @Gender [nchar](1)
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        UPDATE [HumanResources].[Employee] 
        SET [NationalIDNumber] = @NationalIDNumber 
            ,[BirthDate] = @BirthDate 
            ,[MaritalStatus] = @MaritalStatus 
            ,[Gender] = @Gender 
        WHERE [EmployeeID] = @EmployeeID;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

Related objects
dbo.uspLogError
HumanResources.Employee

UDF: dbo.ufnGetAccountingEndDate ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Scalar function used in the uSalesOrderHeader trigger to set the starting account date.

Parameters

Direction	Parameter name	Data type	Description
OUT		datetime

UDF definition


CREATE FUNCTION [dbo].[ufnGetAccountingEndDate]()
RETURNS [datetime] 
AS 
BEGIN
    RETURN DATEADD(millisecond, -2, CONVERT(datetime, '20040701', 112));
END;

UDF: dbo.ufnGetAccountingStartDate ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Scalar function used in the uSalesOrderHeader trigger to set the ending account date.

Parameters

Direction	Parameter name	Data type	Description
OUT		datetime

UDF definition


CREATE FUNCTION [dbo].[ufnGetAccountingStartDate]()
RETURNS [datetime] 
AS 
BEGIN
    RETURN CONVERT(datetime, '20030701', 112);
END;

UDF: dbo.ufnGetContactInformation ( Created: 26 Apr 2006 Last altered: 26 Oct 2008 )

Parameters

Direction	Parameter name	Data type	Description
IN	@ContactID	int	Input parameter for the table value function ufnGetContactInformation. Enter a valid ContactID from the Person.Contact table.

UDF definition


CREATE FUNCTION [dbo].[ufnGetContactInformation](@ContactID int)
RETURNS @retContactInformation TABLE 
(
    -- Columns returned by the function
    [ContactID] int PRIMARY KEY NOT NULL, 
    [FirstName] [nvarchar](50) NULL, 
    [LastName] [nvarchar](50) NULL, 
    [JobTitle] [nvarchar](50) NULL, 
    [ContactType] [nvarchar](50) NULL
)
AS 
-- Returns the first name, last name, job title and contact type for the specified contact.
BEGIN
    DECLARE 
        @FirstName [nvarchar](50), 
        @LastName [nvarchar](50), 
        @JobTitle [nvarchar](50), 
        @ContactType [nvarchar](50);

    -- Get common contact information
    SELECT 
        @ContactID = ContactID, 
        @FirstName = FirstName, 
        @LastName = LastName
    FROM [Person].[Contact] 
    WHERE [ContactID] = @ContactID;

    SET @JobTitle = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e 
                WHERE e.[ContactID] = @ContactID) 
                THEN (SELECT [Title] 
                    FROM [HumanResources].[Employee] 
                    WHERE [ContactID] = @ContactID)

            -- Check for vendor
            WHEN EXISTS(SELECT * FROM [Purchasing].[VendorContact] vc 
                    INNER JOIN [Person].[ContactType] ct 
                    ON vc.[ContactTypeID] = ct.[ContactTypeID] 
                WHERE vc.[ContactID] = @ContactID) 
                THEN (SELECT ct.[Name] 
                    FROM [Purchasing].[VendorContact] vc 
                        INNER JOIN [Person].[ContactType] ct 
                        ON vc.[ContactTypeID] = ct.[ContactTypeID] 
                    WHERE vc.[ContactID] = @ContactID)

            -- Check for store
            WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc 
                    INNER JOIN [Person].[ContactType] ct 
                    ON sc.[ContactTypeID] = ct.[ContactTypeID] 
                WHERE sc.[ContactID] = @ContactID) 
                THEN (SELECT ct.[Name] 
                    FROM [Sales].[StoreContact] sc 
                        INNER JOIN [Person].[ContactType] ct 
                        ON sc.[ContactTypeID] = ct.[ContactTypeID] 
                    WHERE [ContactID] = @ContactID)

            ELSE NULL 
        END;

    SET @ContactType = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e 
                WHERE e.[ContactID] = @ContactID) 
                THEN 'Employee'

            -- Check for vendor
            WHEN EXISTS(SELECT * FROM [Purchasing].[VendorContact] vc 
                    INNER JOIN [Person].[ContactType] ct 
                    ON vc.[ContactTypeID] = ct.[ContactTypeID] 
                WHERE vc.[ContactID] = @ContactID) 
                THEN 'Vendor Contact'

            -- Check for store
            WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc 
                    INNER JOIN [Person].[ContactType] ct 
                    ON sc.[ContactTypeID] = ct.[ContactTypeID] 
                WHERE sc.[ContactID] = @ContactID) 
                THEN 'Store Contact'

            -- Check for individual consumer
            WHEN EXISTS(SELECT * FROM [Sales].[Individual] i 
                WHERE i.[ContactID] = @ContactID) 
                THEN 'Consumer'
        END;

    -- Return the information to the caller
    IF @ContactID IS NOT NULL 
    BEGIN
		EXECUTE [dbo].[uspPrintError];
        INSERT @retContactInformation
        SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
    END;

    RETURN;
END;

UDF: dbo.ufnGetDocumentStatusText ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Scalar function returning the text representation of the Status column in the Document table.

Parameters

Direction	Parameter name	Data type	Description
OUT		nvarchar (16)
IN	@Status	tinyint	Input parameter for the scalar function ufnGetDocumentStatusText. Enter a valid integer.

UDF definition


CREATE FUNCTION [dbo].[ufnGetDocumentStatusText](@Status [tinyint])
RETURNS [nvarchar](16) 
AS 
-- Returns the sales order status text representation for the status value.
BEGIN
    DECLARE @ret [nvarchar](16);

    SET @ret = 
        CASE @Status
            WHEN 1 THEN N'Pending approval'
            WHEN 2 THEN N'Approved'
            WHEN 3 THEN N'Obsolete'
            ELSE N'** Invalid **'
        END;
    
    RETURN @ret
END;

UDF: dbo.ufnGetProductDealerPrice ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Scalar function returning the dealer price for a given product on a particular order date.

Parameters

Direction	Parameter name	Data type	Description
OUT		money
IN	@ProductID	int	Input parameter for the scalar function ufnGetProductDealerPrice. Enter a valid ProductID from the Production.Product table.
IN	@OrderDate	datetime	Input parameter for the scalar function ufnGetProductDealerPrice. Enter a valid order date.

UDF definition


CREATE FUNCTION [dbo].[ufnGetProductDealerPrice](@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
-- Returns the dealer price for the product on a specific date.
BEGIN
    DECLARE @DealerPrice money;
    DECLARE @DealerDiscount money;

    SET @DealerDiscount = 0.60  -- 60% of list price

    SELECT @DealerPrice = plph.[ListPrice] * @DealerDiscount 
    FROM [Production].[Product] p 
        INNER JOIN [Production].[ProductListPriceHistory] plph 
        ON p.[ProductID] = plph.[ProductID] 
            AND p.[ProductID] = @ProductID 
            AND @OrderDate BETWEEN plph.[StartDate] AND COALESCE(plph.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices!

    RETURN @DealerPrice;
END;

UDF: dbo.ufnGetProductListPrice ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Scalar function returning the list price for a given product on a particular order date.

Parameters

Direction	Parameter name	Data type	Description
OUT		money
IN	@ProductID	int	Input parameter for the scalar function ufnGetProductListPrice. Enter a valid ProductID from the Production.Product table.
IN	@OrderDate	datetime	Input parameter for the scalar function ufnGetProductListPrice. Enter a valid order date.

UDF definition


CREATE FUNCTION [dbo].[ufnGetProductListPrice](@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
BEGIN
    DECLARE @ListPrice money;

    SELECT @ListPrice = plph.[ListPrice] 
    FROM [Production].[Product] p 
        INNER JOIN [Production].[ProductListPriceHistory] plph 
        ON p.[ProductID] = plph.[ProductID] 
            AND p.[ProductID] = @ProductID 
            AND @OrderDate BETWEEN plph.[StartDate] AND COALESCE(plph.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices!

    RETURN @ListPrice;
END;

UDF: dbo.ufnGetProductStandardCost ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Scalar function returning the standard cost for a given product on a particular order date.

Parameters

Direction	Parameter name	Data type	Description
OUT		money
IN	@ProductID	int	Input parameter for the scalar function ufnGetProductStandardCost. Enter a valid ProductID from the Production.Product table.
IN	@OrderDate	datetime	Input parameter for the scalar function ufnGetProductStandardCost. Enter a valid order date.

UDF definition


CREATE FUNCTION [dbo].[ufnGetProductStandardCost](@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
-- Returns the standard cost for the product on a specific date.
BEGIN
    DECLARE @StandardCost money;

    SELECT @StandardCost = pch.[StandardCost] 
    FROM [Production].[Product] p 
        INNER JOIN [Production].[ProductCostHistory] pch 
        ON p.[ProductID] = pch.[ProductID] 
            AND p.[ProductID] = @ProductID 
            AND @OrderDate BETWEEN pch.[StartDate] AND COALESCE(pch.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices!

    RETURN @StandardCost;
END;

Related objects
Production.Product
Production.ProductCostHistory

UDF: dbo.ufnGetPurchaseOrderStatusText ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Scalar function returning the text representation of the Status column in the PurchaseOrderHeader table.

Parameters

Direction	Parameter name	Data type	Description
OUT		nvarchar (15)
IN	@Status	tinyint	Input parameter for the scalar function ufnGetPurchaseOrdertStatusText. Enter a valid integer.

UDF definition


CREATE FUNCTION [dbo].[ufnGetPurchaseOrderStatusText](@Status [tinyint])
RETURNS [nvarchar](15) 
AS 
-- Returns the sales order status text representation for the status value.
BEGIN
    DECLARE @ret [nvarchar](15);

    SET @ret = 
        CASE @Status
            WHEN 1 THEN 'Pending'
            WHEN 2 THEN 'Approved'
            WHEN 3 THEN 'Rejected'
            WHEN 4 THEN 'Complete'
            ELSE '** Invalid **'
        END;
    
    RETURN @ret
END;

UDF: dbo.ufnGetSalesOrderStatusText ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Scalar function returning the text representation of the Status column in the SalesOrderHeader table.

Parameters

Direction	Parameter name	Data type	Description
OUT		nvarchar (15)
IN	@Status	tinyint	Input parameter for the scalar function ufnGetSalesOrderStatusText. Enter a valid integer.

UDF definition


CREATE FUNCTION [dbo].[ufnGetSalesOrderStatusText](@Status [tinyint])
RETURNS [nvarchar](15) 
AS 
-- Returns the sales order status text representation for the status value.
BEGIN
    DECLARE @ret [nvarchar](15);

    SET @ret = 
        CASE @Status
            WHEN 1 THEN 'In process'
            WHEN 2 THEN 'Approved'
            WHEN 3 THEN 'Backordered'
            WHEN 4 THEN 'Rejected'
            WHEN 5 THEN 'Shipped'
            WHEN 6 THEN 'Cancelled'
            ELSE '** Invalid **'
        END;
    
    RETURN @ret
END;

UDF: dbo.ufnGetStock ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID.

Parameters

Direction	Parameter name	Data type	Description
OUT		int
IN	@ProductID	int	Input parameter for the scalar function ufnGetStock. Enter a valid ProductID from the Production.ProductInventory table.

UDF definition


CREATE FUNCTION [dbo].[ufnGetStock](@ProductID [int])
RETURNS [int] 
AS 
-- Returns the stock level for the product. This function is used internally only
BEGIN
    DECLARE @ret int;
    
    SELECT @ret = SUM(p.[Quantity]) 
    FROM [Production].[ProductInventory] p 
    WHERE p.[ProductID] = @ProductID 
        AND p.[LocationID] = '6'; -- Only look at inventory in the misc storage
    
    IF (@ret IS NULL) 
        SET @ret = 0
    
    RETURN @ret
END;

Related objects
Production.ProductInventory

UDF: dbo.ufnLeadingZeros ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Scalar function used by the Sales.Customer table to help set the account number.

Parameters

Direction	Parameter name	Data type	Description
OUT		varchar (8)
IN	@Value	int	Input parameter for the scalar function ufnLeadingZeros. Enter a valid integer.

UDF definition


CREATE FUNCTION [dbo].[ufnLeadingZeros](
    @Value int
) 
RETURNS varchar(8) 
WITH SCHEMABINDING 
AS 
BEGIN
    DECLARE @ReturnValue varchar(8);

    SET @ReturnValue = CONVERT(varchar(8), @Value);
    SET @ReturnValue = REPLICATE('0', 8 - DATALENGTH(@ReturnValue)) + @ReturnValue;

    RETURN (@ReturnValue);
END;

Objects that depend on dbo.ufnLeadingZeros
Sales.Customer

XML SCHEMA: HumanResources.HRResumeSchemaCollection ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Collection of XML schemas for the Resume column in the HumanResources.JobCandidate table.

Components

Name	Type	Derivation	Namespace
AddressType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
EducationType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
EmploymentType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
LocationType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
NameType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
ResumeType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
TelephoneType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Addr.Location	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Addr.OrgName	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Addr.PostalCode	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Addr.Street	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Addr.Telephone	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Addr.Type	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Address	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
EMail	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Edu.Degree	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Edu.EndDate	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Edu.GPA	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Edu.GPAAlternate	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Edu.GPAScale	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Edu.Level	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Edu.Location	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Edu.Major	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Edu.Minor	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Edu.School	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Edu.StartDate	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Education	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Emp.EndDate	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Emp.FunctionCategory	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Emp.IndustryCategory	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Emp.JobTitle	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Emp.Location	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Emp.OrgName	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Emp.Responsibility	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Emp.StartDate	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Employment	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Loc.City	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Loc.CountryRegion	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Loc.State	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Location	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Name	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Name.First	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Name.Last	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Name.Middle	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Name.Prefix	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Name.Suffix	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Resume	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Skills	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Tel.AreaCode	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Tel.Extension	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Tel.IntlCode	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Tel.Number	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Tel.Type	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
Telephone	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
WebSite	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume

XML SCHEMA: Person.AdditionalContactInfoSchemaCollection ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Collection of XML schemas for the AdditionalContactInfo column in the Person.Contact table.

Components

Name	Type	Derivation	Namespace
date	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactRecord
addressType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
eMailType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
phoneNumberType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
specialInstructionsType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
AdditionalContactInfo	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo
City	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
ContactRecord	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactRecord
CountryRegion	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
PostalCode	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
SpecialInstructions	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
SpecialInstructions	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
SpecialInstructions	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
StateProvince	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
Street	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
eMail	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
eMailAddress	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
facsimileTelephoneNumber	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
homePostalAddress	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
internationaliSDNNumber	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
mobile	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
number	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
pager	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
physicalDeliveryOfficeName	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
registeredAddress	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
telephoneNumber	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes
telexNumber	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes

XML SCHEMA: Production.ManuInstructionsSchemaCollection ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Collection of XML schemas for the Instructions column in the Production.ProductModel table.

Components

Name	Type	Derivation	Namespace
LaborHours	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions
LocationID	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions
LotSize	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions
MachineHours	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions
SetupHours	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions
StepType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions
Location	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions
blueprint	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions
diag	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions
material	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions
root	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions
specs	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions
step	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions
tool	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions

XML SCHEMA: Production.ProductDescriptionSchemaCollection ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Collection of XML schemas for the CatalogDescription column in the Production.ProductModel table.

Components

Name	Type	Derivation	Namespace
ProductModelID	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
ProductModelName	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Category	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Features	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Manufacturer	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Picture	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
ProductDescription	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Specifications	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Summary	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Angle	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Category	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Code	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Copyright	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
CopyrightURL	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Description	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Description	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain
Description	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain
Features	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Maintenance	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain
Manufacturer	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Name	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Name	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
NoOfYears	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain
Picture	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
ProductDescription	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
ProductPhotoID	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
ProductURL	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Size	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Specifications	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Summary	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Taxonomy	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription
Warranty	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain
WarrantyPeriod	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain

XML SCHEMA: Sales.IndividualSurveySchemaCollection ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Collection of XML schemas for the Demographics column in the Sales.Individual table.

Components

Name	Type	Derivation	Namespace
BirthDate	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey
Comments	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey
CommuteDistance	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey
DateFirstPurchase	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey
Education	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey
Gender	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey
Hobby	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey
HomeOwnerFlag	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey
IndividualSurvey	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey
MaritalStatus	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey
NumberCarsOwned	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey
NumberChildrenAtHome	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey
Occupation	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey
TotalChildren	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey
TotalPurchaseYTD	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey
YearlyIncome	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey
MileRangeType	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey
SalaryType	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey

XML SCHEMA: Sales.StoreSurveySchemaCollection ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )

Collection of XML schemas for the Demographics column in the Sales.Store table.

Components

Name	Type	Derivation	Namespace
AnnualRevenue	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey
AnnualSales	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey
BankName	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey
Brands	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey
BusinessType	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey
Comments	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey
ContactName	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey
Internet	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey
JobTitle	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey
NumberEmployees	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey
Specialty	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey
SquareFeet	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey
StoreSurvey	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey
YearOpened	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey
BrandType	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey
BusinessType	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey
InternetType	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey
SpecialtyType	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey

XML SCHEMA: sys.sys ( Created: 14 Oct 2005 Last altered: 14 Oct 2005 )

Components

Name	Type	Derivation	Namespace
anySimpleType	ANY_SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
anyType	ANY_TYPE	NONE	http://www.w3.org/2001/XMLSchema
base	ATTRIBUTE	NONE	http://www.w3.org/XML/1998/namespace
clrTypeName	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes
lang	ATTRIBUTE	NONE	http://www.w3.org/XML/1998/namespace
localeId	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes
maxLength	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes
precision	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes
scale	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes
space	ATTRIBUTE	NONE	http://www.w3.org/XML/1998/namespace
sqlCollationVersion	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes
sqlCompareOptions	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes
sqlDbType	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes
sqlSortId	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes
sqlTypeAlias	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes
xmlSchemaCollection	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes
xml	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
ENTITIES	LIST_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
IDREFS	LIST_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
NMTOKENS	LIST_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
sqlCompareOptionsList	LIST_TYPE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes
NOTATION	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
QName	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
anyURI	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
base64Binary	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
boolean	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
date	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
dateTime	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
decimal	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
double	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
duration	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
float	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
gDay	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
gMonth	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
gMonthDay	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
gYear	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
gYearMonth	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
hexBinary	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
string	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
time	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
ENTITY	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
ID	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
IDREF	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
NCName	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
NMTOKEN	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
Name	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
bigint	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
binary	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
bit	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
byte	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
char	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
datetime	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
dbobject	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
decimal	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
float	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
image	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
int	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
int	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
integer	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
language	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
long	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
money	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
nchar	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
negativeInteger	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
nonNegativeInteger	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
nonPositiveInteger	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
normalizedString	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
ntext	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
numeric	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
nvarchar	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
positiveInteger	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
real	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
short	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
smalldatetime	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
smallint	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
smallmoney	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
sqlCompareOptionsEnum	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
sqlDbTypeEnum	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
text	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
timestamp	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
timestampNumeric	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
tinyint	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
token	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
uniqueidentifier	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
unsignedByte	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
unsignedInt	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
unsignedLong	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
unsignedShort	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema
varbinary	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
varchar	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes
xmlSpaceEnum	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/XML/1998/namespace