AdventureWorks database

AdventureWorks Sample OLTP Database

Database server:
Database size: 178.75 MB
Documentation date: 30/03/2009

Tables (70)	Views (17 )	Stored procedures (9)
dbo.AWBuildVersion dbo.DatabaseLog dbo.ErrorLog HumanResources.Department HumanResources.Employee HumanResources.EmployeeAddress HumanResources.EmployeeDepartmentHistory HumanResources.EmployeePayHistory HumanResources.JobCandidate HumanResources.Shift Person.Address Person.AddressType Person.Contact Person.ContactType Person.CountryRegion Person.StateProvince Production.BillOfMaterials Production.Culture Production.Document Production.Illustration Production.Location Production.Product Production.ProductCategory Production.ProductCostHistory Production.ProductDescription Production.ProductDocument Production.ProductInventory Production.ProductListPriceHistory Production.ProductModel Production.ProductModelIllustration Production.ProductModelProductDescriptionCulture Production.ProductPhoto Production.ProductProductPhoto Production.ProductReview Production.ProductSubcategory Production.ScrapReason Production.TransactionHistory Production.TransactionHistoryArchive Production.UnitMeasure Production.WorkOrder Production.WorkOrderRouting Purchasing.ProductVendor Purchasing.PurchaseOrderDetail Purchasing.PurchaseOrderHeader Purchasing.ShipMethod Purchasing.Vendor Purchasing.VendorAddress Purchasing.VendorContact Sales.ContactCreditCard Sales.CountryRegionCurrency Sales.CreditCard Sales.Currency Sales.CurrencyRate Sales.Customer Sales.CustomerAddress Sales.Individual Sales.SalesOrderDetail Sales.SalesOrderHeader Sales.SalesOrderHeaderSalesReason Sales.SalesPerson Sales.SalesPersonQuotaHistory Sales.SalesReason Sales.SalesTaxRate Sales.SalesTerritory Sales.SalesTerritoryHistory Sales.ShoppingCartItem Sales.SpecialOffer Sales.SpecialOfferProduct Sales.Store Sales.StoreContact	HumanResources.vEmployee HumanResources.vEmployeeDepartment HumanResources.vEmployeeDepartmentHistory HumanResources.vJobCandidate HumanResources.vJobCandidateEducation HumanResources.vJobCandidateEmployment Person.vAdditionalContactInfo Person.vStateProvinceCountryRegion Production.vProductAndDescription Production.vProductModelCatalogDescription Production.vProductModelInstructions Purchasing.vVendor Sales.vIndividualCustomer Sales.vIndividualDemographics Sales.vSalesPerson Sales.vSalesPersonSalesByFiscalYears Sales.vStoreWithDemographics	dbo.uspGetBillOfMaterials dbo.uspGetEmployeeManagers dbo.uspGetManagerEmployees dbo.uspGetWhereUsedProductID dbo.uspLogError dbo.uspPrintError HumanResources.uspUpdateEmployeeHireInfo HumanResources.uspUpdateEmployeeLogin HumanResources.uspUpdateEmployeePersonalInfo

User defined functions (11)	User defined data types (6)	Users (2)	Roles (10)
dbo.ufnGetAccountingEndDate dbo.ufnGetAccountingStartDate dbo.ufnGetContactInformation dbo.ufnGetDocumentStatusText dbo.ufnGetProductDealerPrice dbo.ufnGetProductListPrice dbo.ufnGetProductStandardCost dbo.ufnGetPurchaseOrderStatusText dbo.ufnGetSalesOrderStatusText dbo.ufnGetStock dbo.ufnLeadingZeros	AccountNumber Flag Name NameStyle OrderNumber Phone	dbo TestUser	db_accessadmin db_backupoperator db_datareader db_datawriter db_ddladmin db_denydatareader db_denydatawriter db_owner db_securityadmin public

(up)

Table: dbo.AWBuildVersion

Current version number of the AdventureWorks sample database.

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: dbo.DatabaseLog

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

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

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.

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: HumanResources.Department

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

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: HumanResources.Employee

Employee information such as salary, department, and title.

Field name

Data type

Nullable

Default value

Description

PK	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	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.

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)

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

Check constraints

Check name	Column name	Check 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: Yes
Trigger 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]

(up)

Table: HumanResources.EmployeeAddress

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

Field name

Data type

Nullable

Default value

Description

PK FK	EmployeeID	int		Primary key. Foreign key to Employee.EmployeeID.
PK FK	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.

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)

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

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]

(up)

Table: HumanResources.EmployeeDepartmentHistory

Employee department transfers.

Field name

Data type

Nullable

Default value

Description

PK FK	EmployeeID	int			Employee identification number. Foreign key to Employee.EmployeeID.
PK FK	DepartmentID	smallint			Department in which the employee worked including currently. Foreign key to Department.DepartmentID.
PK FK	ShiftID	tinyint			Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.
PK	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.

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)

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

Check constraints

Check name	Column name	Check 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]

(up)

Table: HumanResources.EmployeePayHistory

Employee pay history.

Field name

Data type

Nullable

Default value

Description

PK FK	EmployeeID	int		Employee identification number. Foreign key to Employee.EmployeeID.
PK	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.

Foreign keys dependencies:

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

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

Check constraints

Check name	Column name	Check 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]

(up)

Table: HumanResources.JobCandidate

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

Field name

Data type

Nullable

Default value

Description

PK	JobCandidateID	int			Primary key for JobCandidate records.
FK	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.

Foreign keys dependencies:

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

Indexes

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

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]

(up)

Table: HumanResources.Shift

Work shift lookup table.

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: Person.Address

Street address information for customers, employees, and vendors.

Field name

Data type

Nullable

Default value

Description

PK	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	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.

Foreign keys dependencies:

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

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

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]

(up)

Table: Person.AddressType

Types of addresses stored in the Address table.

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: Person.Contact

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

Field name

Data type

Nullable

Default value

Description

PK	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	Check 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]

(up)

Table: Person.ContactType

Lookup table containing the types of contacts stored in Contact.

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: Person.CountryRegion

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

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: Person.StateProvince

State and province lookup table.

Field name

Data type

Nullable

Default value

Description

PK	StateProvinceID	int		Primary key for StateProvince records.
IX	StateProvinceCode	nchar (3)		ISO standard state or province code.
FK	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.

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)

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

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]

(up)

Table: Production.BillOfMaterials

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

Field name

Data type

Nullable

Default value

Description

PK	BillOfMaterialsID	int			Primary key for BillOfMaterials records.
FK	ProductAssemblyID	int	Yes		Parent product identification number. Foreign key to Product.ProductID.
FK	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	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.

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)

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

Check constraints

Check name	Column name	Check 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]

(up)

Table: Production.Culture

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

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: Production.Document

Product maintenance documents.

Field name

Data type

Nullable

Default value

Description

PK	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	Check 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]

(up)

Table: Production.Illustration

Bicycle assembly diagrams.

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: Production.Location

Product inventory and manufacturing locations.

Field name

Data type

Nullable

Default value

Description

PK	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	Check 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]

(up)

Table: Production.Product

Products sold or used in the manfacturing of sold products.

Field name

Data type

Nullable

Default value

Description

PK	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.

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)

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

Check constraints

Check name	Column name	Check 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]

(up)

Table: Production.ProductCategory

High-level product categorization.

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: Production.ProductCostHistory

Changes in the cost of a product over time.

Field name

Data type

Nullable

Default value

Description

PK FK	ProductID	int			Product identification number. Foreign key to Product.ProductID
PK	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.

Foreign keys dependencies:

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

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

Check constraints

Check name	Column name	Check 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]

(up)

Table: Production.ProductDescription

Product descriptions in several languages.

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: Production.ProductDocument

Cross-reference table mapping products to related product documents.

Field name

Data type

Nullable

Default value

Description

PK FK	ProductID	int		Product identification number. Foreign key to Product.ProductID.
PK FK	DocumentID	int		Document identification number. Foreign key to Document.DocumentID.
	ModifiedDate	datetime	(getdate())	Date and time the record was last updated.

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)

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

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]

(up)

Table: Production.ProductInventory

Product inventory information.

Field name

Data type

Nullable

Default value

Description

PK FK	ProductID	int		Product identification number. Foreign key to Product.ProductID.
PK FK	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.

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)

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

Check constraints

Check name	Column name	Check 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]

(up)

Table: Production.ProductListPriceHistory

Changes in the list price of a product over time.

Field name

Data type

Nullable

Default value

Description

PK FK	ProductID	int			Product identification number. Foreign key to Product.ProductID
PK	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.

Foreign keys dependencies:

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

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

Check constraints

Check name	Column name	Check 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]

(up)

Table: Production.ProductModel

Product model classification.

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: Production.ProductModelIllustration

Cross-reference table mapping product models and illustrations.

Field name

Data type

Nullable

Default value

Description

PK FK	ProductModelID	int		Primary key. Foreign key to ProductModel.ProductModelID.
PK FK	IllustrationID	int		Primary key. Foreign key to Illustration.IllustrationID.
	ModifiedDate	datetime	(getdate())	Date and time the record was last updated.

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)

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

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]

(up)

Table: Production.ProductModelProductDescriptionCulture

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

Field name

Data type

Nullable

Default value

Description

PK FK	ProductModelID	int		Primary key. Foreign key to ProductModel.ProductModelID.
PK FK	ProductDescriptionID	int		Primary key. Foreign key to ProductDescription.ProductDescriptionID.
PK FK	CultureID	nchar (6)		Culture identification number. Foreign key to Culture.CultureID.
	ModifiedDate	datetime	(getdate())	Date and time the record was last updated.

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)

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

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]

(up)

Table: Production.ProductPhoto

Product images.

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: Production.ProductProductPhoto

Cross-reference table mapping products and product photos.

Field name

Data type

Nullable

Default value

Description

PK FK	ProductID	int		Product identification number. Foreign key to Product.ProductID.
PK FK	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.

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)

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

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]

(up)

Table: Production.ProductReview

Customer reviews of products they have purchased.

Field name

Data type

Nullable

Default value

Description

PK	ProductReviewID	int			Primary key for ProductReview records.
FK	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.

Foreign keys dependencies:

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

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

Check constraints

Check name	Column name	Check 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]

(up)

Table: Production.ProductSubcategory

Product subcategories. See ProductCategory table.

Field name

Data type

Nullable

Default value

Description

PK	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.

Foreign keys dependencies:

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

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

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]

(up)

Table: Production.ScrapReason

Manufacturing failure reasons lookup table.

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: Production.TransactionHistory

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

Field name

Data type

Nullable

Default value

Description

PK	TransactionID	int		Primary key for TransactionHistory records.
FK	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.

Foreign keys dependencies:

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

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

Check constraints

Check name	Column name	Check 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]

(up)

Table: Production.TransactionHistoryArchive

Transactions for previous years.

Field name

Data type

Nullable

Default value

Description

PK	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	Check 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]

(up)

Table: Production.UnitMeasure

Unit of measure lookup table.

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: Production.WorkOrder

Manufacturing work orders.

Field name

Data type

Nullable

Default value

Description

PK	WorkOrderID	int			Primary key for WorkOrder records.
FK	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	ScrapReasonID	smallint	Yes		Reason for inspection failure.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

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)

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

Check constraints

Check name	Column name	Check 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: Yes
Trigger 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: Yes
Trigger 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]

(up)

Table: Production.WorkOrderRouting

Work order details.

Field name

Data type

Nullable

Default value

Description

PK FK	WorkOrderID	int			Primary key. Foreign key to WorkOrder.WorkOrderID.
PK	ProductID	int			Primary key. Foreign key to Product.ProductID.
PK	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.

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)

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

Check constraints

Check name	Column name	Check 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]

(up)

Table: Purchasing.ProductVendor

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

Field name

Data type

Nullable

Default value

Description

PK FK	ProductID	int			Primary key. Foreign key to Product.ProductID.
PK FK	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	UnitMeasureCode	nchar (3)			The product's unit of measure.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

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)

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

Check constraints

Check name	Column name	Check 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]

(up)

Table: Purchasing.PurchaseOrderDetail

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

Field name

Data type

Nullable

Default value

Description

PK FK	PurchaseOrderID	int		Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
PK	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	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.

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)

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

Check constraints

Check name	Column name	Check 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: Yes
Trigger 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: Yes
Trigger 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]

(up)

Table: Purchasing.PurchaseOrderHeader

General purchase order information. See PurchaseOrderDetail.

Field name

Data type

Nullable

Default value

Description

PK	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	EmployeeID	int			Employee who created the purchase order. Foreign key to Employee.EmployeeID.
FK	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.

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)

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

Check constraints

Check name	Column name	Check 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: Yes
Trigger 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]

(up)

Table: Purchasing.ShipMethod

Shipping company lookup table.

Field name

Data type

Nullable

Default value

Description

PK	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	Check 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]

(up)

Table: Purchasing.Vendor

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

Field name

Data type

Nullable

Default value

Description

PK	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	Check 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: Yes
Trigger 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]

(up)

Table: Purchasing.VendorAddress

Cross-reference mapping vendors and addresses.

Field name

Data type

Nullable

Default value

Description

PK FK	VendorID	int		Primary key. Foreign key to Vendor.VendorID.
PK FK	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.

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)

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

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]

(up)

Table: Purchasing.VendorContact

Cross-reference table mapping vendors and their employees.

Field name

Data type

Nullable

Default value

Description

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

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)

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

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]

(up)

Table: Sales.ContactCreditCard

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

Field name

Data type

Nullable

Default value

Description

PK FK	ContactID	int		Customer identification number. Foreign key to Contact.ContactID.
PK FK	CreditCardID	int		Credit card identification number. Foreign key to CreditCard.CreditCardID.
	ModifiedDate	datetime	(getdate())	Date and time the record was last updated.

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)

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

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]

(up)

Table: Sales.CountryRegionCurrency

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

Field name

Data type

Nullable

Default value

Description

PK FK	CountryRegionCode	nvarchar (3)		ISO code for countries and regions. Foreign key to CountryRegion.CountryRegionCode.
PK FK	CurrencyCode	nchar (3)		ISO standard currency code. Foreign key to Currency.CurrencyCode.
	ModifiedDate	datetime	(getdate())	Date and time the record was last updated.

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)

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

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]

(up)

Table: Sales.CreditCard

Customer credit card information.

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: Sales.Currency

Lookup table containing standard ISO currencies.

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: Sales.CurrencyRate

Currency exchange rates.

Field name

Data type

Nullable

Default value

Description

PK	CurrencyRateID	int		Primary key for CurrencyRate records.
IX	CurrencyRateDate	datetime		Date and time the exchange rate was obtained.
FK	FromCurrencyCode	nchar (3)		Exchange rate was converted from this currency code.
FK	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.

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)

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

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]

(up)

Table: Sales.Customer

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

Field name

Data type

Nullable

Default value

Description

PK	CustomerID	int			Primary key for Customer records.
FK	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.

Foreign keys dependencies:

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

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

Check constraints

Check name	Column name	Check 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]

(up)

Table: Sales.CustomerAddress

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

Field name

Data type

Nullable

Default value

Description

PK FK	CustomerID	int		Primary key. Foreign key to Customer.CustomerID.
PK FK	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.

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)

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

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]

(up)

Table: Sales.Individual

Demographic data about customers that purchase Adventure Works products online.

Field name

Data type

Nullable

Default value

Description

PK FK	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.

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)

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

Triggers

Trigger name: Sales.iuIndividual (Created: 26 Apr 2006)
Trigger type: INSERT
Trigger active: Yes
Trigger 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]

(up)

Table: Sales.SalesOrderDetail

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

Field name

Data type

Nullable

Default value

Description

PK FK	SalesOrderID	int			Primary key. Foreign key to SalesOrderHeader.SalesOrderID.
PK	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	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.

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)

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

Check constraints

Check name	Column name	Check 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: Yes
Trigger 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]

(up)

Table: Sales.SalesOrderHeader

General sales order information.

Field name

Data type

Nullable

Default value

Description

PK	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	CustomerID	int			Customer identification number. Foreign key to Customer.CustomerID.
FK	ContactID	int			Customer contact identification number. Foreign key to Contact.ContactID.
FK	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.

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)

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

Check constraints

Check name	Column name	Check 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: Yes
Trigger 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]

(up)

Table: Sales.SalesOrderHeaderSalesReason

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

Field name

Data type

Nullable

Default value

Description

PK FK	SalesOrderID	int		Primary key. Foreign key to SalesOrderHeader.SalesOrderID.
PK FK	SalesReasonID	int		Primary key. Foreign key to SalesReason.SalesReasonID.
	ModifiedDate	datetime	(getdate())	Date and time the record was last updated.

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)

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

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]

(up)

Table: Sales.SalesPerson

Sales representative current information.

Field name

Data type

Nullable

Default value

Description

PK FK	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.

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)

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

Check constraints

Check name	Column name	Check 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]

(up)

Table: Sales.SalesPersonQuotaHistory

Sales performance tracking.

Field name

Data type

Nullable

Default value

Description

PK FK	SalesPersonID	int		Sales person identification number. Foreign key to SalesPerson.SalesPersonID.
PK	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.

Foreign keys dependencies:

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

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

Check constraints

Check name	Column name	Check 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]

(up)

Table: Sales.SalesReason

Lookup table of customer purchase reasons.

Field name

Data type

Nullable

Default value

Description

PK	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]

(up)

Table: Sales.SalesTaxRate

Tax rate lookup table.

Field name

Data type

Nullable

Default value

Description

PK	SalesTaxRateID	int		Primary key for SalesTaxRate records.
FK	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.

Foreign keys dependencies:

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

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

Check constraints

Check name	Column name	Check 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]

(up)

Table: Sales.SalesTerritory

Sales territory lookup table.

Field name

Data type

Nullable

Default value

Description

PK	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	Check 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]

(up)

Table: Sales.SalesTerritoryHistory

Sales representative transfers to other sales territories.

Field name

Data type

Nullable

Default value

Description

PK FK	SalesPersonID	int			Primary key for SalesTerritoryHistory records.
PK FK	TerritoryID	int			Territory identification number. Foreign key to SalesTerritory.SalesTerritoryID.
PK	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.

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)

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

Check constraints

Check name	Column name	Check 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]

(up)

Table: Sales.ShoppingCartItem

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

Field name

Data type

Nullable

Default value

Description

PK	ShoppingCartItemID	int		Primary key for ShoppingCartItem records.
IX	ShoppingCartID	nvarchar (50)		Shopping cart identification number.
	Quantity	int	((1))	Product quantity ordered.
FK	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.

Foreign keys dependencies:

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

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

Check constraints

Check name	Column name	Check 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]

(up)

Table: Sales.SpecialOffer

Sale discounts lookup table.

Field name

Data type

Nullable

Default value

Description

PK	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	Check 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]

(up)

Table: Sales.SpecialOfferProduct

Cross-reference table mapping products to special offer discounts.

Field name

Data type

Nullable

Default value

Description

PK FK	SpecialOfferID	int		Primary key for SpecialOfferProduct records.
PK FK	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.

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)

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

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]

(up)

Table: Sales.Store

Customers (resellers) of Adventure Works products.

Field name

Data type

Nullable

Default value

Description

PK FK	CustomerID	int			Primary key. Foreign key to Customer.CustomerID.
	Name	Name			Name of the store.
FK	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.

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)

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

Triggers

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


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

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

    SET NOCOUNT ON;

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

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

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

Objects that depend on Sales.Store

Sales.iuIndividual
Sales.vStoreWithDemographics

Table definition

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

(up)

Table: Sales.StoreContact

Cross-reference table mapping stores and their employees.

Field name

Data type

Nullable

Default value

Description

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

Foreign keys dependencies:

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

Indexes

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

Objects that depend on Sales.StoreContact

Sales.vStoreWithDemographics

Table definition

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

(up)

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

Employee names and addresses.

Resultset

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

View definition


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

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

Returns employee name, title, and current department.

Resultset

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

View definition


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

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

Returns employee name and current and previous departments.

Resultset

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

View definition


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

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

Job candidate names and resumes.

Resultset

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

View definition


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

Related objects

HumanResources.JobCandidate

(up)

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

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

Resultset

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

View definition


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

Related objects

HumanResources.JobCandidate

(up)

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

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

Resultset

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

View definition


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

Related objects

HumanResources.JobCandidate

(up)

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

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

Resultset

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

View definition


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

Related objects

Person.Contact

(up)

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

Joins StateProvince table with CountryRegion table.

Resultset

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

View definition


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

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

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

Resultset

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

View definition


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

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

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

Resultset

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

View definition


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

Related objects

Production.ProductModel

(up)

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

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

Resultset

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

View definition


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

Related objects

Production.ProductModel

(up)

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

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

Resultset

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

View definition


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

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

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

Resultset

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

View definition


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

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

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

Resultset

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

View definition


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

Related objects

Sales.Individual

(up)

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

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

Resultset

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

View definition


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

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

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

Resultset

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

View definition


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

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

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

Resultset

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

View definition


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

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

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

Parameters

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

Stored procedure definition


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

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

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

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

Parameters

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

Stored procedure definition


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

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

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

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

Parameters

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

Stored procedure definition


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

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

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

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

Parameters

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

Stored procedure definition


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

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

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

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

Parameters

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

Stored procedure definition


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

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

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

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

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

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

Related objects

dbo.uspPrintError
dbo.ErrorLog

Objects that depend on dbo.uspLogError

HumanResources.uspUpdateEmployeeHireInfo
HumanResources.uspUpdateEmployeeLogin
HumanResources.uspUpdateEmployeePersonalInfo
Production.iWorkOrder
Production.uWorkOrder
Purchasing.dVendor
Purchasing.iPurchaseOrderDetail
Purchasing.uPurchaseOrderDetail
Purchasing.uPurchaseOrderHeader
Sales.iduSalesOrderDetail
Sales.iStore
Sales.uSalesOrderHeader

(up)

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

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

Stored procedure definition


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

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

Objects that depend on dbo.uspPrintError

dbo.ufnGetContactInformation
dbo.uspLogError
Production.iWorkOrder
Production.uWorkOrder
Purchasing.dVendor
Purchasing.iPurchaseOrderDetail
Purchasing.uPurchaseOrderDetail
Purchasing.uPurchaseOrderHeader
Sales.iduSalesOrderDetail
Sales.iStore
Sales.uSalesOrderHeader

(up)

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

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

Parameters

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

Stored procedure definition


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

    BEGIN TRY
        BEGIN TRANSACTION;

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

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

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

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

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

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

Parameters

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

Stored procedure definition


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

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

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

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

Parameters

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

Stored procedure definition


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

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

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

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

Parameters

Direction	Parameter name	Data type	Description
OUT		datetime

UDF definition


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

(up)

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

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

Parameters

Direction	Parameter name	Data type	Description
OUT		datetime

UDF definition


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

(up)

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

Parameters

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

UDF definition


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

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

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

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

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

            ELSE NULL 
        END;

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

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

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

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

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

    RETURN;
END;

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

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

Parameters

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

UDF definition


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

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

(up)

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

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

Parameters

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

UDF definition


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

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

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

    RETURN @DealerPrice;
END;

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

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

Parameters

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

UDF definition


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

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

    RETURN @ListPrice;
END;

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

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

Parameters

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

UDF definition


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

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

    RETURN @StandardCost;
END;

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

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

Parameters

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

UDF definition


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

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

(up)

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

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

Parameters

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

UDF definition


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

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

(up)

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

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

Parameters

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

UDF definition


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

Related objects

Production.ProductInventory

(up)

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

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

Parameters

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

UDF definition


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

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

    RETURN (@ReturnValue);
END;

Objects that depend on dbo.ufnLeadingZeros

Sales.Customer

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 )

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