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
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
CustomerAddress (Sales) AddressTypeID AddressTypeID
VendorAddress (Purchasing) AddressTypeID AddressTypeID
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
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
Detail Table Column Referencing Column
Triggers
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.
Foreign Key Column Referenced Column
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
Triggers
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
Detail Table Column Referencing Column
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
StoreContact (Sales) ContactTypeID ContactTypeID
VendorContact (Purchasing) ContactTypeID ContactTypeID
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
CountryRegionCurrency (Sales) CountryRegionCode CountryRegionCode
StateProvince (Person) CountryRegionCode CountryRegionCode
Triggers
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
Detail Table Column Referencing Column
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ContactCreditCard (Sales) CreditCardID CreditCardID
SalesOrderHeader (Sales) CreditCardID CreditCardID
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ProductModelProductDescriptionCulture (Production) CultureID CultureID
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
CountryRegionCurrency (Sales) CurrencyCode CurrencyCode
CurrencyRate (Sales) CurrencyCode FromCurrencyCode
CurrencyRate (Sales) CurrencyCode ToCurrencyCode
Triggers
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
Triggers
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
Triggers
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
Detail Table Column Referencing Column
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
EmployeeDepartmentHistory (HumanResources) DepartmentID DepartmentID
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ProductDocument (Production) DocumentID DocumentID
Triggers
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
Detail Table Column Referencing Column
Triggers
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
Detail Table Column Referencing Column
Triggers
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
Detail Table Column Referencing Column
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ProductModelIllustration (Production) IllustrationID IllustrationID
Triggers
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
Detail Table Column Referencing Column
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
Detail Table Column Referencing Column
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ProductInventory (Production) LocationID LocationID
WorkOrderRouting (Production) LocationID LocationID
Triggers
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
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ProductSubcategory (Production) ProductCategoryID ProductCategoryID
Triggers
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
Detail Table Column Referencing Column
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ProductModelProductDescriptionCulture (Production) ProductDescriptionID ProductDescriptionID
Triggers
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
Detail Table Column Referencing Column
Triggers
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
Detail Table Column Referencing Column
Triggers
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
Detail Table Column Referencing Column
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Product (Production) ProductModelID ProductModelID
ProductModelIllustration (Production) ProductModelID ProductModelID
ProductModelProductDescriptionCulture (Production) ProductModelID ProductModelID
Triggers
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
Detail Table Column Referencing Column
Triggers
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
Detail Table Column Referencing Column
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ProductProductPhoto (Production) ProductPhotoID ProductPhotoID
Triggers
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
Detail Table Column Referencing Column
Triggers
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
Detail Table Column Referencing Column
Triggers
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
Triggers
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
Detail Table Column Referencing Column
Triggers
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
Detail Table Column Referencing Column
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
Detail Table Column Referencing Column
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
Detail Table Column Referencing Column
Triggers
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
Triggers
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
Detail Table Column Referencing Column
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
SalesOrderHeaderSalesReason (Sales) SalesReasonID SalesReasonID
Triggers
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
Detail Table Column Referencing Column
Triggers
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.
Foreign Key Column Referenced Column
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
Triggers
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
Detail Table Column Referencing Column
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
WorkOrder (Production) ScrapReasonID ScrapReasonID
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
EmployeeDepartmentHistory (HumanResources) ShiftID ShiftID
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
PurchaseOrderHeader (Purchasing) ShipMethodID ShipMethodID
SalesOrderHeader (Sales) ShipMethodID ShipMethodID
Triggers
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
Detail Table Column Referencing Column
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
SpecialOfferProduct (Sales) SpecialOfferID SpecialOfferID
Triggers
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
Triggers
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
Triggers
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
Detail Table Column Referencing Column
Triggers
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
Detail Table Column Referencing Column
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
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.
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
BillOfMaterials (Production) UnitMeasureCode UnitMeasureCode
Product (Production) UnitMeasureCode SizeUnitMeasureCode
Product (Production) UnitMeasureCode WeightUnitMeasureCode
ProductVendor (Purchasing) UnitMeasureCode UnitMeasureCode
Triggers
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.
Foreign Key Column Referenced Column
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
Detail Table Column Referencing Column
Triggers
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
Detail Table Column Referencing Column
Triggers
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
Detail Table Column Referencing Column
Triggers
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