Project ms new parser Version 20091126 231028 AdvWorks2008-05-oltp.instawdb.sql

Project	ms new parser
Version	20091126 231028 AdvWorks2008-05-oltp.instawdb.sql
Version Date	2009-11-26

Tables

Address (Person)

Street address information for customers, employees, and vendors.

Column	Data Type	Nullable	Default	Description / PK / Index
AddressID	int	not null		Primary key for Address records. PK PK_Address_AddressID
AddressLine1	nvarchar(60)	not null		First street address line. index IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
AddressLine2	nvarchar(60)	null		Second street address line. index IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
City	nvarchar(30)	not null		Name of the city. index IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
StateProvinceID	int	not null		Unique identification number for the state or province. Foreign key to StateProvince table. index IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode index IX_Address_StateProvinceID
PostalCode	nvarchar(15)	not null		Postal code for the street address. index IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_Address_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
StateProvince (Person)	StateProvinceID	StateProvinceID

Detail Table	Column	Referencing Column
CustomerAddress (Sales)	AddressID	AddressID
EmployeeAddress (HumanResources)	AddressID	AddressID
SalesOrderHeader (Sales)	AddressID	BillToAddressID
SalesOrderHeader (Sales)	AddressID	ShipToAddressID
VendorAddress (Purchasing)	AddressID	AddressID

AddressType (Person)

Types of addresses stored in the Address table.

Column	Data Type	Nullable	Default	Description / PK / Index
AddressTypeID	int	not null		Primary key for AddressType records. PK PK_AddressType_AddressTypeID
Name	Name	not null		Address type description. For example, Billing, Home, or Shipping. index AK_AddressType_Name
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_AddressType_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
CustomerAddress (Sales)	AddressTypeID	AddressTypeID
VendorAddress (Purchasing)	AddressTypeID	AddressTypeID

AWBuildVersion (dbo)

Current version number of the AdventureWorks sample database.

Column	Data Type	Nullable	Default	Description / PK / Index
SystemInformationID	tinyint	not null		Primary key for AWBuildVersion records. PK PK_AWBuildVersion_SystemInformationID
Database Version	nvarchar(25)	not null		Version number of the database in 9.yy.mm.dd.00 format.
VersionDate	datetime	not null		Date and time the record was last updated.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

BillOfMaterials (Production)

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

Column	Data Type	Nullable	Default	Description / PK / Index
BillOfMaterialsID	int	not null		Primary key for BillOfMaterials records. PK PK_BillOfMaterials_BillOfMaterialsID
ProductAssemblyID	int	null		Parent product identification number. Foreign key to Product.ProductID. index AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
ComponentID	int	not null		Component identification number. Foreign key to Product.ProductID. index AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
StartDate	datetime	not null	(GETDATE())	Date the component started being used in the assembly item. index AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
EndDate	datetime	null		Date the component stopped being used in the assembly item.
UnitMeasureCode	nchar(3)	not null		Standard code identifying the unit of measure for the quantity. index IX_BillOfMaterials_UnitMeasureCode
BOMLevel	smallint	not null		Indicates the depth the component is from its parent (AssemblyID).
PerAssemblyQty	decimal(8, 2)	not null	(1.00)	Quantity of the component needed to create the assembly.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Product (Production)	ComponentID	ProductID
Product (Production)	ProductAssemblyID	ProductID
UnitMeasure (Production)	UnitMeasureCode	UnitMeasureCode

Contact (Person)

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

Column	Data Type	Nullable	Default	Description / PK / Index
ContactID	int	not null		Primary key for Contact records. PK PK_Contact_ContactID
NameStyle	NameStyle	not null	(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)	null		A courtesy title. For example, Mr. or Ms.
FirstName	Name	not null		First name of the person.
MiddleName	Name	null		Middle name or middle initial of the person.
LastName	Name	not null		Last name of the person.
Suffix	nvarchar(10)	null		Surname suffix. For example, Sr. or Jr.
EmailAddress	nvarchar(50)	null		E-mail address for the person. index IX_Contact_EmailAddress
EmailPromotion	int	not null	(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	null		Phone number associated with the person.
PasswordHash	varchar(128)	not null		Password for the e-mail account.
PasswordSalt	varchar(10)	not null		Random value concatenated with the password string before the password is hashed.
AdditionalContactInfo	XML	null		Additional contact information about the person stored in xml format. index PXML_Contact_AddContact
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_Contact_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
ContactCreditCard (Sales)	ContactID	ContactID
Employee (HumanResources)	ContactID	ContactID
Individual (Sales)	ContactID	ContactID
SalesOrderHeader (Sales)	ContactID	ContactID
StoreContact (Sales)	ContactID	ContactID
VendorContact (Purchasing)	ContactID	ContactID

ContactCreditCard (Sales)

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

Column	Data Type	Nullable	Default	Description / PK / Index
ContactID	int	not null		Customer identification number. Foreign key to Contact.ContactID. PK PK_ContactCreditCard_ContactID_CreditCardID
CreditCardID	int	not null		Credit card identification number. Foreign key to CreditCard.CreditCardID. PK PK_ContactCreditCard_ContactID_CreditCardID
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Contact (Person)	ContactID	ContactID
CreditCard (Sales)	CreditCardID	CreditCardID

ContactType (Person)

Lookup table containing the types of contacts stored in Contact.

Column	Data Type	Nullable	Default	Description / PK / Index
ContactTypeID	int	not null		Primary key for ContactType records. PK PK_ContactType_ContactTypeID
Name	Name	not null		Contact type description. index AK_ContactType_Name
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
StoreContact (Sales)	ContactTypeID	ContactTypeID
VendorContact (Purchasing)	ContactTypeID	ContactTypeID

CountryRegion (Person)

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

Column	Data Type	Nullable	Default	Description / PK / Index
CountryRegionCode	nvarchar(3)	not null		ISO standard code for countries and regions. PK PK_CountryRegion_CountryRegionCode
Name	Name	not null		Country or region name. index AK_CountryRegion_Name
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
CountryRegionCurrency (Sales)	CountryRegionCode	CountryRegionCode
StateProvince (Person)	CountryRegionCode	CountryRegionCode

CountryRegionCurrency (Sales)

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

Column	Data Type	Nullable	Default	Description / PK / Index
CountryRegionCode	nvarchar(3)	not null		ISO code for countries and regions. Foreign key to CountryRegion.CountryRegionCode. PK PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode
CurrencyCode	nchar(3)	not null		ISO standard currency code. Foreign key to Currency.CurrencyCode. PK PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode index IX_CountryRegionCurrency_CurrencyCode
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
CountryRegion (Person)	CountryRegionCode	CountryRegionCode
Currency (Sales)	CurrencyCode	CurrencyCode

CreditCard (Sales)

Customer credit card information.

Column	Data Type	Nullable	Default	Description / PK / Index
CreditCardID	int	not null		Primary key for CreditCard records. PK PK_CreditCard_CreditCardID
CardType	nvarchar(50)	not null		Credit card name.
CardNumber	nvarchar(25)	not null		Credit card number. index AK_CreditCard_CardNumber
ExpMonth	tinyint	not null		Credit card expiration month.
ExpYear	smallint	not null		Credit card expiration year.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
ContactCreditCard (Sales)	CreditCardID	CreditCardID
SalesOrderHeader (Sales)	CreditCardID	CreditCardID

Culture (Production)

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

Column	Data Type	Nullable	Default	Description / PK / Index
CultureID	nchar(6)	not null		Primary key for Culture records. PK PK_Culture_CultureID
Name	Name	not null		Culture description. index AK_Culture_Name
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
ProductModelProductDescriptionCulture (Production)	CultureID	CultureID

Currency (Sales)

Lookup table containing standard ISO currencies.

Column	Data Type	Nullable	Default	Description / PK / Index
CurrencyCode	nchar(3)	not null		The ISO code for the Currency. PK PK_Currency_CurrencyCode
Name	Name	not null		Currency name. index AK_Currency_Name
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
CountryRegionCurrency (Sales)	CurrencyCode	CurrencyCode
CurrencyRate (Sales)	CurrencyCode	FromCurrencyCode
CurrencyRate (Sales)	CurrencyCode	ToCurrencyCode

CurrencyRate (Sales)

Currency exchange rates.

Column	Data Type	Nullable	Default	Description / PK / Index
CurrencyRateID	int	not null		Primary key for CurrencyRate records. PK PK_CurrencyRate_CurrencyRateID
CurrencyRateDate	datetime	not null		Date and time the exchange rate was obtained. index AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode
FromCurrencyCode	nchar(3)	not null		Exchange rate was converted from this currency code. index AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode
ToCurrencyCode	nchar(3)	not null		Exchange rate was converted to this currency code. index AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode
AverageRate	money	not null		Average exchange rate for the day.
EndOfDayRate	money	not null		Final exchange rate for the day.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Currency (Sales)	FromCurrencyCode	CurrencyCode
Currency (Sales)	ToCurrencyCode	CurrencyCode

Detail Table	Column	Referencing Column
SalesOrderHeader (Sales)	CurrencyRateID	CurrencyRateID

Customer (Sales)

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

Column	Data Type	Nullable	Default	Description / PK / Index
CustomerID	int	not null		Primary key for Customer records. PK PK_Customer_CustomerID
TerritoryID	int	null		ID of the territory in which the customer is located. Foreign key to SalesTerritory.SalesTerritoryID. index IX_Customer_TerritoryID
AccountNumber				Unique number identifying the customer assigned by the accounting system. index AK_Customer_AccountNumber
CustomerType	nchar(1)	not null		Customer type: I = Individual, S = Store
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_Customer_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
SalesTerritory (Sales)	TerritoryID	TerritoryID

Detail Table	Column	Referencing Column
CustomerAddress (Sales)	CustomerID	CustomerID
Individual (Sales)	CustomerID	CustomerID
SalesOrderHeader (Sales)	CustomerID	CustomerID
Store (Sales)	CustomerID	CustomerID

CustomerAddress (Sales)

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

Column	Data Type	Nullable	Default	Description / PK / Index
CustomerID	int	not null		Primary key. Foreign key to Customer.CustomerID. PK PK_CustomerAddress_CustomerID_AddressID
AddressID	int	not null		Primary key. Foreign key to Address.AddressID. PK PK_CustomerAddress_CustomerID_AddressID
AddressTypeID	int	not null		Address type. Foreign key to AddressType.AddressTypeID.
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_CustomerAddress_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Address (Person)	AddressID	AddressID
AddressType (Person)	AddressTypeID	AddressTypeID
Customer (Sales)	CustomerID	CustomerID

DatabaseLog (dbo)

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

Column	Data Type	Nullable	Default	Description / PK / Index
DatabaseLogID	int	not null		Primary key for DatabaseLog records. PK PK_DatabaseLog_DatabaseLogID
PostTime	datetime	not null		The date and time the DDL change occurred.
DatabaseUser	sysname	not null		The user who implemented the DDL change.
Event	sysname	not null		The type of DDL statement that was executed.
Schema	sysname	null		The schema to which the changed object belongs.
Object	sysname	null		The object that was changed by the DDL statment.
TSQL	nvarchar(max)	not null		The exact Transact-SQL statement that was executed.
XmlEvent	xml	not null		The raw XML data generated by database trigger.

Department (HumanResources)

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

Column	Data Type	Nullable	Default	Description / PK / Index
DepartmentID	smallint	not null		Primary key for Department records. PK PK_Department_DepartmentID
Name	Name	not null		Name of the department. index AK_Department_Name
GroupName	Name	not null		Name of the group to which the department belongs.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
EmployeeDepartmentHistory (HumanResources)	DepartmentID	DepartmentID

Document (Production)

Product maintenance documents.

Column	Data Type	Nullable	Default	Description / PK / Index
DocumentID	int	not null		Primary key for Document records. PK PK_Document_DocumentID
Title	nvarchar(50)	not null		Title of the document.
FileName	nvarchar(400)	not null		Directory path and file name of the document index AK_Document_FileName_Revision
FileExtension	nvarchar(8)	not null		File extension indicating the document type. For example, .doc or .txt.
Revision	nchar(5)	not null		Revision number of the document. index AK_Document_FileName_Revision
ChangeNumber	int	not null	(0)	Engineering change approval number.
Status	tinyint	not null		1 = Pending approval, 2 = Approved, 3 = Obsolete
DocumentSummary	nvarchar(max)	null		Document abstract.
Document	varbinary(max)	null		Complete document.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
ProductDocument (Production)	DocumentID	DocumentID

Employee (HumanResources)

Employee information such as salary, department, and title.

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

Foreign Key	Column	Referenced Column
Contact (Person)	ContactID	ContactID
Employee (HumanResources)	ManagerID	EmployeeID

Detail Table	Column	Referencing Column
Employee (HumanResources)	EmployeeID	ManagerID
EmployeeAddress (HumanResources)	EmployeeID	EmployeeID
EmployeeDepartmentHistory (HumanResources)	EmployeeID	EmployeeID
EmployeePayHistory (HumanResources)	EmployeeID	EmployeeID
JobCandidate (HumanResources)	EmployeeID	EmployeeID
PurchaseOrderHeader (Purchasing)	EmployeeID	EmployeeID
SalesPerson (Sales)	EmployeeID	SalesPersonID

Triggers
dEmployee	INSTEAD OF DELETE

EmployeeAddress (HumanResources)

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

Column	Data Type	Nullable	Default	Description / PK / Index
EmployeeID	int	not null		Primary key. Foreign key to Employee.EmployeeID. PK PK_EmployeeAddress_EmployeeID_AddressID
AddressID	int	not null		Primary key. Foreign key to Address.AddressID. PK PK_EmployeeAddress_EmployeeID_AddressID
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_EmployeeAddress_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Address (Person)	AddressID	AddressID
Employee (HumanResources)	EmployeeID	EmployeeID

EmployeeDepartmentHistory (HumanResources)

Employee department transfers.

Column	Data Type	Nullable	Default	Description / PK / Index
EmployeeID	int	not null		Employee identification number. Foreign key to Employee.EmployeeID. PK PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID
DepartmentID	smallint	not null		Department in which the employee worked including currently. Foreign key to Department.DepartmentID. PK PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID index IX_EmployeeDepartmentHistory_DepartmentID
ShiftID	tinyint	not null		Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID. PK PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID index IX_EmployeeDepartmentHistory_ShiftID
StartDate	datetime	not null		Date the employee started work in the department. PK PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID
EndDate	datetime	null		Date the employee left the department. NULL = Current department.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Department (HumanResources)	DepartmentID	DepartmentID
Employee (HumanResources)	EmployeeID	EmployeeID
Shift (HumanResources)	ShiftID	ShiftID

EmployeePayHistory (HumanResources)

Employee pay history.

Column	Data Type	Nullable	Default	Description / PK / Index
EmployeeID	int	not null		Employee identification number. Foreign key to Employee.EmployeeID. PK PK_EmployeePayHistory_EmployeeID_RateChangeDate
RateChangeDate	datetime	not null		Date the change in pay is effective PK PK_EmployeePayHistory_EmployeeID_RateChangeDate
Rate	money	not null		Salary hourly rate.
PayFrequency	tinyint	not null		1 = Salary received monthly, 2 = Salary received biweekly
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Employee (HumanResources)	EmployeeID	EmployeeID

ErrorLog (dbo)

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.

Column	Data Type	Nullable	Default	Description / PK / Index
ErrorLogID	int	not null		Primary key for ErrorLog records. PK PK_ErrorLog_ErrorLogID
ErrorTime	datetime	not null	(GETDATE())	The date and time at which the error occurred.
UserName	sysname	not null		The user who executed the batch in which the error occurred.
ErrorNumber	int	not null		The error number of the error that occurred.
ErrorSeverity	int	null		The severity of the error that occurred.
ErrorState	int	null		The state number of the error that occurred.
ErrorProcedure	nvarchar(126)	null		The name of the stored procedure or trigger where the error occurred.
ErrorLine	int	null		The line number at which the error occurred.
ErrorMessage	nvarchar(4000)	not null		The message text of the error that occurred.

Illustration (Production)

Bicycle assembly diagrams.

Column	Data Type	Nullable	Default	Description / PK / Index
IllustrationID	int	not null		Primary key for Illustration records. PK PK_Illustration_IllustrationID
Diagram	XML	null		Illustrations used in manufacturing instructions. Stored as XML.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
ProductModelIllustration (Production)	IllustrationID	IllustrationID

Individual (Sales)

Demographic data about customers that purchase Adventure Works products online.

Column	Data Type	Nullable	Default	Description / PK / Index
CustomerID	int	not null		Unique customer identification number. Foreign key to Customer.CustomerID. PK PK_Individual_CustomerID
ContactID	int	not null		Identifies the customer in the Contact table. Foreign key to Contact.ContactID.
Demographics	XML	null		Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis. index PXML_Individual_Demographics index XMLPATH_Individual_Demographics index XMLPROPERTY_Individual_Demographics index XMLVALUE_Individual_Demographics
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Contact (Person)	ContactID	ContactID
Customer (Sales)	CustomerID	CustomerID

Triggers
iuIndividual	ON INSERT UPDATE

JobCandidate (HumanResources)

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

Column	Data Type	Nullable	Default	Description / PK / Index
JobCandidateID	int	not null		Primary key for JobCandidate records. PK PK_JobCandidate_JobCandidateID
EmployeeID	int	null		Employee identification number if applicant was hired. Foreign key to Employee.EmployeeID. index IX_JobCandidate_EmployeeID
Resume	XML	null		R�sum� in XML format.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Employee (HumanResources)	EmployeeID	EmployeeID

Location (Production)

Product inventory and manufacturing locations.

Column	Data Type	Nullable	Default	Description / PK / Index
LocationID	smallint	not null		Primary key for Location records. PK PK_Location_LocationID
Name	Name	not null		Location description. index AK_Location_Name
CostRate	smallmoney	not null	(0.00)	Standard hourly cost of the manufacturing location.
Availability	decimal(8, 2)	not null	(0.00)	Work capacity (in hours) of the manufacturing location.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
ProductInventory (Production)	LocationID	LocationID
WorkOrderRouting (Production)	LocationID	LocationID

Product (Production)

Products sold or used in the manfacturing of sold products.

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

Foreign Key	Column	Referenced Column
ProductModel (Production)	ProductModelID	ProductModelID
ProductSubcategory (Production)	ProductSubcategoryID	ProductSubcategoryID
UnitMeasure (Production)	SizeUnitMeasureCode	UnitMeasureCode
UnitMeasure (Production)	WeightUnitMeasureCode	UnitMeasureCode

Detail Table	Column	Referencing Column
BillOfMaterials (Production)	ProductID	ComponentID
BillOfMaterials (Production)	ProductID	ProductAssemblyID
ProductCostHistory (Production)	ProductID	ProductID
ProductDocument (Production)	ProductID	ProductID
ProductInventory (Production)	ProductID	ProductID
ProductListPriceHistory (Production)	ProductID	ProductID
ProductProductPhoto (Production)	ProductID	ProductID
ProductReview (Production)	ProductID	ProductID
ProductVendor (Purchasing)	ProductID	ProductID
PurchaseOrderDetail (Purchasing)	ProductID	ProductID
ShoppingCartItem (Sales)	ProductID	ProductID
SpecialOfferProduct (Sales)	ProductID	ProductID
TransactionHistory (Production)	ProductID	ProductID
WorkOrder (Production)	ProductID	ProductID

ProductCategory (Production)

High-level product categorization.

Column	Data Type	Nullable	Default	Description / PK / Index
ProductCategoryID	int	not null		Primary key for ProductCategory records. PK PK_ProductCategory_ProductCategoryID
Name	Name	not null		Category description. index AK_ProductCategory_Name
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_ProductCategory_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
ProductSubcategory (Production)	ProductCategoryID	ProductCategoryID

ProductCostHistory (Production)

Changes in the cost of a product over time.

Column	Data Type	Nullable	Default	Description / PK / Index
ProductID	int	not null		Product identification number. Foreign key to Product.ProductID PK PK_ProductCostHistory_ProductID_StartDate
StartDate	datetime	not null		Product cost start date. PK PK_ProductCostHistory_ProductID_StartDate
EndDate	datetime	null		Product cost end date.
StandardCost	money	not null		Standard cost of the product.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Product (Production)	ProductID	ProductID

ProductDescription (Production)

Product descriptions in several languages.

Column	Data Type	Nullable	Default	Description / PK / Index
ProductDescriptionID	int	not null		Primary key for ProductDescription records. PK PK_ProductDescription_ProductDescriptionID
Description	nvarchar(400)	not null		Description of the product.
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_ProductDescription_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
ProductModelProductDescriptionCulture (Production)	ProductDescriptionID	ProductDescriptionID

ProductDocument (Production)

Cross-reference table mapping products to related product documents.

Column	Data Type	Nullable	Default	Description / PK / Index
ProductID	int	not null		Product identification number. Foreign key to Product.ProductID. PK PK_ProductDocument_ProductID_DocumentID
DocumentID	int	not null		Document identification number. Foreign key to Document.DocumentID. PK PK_ProductDocument_ProductID_DocumentID
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Document (Production)	DocumentID	DocumentID
Product (Production)	ProductID	ProductID

ProductInventory (Production)

Product inventory information.

Column	Data Type	Nullable	Default	Description / PK / Index
ProductID	int	not null		Product identification number. Foreign key to Product.ProductID. PK PK_ProductInventory_ProductID_LocationID
LocationID	smallint	not null		Inventory location identification number. Foreign key to Location.LocationID. PK PK_ProductInventory_ProductID_LocationID
Shelf	nvarchar(10)	not null		Storage compartment within an inventory location.
Bin	tinyint	not null		Storage container on a shelf in an inventory location.
Quantity	smallint	not null	(0)	Quantity of products in the inventory location.
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Location (Production)	LocationID	LocationID
Product (Production)	ProductID	ProductID

ProductListPriceHistory (Production)

Changes in the list price of a product over time.

Column	Data Type	Nullable	Default	Description / PK / Index
ProductID	int	not null		Product identification number. Foreign key to Product.ProductID PK PK_ProductListPriceHistory_ProductID_StartDate
StartDate	datetime	not null		List price start date. PK PK_ProductListPriceHistory_ProductID_StartDate
EndDate	datetime	null		List price end date
ListPrice	money	not null		Product list price.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Product (Production)	ProductID	ProductID

ProductModel (Production)

Product model classification.

Column	Data Type	Nullable	Default	Description / PK / Index
ProductModelID	int	not null		Primary key for ProductModel records. PK PK_ProductModel_ProductModelID
Name	Name	not null		Product model description. index AK_ProductModel_Name
CatalogDescription	XML	null		Detailed product catalog information in xml format. index PXML_ProductModel_CatalogDescription
Instructions	XML	null		Manufacturing instructions in xml format. index PXML_ProductModel_Instructions
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_ProductModel_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
Product (Production)	ProductModelID	ProductModelID
ProductModelIllustration (Production)	ProductModelID	ProductModelID
ProductModelProductDescriptionCulture (Production)	ProductModelID	ProductModelID

ProductModelIllustration (Production)

Cross-reference table mapping product models and illustrations.

Column	Data Type	Nullable	Default	Description / PK / Index
ProductModelID	int	not null		Primary key. Foreign key to ProductModel.ProductModelID. PK PK_ProductModelIllustration_ProductModelID_IllustrationID
IllustrationID	int	not null		Primary key. Foreign key to Illustration.IllustrationID. PK PK_ProductModelIllustration_ProductModelID_IllustrationID
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Illustration (Production)	IllustrationID	IllustrationID
ProductModel (Production)	ProductModelID	ProductModelID

ProductModelProductDescriptionCulture (Production)

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

Column	Data Type	Nullable	Default	Description / PK / Index
ProductModelID	int	not null		Primary key. Foreign key to ProductModel.ProductModelID. PK PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID
ProductDescriptionID	int	not null		Primary key. Foreign key to ProductDescription.ProductDescriptionID. PK PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID
CultureID	nchar(6)	not null		Culture identification number. Foreign key to Culture.CultureID. PK PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Culture (Production)	CultureID	CultureID
ProductDescription (Production)	ProductDescriptionID	ProductDescriptionID
ProductModel (Production)	ProductModelID	ProductModelID

ProductPhoto (Production)

Product images.

Column	Data Type	Nullable	Default	Description / PK / Index
ProductPhotoID	int	not null		Primary key for ProductPhoto records. PK PK_ProductPhoto_ProductPhotoID
ThumbNailPhoto	varbinary(max)	null		Small image of the product.
ThumbnailPhotoFileName	nvarchar(50)	null		Small image file name.
LargePhoto	varbinary(max)	null		Large image of the product.
LargePhotoFileName	nvarchar(50)	null		Large image file name.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
ProductProductPhoto (Production)	ProductPhotoID	ProductPhotoID

ProductProductPhoto (Production)

Cross-reference table mapping products and product photos.

Column	Data Type	Nullable	Default	Description / PK / Index
ProductID	int	not null		Product identification number. Foreign key to Product.ProductID. PK PK_ProductProductPhoto_ProductID_ProductPhotoID
ProductPhotoID	int	not null		Product photo identification number. Foreign key to ProductPhoto.ProductPhotoID. PK PK_ProductProductPhoto_ProductID_ProductPhotoID
Primary	Flag	not null	(0)	0 = Photo is not the principal image. 1 = Photo is the principal image.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Product (Production)	ProductID	ProductID
ProductPhoto (Production)	ProductPhotoID	ProductPhotoID

ProductReview (Production)

Customer reviews of products they have purchased.

Column	Data Type	Nullable	Default	Description / PK / Index
ProductReviewID	int	not null		Primary key for ProductReview records. PK PK_ProductReview_ProductReviewID
ProductID	int	not null		Product identification number. Foreign key to Product.ProductID. index IX_ProductReview_ProductID_Name
ReviewerName	Name	not null		Name of the reviewer. index IX_ProductReview_ProductID_Name
ReviewDate	datetime	not null	(GETDATE())	Date review was submitted.
EmailAddress	nvarchar(50)	not null		Reviewer's e-mail address.
Rating	int	not null		Product rating given by the reviewer. Scale is 1 to 5 with 5 as the highest rating.
Comments	nvarchar(3850)	not null		Reviewer's comments index IX_ProductReview_ProductID_Name
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Product (Production)	ProductID	ProductID

ProductSubcategory (Production)

Product subcategories. See ProductCategory table.

Column	Data Type	Nullable	Default	Description / PK / Index
ProductSubcategoryID	int	not null		Primary key for ProductSubcategory records. PK PK_ProductSubcategory_ProductSubcategoryID
ProductCategoryID	int	not null		Product category identification number. Foreign key to ProductCategory.ProductCategoryID.
Name	Name	not null		Subcategory description. index AK_ProductSubcategory_Name
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_ProductSubcategory_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
ProductCategory (Production)	ProductCategoryID	ProductCategoryID

Detail Table	Column	Referencing Column
Product (Production)	ProductSubcategoryID	ProductSubcategoryID

ProductVendor (Purchasing)

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

Column	Data Type	Nullable	Default	Description / PK / Index
ProductID	int	not null		Primary key. Foreign key to Product.ProductID. PK PK_ProductVendor_ProductID_VendorID
VendorID	int	not null		Primary key. Foreign key to Vendor.VendorID. PK PK_ProductVendor_ProductID_VendorID index IX_ProductVendor_VendorID
AverageLeadTime	int	not null		The average span of time (in days) between placing an order with the vendor and receiving the purchased product.
StandardPrice	money	not null		The vendor's usual selling price.
LastReceiptCost	money	null		The selling price when last purchased.
LastReceiptDate	datetime	null		Date the product was last received by the vendor.
MinOrderQty	int	not null		The maximum quantity that should be ordered.
MaxOrderQty	int	not null		The minimum quantity that should be ordered.
OnOrderQty	int	null		The quantity currently on order.
UnitMeasureCode	nchar(3)	not null		The product's unit of measure. index IX_ProductVendor_UnitMeasureCode
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Product (Production)	ProductID	ProductID
UnitMeasure (Production)	UnitMeasureCode	UnitMeasureCode
Vendor (Purchasing)	VendorID	VendorID

PurchaseOrderDetail (Purchasing)

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

Column	Data Type	Nullable	Default	Description / PK / Index
PurchaseOrderID	int	not null		Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID. PK PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
PurchaseOrderDetailID	int	not null		Primary key. One line number per purchased product. PK PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
DueDate	datetime	not null		Date the product is expected to be received.
OrderQty	smallint	not null		Quantity ordered.
ProductID	int	not null		Product identification number. Foreign key to Product.ProductID. index IX_PurchaseOrderDetail_ProductID
UnitPrice	money	not null		Vendor's selling price of a single product.
LineTotal				Per product subtotal. Computed as OrderQty * UnitPrice.
ReceivedQty	decimal(8, 2)	not null		Quantity actually received from the vendor.
RejectedQty	decimal(8, 2)	not null		Quantity rejected during inspection.
StockedQty				Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Product (Production)	ProductID	ProductID
PurchaseOrderHeader (Purchasing)	PurchaseOrderID	PurchaseOrderID

Triggers
iPurchaseOrderDetail	ON INSERT
uPurchaseOrderDetail	ON UPDATE

PurchaseOrderHeader (Purchasing)

General purchase order information. See PurchaseOrderDetail.

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

Foreign Key	Column	Referenced Column
Employee (HumanResources)	EmployeeID	EmployeeID
ShipMethod (Purchasing)	ShipMethodID	ShipMethodID
Vendor (Purchasing)	VendorID	VendorID

Detail Table	Column	Referencing Column
PurchaseOrderDetail (Purchasing)	PurchaseOrderID	PurchaseOrderID

Triggers
uPurchaseOrderHeader	ON UPDATE

SalesOrderDetail (Sales)

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

Column	Data Type	Nullable	Default	Description / PK / Index
SalesOrderID	int	not null		Primary key. Foreign key to SalesOrderHeader.SalesOrderID. PK PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
SalesOrderDetailID	int	not null		Primary key. One incremental unique number per product sold. PK PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
CarrierTrackingNumber	nvarchar(25)	null		Shipment tracking number supplied by the shipper.
OrderQty	smallint	not null		Quantity ordered per product.
ProductID	int	not null		Product sold to customer. Foreign key to Product.ProductID. index IX_SalesOrderDetail_ProductID
SpecialOfferID	int	not null		Promotional code. Foreign key to SpecialOffer.SpecialOfferID.
UnitPrice	money	not null		Selling price of a single product.
UnitPriceDiscount	money	not null	(0.0)	Discount amount.
LineTotal				Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_SalesOrderDetail_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
SalesOrderHeader (Sales)	SalesOrderID	SalesOrderID
SpecialOfferProduct (Sales)	SpecialOfferID	SpecialOfferID

Triggers
iduSalesOrderDetail	ON INSERT UPDATE DELETE

SalesOrderHeader (Sales)

General sales order information.

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

Foreign Key	Column	Referenced Column
Address (Person)	BillToAddressID	AddressID
Address (Person)	ShipToAddressID	AddressID
Contact (Person)	ContactID	ContactID
CreditCard (Sales)	CreditCardID	CreditCardID
CurrencyRate (Sales)	CurrencyRateID	CurrencyRateID
Customer (Sales)	CustomerID	CustomerID
SalesPerson (Sales)	SalesPersonID	SalesPersonID
SalesTerritory (Sales)	TerritoryID	TerritoryID
ShipMethod (Purchasing)	ShipMethodID	ShipMethodID

Detail Table	Column	Referencing Column
SalesOrderDetail (Sales)	SalesOrderID	SalesOrderID
SalesOrderHeaderSalesReason (Sales)	SalesOrderID	SalesOrderID

Triggers
uSalesOrderHeader	ON UPDATE

SalesOrderHeaderSalesReason (Sales)

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

Column	Data Type	Nullable	Default	Description / PK / Index
SalesOrderID	int	not null		Primary key. Foreign key to SalesOrderHeader.SalesOrderID. PK PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID
SalesReasonID	int	not null		Primary key. Foreign key to SalesReason.SalesReasonID. PK PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
SalesOrderHeader (Sales)	SalesOrderID	SalesOrderID
SalesReason (Sales)	SalesReasonID	SalesReasonID

SalesPerson (Sales)

Sales representative current information.

Column	Data Type	Nullable	Default	Description / PK / Index
SalesPersonID	int	not null		Primary key for SalesPerson records. PK PK_SalesPerson_SalesPersonID
TerritoryID	int	null		Territory currently assigned to. Foreign key to SalesTerritory.SalesTerritoryID.
SalesQuota	money	null		Projected yearly sales.
Bonus	money	not null	(0.00)	Bonus due if quota is met.
CommissionPct	smallmoney	not null	(0.00)	Commision percent received per sale.
SalesYTD	money	not null	(0.00)	Sales total year to date.
SalesLastYear	money	not null	(0.00)	Sales total of previous year.
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_SalesPerson_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Employee (HumanResources)	SalesPersonID	EmployeeID
SalesTerritory (Sales)	TerritoryID	TerritoryID

Detail Table	Column	Referencing Column
SalesOrderHeader (Sales)	SalesPersonID	SalesPersonID
SalesPersonQuotaHistory (Sales)	SalesPersonID	SalesPersonID
SalesTerritoryHistory (Sales)	SalesPersonID	SalesPersonID
Store (Sales)	SalesPersonID	SalesPersonID

SalesPersonQuotaHistory (Sales)

Sales performance tracking.

Column	Data Type	Nullable	Default	Description / PK / Index
SalesPersonID	int	not null		Sales person identification number. Foreign key to SalesPerson.SalesPersonID. PK PK_SalesPersonQuotaHistory_SalesPersonID_QuotaDate
QuotaDate	datetime	not null		Sales quota date. PK PK_SalesPersonQuotaHistory_SalesPersonID_QuotaDate
SalesQuota	money	not null		Sales quota amount.
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_SalesPersonQuotaHistory_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
SalesPerson (Sales)	SalesPersonID	SalesPersonID

SalesReason (Sales)

Lookup table of customer purchase reasons.

Column	Data Type	Nullable	Default	Description / PK / Index
SalesReasonID	int	not null		Primary key for SalesReason records. PK PK_SalesReason_SalesReasonID
Name	Name	not null		Sales reason description.
ReasonType	Name	not null		Category the sales reason belongs to.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
SalesOrderHeaderSalesReason (Sales)	SalesReasonID	SalesReasonID

SalesTaxRate (Sales)

Tax rate lookup table.

Column	Data Type	Nullable	Default	Description / PK / Index
SalesTaxRateID	int	not null		Primary key for SalesTaxRate records. PK PK_SalesTaxRate_SalesTaxRateID
StateProvinceID	int	not null		State, province, or country/region the sales tax applies to. index AK_SalesTaxRate_StateProvinceID_TaxType
TaxType	tinyint	not null		1 = Tax applied to retail transactions, 2 = Tax applied to wholesale transactions, 3 = Tax applied to all sales (retail and wholesale) transactions. index AK_SalesTaxRate_StateProvinceID_TaxType
TaxRate	smallmoney	not null	(0.00)	Tax rate amount.
Name	Name	not null		Tax rate description.
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_SalesTaxRate_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
StateProvince (Person)	StateProvinceID	StateProvinceID

SalesTerritory (Sales)

Sales territory lookup table.

Column	Data Type	Nullable	Default	Description / PK / Index
TerritoryID	int	not null		Primary key for SalesTerritory records. PK PK_SalesTerritory_TerritoryID
Name	Name	not null		Sales territory description index AK_SalesTerritory_Name
CountryRegionCode	nvarchar(3)	not null		ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.
Group	nvarchar(50)	not null		Geographic area to which the sales territory belong.
SalesYTD	money	not null	(0.00)	Sales in the territory year to date.
SalesLastYear	money	not null	(0.00)	Sales in the territory the previous year.
CostYTD	money	not null	(0.00)	Business costs in the territory year to date.
CostLastYear	money	not null	(0.00)	Business costs in the territory the previous year.
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_SalesTerritory_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
Customer (Sales)	TerritoryID	TerritoryID
SalesOrderHeader (Sales)	TerritoryID	TerritoryID
SalesPerson (Sales)	TerritoryID	TerritoryID
SalesTerritoryHistory (Sales)	TerritoryID	TerritoryID
StateProvince (Person)	TerritoryID	TerritoryID

SalesTerritoryHistory (Sales)

Sales representative transfers to other sales territories.

Column	Data Type	Nullable	Default	Description / PK / Index
SalesPersonID	int	not null		Primary key for SalesTerritoryHistory records. PK PK_SalesTerritoryHistory_SalesPersonID_StartDate_TerritoryID
TerritoryID	int	not null		Territory identification number. Foreign key to SalesTerritory.SalesTerritoryID. PK PK_SalesTerritoryHistory_SalesPersonID_StartDate_TerritoryID
StartDate	datetime	not null		Date the sales representive started work in the territory. PK PK_SalesTerritoryHistory_SalesPersonID_StartDate_TerritoryID
EndDate	datetime	null		Date the sales representative left work in the territory.
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_SalesTerritoryHistory_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
SalesPerson (Sales)	SalesPersonID	SalesPersonID
SalesTerritory (Sales)	TerritoryID	TerritoryID

ScrapReason (Production)

Manufacturing failure reasons lookup table.

Column	Data Type	Nullable	Default	Description / PK / Index
ScrapReasonID	smallint	not null		Primary key for ScrapReason records. PK PK_ScrapReason_ScrapReasonID
Name	Name	not null		Failure description. index AK_ScrapReason_Name
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
WorkOrder (Production)	ScrapReasonID	ScrapReasonID

Shift (HumanResources)

Work shift lookup table.

Column	Data Type	Nullable	Default	Description / PK / Index
ShiftID	tinyint	not null		Primary key for Shift records. PK PK_Shift_ShiftID
Name	Name	not null		Shift description. index AK_Shift_Name
StartTime	datetime	not null		Shift start time. index AK_Shift_StartTime_EndTime
EndTime	datetime	not null		Shift end time. index AK_Shift_StartTime_EndTime
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
EmployeeDepartmentHistory (HumanResources)	ShiftID	ShiftID

ShipMethod (Purchasing)

Shipping company lookup table.

Column	Data Type	Nullable	Default	Description / PK / Index
ShipMethodID	int	not null		Primary key for ShipMethod records. PK PK_ShipMethod_ShipMethodID
Name	Name	not null		Shipping company name. index AK_ShipMethod_Name
ShipBase	money	not null	(0.00)	Minimum shipping charge.
ShipRate	money	not null	(0.00)	Shipping charge per pound.
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_ShipMethod_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
PurchaseOrderHeader (Purchasing)	ShipMethodID	ShipMethodID
SalesOrderHeader (Sales)	ShipMethodID	ShipMethodID

ShoppingCartItem (Sales)

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

Column	Data Type	Nullable	Default	Description / PK / Index
ShoppingCartItemID	int	not null		Primary key for ShoppingCartItem records. PK PK_ShoppingCartItem_ShoppingCartItemID
ShoppingCartID	nvarchar(50)	not null		Shopping cart identification number. index IX_ShoppingCartItem_ShoppingCartID_ProductID
Quantity	int	not null	(1)	Product quantity ordered.
ProductID	int	not null		Product ordered. Foreign key to Product.ProductID. index IX_ShoppingCartItem_ShoppingCartID_ProductID
DateCreated	datetime	not null	(GETDATE())	Date the time the record was created.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Product (Production)	ProductID	ProductID

SpecialOffer (Sales)

Sale discounts lookup table.

Column	Data Type	Nullable	Default	Description / PK / Index
SpecialOfferID	int	not null		Primary key for SpecialOffer records. PK PK_SpecialOffer_SpecialOfferID
Description	nvarchar(255)	not null		Discount description.
DiscountPct	smallmoney	not null	(0.00)	Discount precentage.
Type	nvarchar(50)	not null		Discount type category.
Category	nvarchar(50)	not null		Group the discount applies to such as Reseller or Customer.
StartDate	datetime	not null		Discount start date.
EndDate	datetime	not null		Discount end date.
MinQty	int	not null	(0)	Minimum discount percent allowed.
MaxQty	int	null		Maximum discount percent allowed.
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_SpecialOffer_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
SpecialOfferProduct (Sales)	SpecialOfferID	SpecialOfferID

SpecialOfferProduct (Sales)

Cross-reference table mapping products to special offer discounts.

Column	Data Type	Nullable	Default	Description / PK / Index
SpecialOfferID	int	not null		Primary key for SpecialOfferProduct records. PK PK_SpecialOfferProduct_SpecialOfferID_ProductID
ProductID	int	not null		Product identification number. Foreign key to Product.ProductID. PK PK_SpecialOfferProduct_SpecialOfferID_ProductID index IX_SpecialOfferProduct_ProductID
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_SpecialOfferProduct_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Product (Production)	ProductID	ProductID
SpecialOffer (Sales)	SpecialOfferID	SpecialOfferID

Detail Table	Column	Referencing Column
SalesOrderDetail (Sales)	SpecialOfferID	SpecialOfferID
SalesOrderDetail (Sales)	SpecialOfferID	SpecialOfferID

StateProvince (Person)

State and province lookup table.

Column	Data Type	Nullable	Default	Description / PK / Index
StateProvinceID	int	not null		Primary key for StateProvince records. PK PK_StateProvince_StateProvinceID
StateProvinceCode	nchar(3)	not null		ISO standard state or province code. index AK_StateProvince_StateProvinceCode_CountryRegionCode
CountryRegionCode	nvarchar(3)	not null		ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode. index AK_StateProvince_StateProvinceCode_CountryRegionCode
IsOnlyStateProvinceFlag	Flag	not null	(1)	0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode.
Name	Name	not null		State or province description. index AK_StateProvince_Name
TerritoryID	int	not null		ID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID.
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_StateProvince_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
CountryRegion (Person)	CountryRegionCode	CountryRegionCode
SalesTerritory (Sales)	TerritoryID	TerritoryID

Detail Table	Column	Referencing Column
Address (Person)	StateProvinceID	StateProvinceID
SalesTaxRate (Sales)	StateProvinceID	StateProvinceID

Store (Sales)

Customers (resellers) of Adventure Works products.

Column	Data Type	Nullable	Default	Description / PK / Index
CustomerID	int	not null		Primary key. Foreign key to Customer.CustomerID. PK PK_Store_CustomerID
Name	Name	not null		Name of the store.
SalesPersonID	int	null		ID of the sales person assigned to the customer. Foreign key to SalesPerson.SalesPersonID. index IX_Store_SalesPersonID
Demographics	XML	null		Demographic informationg about the store such as the number of employees, annual sales and store type. index PXML_Store_Demographics
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_Store_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Customer (Sales)	CustomerID	CustomerID
SalesPerson (Sales)	SalesPersonID	SalesPersonID

Detail Table	Column	Referencing Column
StoreContact (Sales)	CustomerID	CustomerID

Triggers
iStore	ON INSERT

StoreContact (Sales)

Cross-reference table mapping stores and their employees.

Column	Data Type	Nullable	Default	Description / PK / Index
CustomerID	int	not null		Store identification number. Foreign key to Customer.CustomerID. PK PK_StoreContact_CustomerID_ContactID
ContactID	int	not null		Contact (store employee) identification number. Foreign key to Contact.ContactID. PK PK_StoreContact_CustomerID_ContactID index IX_StoreContact_ContactID
ContactTypeID	int	not null		Contact type such as owner or purchasing agent. Foreign key to ContactType.ContactTypeID. index IX_StoreContact_ContactTypeID
rowguid	uniqueidentifier	not null	(NEWID())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. index AK_StoreContact_rowguid
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Contact (Person)	ContactID	ContactID
ContactType (Person)	ContactTypeID	ContactTypeID
Store (Sales)	CustomerID	CustomerID

TransactionHistory (Production)

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

Column	Data Type	Nullable	Default	Description / PK / Index
TransactionID	int	not null		Primary key for TransactionHistory records. PK PK_TransactionHistory_TransactionID
ProductID	int	not null		Product identification number. Foreign key to Product.ProductID. index IX_TransactionHistory_ProductID
ReferenceOrderID	int	not null		Purchase order, sales order, or work order identification number. index IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
ReferenceOrderLineID	int	not null	(0)	Line number associated with the purchase order, sales order, or work order. index IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
TransactionDate	datetime	not null	(GETDATE())	Date and time of the transaction.
TransactionType	nchar(1)	not null		W = WorkOrder, S = SalesOrder, P = PurchaseOrder
Quantity	int	not null		Product quantity.
ActualCost	money	not null		Product cost.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Product (Production)	ProductID	ProductID

TransactionHistoryArchive (Production)

Transactions for previous years.

Column	Data Type	Nullable	Default	Description / PK / Index
TransactionID	int	not null		Primary key for TransactionHistoryArchive records. PK PK_TransactionHistoryArchive_TransactionID
ProductID	int	not null		Product identification number. Foreign key to Product.ProductID. index IX_TransactionHistoryArchive_ProductID
ReferenceOrderID	int	not null		Purchase order, sales order, or work order identification number. index IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID
ReferenceOrderLineID	int	not null	(0)	Line number associated with the purchase order, sales order, or work order. index IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID
TransactionDate	datetime	not null	(GETDATE())	Date and time of the transaction.
TransactionType	nchar(1)	not null		W = Work Order, S = Sales Order, P = Purchase Order
Quantity	int	not null		Product quantity.
ActualCost	money	not null		Product cost.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

UnitMeasure (Production)

Unit of measure lookup table.

Column	Data Type	Nullable	Default	Description / PK / Index
UnitMeasureCode	nchar(3)	not null		Primary key. PK PK_UnitMeasure_UnitMeasureCode
Name	Name	not null		Unit of measure description. index AK_UnitMeasure_Name
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Detail Table	Column	Referencing Column
BillOfMaterials (Production)	UnitMeasureCode	UnitMeasureCode
Product (Production)	UnitMeasureCode	SizeUnitMeasureCode
Product (Production)	UnitMeasureCode	WeightUnitMeasureCode
ProductVendor (Purchasing)	UnitMeasureCode	UnitMeasureCode

Vendor (Purchasing)

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

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

Detail Table	Column	Referencing Column
ProductVendor (Purchasing)	VendorID	VendorID
PurchaseOrderHeader (Purchasing)	VendorID	VendorID
VendorAddress (Purchasing)	VendorID	VendorID
VendorContact (Purchasing)	VendorID	VendorID

Triggers
dVendor	INSTEAD OF DELETE

VendorAddress (Purchasing)

Cross-reference mapping vendors and addresses.

Column	Data Type	Nullable	Default	Description / PK / Index
VendorID	int	not null		Primary key. Foreign key to Vendor.VendorID. PK PK_VendorAddress_VendorID_AddressID
AddressID	int	not null		Primary key. Foreign key to Address.AddressID. PK PK_VendorAddress_VendorID_AddressID index IX_VendorAddress_AddressID
AddressTypeID	int	not null		Address type. Foreign key to AddressType.AddressTypeID.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Address (Person)	AddressID	AddressID
AddressType (Person)	AddressTypeID	AddressTypeID
Vendor (Purchasing)	VendorID	VendorID

VendorContact (Purchasing)

Cross-reference table mapping vendors and their employees.

Column	Data Type	Nullable	Default	Description / PK / Index
VendorID	int	not null		Primary key. PK PK_VendorContact_VendorID_ContactID
ContactID	int	not null		Contact (Vendor employee) identification number. Foreign key to Contact.ContactID. PK PK_VendorContact_VendorID_ContactID index IX_VendorContact_ContactID
ContactTypeID	int	not null		Contact type such as sales manager, or sales agent. index IX_VendorContact_ContactTypeID
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Contact (Person)	ContactID	ContactID
ContactType (Person)	ContactTypeID	ContactTypeID
Vendor (Purchasing)	VendorID	VendorID

WorkOrder (Production)

Manufacturing work orders.

Column	Data Type	Nullable	Default	Description / PK / Index
WorkOrderID	int	not null		Primary key for WorkOrder records. PK PK_WorkOrder_WorkOrderID
ProductID	int	not null		Product identification number. Foreign key to Product.ProductID. index IX_WorkOrder_ProductID
OrderQty	int	not null		Product quantity to build.
StockedQty				Quantity built and put in inventory.
ScrappedQty	smallint	not null		Quantity that failed inspection.
StartDate	datetime	not null		Work order start date.
EndDate	datetime	null		Work order end date.
DueDate	datetime	not null		Work order due date.
ScrapReasonID	smallint	null		Reason for inspection failure. index IX_WorkOrder_ScrapReasonID
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Product (Production)	ProductID	ProductID
ScrapReason (Production)	ScrapReasonID	ScrapReasonID

Detail Table	Column	Referencing Column
WorkOrderRouting (Production)	WorkOrderID	WorkOrderID

Triggers
iWorkOrder	ON INSERT
uWorkOrder	ON UPDATE

WorkOrderRouting (Production)

Work order details.

Column	Data Type	Nullable	Default	Description / PK / Index
WorkOrderID	int	not null		Primary key. Foreign key to WorkOrder.WorkOrderID. PK PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence
ProductID	int	not null		Primary key. Foreign key to Product.ProductID. PK PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence index IX_WorkOrderRouting_ProductID
OperationSequence	smallint	not null		Primary key. Indicates the manufacturing process sequence. PK PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence
LocationID	smallint	not null		Manufacturing location where the part is processed. Foreign key to Location.LocationID.
ScheduledStartDate	datetime	not null		Planned manufacturing start date.
ScheduledEndDate	datetime	not null		Planned manufacturing end date.
ActualStartDate	datetime	null		Actual start date.
ActualEndDate	datetime	null		Actual end date.
ActualResourceHrs	decimal(9, 4)	null		Number of manufacturing hours used.
PlannedCost	money	not null		Estimated manufacturing cost.
ActualCost	money	null		Actual manufacturing cost.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Foreign Key	Column	Referenced Column
Location (Production)	LocationID	LocationID
WorkOrder (Production)	WorkOrderID	WorkOrderID

Views

vAdditionalContactInfo (Person)

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

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;

vEmployee (HumanResources)

Employee names and addresses.

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

vEmployeeDepartment (HumanResources)

Returns employee name, title, and current department.

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());

vEmployeeDepartmentHistory (HumanResources)

Returns employee name and current and previous departments.

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

vIndividualCustomer (Sales)

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

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');

vIndividualDemographics (Sales)

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

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;

vJobCandidate (HumanResources)

Job candidate names and resumes.

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);

vJobCandidateEducation (HumanResources)

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.

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);

vJobCandidateEmployment (HumanResources)

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.

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);

vProductAndDescription (Production)

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

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

vProductModelCatalogDescription (Production)

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

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;

vProductModelInstructions (Production)

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

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);

vSalesPerson (Sales)

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

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

vSalesPersonSalesByFiscalYears (Sales)

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

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;

vStateProvinceCountryRegion (Person)

Joins StateProvince table with CountryRegion table.

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

vStoreWithDemographics (Sales)

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

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');

vVendor (Purchasing)

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

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

Procedures

uspGetBillOfMaterials (dbo)

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

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;

uspGetEmployeeManagers (dbo)

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

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;

uspGetManagerEmployees (dbo)

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

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;

uspGetWhereUsedProductID (dbo)

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

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;

uspLogError (dbo)

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.

-- 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;

uspPrintError (dbo)

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.

-- 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;

uspUpdateEmployeeHireInfo (HumanResources)

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

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;

uspUpdateEmployeeLogin (HumanResources)

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

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;

uspUpdateEmployeePersonalInfo (HumanResources)

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

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;

Functions

ufnGetAccountingEndDate (dbo)

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

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

ufnGetAccountingStartDate (dbo)

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

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

ufnGetDocumentStatusText (dbo)

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

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;

ufnGetProductDealerPrice (dbo)

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

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;

ufnGetProductListPrice (dbo)

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

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;

ufnGetProductStandardCost (dbo)

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

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;

ufnGetPurchaseOrderStatusText (dbo)

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

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;

ufnGetSalesOrderStatusText (dbo)

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

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;

ufnGetStock (dbo)

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

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;

ufnLeadingZeros (dbo)

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

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;

ufnGetContactInformation (dbo)

Table value function returning the first name, last name, job title and contact type for a given contact.

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
        INSERT @retContactInformation
        SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
    END;

    RETURN;
END;

Schemas

HumanResources

Contains objects related to employees and departments.

CREATE SCHEMA HumanResources AUTHORIZATION dbo

Person

Contains objects related to names and addresses of customers, vendors, and employees

CREATE SCHEMA Person AUTHORIZATION dbo

Production

Contains objects related to products, inventory, and manufacturing.

CREATE SCHEMA Production AUTHORIZATION dbo

Purchasing

Contains objects related to vendors and purchase orders.

CREATE SCHEMA Purchasing AUTHORIZATION dbo

Sales

Contains objects related to customers, sales orders, and sales territories.

CREATE SCHEMA Sales AUTHORIZATION dbo

Types

AccountNumber (dbo)

CREATE TYPE dbo.AccountNumber FROM nvarchar(15) NULL

Flag (dbo)

CREATE TYPE dbo.Flag FROM bit NOT NULL

Name (dbo)

CREATE TYPE dbo.[Name] FROM nvarchar(50) NULL

NameStyle (dbo)

CREATE TYPE dbo.NameStyle FROM bit NOT NULL

OrderNumber (dbo)

CREATE TYPE dbo.OrderNumber FROM nvarchar(25) NULL

Phone (dbo)

CREATE TYPE dbo.Phone FROM nvarchar(25) NULL

XML Schema Collections

AdditionalContactInfoSchemaCollection (Person)

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

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo" elementFormDefault="qualified" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <!-- the following imports are not needed. They simply provide readability -->
  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactRecord" />
  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes" />
  <xsd:element name="AdditionalContactInfo">
    <xsd:complexType mixed="true">
      <xsd:sequence>
        <xsd:any processContents="strict" namespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactRecord &#xD;&#xA;                        http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes" minOccurs="0" maxOccurs="unbounded" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

HRResumeSchemaCollection (HumanResources)

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

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume" xmlns:xsd="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
  <xsd:element name="Resume" type="ResumeType" />
  <xsd:element name="Address" type="AddressType" />
  <xsd:element name="Education" type="EducationType" />
  <xsd:element name="Employment" type="EmploymentType" />
  <xsd:element name="Location" type="LocationType" />
  <xsd:element name="Name" type="NameType" />
  <xsd:element name="Telephone" type="TelephoneType" />
  <xsd:complexType name="ResumeType">
    <xsd:sequence>
      <xsd:element ref="Name" />
      <xsd:element name="Skills" type="xsd:string" minOccurs="0" />
      <xsd:element ref="Employment" maxOccurs="unbounded" />
      <xsd:element ref="Education" maxOccurs="unbounded" />
      <xsd:element ref="Address" maxOccurs="unbounded" />
      <xsd:element ref="Telephone" minOccurs="0" />
      <xsd:element name="EMail" type="xsd:string" minOccurs="0" />
      <xsd:element name="WebSite" type="xsd:string" minOccurs="0" />
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="AddressType">
    <xsd:sequence>
      <xsd:element name="Addr.Type" type="xsd:string">
        <xsd:annotation>
          <xsd:documentation>Home|Work|Permanent</xsd:documentation>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Addr.OrgName" type="xsd:string" minOccurs="0" />
      <xsd:element name="Addr.Street" type="xsd:string" maxOccurs="unbounded" />
      <xsd:element name="Addr.Location">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element ref="Location" />
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
      <xsd:element name="Addr.PostalCode" type="xsd:string" />
      <xsd:element name="Addr.Telephone" minOccurs="0">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element ref="Telephone" maxOccurs="unbounded" />
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="EducationType">
    <xsd:sequence>
      <xsd:element name="Edu.Level" type="xsd:string">
        <xsd:annotation>
          <xsd:documentation>High School|Associate|Bachelor|Master|Doctorate</xsd:documentation>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Edu.StartDate" type="xsd:date" />
      <xsd:element name="Edu.EndDate" type="xsd:date" />
      <xsd:element name="Edu.Degree" type="xsd:string" minOccurs="0" />
      <xsd:element name="Edu.Major" type="xsd:string" minOccurs="0" />
      <xsd:element name="Edu.Minor" type="xsd:string" minOccurs="0" />
      <xsd:element name="Edu.GPA" type="xsd:string" minOccurs="0" />
      <xsd:element name="Edu.GPAAlternate" type="xsd:decimal" minOccurs="0">
        <xsd:annotation>
          <xsd:documentation>In case the institution does not follow a GPA system</xsd:documentation>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Edu.GPAScale" type="xsd:decimal" minOccurs="0" />
      <xsd:element name="Edu.School" type="xsd:string" minOccurs="0" />
      <xsd:element name="Edu.Location" minOccurs="0">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element ref="Location" />
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="EmploymentType">
    <xsd:sequence>
      <xsd:element name="Emp.StartDate" type="xsd:date" minOccurs="0" />
      <xsd:element name="Emp.EndDate" type="xsd:date" minOccurs="0" />
      <xsd:element name="Emp.OrgName" type="xsd:string" />
      <xsd:element name="Emp.JobTitle" type="xsd:string" />
      <xsd:element name="Emp.Responsibility" type="xsd:string" />
      <xsd:element name="Emp.FunctionCategory" type="xsd:string" minOccurs="0" />
      <xsd:element name="Emp.IndustryCategory" type="xsd:string" minOccurs="0" />
      <xsd:element name="Emp.Location" minOccurs="0">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element ref="Location" />
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="LocationType">
    <xsd:sequence>
      <xsd:element name="Loc.CountryRegion" type="xsd:string">
        <xsd:annotation>
          <xsd:documentation>ISO 3166 Country Code</xsd:documentation>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Loc.State" type="xsd:string" minOccurs="0" />
      <xsd:element name="Loc.City" type="xsd:string" minOccurs="0" />
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="NameType">
    <xsd:sequence>
      <xsd:element name="Name.Prefix" type="xsd:string" minOccurs="0" />
      <xsd:element name="Name.First" type="xsd:string" />
      <xsd:element name="Name.Middle" type="xsd:string" minOccurs="0" />
      <xsd:element name="Name.Last" type="xsd:string" />
      <xsd:element name="Name.Suffix" type="xsd:string" minOccurs="0" />
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="TelephoneType">
    <xsd:sequence>
      <xsd:element name="Tel.Type" minOccurs="0">
        <xsd:annotation>
          <xsd:documentation>Voice|Fax|Pager</xsd:documentation>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Tel.IntlCode" type="xsd:int" minOccurs="0" />
      <xsd:element name="Tel.AreaCode" type="xsd:int" minOccurs="0" />
      <xsd:element name="Tel.Number" type="xsd:string" />
      <xsd:element name="Tel.Extension" type="xsd:int" minOccurs="0" />
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>

IndividualSurveySchemaCollection (Sales)

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

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey" elementFormDefault="qualified" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:simpleType name="SalaryType">
    <xsd:restriction base="xsd:string">
      <xsd:enumeration value="0-25000" />
      <xsd:enumeration value="25001-50000" />
      <xsd:enumeration value="50001-75000" />
      <xsd:enumeration value="75001-100000" />
      <xsd:enumeration value="greater than 100000" />
    </xsd:restriction>
  </xsd:simpleType>
  <xsd:simpleType name="MileRangeType">
    <xsd:restriction base="xsd:string">
      <xsd:enumeration value="0-1 Miles" />
      <xsd:enumeration value="1-2 Miles" />
      <xsd:enumeration value="2-5 Miles" />
      <xsd:enumeration value="5-10 Miles" />
      <xsd:enumeration value="10+ Miles" />
    </xsd:restriction>
  </xsd:simpleType>
  <xsd:element name="IndividualSurvey">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="TotalPurchaseYTD" type="xsd:decimal" minOccurs="0" maxOccurs="1" />
        <xsd:element name="DateFirstPurchase" type="xsd:date" minOccurs="0" maxOccurs="1" />
        <xsd:element name="BirthDate" type="xsd:date" minOccurs="0" maxOccurs="1" />
        <xsd:element name="MaritalStatus" type="xsd:string" minOccurs="0" maxOccurs="1" />
        <xsd:element name="YearlyIncome" type="SalaryType" minOccurs="0" maxOccurs="1" />
        <xsd:element name="Gender" type="xsd:string" minOccurs="0" maxOccurs="1" />
        <xsd:element name="TotalChildren" type="xsd:int" minOccurs="0" maxOccurs="1" />
        <xsd:element name="NumberChildrenAtHome" type="xsd:int" minOccurs="0" maxOccurs="1" />
        <xsd:element name="Education" type="xsd:string" minOccurs="0" maxOccurs="1" />
        <xsd:element name="Occupation" type="xsd:string" minOccurs="0" maxOccurs="1" />
        <xsd:element name="HomeOwnerFlag" type="xsd:string" minOccurs="0" maxOccurs="1" />
        <xsd:element name="NumberCarsOwned" type="xsd:int" minOccurs="0" maxOccurs="1" />
        <xsd:element name="Hobby" type="xsd:string" minOccurs="0" maxOccurs="unbounded" />
        <xsd:element name="CommuteDistance" type="MileRangeType" minOccurs="0" maxOccurs="1" />
        <xsd:element name="Comments" type="xsd:string" minOccurs="0" maxOccurs="1" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

ManuInstructionsSchemaCollection (Production)

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

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" elementFormDefault="qualified" attributeFormDefault="unqualified" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:annotation>
    <xsd:documentation>
            SetupHour   is the time it takes to set up the machine.
            MachineHour is the time the machine is busy manufcturing
            LaborHour   is the labor hours in the manu process
            LotSize     is the minimum quanity manufactured. For example,
                    no. of frames cut from the sheet metal
        </xsd:documentation>
  </xsd:annotation>
  <xsd:complexType name="StepType" mixed="true">
    <xsd:choice minOccurs="0" maxOccurs="unbounded">
      <xsd:element name="tool" type="xsd:string" />
      <xsd:element name="material" type="xsd:string" />
      <xsd:element name="blueprint" type="xsd:string" />
      <xsd:element name="specs" type="xsd:string" />
      <xsd:element name="diag" type="xsd:string" />
    </xsd:choice>
  </xsd:complexType>
  <xsd:element name="root">
    <xsd:complexType mixed="true">
      <xsd:sequence>
        <xsd:element name="Location" minOccurs="1" maxOccurs="unbounded">
          <xsd:complexType mixed="true">
            <xsd:sequence>
              <xsd:element name="step" type="StepType" minOccurs="1" maxOccurs="unbounded" />
            </xsd:sequence>
            <xsd:attribute name="LocationID" type="xsd:integer" use="required" />
            <xsd:attribute name="SetupHours" type="xsd:decimal" use="optional" />
            <xsd:attribute name="MachineHours" type="xsd:decimal" use="optional" />
            <xsd:attribute name="LaborHours" type="xsd:decimal" use="optional" />
            <xsd:attribute name="LotSize" type="xsd:decimal" use="optional" />
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

ProductDescriptionSchemaCollection (Production)

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

<?xml version="1.0" encoding="utf-8"?>
<xsd:schema targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" elementFormDefault="qualified" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="Warranty">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="WarrantyPeriod" type="xsd:string" />
        <xsd:element name="Description" type="xsd:string" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
  <xsd:element name="Maintenance">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="NoOfYears" type="xsd:string" />
        <xsd:element name="Description" type="xsd:string" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

StoreSurveySchemaCollection (Sales)

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

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey" elementFormDefault="qualified" attributeFormDefault="unqualified">
  <!-- BM=Bicycle manu BS=bicyle store OS=online store SGS=sporting goods store D=Discount Store -->
  <xsd:simpleType name="BusinessType">
    <xsd:restriction base="xsd:string">
      <xsd:enumeration value="BM" />
      <xsd:enumeration value="BS" />
      <xsd:enumeration value="D" />
      <xsd:enumeration value="OS" />
      <xsd:enumeration value="SGS" />
    </xsd:restriction>
  </xsd:simpleType>
  <!-- BMX=BMX Racing -->
  <xsd:simpleType name="SpecialtyType">
    <xsd:restriction base="xsd:string">
      <xsd:enumeration value="Family" />
      <xsd:enumeration value="Kids" />
      <xsd:enumeration value="BMX" />
      <xsd:enumeration value="Touring" />
      <xsd:enumeration value="Road" />
      <xsd:enumeration value="Mountain" />
      <xsd:enumeration value="All" />
    </xsd:restriction>
  </xsd:simpleType>
  <!-- AW=AdventureWorks only 2= AdvWorks+1 other brand other brand -->
  <xsd:simpleType name="BrandType">
    <xsd:restriction base="xsd:string">
      <xsd:enumeration value="AW" />
      <xsd:enumeration value="2" />
      <xsd:enumeration value="3" />
      <xsd:enumeration value="4+" />
    </xsd:restriction>
  </xsd:simpleType>
  <xsd:simpleType name="InternetType">
    <xsd:restriction base="xsd:string">
      <xsd:enumeration value="56kb" />
      <xsd:enumeration value="ISDN" />
      <xsd:enumeration value="DSL" />
      <xsd:enumeration value="T1" />
      <xsd:enumeration value="T2" />
      <xsd:enumeration value="T3" />
    </xsd:restriction>
  </xsd:simpleType>
  <xsd:element name="StoreSurvey">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="ContactName" type="xsd:string" minOccurs="0" maxOccurs="1" />
        <xsd:element name="JobTitle" type="xsd:string" minOccurs="0" maxOccurs="1" />
        <xsd:element name="AnnualSales" type="xsd:decimal" minOccurs="0" maxOccurs="1" />
        <xsd:element name="AnnualRevenue" type="xsd:decimal" minOccurs="0" maxOccurs="1" />
        <xsd:element name="BankName" type="xsd:string" minOccurs="0" maxOccurs="1" />
        <xsd:element name="BusinessType" type="BusinessType" minOccurs="0" maxOccurs="1" />
        <xsd:element name="YearOpened" type="xsd:gYear" minOccurs="0" maxOccurs="1" />
        <xsd:element name="Specialty" type="SpecialtyType" minOccurs="0" maxOccurs="1" />
        <xsd:element name="SquareFeet" type="xsd:float" minOccurs="0" maxOccurs="1" />
        <xsd:element name="Brands" type="BrandType" minOccurs="0" maxOccurs="1" />
        <xsd:element name="Internet" type="InternetType" minOccurs="0" maxOccurs="1" />
        <xsd:element name="NumberEmployees" type="xsd:int" minOccurs="0" maxOccurs="1" />
        <xsd:element name="Comments" type="xsd:string" minOccurs="0" maxOccurs="1" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

generated by dbscript