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