Project adventureworks
Version mie60
Version Date 2009-12-28

Tables
Address (Person)
Street address information for customers, employees, and vendors.
Column Data Type Nullable Default Description
AddressID int not null Primary key for Address records.
AddressLine1 nvarchar(60) not null First street address line.
AddressLine2 nvarchar(60) null Second street address line.
City nvarchar(30) not null Name of the city.
StateProvinceID int not null Unique identification number for the state or province. Foreign key to StateProvince table.
PostalCode nvarchar(15) not null Postal code for the street address.
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.
Primary Key Columns
PK_Address_AddressID AddressID
Index Type Columns
AK_Address_rowguid Unique rowguid
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode Unique AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
IX_Address_StateProvinceID StateProvinceID
Foreign Key Column Referenced Column
StateProvince (Person) StateProvinceID StateProvinceID
Detail Table Column Referencing Column
CustomerAddress (Sales) AddressID AddressID
EmployeeAddress (HumanResources) AddressID AddressID
SalesOrderHeader (Sales) AddressID BillToAddressID
SalesOrderHeader (Sales) AddressID ShipToAddressID
VendorAddress (Purchasing) AddressID AddressID
AddressType (Person)
Types of addresses stored in the Address table.
Column Data Type Nullable Default Description
AddressTypeID int not null Primary key for AddressType records.
Name dbo.Name not null Address type description. For example, Billing, Home, or Shipping.
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.
Primary Key Columns
PK_AddressType_AddressTypeID AddressTypeID
Index Type Columns
AK_AddressType_rowguid Unique rowguid
AK_AddressType_Name Unique Name
Detail Table Column Referencing Column
CustomerAddress (Sales) AddressTypeID AddressTypeID
VendorAddress (Purchasing) AddressTypeID AddressTypeID
AWBuildVersion (dbo)
Current version number of the AdventureWorks sample database.
Column Data Type Nullable Default Description
SystemInformationID tinyint not null Primary key for AWBuildVersion records.
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.
Primary Key Columns
PK_AWBuildVersion_SystemInformationID SystemInformationID
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
BillOfMaterialsID int not null Primary key for BillOfMaterials records.
ProductAssemblyID int null Parent product identification number. Foreign key to Product.ProductID.
ComponentID int not null Component identification number. Foreign key to Product.ProductID.
StartDate datetime not null (getdate()) Date the component started being used in the assembly item.
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.
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.
Primary Key Columns
PK_BillOfMaterials_BillOfMaterialsID BillOfMaterialsID
Index Type Columns
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate Unique ProductAssemblyID, ComponentID, StartDate
IX_BillOfMaterials_UnitMeasureCode UnitMeasureCode
Name Expression Description
CK_BillOfMaterials_BOMLevel ([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1)) Check constraint [ProductAssemblyID] IS NULL AND [BOMLevel] = (0) AND [PerAssemblyQty] = (1) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel] >= (1)
CK_BillOfMaterials_EndDate ([EndDate]>[StartDate] OR [EndDate] IS NULL) Check constraint EndDate] > [StartDate] OR [EndDate] IS NULL
CK_BillOfMaterials_PerAssemblyQty ([PerAssemblyQty]>=(1.00)) Check constraint [PerAssemblyQty] >= (1.00)
CK_BillOfMaterials_ProductAssemblyID ([ProductAssemblyID]<>[ComponentID]) Check constraint [ProductAssemblyID] <> [ComponentID]
Foreign Key Column Referenced Column
Product (Production) ComponentID ProductID
Product (Production) ProductAssemblyID ProductID
UnitMeasure (Production) UnitMeasureCode UnitMeasureCode
Contact (Person)
Names of each employee, customer contact, and vendor contact.
Column Data Type Nullable Default Description
ContactID int not null Primary key for Contact records.
NameStyle dbo.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 dbo.Name not null First name of the person.
MiddleName dbo.Name null Middle name or middle initial of the person.
LastName dbo.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.
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 dbo.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.
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.
Primary Key Columns
PK_Contact_ContactID ContactID
Index Type Columns
AK_Contact_rowguid Unique rowguid
IX_Contact_EmailAddress EmailAddress
PXML_Contact_AddContact AdditionalContactInfo
Name Expression Description
CK_Contact_EmailPromotion ([EmailPromotion]>=(0) AND [EmailPromotion]<=(2)) Check constraint [EmailPromotion] >= (0) AND [EmailPromotion] <= (2)
Detail Table Column Referencing Column
ContactCreditCard (Sales) ContactID ContactID
Employee (HumanResources) ContactID ContactID
Individual (Sales) ContactID ContactID
SalesOrderHeader (Sales) ContactID ContactID
StoreContact (Sales) ContactID ContactID
VendorContact (Purchasing) ContactID ContactID
ContactCreditCard (Sales)
Cross-reference table mapping customers in the Contact table to their credit card information in the CreditCard table.
Column Data Type Nullable Default Description
ContactID int not null Customer identification number. Foreign key to Contact.ContactID.
CreditCardID int not null Credit card identification number. Foreign key to CreditCard.CreditCardID.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_ContactCreditCard_ContactID_CreditCardID ContactID, CreditCardID
Foreign Key Column Referenced Column
Contact (Person) ContactID ContactID
CreditCard (Sales) CreditCardID CreditCardID
ContactType (Person)
Lookup table containing the types of contacts stored in Contact.
Column Data Type Nullable Default Description
ContactTypeID int not null Primary key for ContactType records.
Name dbo.Name not null Contact type description.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_ContactType_ContactTypeID ContactTypeID
Index Type Columns
AK_ContactType_Name Unique Name
Detail Table Column Referencing Column
StoreContact (Sales) ContactTypeID ContactTypeID
VendorContact (Purchasing) ContactTypeID ContactTypeID
CountryRegion (Person)
Lookup table containing the ISO standard codes for countries and regions.
Column Data Type Nullable Default Description
CountryRegionCode nvarchar(3) not null ISO standard code for countries and regions.
Name dbo.Name not null Country or region name.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_CountryRegion_CountryRegionCode CountryRegionCode
Index Type Columns
AK_CountryRegion_Name Unique Name
Detail Table Column Referencing Column
CountryRegionCurrency (Sales) CountryRegionCode CountryRegionCode
StateProvince (Person) CountryRegionCode CountryRegionCode
CountryRegionCurrency (Sales)
Cross-reference table mapping ISO currency codes to a country or region.
Column Data Type Nullable Default Description
CountryRegionCode nvarchar(3) not null ISO code for countries and regions. Foreign key to CountryRegion.CountryRegionCode.
CurrencyCode nchar(3) not null ISO standard currency code. Foreign key to Currency.CurrencyCode.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode CountryRegionCode, CurrencyCode
Index Type Columns
IX_CountryRegionCurrency_CurrencyCode CurrencyCode
Foreign Key Column Referenced Column
CountryRegion (Person) CountryRegionCode CountryRegionCode
Currency (Sales) CurrencyCode CurrencyCode
CreditCard (Sales)
Customer credit card information.
Column Data Type Nullable Default Description
CreditCardID int not null Primary key for CreditCard records.
CardType nvarchar(50) not null Credit card name.
CardNumber nvarchar(25) not null Credit card number.
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.
Primary Key Columns
PK_CreditCard_CreditCardID CreditCardID
Index Type Columns
AK_CreditCard_CardNumber Unique CardNumber
Detail Table Column Referencing Column
ContactCreditCard (Sales) CreditCardID CreditCardID
SalesOrderHeader (Sales) CreditCardID CreditCardID
Culture (Production)
Lookup table containing the languages in which some AdventureWorks data is stored.
Column Data Type Nullable Default Description
CultureID nchar(6) not null Primary key for Culture records.
Name dbo.Name not null Culture description.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_Culture_CultureID CultureID
Index Type Columns
AK_Culture_Name Unique Name
Detail Table Column Referencing Column
ProductModelProductDescriptionCulture (Production) CultureID CultureID
Currency (Sales)
Lookup table containing standard ISO currencies.
Column Data Type Nullable Default Description
CurrencyCode nchar(3) not null The ISO code for the Currency.
Name dbo.Name not null Currency name.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_Currency_CurrencyCode CurrencyCode
Index Type Columns
AK_Currency_Name Unique Name
Detail Table Column Referencing Column
CountryRegionCurrency (Sales) CurrencyCode CurrencyCode
CurrencyRate (Sales) CurrencyCode FromCurrencyCode
CurrencyRate (Sales) CurrencyCode ToCurrencyCode
CurrencyRate (Sales)
Currency exchange rates.
Column Data Type Nullable Default Description
CurrencyRateID int not null Primary key for CurrencyRate records.
CurrencyRateDate datetime not null Date and time the exchange rate was obtained.
FromCurrencyCode nchar(3) not null Exchange rate was converted from this currency code.
ToCurrencyCode nchar(3) not null Exchange rate was converted to this currency code.
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.
Primary Key Columns
PK_CurrencyRate_CurrencyRateID CurrencyRateID
Index Type Columns
AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode Unique CurrencyRateDate, FromCurrencyCode, ToCurrencyCode
Foreign Key Column Referenced Column
Currency (Sales) FromCurrencyCode CurrencyCode
Currency (Sales) ToCurrencyCode CurrencyCode
Detail Table Column Referencing Column
SalesOrderHeader (Sales) CurrencyRateID CurrencyRateID
Customer (Sales)
Current customer information. Also see the Individual and Store tables.
Column Data Type Nullable Default Description
CustomerID int not null Primary key for Customer records.
TerritoryID int null ID of the territory in which the customer is located. Foreign key to SalesTerritory.SalesTerritoryID.
AccountNumber Unique number identifying the customer assigned by the accounting system.
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.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_Customer_CustomerID CustomerID
Index Type Columns
AK_Customer_rowguid Unique rowguid
AK_Customer_AccountNumber Unique AccountNumber
IX_Customer_TerritoryID TerritoryID
Name Expression Description
CK_Customer_CustomerType (upper([CustomerType])='I' OR upper([CustomerType])='S') Check constraint [CustomerType]='I' OR [CustomerType]='i' OR [CustomerType]='S' OR [CustomerType]='s'
Foreign Key Column Referenced Column
SalesTerritory (Sales) TerritoryID TerritoryID
Detail Table Column Referencing Column
CustomerAddress (Sales) CustomerID CustomerID
Individual (Sales) CustomerID CustomerID
SalesOrderHeader (Sales) CustomerID CustomerID
Store (Sales) CustomerID CustomerID
CustomerAddress (Sales)
Cross-reference table mapping customers to their address(es).
Column Data Type Nullable Default Description
CustomerID int not null Primary key. Foreign key to Customer.CustomerID.
AddressID int not null Primary key. Foreign key to Address.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.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_CustomerAddress_CustomerID_AddressID CustomerID, AddressID
Index Type Columns
AK_CustomerAddress_rowguid Unique rowguid
Foreign Key Column Referenced Column
Address (Person) AddressID AddressID
AddressType (Person) AddressTypeID AddressTypeID
Customer (Sales) CustomerID CustomerID
DatabaseLog (dbo)
Audit table tracking all DDL changes made to the AdventureWorks database. Data is captured by the database trigger ddlDatabaseTriggerLog.
Column Data Type Nullable Default Description
DatabaseLogID int not null Primary key for DatabaseLog records.
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.
Primary Key Columns
PK_DatabaseLog_DatabaseLogID DatabaseLogID
Department (HumanResources)
Lookup table containing the departments within the Adventure Works Cycles company.
Column Data Type Nullable Default Description
DepartmentID smallint not null Primary key for Department records.
Name dbo.Name not null Name of the department.
GroupName dbo.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.
Primary Key Columns
PK_Department_DepartmentID DepartmentID
Index Type Columns
AK_Department_Name Unique Name
Detail Table Column Referencing Column
EmployeeDepartmentHistory (HumanResources) DepartmentID DepartmentID
Document (Production)
Product maintenance documents.
Column Data Type Nullable Default Description
DocumentID int not null Primary key for Document records.
Title nvarchar(50) not null Title of the document.
FileName nvarchar(400) not null Directory path and file name of the document
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.
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.
Primary Key Columns
PK_Document_DocumentID DocumentID
Index Type Columns
AK_Document_FileName_Revision Unique FileName, Revision
Name Expression Description
CK_Document_Status ([Status]>=(1) AND [Status]<=(3)) Check constraint [Status] BETWEEN (1) AND (3)
Detail Table Column Referencing Column
ProductDocument (Production) DocumentID DocumentID
Employee (HumanResources)
Employee information such as salary, department, and title.
Column Data Type Nullable Default Description
EmployeeID int not null Primary key for Employee records.
NationalIDNumber nvarchar(15) not null Unique national identification number such as a social security number.
ContactID int not null Identifies the employee in the Contact table. Foreign key to Contact.ContactID.
LoginID nvarchar(256) not null Network login.
ManagerID int null Manager to whom the employee is assigned. Foreign Key to Employee.M
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 dbo.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 dbo.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.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_Employee_EmployeeID EmployeeID
Index Type Columns
AK_Employee_LoginID Unique LoginID
AK_Employee_NationalIDNumber Unique NationalIDNumber
AK_Employee_rowguid Unique rowguid
IX_Employee_ManagerID ManagerID
Name Expression Description
CK_Employee_BirthDate ([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate())) Check constraint [BirthDate] >= '1930-01-01' AND [BirthDate] <= dateadd(year,(-18),GETDATE())
CK_Employee_Gender (upper([Gender])='F' OR upper([Gender])='M') Check constraint [Gender]='f' OR [Gender]='m' OR [Gender]='F' OR [Gender]='M'
CK_Employee_HireDate ([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate())) Check constraint [HireDate] >= '1996-07-01' AND [HireDate] <= dateadd(day,(1),GETDATE())
CK_Employee_MaritalStatus (upper([MaritalStatus])='S' OR upper([MaritalStatus])='M') Check constraint [MaritalStatus]='s' OR [MaritalStatus]='m' OR [MaritalStatus]='S' OR [MaritalStatus]='M'
CK_Employee_SickLeaveHours ([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120)) Check constraint [SickLeaveHours] >= (0) AND [SickLeaveHours] <= (120)
CK_Employee_VacationHours ([VacationHours]>=(-40) AND [VacationHours]<=(240)) Check constraint [VacationHours] >= (-40) AND [VacationHours] <= (240)
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
EmployeeID int not null Primary key. Foreign key to Employee.EmployeeID.
AddressID int not null Primary key. Foreign key to Address.AddressID.
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.
Primary Key Columns
PK_EmployeeAddress_EmployeeID_AddressID EmployeeID, AddressID
Index Type Columns
AK_EmployeeAddress_rowguid Unique rowguid
Foreign Key Column Referenced Column
Address (Person) AddressID AddressID
Employee (HumanResources) EmployeeID EmployeeID
EmployeeDepartmentHistory (HumanResources)
Employee department transfers.
Column Data Type Nullable Default Description
EmployeeID int not null Employee identification number. Foreign key to Employee.EmployeeID.
DepartmentID smallint not null Department in which the employee worked including currently. Foreign key to Department.DepartmentID.
ShiftID tinyint not null Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.
StartDate datetime not null Date the employee started work in the department.
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.
Primary Key Columns
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID EmployeeID, DepartmentID, ShiftID, StartDate
Index Type Columns
IX_EmployeeDepartmentHistory_DepartmentID DepartmentID
IX_EmployeeDepartmentHistory_ShiftID ShiftID
Name Expression Description
CK_EmployeeDepartmentHistory_EndDate ([EndDate]>=[StartDate] OR [EndDate] IS NULL) Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NUL
Foreign Key Column Referenced Column
Department (HumanResources) DepartmentID DepartmentID
Employee (HumanResources) EmployeeID EmployeeID
Shift (HumanResources) ShiftID ShiftID
EmployeePayHistory (HumanResources)
Employee pay history.
Column Data Type Nullable Default Description
EmployeeID int not null Employee identification number. Foreign key to Employee.EmployeeID.
RateChangeDate datetime not null Date the change in pay is effective
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.
Primary Key Columns
PK_EmployeePayHistory_EmployeeID_RateChangeDate EmployeeID, RateChangeDate
Name Expression Description
CK_EmployeePayHistory_PayFrequency ([PayFrequency]=(2) OR [PayFrequency]=(1)) Check constraint [PayFrequency]=(3) OR [PayFrequency]=(2) OR [PayFrequency]=(1)
CK_EmployeePayHistory_Rate ([Rate]>=(6.50) AND [Rate]<=(200.00)) Check constraint [Rate] >= (6.50) AND [Rate] <= (200.00)
Foreign Key Column Referenced Column
Employee (HumanResources) EmployeeID EmployeeID
ErrorLog (dbo)
Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.
Column Data Type Nullable Default Description
ErrorLogID int not null Primary key for ErrorLog records.
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.
Primary Key Columns
PK_ErrorLog_ErrorLogID ErrorLogID
Illustration (Production)
Bicycle assembly diagrams.
Column Data Type Nullable Default Description
IllustrationID int not null Primary key for Illustration records.
Diagram xml null Illustrations used in manufacturing instructions. Stored as XML.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_Illustration_IllustrationID IllustrationID
Detail Table Column Referencing Column
ProductModelIllustration (Production) IllustrationID IllustrationID
Individual (Sales)
Demographic data about customers that purchase Adventure Works products online.
Column Data Type Nullable Default Description
CustomerID int not null Unique customer identification number. Foreign key to Customer.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.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_Individual_CustomerID CustomerID
Index Type Columns
PXML_Individual_Demographics Demographics
XMLPATH_Individual_Demographics Demographics
XMLPROPERTY_Individual_Demographics Demographics
XMLVALUE_Individual_Demographics Demographics
Foreign Key Column Referenced Column
Contact (Person) ContactID ContactID
Customer (Sales) CustomerID CustomerID
Triggers
iuIndividual ON INSERT UPDATE
JobCandidate (HumanResources)
Résumés submitted to Human Resources by job applicants.
Column Data Type Nullable Default Description
JobCandidateID int not null Primary key for JobCandidate records.
EmployeeID int null Employee identification number if applicant was hired. Foreign key to Employee.EmployeeID.
Resume xml null Résumé in XML format.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_JobCandidate_JobCandidateID JobCandidateID
Index Type Columns
IX_JobCandidate_EmployeeID EmployeeID
Foreign Key Column Referenced Column
Employee (HumanResources) EmployeeID EmployeeID
Location (Production)
Product inventory and manufacturing locations.
Column Data Type Nullable Default Description
LocationID smallint not null Primary key for Location records.
Name dbo.Name not null Location description.
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.
Primary Key Columns
PK_Location_LocationID LocationID
Index Type Columns
AK_Location_Name Unique Name
Name Expression Description
CK_Location_Availability ([Availability]>=(0.00)) Check constraint [Availability] >= (0.00)
CK_Location_CostRate ([CostRate]>=(0.00)) Check constraint [CostRate] >= (0.00)
Detail Table Column Referencing Column
ProductInventory (Production) LocationID LocationID
WorkOrderRouting (Production) LocationID LocationID
Product (Production)
Products sold or used in the manfacturing of sold products.
Column Data Type Nullable Default Description
ProductID int not null Primary key for Product records.
Name dbo.Name not null Name of the product.
ProductNumber nvarchar(25) not null Unique product identification number.
MakeFlag dbo.Flag not null ((1)) 0 = Product is purchased, 1 = Product is manufactured in-house.
FinishedGoodsFlag dbo.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.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_Product_ProductID ProductID
Index Type Columns
AK_Product_ProductNumber Unique ProductNumber
AK_Product_Name Unique Name
AK_Product_rowguid Unique rowguid
Name Expression Description
CK_Product_Class (upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL) Check constraint [Class]='h' OR [Class]='m' OR [Class]='l' OR [Class]='H' OR [Class]='M' OR [Class]='L' OR [Class] IS NULL
CK_Product_DaysToManufacture ([DaysToManufacture]>=(0)) Check constraint [DaysToManufacture] >= (0)
CK_Product_ListPrice ([ListPrice]>=(0.00)) Check constraint [ListPrice] >= (0.00)
CK_Product_ProductLine (upper([ProductLine])='R' OR upper([ProductLine])='M' OR upper([ProductLine])='T' OR upper([ProductLine])='S' OR [ProductLine] IS NULL) Check constraint [ProductLine]='r' OR [ProductLine]='m' OR [ProductLine]='t' OR [ProductLine]='s' OR [ProductLine]='R' OR [ProductLine]='M' OR [ProductLine]='T' OR [ProductLine]='S' OR [ProductLine] IS NULL
CK_Product_ReorderPoint ([ReorderPoint]>(0)) Check constraint [ReorderPoint] > (0)
CK_Product_SafetyStockLevel ([SafetyStockLevel]>(0)) Check constraint [SafetyStockLevel] > (0)
CK_Product_SellEndDate ([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL) Check constraint [SellEndDate] >= [SellStartDate] OR [SellEndDate] IS NULL
CK_Product_StandardCost ([StandardCost]>=(0.00)) Check constraint [SafetyStockLevel] > (0)
CK_Product_Style (upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL) Check constraint [Style]='u' OR [Style]='m' OR [Style]='w' OR [Style]='U' OR [Style]='M' OR [Style]='W' OR [Style] IS NULL
CK_Product_Weight ([Weight]>(0.00)) Check constraint [Weight] > (0.00)
Foreign Key Column Referenced Column
ProductModel (Production) ProductModelID ProductModelID
ProductSubcategory (Production) ProductSubcategoryID ProductSubcategoryID
UnitMeasure (Production) SizeUnitMeasureCode UnitMeasureCode
UnitMeasure (Production) WeightUnitMeasureCode UnitMeasureCode
Detail Table Column Referencing Column
BillOfMaterials (Production) ProductID ComponentID
BillOfMaterials (Production) ProductID ProductAssemblyID
ProductCostHistory (Production) ProductID ProductID
ProductDocument (Production) ProductID ProductID
ProductInventory (Production) ProductID ProductID
ProductListPriceHistory (Production) ProductID ProductID
ProductProductPhoto (Production) ProductID ProductID
ProductReview (Production) ProductID ProductID
ProductVendor (Purchasing) ProductID ProductID
PurchaseOrderDetail (Purchasing) ProductID ProductID
ShoppingCartItem (Sales) ProductID ProductID
SpecialOfferProduct (Sales) ProductID ProductID
TransactionHistory (Production) ProductID ProductID
WorkOrder (Production) ProductID ProductID
ProductCategory (Production)
High-level product categorization.
Column Data Type Nullable Default Description
ProductCategoryID int not null Primary key for ProductCategory records.
Name dbo.Name not null Category description.
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.
Primary Key Columns
PK_ProductCategory_ProductCategoryID ProductCategoryID
Index Type Columns
AK_ProductCategory_Name Unique Name
AK_ProductCategory_rowguid Unique rowguid
Detail Table Column Referencing Column
ProductSubcategory (Production) ProductCategoryID ProductCategoryID
ProductCostHistory (Production)
Changes in the cost of a product over time.
Column Data Type Nullable Default Description
ProductID int not null Product identification number. Foreign key to Product.ProductID
StartDate datetime not null Product cost start date.
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.
Primary Key Columns
PK_ProductCostHistory_ProductID_StartDate ProductID, StartDate
Name Expression Description
CK_ProductCostHistory_EndDate ([EndDate]>=[StartDate] OR [EndDate] IS NULL) Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL
CK_ProductCostHistory_StandardCost ([StandardCost]>=(0.00)) Check constraint [StandardCost] >= (0.00)
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
ProductDescription (Production)
Product descriptions in several languages.
Column Data Type Nullable Default Description
ProductDescriptionID int not null Primary key for ProductDescription records.
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.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_ProductDescription_ProductDescriptionID ProductDescriptionID
Index Type Columns
AK_ProductDescription_rowguid Unique rowguid
Detail Table Column Referencing Column
ProductModelProductDescriptionCulture (Production) ProductDescriptionID ProductDescriptionID
ProductDocument (Production)
Cross-reference table mapping products to related product documents.
Column Data Type Nullable Default Description
ProductID int not null Product identification number. Foreign key to Product.ProductID.
DocumentID int not null Document identification number. Foreign key to Document.DocumentID.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_ProductDocument_ProductID_DocumentID ProductID, DocumentID
Foreign Key Column Referenced Column
Document (Production) DocumentID DocumentID
Product (Production) ProductID ProductID
ProductInventory (Production)
Product inventory information.
Column Data Type Nullable Default Description
ProductID int not null Product identification number. Foreign key to Product.ProductID.
LocationID smallint not null Inventory location identification number. Foreign key to Location.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.
Primary Key Columns
PK_ProductInventory_ProductID_LocationID ProductID, LocationID
Name Expression Description
CK_ProductInventory_Bin ([Bin]>=(0) AND [Bin]<=(100)) Check constraint [Bin] BETWEEN (0) AND (100)
CK_ProductInventory_Shelf ([Shelf] like '[A-Za-z]' OR [Shelf]='N/A') Check constraint [Shelf] like '[A-Za-z]' OR [Shelf]='N/A'
Foreign Key Column Referenced Column
Location (Production) LocationID LocationID
Product (Production) ProductID ProductID
ProductListPriceHistory (Production)
Changes in the list price of a product over time.
Column Data Type Nullable Default Description
ProductID int not null Product identification number. Foreign key to Product.ProductID
StartDate datetime not null List price start date.
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.
Primary Key Columns
PK_ProductListPriceHistory_ProductID_StartDate ProductID, StartDate
Name Expression Description
CK_ProductListPriceHistory_EndDate ([EndDate]>=[StartDate] OR [EndDate] IS NULL) Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL
CK_ProductListPriceHistory_ListPrice ([ListPrice]>(0.00)) Check constraint [ListPrice] > (0.00)
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
ProductModel (Production)
Product model classification.
Column Data Type Nullable Default Description
ProductModelID int not null Primary key for ProductModel records.
Name dbo.Name not null Product model description.
CatalogDescription xml null Detailed product catalog information in xml format.
Instructions xml null Manufacturing instructions in xml format.
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.
Primary Key Columns
PK_ProductModel_ProductModelID ProductModelID
Index Type Columns
AK_ProductModel_Name Unique Name
AK_ProductModel_rowguid Unique rowguid
PXML_ProductModel_CatalogDescription CatalogDescription
PXML_ProductModel_Instructions Instructions
Detail Table Column Referencing Column
Product (Production) ProductModelID ProductModelID
ProductModelIllustration (Production) ProductModelID ProductModelID
ProductModelProductDescriptionCulture (Production) ProductModelID ProductModelID
ProductModelIllustration (Production)
Cross-reference table mapping product models and illustrations.
Column Data Type Nullable Default Description
ProductModelID int not null Primary key. Foreign key to ProductModel.ProductModelID.
IllustrationID int not null Primary key. Foreign key to Illustration.IllustrationID.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_ProductModelIllustration_ProductModelID_IllustrationID ProductModelID, IllustrationID
Foreign Key Column Referenced Column
Illustration (Production) IllustrationID IllustrationID
ProductModel (Production) ProductModelID ProductModelID
ProductModelProductDescriptionCulture (Production)
Cross-reference table mapping product descriptions and the language the description is written in.
Column Data Type Nullable Default Description
ProductModelID int not null Primary key. Foreign key to ProductModel.ProductModelID.
ProductDescriptionID int not null Primary key. Foreign key to ProductDescription.ProductDescriptionID.
CultureID nchar(6) not null Culture identification number. Foreign key to Culture.CultureID.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID ProductModelID, ProductDescriptionID, CultureID
Foreign Key Column Referenced Column
Culture (Production) CultureID CultureID
ProductDescription (Production) ProductDescriptionID ProductDescriptionID
ProductModel (Production) ProductModelID ProductModelID
ProductPhoto (Production)
Product images.
Column Data Type Nullable Default Description
ProductPhotoID int not null Primary key for ProductPhoto records.
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.
Primary Key Columns
PK_ProductPhoto_ProductPhotoID ProductPhotoID
Detail Table Column Referencing Column
ProductProductPhoto (Production) ProductPhotoID ProductPhotoID
ProductProductPhoto (Production)
Cross-reference table mapping products and product photos.
Column Data Type Nullable Default Description
ProductID int not null Product identification number. Foreign key to Product.ProductID.
ProductPhotoID int not null Product photo identification number. Foreign key to ProductPhoto.ProductPhotoID.
Primary dbo.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.
Primary Key Columns
PK_ProductProductPhoto_ProductID_ProductPhotoID ProductID, ProductPhotoID
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
ProductPhoto (Production) ProductPhotoID ProductPhotoID
ProductReview (Production)
Customer reviews of products they have purchased.
Column Data Type Nullable Default Description
ProductReviewID int not null Primary key for ProductReview records.
ProductID int not null Product identification number. Foreign key to Product.ProductID.
ReviewerName dbo.Name not null Name of the reviewer.
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) null Reviewer's comments
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_ProductReview_ProductReviewID ProductReviewID
Index Type Columns
IX_ProductReview_ProductID_Name ProductID, ReviewerName, Comments
Name Expression Description
CK_ProductReview_Rating ([Rating]>=(1) AND [Rating]<=(5)) Check constraint [Rating] BETWEEN (1) AND (5)
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
ProductSubcategory (Production)
Product subcategories. See ProductCategory table.
Column Data Type Nullable Default Description
ProductSubcategoryID int not null Primary key for ProductSubcategory records.
ProductCategoryID int not null Product category identification number. Foreign key to ProductCategory.ProductCategoryID.
Name dbo.Name not null Subcategory description.
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.
Primary Key Columns
PK_ProductSubcategory_ProductSubcategoryID ProductSubcategoryID
Index Type Columns
AK_ProductSubcategory_Name Unique Name
AK_ProductSubcategory_rowguid Unique rowguid
Foreign Key Column Referenced Column
ProductCategory (Production) ProductCategoryID ProductCategoryID
Detail Table Column Referencing Column
Product (Production) ProductSubcategoryID ProductSubcategoryID
ProductVendor (Purchasing)
Cross-reference table mapping vendors with the products they supply.
Column Data Type Nullable Default Description
ProductID int not null Primary key. Foreign key to Product.ProductID.
VendorID int not null Primary key. Foreign key to Vendor.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.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_ProductVendor_ProductID_VendorID ProductID, VendorID
Index Type Columns
IX_ProductVendor_UnitMeasureCode UnitMeasureCode
IX_ProductVendor_VendorID VendorID
Name Expression Description
CK_ProductVendor_AverageLeadTime ([AverageLeadTime]>=(1)) Check constraint [AverageLeadTime] >= (1)
CK_ProductVendor_LastReceiptCost ([LastReceiptCost]>(0.00)) Check constraint [LastReceiptCost] > (0.00)
CK_ProductVendor_MaxOrderQty ([MaxOrderQty]>=(1)) Check constraint [MaxOrderQty] >= (1)
CK_ProductVendor_MinOrderQty ([MinOrderQty]>=(1)) Check constraint [MinOrderQty] >= (1)
CK_ProductVendor_OnOrderQty ([OnOrderQty]>=(0)) Check constraint [OnOrderQty] >= (0)
CK_ProductVendor_StandardPrice ([StandardPrice]>(0.00)) Check constraint [StandardPrice] > (0.00)
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
UnitMeasure (Production) UnitMeasureCode UnitMeasureCode
Vendor (Purchasing) VendorID VendorID
PurchaseOrderDetail (Purchasing)
Individual products associated with a specific purchase order. See PurchaseOrderHeader.
Column Data Type Nullable Default Description
PurchaseOrderID int not null Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
PurchaseOrderDetailID int not null Primary key. One line number per purchased product.
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.
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.
Primary Key Columns
PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID PurchaseOrderID, PurchaseOrderDetailID
Index Type Columns
IX_PurchaseOrderDetail_ProductID ProductID
Name Expression Description
CK_PurchaseOrderDetail_OrderQty ([OrderQty]>(0)) Check constraint [OrderQty] > (0)
CK_PurchaseOrderDetail_ReceivedQty ([ReceivedQty]>=(0.00)) Check constraint [ReceivedQty] >= (0.00)
CK_PurchaseOrderDetail_RejectedQty ([RejectedQty]>=(0.00)) Check constraint [RejectedQty] >= (0.00)
CK_PurchaseOrderDetail_UnitPrice ([UnitPrice]>=(0.00)) Check constraint [UnitPrice] >= (0.00)
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
PurchaseOrderHeader (Purchasing) PurchaseOrderID PurchaseOrderID
Triggers
iPurchaseOrderDetail ON INSERT
uPurchaseOrderDetail ON UPDATE
PurchaseOrderHeader (Purchasing)
General purchase order information. See PurchaseOrderDetail.
Column Data Type Nullable Default Description
PurchaseOrderID int not null Primary key.
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.
VendorID int not null Vendor with whom the purchase order is placed. Foreign key to Vendor.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.
Primary Key Columns
PK_PurchaseOrderHeader_PurchaseOrderID PurchaseOrderID
Index Type Columns
IX_PurchaseOrderHeader_VendorID VendorID
IX_PurchaseOrderHeader_EmployeeID EmployeeID
Name Expression Description
CK_PurchaseOrderHeader_Freight ([Freight]>=(0.00)) Check constraint [Freight] >= (0.00)
CK_PurchaseOrderHeader_ShipDate ([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL) Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL
CK_PurchaseOrderHeader_Status ([Status]>=(1) AND [Status]<=(4)) Check constraint [Status] BETWEEN (1) AND (4)
CK_PurchaseOrderHeader_SubTotal ([SubTotal]>=(0.00)) Check constraint [SubTotal] >= (0.00)
CK_PurchaseOrderHeader_TaxAmt ([TaxAmt]>=(0.00)) Check constraint [TaxAmt] >= (0.00)
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
SalesOrderID int not null Primary key. Foreign key to SalesOrderHeader.SalesOrderID.
SalesOrderDetailID int not null Primary key. One incremental unique number per product sold.
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.
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.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID SalesOrderID, SalesOrderDetailID
Index Type Columns
AK_SalesOrderDetail_rowguid Unique rowguid
IX_SalesOrderDetail_ProductID ProductID
Name Expression Description
CK_SalesOrderDetail_OrderQty ([OrderQty]>(0)) Check constraint [OrderQty] > (0)
CK_SalesOrderDetail_UnitPrice ([UnitPrice]>=(0.00)) Check constraint [UnitPrice] >= (0.00)
CK_SalesOrderDetail_UnitPriceDiscount ([UnitPriceDiscount]>=(0.00)) Check constraint [UnitPriceDiscount] >= (0.00)
Foreign Key Column Referenced Column
SalesOrderHeader (Sales) SalesOrderID SalesOrderID
SpecialOfferProduct (Sales) SpecialOfferID SpecialOfferID
Triggers
iduSalesOrderDetail ON INSERT UPDATE DELETE
SalesOrderHeader (Sales)
General sales order information.
Column Data Type Nullable Default Description
SalesOrderID int not null Primary key.
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 dbo.Flag not null ((1)) 0 = Order placed by sales person. 1 = Order placed online by customer.
SalesOrderNumber Unique sales order identification number.
PurchaseOrderNumber dbo.OrderNumber null Customer purchase order number reference.
AccountNumber dbo.AccountNumber null Financial accounting number reference.
CustomerID int not null Customer identification number. Foreign key to Customer.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.
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.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_SalesOrderHeader_SalesOrderID SalesOrderID
Index Type Columns
AK_SalesOrderHeader_rowguid Unique rowguid
AK_SalesOrderHeader_SalesOrderNumber Unique SalesOrderNumber
IX_SalesOrderHeader_CustomerID CustomerID
IX_SalesOrderHeader_SalesPersonID SalesPersonID
Name Expression Description
CK_SalesOrderHeader_DueDate ([DueDate]>=[OrderDate]) Check constraint [DueDate] >= [OrderDate]
CK_SalesOrderHeader_Freight ([Freight]>=(0.00)) Check constraint [Freight] >= (0.00)
CK_SalesOrderHeader_ShipDate ([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL) Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL
CK_SalesOrderHeader_Status ([Status]>=(0) AND [Status]<=(8)) Check constraint [Status] BETWEEN (0) AND (8)
CK_SalesOrderHeader_SubTotal ([SubTotal]>=(0.00)) Check constraint [SubTotal] >= (0.00)
CK_SalesOrderHeader_TaxAmt ([TaxAmt]>=(0.00)) Check constraint [TaxAmt] >= (0.00)
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
SalesOrderID int not null Primary key. Foreign key to SalesOrderHeader.SalesOrderID.
SalesReasonID int not null Primary key. Foreign key to SalesReason.SalesReasonID.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID SalesOrderID, SalesReasonID
Foreign Key Column Referenced Column
SalesOrderHeader (Sales) SalesOrderID SalesOrderID
SalesReason (Sales) SalesReasonID SalesReasonID
SalesPerson (Sales)
Sales representative current information.
Column Data Type Nullable Default Description
SalesPersonID int not null Primary key for SalesPerson records.
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.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_SalesPerson_SalesPersonID SalesPersonID
Index Type Columns
AK_SalesPerson_rowguid Unique rowguid
Name Expression Description
CK_SalesPerson_Bonus ([Bonus]>=(0.00)) Check constraint [Bonus] >= (0.00)
CK_SalesPerson_CommissionPct ([CommissionPct]>=(0.00)) Check constraint [CommissionPct] >= (0.00)
CK_SalesPerson_SalesLastYear ([SalesLastYear]>=(0.00)) Check constraint [SalesLastYear] >= (0.00)
CK_SalesPerson_SalesQuota ([SalesQuota]>(0.00)) Check constraint [SalesQuota] > (0.00)
CK_SalesPerson_SalesYTD ([SalesYTD]>=(0.00)) Check constraint [SalesYTD] >= (0.00)
Foreign Key Column Referenced Column
Employee (HumanResources) SalesPersonID EmployeeID
SalesTerritory (Sales) TerritoryID TerritoryID
Detail Table Column Referencing Column
SalesOrderHeader (Sales) SalesPersonID SalesPersonID
SalesPersonQuotaHistory (Sales) SalesPersonID SalesPersonID
SalesTerritoryHistory (Sales) SalesPersonID SalesPersonID
Store (Sales) SalesPersonID SalesPersonID
SalesPersonQuotaHistory (Sales)
Sales performance tracking.
Column Data Type Nullable Default Description
SalesPersonID int not null Sales person identification number. Foreign key to SalesPerson.SalesPersonID.
QuotaDate datetime not null Sales quota date.
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.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_SalesPersonQuotaHistory_SalesPersonID_QuotaDate SalesPersonID, QuotaDate
Index Type Columns
AK_SalesPersonQuotaHistory_rowguid Unique rowguid
Name Expression Description
CK_SalesPersonQuotaHistory_SalesQuota ([SalesQuota]>(0.00)) Check constraint [SalesQuota] > (0.00)
Foreign Key Column Referenced Column
SalesPerson (Sales) SalesPersonID SalesPersonID
SalesReason (Sales)
Lookup table of customer purchase reasons.
Column Data Type Nullable Default Description
SalesReasonID int not null Primary key for SalesReason records.
Name dbo.Name not null Sales reason description.
ReasonType dbo.Name not null Category the sales reason belongs to.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_SalesReason_SalesReasonID SalesReasonID
Detail Table Column Referencing Column
SalesOrderHeaderSalesReason (Sales) SalesReasonID SalesReasonID
SalesTaxRate (Sales)
Tax rate lookup table.
Column Data Type Nullable Default Description
SalesTaxRateID int not null Primary key for SalesTaxRate records.
StateProvinceID int not null State, province, or country/region the sales tax applies to.
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.
TaxRate smallmoney not null ((0.00)) Tax rate amount.
Name dbo.Name not null Tax rate description.
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.
Primary Key Columns
PK_SalesTaxRate_SalesTaxRateID SalesTaxRateID
Index Type Columns
AK_SalesTaxRate_StateProvinceID_TaxType Unique StateProvinceID, TaxType
AK_SalesTaxRate_rowguid Unique rowguid
Name Expression Description
CK_SalesTaxRate_TaxType ([TaxType]>=(1) AND [TaxType]<=(3)) Check constraint [TaxType] BETWEEN (1) AND (3)
Foreign Key Column Referenced Column
StateProvince (Person) StateProvinceID StateProvinceID
SalesTerritory (Sales)
Sales territory lookup table.
Column Data Type Nullable Default Description
TerritoryID int not null Primary key for SalesTerritory records.
Name dbo.Name not null Sales territory description
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.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_SalesTerritory_TerritoryID TerritoryID
Index Type Columns
AK_SalesTerritory_Name Unique Name
AK_SalesTerritory_rowguid Unique rowguid
Name Expression Description
CK_SalesTerritory_CostLastYear ([CostLastYear]>=(0.00)) Check constraint [CostLastYear] >= (0.00)
CK_SalesTerritory_CostYTD ([CostYTD]>=(0.00)) Check constraint [CostYTD] >= (0.00)
CK_SalesTerritory_SalesLastYear ([SalesLastYear]>=(0.00)) Check constraint [SalesLastYear] >= (0.00)
CK_SalesTerritory_SalesYTD ([SalesYTD]>=(0.00)) Check constraint [SalesYTD] >= (0.00)
Detail Table Column Referencing Column
Customer (Sales) TerritoryID TerritoryID
SalesOrderHeader (Sales) TerritoryID TerritoryID
SalesPerson (Sales) TerritoryID TerritoryID
SalesTerritoryHistory (Sales) TerritoryID TerritoryID
StateProvince (Person) TerritoryID TerritoryID
SalesTerritoryHistory (Sales)
Sales representative transfers to other sales territories.
Column Data Type Nullable Default Description
SalesPersonID int not null Primary key for SalesTerritoryHistory records.
TerritoryID int not null Territory identification number. Foreign key to SalesTerritory.SalesTerritoryID.
StartDate datetime not null Date the sales representive started work in the territory.
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.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_SalesTerritoryHistory_SalesPersonID_StartDate_TerritoryID SalesPersonID, TerritoryID, StartDate
Index Type Columns
AK_SalesTerritoryHistory_rowguid Unique rowguid
Name Expression Description
CK_SalesTerritoryHistory_EndDate ([EndDate]>=[StartDate] OR [EndDate] IS NULL) Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL
Foreign Key Column Referenced Column
SalesPerson (Sales) SalesPersonID SalesPersonID
SalesTerritory (Sales) TerritoryID TerritoryID
ScrapReason (Production)
Manufacturing failure reasons lookup table.
Column Data Type Nullable Default Description
ScrapReasonID smallint not null Primary key for ScrapReason records.
Name dbo.Name not null Failure description.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_ScrapReason_ScrapReasonID ScrapReasonID
Index Type Columns
AK_ScrapReason_Name Unique Name
Detail Table Column Referencing Column
WorkOrder (Production) ScrapReasonID ScrapReasonID
Shift (HumanResources)
Work shift lookup table.
Column Data Type Nullable Default Description
ShiftID tinyint not null Primary key for Shift records.
Name dbo.Name not null Shift description.
StartTime datetime not null Shift start time.
EndTime datetime not null Shift end time.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_Shift_ShiftID ShiftID
Index Type Columns
AK_Shift_Name Unique Name
AK_Shift_StartTime_EndTime Unique StartTime, EndTime
Detail Table Column Referencing Column
EmployeeDepartmentHistory (HumanResources) ShiftID ShiftID
ShipMethod (Purchasing)
Shipping company lookup table.
Column Data Type Nullable Default Description
ShipMethodID int not null Primary key for ShipMethod records.
Name dbo.Name not null Shipping company 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.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_ShipMethod_ShipMethodID ShipMethodID
Index Type Columns
AK_ShipMethod_Name Unique Name
AK_ShipMethod_rowguid Unique rowguid
Name Expression Description
CK_ShipMethod_ShipBase ([ShipBase]>(0.00)) Check constraint [ShipBase] > (0.00)
CK_ShipMethod_ShipRate ([ShipRate]>(0.00)) Check constraint [ShipRate] > (0.00)
Detail Table Column Referencing Column
PurchaseOrderHeader (Purchasing) ShipMethodID ShipMethodID
SalesOrderHeader (Sales) ShipMethodID ShipMethodID
ShoppingCartItem (Sales)
Contains online customer orders until the order is submitted or cancelled.
Column Data Type Nullable Default Description
ShoppingCartItemID int not null Primary key for ShoppingCartItem records.
ShoppingCartID nvarchar(50) not null Shopping cart identification number.
Quantity int not null ((1)) Product quantity ordered.
ProductID int not null Product ordered. Foreign key to Product.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.
Primary Key Columns
PK_ShoppingCartItem_ShoppingCartItemID ShoppingCartItemID
Index Type Columns
IX_ShoppingCartItem_ShoppingCartID_ProductID ShoppingCartID, ProductID
Name Expression Description
CK_ShoppingCartItem_Quantity ([Quantity]>=(1)) Check constraint [Quantity] >= (1)
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
SpecialOffer (Sales)
Sale discounts lookup table.
Column Data Type Nullable Default Description
SpecialOfferID int not null Primary key for SpecialOffer records.
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.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_SpecialOffer_SpecialOfferID SpecialOfferID
Index Type Columns
AK_SpecialOffer_rowguid Unique rowguid
Name Expression Description
CK_SpecialOffer_DiscountPct ([DiscountPct]>=(0.00)) Check constraint [DiscountPct] >= (0.00)
CK_SpecialOffer_EndDate ([EndDate]>=[StartDate]) Check constraint [EndDate] >= [StartDate]
CK_SpecialOffer_MaxQty ([MaxQty]>=(0)) Check constraint [MaxQty] >= (0)
CK_SpecialOffer_MinQty ([MinQty]>=(0)) Check constraint [MinQty] >= (0)
Detail Table Column Referencing Column
SpecialOfferProduct (Sales) SpecialOfferID SpecialOfferID
SpecialOfferProduct (Sales)
Cross-reference table mapping products to special offer discounts.
Column Data Type Nullable Default Description
SpecialOfferID int not null Primary key for SpecialOfferProduct records.
ProductID int not null Product identification number. Foreign key to Product.ProductID.
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.
Primary Key Columns
PK_SpecialOfferProduct_SpecialOfferID_ProductID SpecialOfferID, ProductID
Index Type Columns
AK_SpecialOfferProduct_rowguid Unique rowguid
IX_SpecialOfferProduct_ProductID ProductID
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
SpecialOffer (Sales) SpecialOfferID SpecialOfferID
Detail Table Column Referencing Column
SalesOrderDetail (Sales) SpecialOfferID SpecialOfferID
SalesOrderDetail (Sales) SpecialOfferID SpecialOfferID
StateProvince (Person)
State and province lookup table.
Column Data Type Nullable Default Description
StateProvinceID int not null Primary key for StateProvince records.
StateProvinceCode nchar(3) not null ISO standard state or province code.
CountryRegionCode nvarchar(3) not null ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.
IsOnlyStateProvinceFlag dbo.Flag not null ((1)) 0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode.
Name dbo.Name not null State or province description.
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.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_StateProvince_StateProvinceID StateProvinceID
Index Type Columns
AK_StateProvince_Name Unique Name
AK_StateProvince_StateProvinceCode_CountryRegionCode Unique StateProvinceCode, CountryRegionCode
AK_StateProvince_rowguid Unique rowguid
Foreign Key Column Referenced Column
CountryRegion (Person) CountryRegionCode CountryRegionCode
SalesTerritory (Sales) TerritoryID TerritoryID
Detail Table Column Referencing Column
Address (Person) StateProvinceID StateProvinceID
SalesTaxRate (Sales) StateProvinceID StateProvinceID
Store (Sales)
Customers (resellers) of Adventure Works products.
Column Data Type Nullable Default Description
CustomerID int not null Primary key. Foreign key to Customer.CustomerID.
Name dbo.Name not null Name of the store.
SalesPersonID int null ID of the sales person assigned to the customer. Foreign key to SalesPerson.SalesPersonID.
Demographics xml null Demographic informationg about the store such as the number of employees, annual sales and store type.
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.
Primary Key Columns
PK_Store_CustomerID CustomerID
Index Type Columns
AK_Store_rowguid Unique rowguid
IX_Store_SalesPersonID SalesPersonID
PXML_Store_Demographics Demographics
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
CustomerID int not null Store identification number. Foreign key to Customer.CustomerID.
ContactID int not null Contact (store employee) identification number. Foreign key to Contact.ContactID.
ContactTypeID int not null Contact type such as owner or purchasing agent. Foreign key to ContactType.ContactTypeID.
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.
Primary Key Columns
PK_StoreContact_CustomerID_ContactID CustomerID, ContactID
Index Type Columns
AK_StoreContact_rowguid Unique rowguid
IX_StoreContact_ContactID ContactID
IX_StoreContact_ContactTypeID ContactTypeID
Foreign Key Column Referenced Column
Contact (Person) ContactID ContactID
ContactType (Person) ContactTypeID ContactTypeID
Store (Sales) CustomerID CustomerID
TransactionHistory (Production)
Record of each purchase order, sales order, or work order transaction year to date.
Column Data Type Nullable Default Description
TransactionID int not null Primary key for TransactionHistory records.
ProductID int not null Product identification number. Foreign key to Product.ProductID.
ReferenceOrderID int not null Purchase order, sales order, or work order identification number.
ReferenceOrderLineID int not null ((0)) Line number associated with the purchase order, sales order, or work order.
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.
Primary Key Columns
PK_TransactionHistory_TransactionID TransactionID
Index Type Columns
IX_TransactionHistory_ProductID ProductID
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID ReferenceOrderID, ReferenceOrderLineID
Name Expression Description
CK_TransactionHistory_TransactionType (upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W') Check constraint [TransactionType]='p' OR [TransactionType]='s' OR [TransactionType]='w' OR [TransactionType]='P' OR [TransactionType]='S' OR [TransactionType]='W')
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
TransactionHistoryArchive (Production)
Transactions for previous years.
Column Data Type Nullable Default Description
TransactionID int not null Primary key for TransactionHistoryArchive records.
ProductID int not null Product identification number. Foreign key to Product.ProductID.
ReferenceOrderID int not null Purchase order, sales order, or work order identification number.
ReferenceOrderLineID int not null ((0)) Line number associated with the purchase order, sales order, or work order.
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.
Primary Key Columns
PK_TransactionHistoryArchive_TransactionID TransactionID
Index Type Columns
IX_TransactionHistoryArchive_ProductID ProductID
IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID ReferenceOrderID, ReferenceOrderLineID
Name Expression Description
CK_TransactionHistoryArchive_TransactionType (upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W') Check constraint [TransactionType]='p' OR [TransactionType]='s' OR [TransactionType]='w' OR [TransactionType]='P' OR [TransactionType]='S' OR [TransactionType]='W'
UnitMeasure (Production)
Unit of measure lookup table.
Column Data Type Nullable Default Description
UnitMeasureCode nchar(3) not null Primary key.
Name dbo.Name not null Unit of measure description.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_UnitMeasure_UnitMeasureCode UnitMeasureCode
Index Type Columns
AK_UnitMeasure_Name Unique Name
Detail Table Column Referencing Column
BillOfMaterials (Production) UnitMeasureCode UnitMeasureCode
Product (Production) UnitMeasureCode SizeUnitMeasureCode
Product (Production) UnitMeasureCode WeightUnitMeasureCode
ProductVendor (Purchasing) UnitMeasureCode UnitMeasureCode
Vendor (Purchasing)
Companies from whom Adventure Works Cycles purchases parts or other goods.
Column Data Type Nullable Default Description
VendorID int not null Primary key for Vendor records.
AccountNumber dbo.AccountNumber not null Vendor account (identification) number.
Name dbo.Name not null Company name.
CreditRating tinyint not null 1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average
PreferredVendorStatus dbo.Flag not null ((1)) 0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.
ActiveFlag dbo.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.
Primary Key Columns
PK_Vendor_VendorID VendorID
Index Type Columns
AK_Vendor_AccountNumber Unique AccountNumber
Name Expression Description
CK_Vendor_CreditRating ([CreditRating]>=(1) AND [CreditRating]<=(5)) Check constraint [CreditRating] BETWEEN (1) AND (5)
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
VendorID int not null Primary key. Foreign key to Vendor.VendorID.
AddressID int not null Primary key. Foreign key to Address.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.
Primary Key Columns
PK_VendorAddress_VendorID_AddressID VendorID, AddressID
Index Type Columns
IX_VendorAddress_AddressID AddressID
Foreign Key Column Referenced Column
Address (Person) AddressID AddressID
AddressType (Person) AddressTypeID AddressTypeID
Vendor (Purchasing) VendorID VendorID
VendorContact (Purchasing)
Cross-reference table mapping vendors and their employees.
Column Data Type Nullable Default Description
VendorID int not null Primary key.
ContactID int not null Contact (Vendor employee) identification number. Foreign key to Contact.ContactID.
ContactTypeID int not null Contact type such as sales manager, or sales agent.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_VendorContact_VendorID_ContactID VendorID, ContactID
Index Type Columns
IX_VendorContact_ContactID ContactID
IX_VendorContact_ContactTypeID ContactTypeID
Foreign Key Column Referenced Column
Contact (Person) ContactID ContactID
ContactType (Person) ContactTypeID ContactTypeID
Vendor (Purchasing) VendorID VendorID
WorkOrder (Production)
Manufacturing work orders.
Column Data Type Nullable Default Description
WorkOrderID int not null Primary key for WorkOrder records.
ProductID int not null Product identification number. Foreign key to Product.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.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.
Primary Key Columns
PK_WorkOrder_WorkOrderID WorkOrderID
Index Type Columns
IX_WorkOrder_ScrapReasonID ScrapReasonID
IX_WorkOrder_ProductID ProductID
Name Expression Description
CK_WorkOrder_EndDate ([EndDate]>=[StartDate] OR [EndDate] IS NULL) Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL
CK_WorkOrder_OrderQty ([OrderQty]>(0)) Check constraint [OrderQty] > (0)
CK_WorkOrder_ScrappedQty ([ScrappedQty]>=(0)) Check constraint [ScrappedQty] >= (0)
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
WorkOrderID int not null Primary key. Foreign key to WorkOrder.WorkOrderID.
ProductID int not null Primary key. Foreign key to Product.ProductID.
OperationSequence smallint not null Primary key. Indicates the manufacturing process sequence.
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.
Primary Key Columns
PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence WorkOrderID, ProductID, OperationSequence
Index Type Columns
IX_WorkOrderRouting_ProductID ProductID
Name Expression Description
CK_WorkOrderRouting_ActualCost ([ActualCost]>(0.00)) Check constraint [ActualCost] > (0.00)
CK_WorkOrderRouting_ActualEndDate ([ActualEndDate]>=[ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL) Check constraint [ActualEndDate] >= [ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL
CK_WorkOrderRouting_ActualResourceHrs ([ActualResourceHrs]>=(0.0000)) Check constraint [ActualResourceHrs] >= (0.0000)
CK_WorkOrderRouting_PlannedCost ([PlannedCost]>(0.00)) Check constraint [PlannedCost] > (0.00)
CK_WorkOrderRouting_ScheduledEndDate ([ScheduledEndDate]>=[ScheduledStartDate]) Check constraint [ScheduledEndDate] >= [ScheduledStartDate]
Foreign Key Column Referenced Column
Location (Production) LocationID LocationID
WorkOrder (Production) WorkOrderID WorkOrderID
Views
vAdditionalContactInfo (Person)
Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.
CREATE VIEW [Person].[vAdditionalContactInfo] 
AS 
SELECT 
    [ContactID] 
    ,[FirstName]
    ,[MiddleName]
    ,[LastName]
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:telephoneNumber)[1]/act:number', 'nvarchar(50)') AS [TelephoneNumber] 
    ,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:telephoneNumber/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [TelephoneSpecialInstructions] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
        (act:homePostalAddress/act:Street)[1]', 'nvarchar(50)') AS [Street] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:City)[1]', 'nvarchar(50)') AS [City] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:StateProvince)[1]', 'nvarchar(50)') AS [StateProvince] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:PostalCode)[1]', 'nvarchar(50)') AS [PostalCode] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:CountryRegion)[1]', 'nvarchar(50)') AS [CountryRegion] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:SpecialInstructions/text())[1]', 'nvarchar(max)') AS [HomeAddressSpecialInstructions] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:eMail/act:eMailAddress)[1]', 'nvarchar(128)') AS [EMailAddress] 
    ,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:eMail/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [EMailSpecialInstructions] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:eMail/act:SpecialInstructions/act:telephoneNumber/act:number)[1]', 'nvarchar(50)') AS [EMailTelephoneNumber] 
    ,[rowguid] 
    ,[ModifiedDate]
FROM [Person].[Contact]
OUTER APPLY [AdditionalContactInfo].nodes(
    'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
    /ci:AdditionalContactInfo') AS ContactInfo(ref) 
WHERE [AdditionalContactInfo] IS NOT NULL;
vEmployee (HumanResources)
Employee names and addresses.
CREATE VIEW [HumanResources].[vEmployee] 
AS 
SELECT 
    e.[EmployeeID]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,e.[Title] AS [JobTitle] 
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode]
    ,cr.[Name] AS [CountryRegionName] 
    ,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeAddress] ea 
    ON e.[EmployeeID] = ea.[EmployeeID] 
    INNER JOIN [Person].[Address] a 
    ON ea.[AddressID] = a.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
vEmployeeDepartment (HumanResources)
Returns employee name, title, and current department.
CREATE VIEW [HumanResources].[vEmployeeDepartment] 
AS 
SELECT 
    e.[EmployeeID] 
    ,c.[Title] 
    ,c.[FirstName] 
    ,c.[MiddleName] 
    ,c.[LastName] 
    ,c.[Suffix] 
    ,e.[Title] AS [JobTitle] 
    ,d.[Name] AS [Department] 
    ,d.[GroupName] 
    ,edh.[StartDate] 
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
    ON e.[EmployeeID] = edh.[EmployeeID] 
    INNER JOIN [HumanResources].[Department] d 
    ON edh.[DepartmentID] = d.[DepartmentID] 
WHERE GETDATE() BETWEEN edh.[StartDate] AND ISNULL(edh.[EndDate], GETDATE());
vEmployeeDepartmentHistory (HumanResources)
Returns employee name and current and previous departments.
CREATE VIEW [HumanResources].[vEmployeeDepartmentHistory] 
AS 
SELECT 
    e.[EmployeeID] 
    ,c.[Title] 
    ,c.[FirstName] 
    ,c.[MiddleName] 
    ,c.[LastName] 
    ,c.[Suffix] 
    ,s.[Name] AS [Shift]
    ,d.[Name] AS [Department] 
    ,d.[GroupName] 
    ,edh.[StartDate] 
    ,edh.[EndDate]
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
    ON e.[EmployeeID] = edh.[EmployeeID] 
    INNER JOIN [HumanResources].[Department] d 
    ON edh.[DepartmentID] = d.[DepartmentID] 
    INNER JOIN [HumanResources].[Shift] s
    ON s.[ShiftID] = edh.[ShiftID];
vIndividualCustomer (Sales)
Individual customers (names and addresses) that purchase Adventure Works Cycles products online.
CREATE VIEW [Sales].[vIndividualCustomer] 
AS 
SELECT 
    i.[CustomerID]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,at.[Name] AS [AddressType]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
    ,i.[Demographics]
FROM [Sales].[Individual] i
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = i.[ContactID]
    INNER JOIN [Sales].[CustomerAddress] ca 
    ON ca.[CustomerID] = i.[CustomerID]
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = ca.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    INNER JOIN [Person].[AddressType] at 
    ON ca.[AddressTypeID] = at.[AddressTypeID]
WHERE i.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID] 
    FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'I');
vIndividualDemographics (Sales)
Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table.
CREATE VIEW [Sales].[vIndividualDemographics] 
AS 
SELECT 
    i.[CustomerID] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        TotalPurchaseYTD[1]', 'money') AS [TotalPurchaseYTD] 
    ,CONVERT(datetime, REPLACE([IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        DateFirstPurchase[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [DateFirstPurchase] 
    ,CONVERT(datetime, REPLACE([IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        BirthDate[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [BirthDate] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        MaritalStatus[1]', 'nvarchar(1)') AS [MaritalStatus] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        YearlyIncome[1]', 'nvarchar(30)') AS [YearlyIncome] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        Gender[1]', 'nvarchar(1)') AS [Gender] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        TotalChildren[1]', 'integer') AS [TotalChildren] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        NumberChildrenAtHome[1]', 'integer') AS [NumberChildrenAtHome] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        Education[1]', 'nvarchar(30)') AS [Education] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        Occupation[1]', 'nvarchar(30)') AS [Occupation] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        HomeOwnerFlag[1]', 'bit') AS [HomeOwnerFlag] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        NumberCarsOwned[1]', 'integer') AS [NumberCarsOwned] 
FROM [Sales].[Individual] i 
CROSS APPLY i.[Demographics].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
    /IndividualSurvey') AS [IndividualSurvey](ref) 
WHERE [Demographics] IS NOT NULL;
vJobCandidate (HumanResources)
Job candidate names and resumes.
CREATE VIEW [HumanResources].[vJobCandidate] 
AS 
SELECT 
    jc.[JobCandidateID] 
    ,jc.[EmployeeID] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Name/Name.Prefix)[1]', 'nvarchar(30)') AS [Name.Prefix] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
        (/Resume/Name/Name.First)[1]', 'nvarchar(30)') AS [Name.First] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Name/Name.Middle)[1]', 'nvarchar(30)') AS [Name.Middle] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Name/Name.Last)[1]', 'nvarchar(30)') AS [Name.Last] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Name/Name.Suffix)[1]', 'nvarchar(30)') AS [Name.Suffix] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Skills)[1]', 'nvarchar(max)') AS [Skills] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.Type)[1]', 'nvarchar(30)') AS [Addr.Type]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(100)') AS [Addr.Loc.CountryRegion]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.Location/Location/Loc.State)[1]', 'nvarchar(100)') AS [Addr.Loc.State]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.Location/Location/Loc.City)[1]', 'nvarchar(100)') AS [Addr.Loc.City]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.PostalCode)[1]', 'nvarchar(20)') AS [Addr.PostalCode]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/EMail)[1]', 'nvarchar(max)') AS [EMail] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/WebSite)[1]', 'nvarchar(max)') AS [WebSite] 
    ,jc.[ModifiedDate] 
FROM [HumanResources].[JobCandidate] jc 
CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
    /Resume') AS Resume(ref);
vJobCandidateEducation (HumanResources)
Displays the content from each education related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed.
CREATE VIEW [HumanResources].[vJobCandidateEducation] 
AS 
SELECT 
    jc.[JobCandidateID] 
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Level)[1]', 'nvarchar(max)') AS [Edu.Level]
    ,CONVERT(datetime, REPLACE([Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.StartDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Edu.StartDate] 
    ,CONVERT(datetime, REPLACE([Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.EndDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Edu.EndDate] 
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Degree)[1]', 'nvarchar(50)') AS [Edu.Degree]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Major)[1]', 'nvarchar(50)') AS [Edu.Major]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Minor)[1]', 'nvarchar(50)') AS [Edu.Minor]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.GPA)[1]', 'nvarchar(5)') AS [Edu.GPA]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.GPAScale)[1]', 'nvarchar(5)') AS [Edu.GPAScale]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.School)[1]', 'nvarchar(100)') AS [Edu.School]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(100)') AS [Edu.Loc.CountryRegion]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Location/Location/Loc.State)[1]', 'nvarchar(100)') AS [Edu.Loc.State]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Location/Location/Loc.City)[1]', 'nvarchar(100)') AS [Edu.Loc.City]
FROM [HumanResources].[JobCandidate] jc 
CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
    /Resume/Education') AS [Education](ref);
vJobCandidateEmployment (HumanResources)
Displays the content from each employement history related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed.
CREATE VIEW [HumanResources].[vJobCandidateEmployment] 
AS 
SELECT 
    jc.[JobCandidateID] 
    ,CONVERT(datetime, REPLACE([Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.StartDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Emp.StartDate] 
    ,CONVERT(datetime, REPLACE([Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.EndDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Emp.EndDate] 
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.OrgName)[1]', 'nvarchar(100)') AS [Emp.OrgName]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.JobTitle)[1]', 'nvarchar(100)') AS [Emp.JobTitle]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.Responsibility)[1]', 'nvarchar(max)') AS [Emp.Responsibility]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.FunctionCategory)[1]', 'nvarchar(max)') AS [Emp.FunctionCategory]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.IndustryCategory)[1]', 'nvarchar(max)') AS [Emp.IndustryCategory]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(max)') AS [Emp.Loc.CountryRegion]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.Location/Location/Loc.State)[1]', 'nvarchar(max)') AS [Emp.Loc.State]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.Location/Location/Loc.City)[1]', 'nvarchar(max)') AS [Emp.Loc.City]
FROM [HumanResources].[JobCandidate] jc 
CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
    /Resume/Employment') AS Employment(ref);
vProductAndDescription (Production)
Product names and descriptions. Product descriptions are provided in multiple languages.
CREATE VIEW [Production].[vProductAndDescription] 
WITH SCHEMABINDING 
AS 
-- View (indexed or standard) to display products and product descriptions by language.
SELECT 
    p.[ProductID] 
    ,p.[Name] 
    ,pm.[Name] AS [ProductModel] 
    ,pmx.[CultureID] 
    ,pd.[Description] 
FROM [Production].[Product] p 
    INNER JOIN [Production].[ProductModel] pm 
    ON p.[ProductModelID] = pm.[ProductModelID] 
    INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx 
    ON pm.[ProductModelID] = pmx.[ProductModelID] 
    INNER JOIN [Production].[ProductDescription] pd 
    ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID];
vProductModelCatalogDescription (Production)
Displays the content from each element in the xml column CatalogDescription for each product in the Production.ProductModel table that has catalog data.
CREATE VIEW [Production].[vProductModelCatalogDescription] 
AS 
SELECT 
    [ProductModelID] 
    ,[Name] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace html="http://www.w3.org/1999/xhtml"; 
        (/p1:ProductDescription/p1:Summary/html:p)[1]', 'nvarchar(max)') AS [Summary] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Manufacturer/p1:Name)[1]', 'nvarchar(max)') AS [Manufacturer] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Manufacturer/p1:Copyright)[1]', 'nvarchar(30)') AS [Copyright] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Manufacturer/p1:ProductURL)[1]', 'nvarchar(256)') AS [ProductURL] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; 
        (/p1:ProductDescription/p1:Features/wm:Warranty/wm:WarrantyPeriod)[1]', 'nvarchar(256)') AS [WarrantyPeriod] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; 
        (/p1:ProductDescription/p1:Features/wm:Warranty/wm:Description)[1]', 'nvarchar(256)') AS [WarrantyDescription] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; 
        (/p1:ProductDescription/p1:Features/wm:Maintenance/wm:NoOfYears)[1]', 'nvarchar(256)') AS [NoOfYears] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; 
        (/p1:ProductDescription/p1:Features/wm:Maintenance/wm:Description)[1]', 'nvarchar(256)') AS [MaintenanceDescription] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:wheel)[1]', 'nvarchar(256)') AS [Wheel] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:saddle)[1]', 'nvarchar(256)') AS [Saddle] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:pedal)[1]', 'nvarchar(256)') AS [Pedal] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:BikeFrame)[1]', 'nvarchar(max)') AS [BikeFrame] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:crankset)[1]', 'nvarchar(256)') AS [Crankset] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Picture/p1:Angle)[1]', 'nvarchar(256)') AS [PictureAngle] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Picture/p1:Size)[1]', 'nvarchar(256)') AS [PictureSize] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Picture/p1:ProductPhotoID)[1]', 'nvarchar(256)') AS [ProductPhotoID] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/Material)[1]', 'nvarchar(256)') AS [Material] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/Color)[1]', 'nvarchar(256)') AS [Color] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/ProductLine)[1]', 'nvarchar(256)') AS [ProductLine] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/Style)[1]', 'nvarchar(256)') AS [Style] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/RiderExperience)[1]', 'nvarchar(1024)') AS [RiderExperience] 
    ,[rowguid] 
    ,[ModifiedDate]
FROM [Production].[ProductModel] 
WHERE [CatalogDescription] IS NOT NULL;
vProductModelInstructions (Production)
Displays the content from each element in the xml column Instructions for each product in the Production.ProductModel table that has manufacturing instructions.
CREATE VIEW [Production].[vProductModelInstructions] 
AS 
SELECT 
    [ProductModelID] 
    ,[Name] 
    ,[Instructions].value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
        (/root/text())[1]', 'nvarchar(max)') AS [Instructions] 
    ,[MfgInstructions].ref.value('@LocationID[1]', 'int') AS [LocationID] 
    ,[MfgInstructions].ref.value('@SetupHours[1]', 'decimal(9, 4)') AS [SetupHours] 
    ,[MfgInstructions].ref.value('@MachineHours[1]', 'decimal(9, 4)') AS [MachineHours] 
    ,[MfgInstructions].ref.value('@LaborHours[1]', 'decimal(9, 4)') AS [LaborHours] 
    ,[MfgInstructions].ref.value('@LotSize[1]', 'int') AS [LotSize] 
    ,[Steps].ref.value('string(.)[1]', 'nvarchar(1024)') AS [Step] 
    ,[rowguid] 
    ,[ModifiedDate]
FROM [Production].[ProductModel] 
CROSS APPLY [Instructions].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
    /root/Location') MfgInstructions(ref)
CROSS APPLY [MfgInstructions].ref.nodes('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
    step') Steps(ref);
vSalesPerson (Sales)
Sales representiatives (names and addresses) and their sales-related information.
CREATE VIEW [Sales].[vSalesPerson] 
AS 
SELECT 
    s.[SalesPersonID]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,[JobTitle] = e.[Title]
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
    ,[TerritoryName] = st.[Name]
    ,[TerritoryGroup] = st.[Group]
    ,s.[SalesQuota]
    ,s.[SalesYTD]
    ,s.[SalesLastYear]
FROM [Sales].[SalesPerson] s
    INNER JOIN [HumanResources].[Employee] e 
    ON e.[EmployeeID] = s.[SalesPersonID]
    LEFT OUTER JOIN [Sales].[SalesTerritory] st 
    ON st.[TerritoryID] = s.[TerritoryID]
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeAddress] ea 
    ON e.[EmployeeID] = ea.[EmployeeID] 
    INNER JOIN [Person].[Address] a 
    ON ea.[AddressID] = a.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
vSalesPersonSalesByFiscalYears (Sales)
Uses PIVOT to return aggregated sales information for each sales representative.
CREATE VIEW [Sales].[vSalesPersonSalesByFiscalYears] 
AS 
SELECT 
    pvt.[SalesPersonID]
    ,pvt.[FullName]
    ,pvt.[Title]
    ,pvt.[SalesTerritory]
    ,pvt.[2002]
    ,pvt.[2003]
    ,pvt.[2004] 
FROM (SELECT 
        soh.[SalesPersonID]
        ,c.[FirstName] + ' ' + COALESCE(c.[MiddleName], '') + ' ' + c.[LastName] AS [FullName]
        ,e.[Title]
        ,st.[Name] AS [SalesTerritory]
        ,soh.[SubTotal]
        ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
    FROM [Sales].[SalesPerson] sp 
        INNER JOIN [Sales].[SalesOrderHeader] soh 
        ON sp.[SalesPersonID] = soh.[SalesPersonID]
        INNER JOIN [Sales].[SalesTerritory] st 
        ON sp.[TerritoryID] = st.[TerritoryID] 
        INNER JOIN [HumanResources].[Employee] e 
        ON soh.[SalesPersonID] = e.[EmployeeID] 
        INNER JOIN [Person].[Contact] c 
        ON e.[ContactID] = c.ContactID 
    ) AS soh 
PIVOT 
(
    SUM([SubTotal]) 
    FOR [FiscalYear] 
    IN ([2002], [2003], [2004])
) AS pvt;
vStateProvinceCountryRegion (Person)
Joins StateProvince table with CountryRegion table.
CREATE VIEW [Person].[vStateProvinceCountryRegion] 
WITH SCHEMABINDING 
AS 
SELECT 
    sp.[StateProvinceID] 
    ,sp.[StateProvinceCode] 
    ,sp.[IsOnlyStateProvinceFlag] 
    ,sp.[Name] AS [StateProvinceName] 
    ,sp.[TerritoryID] 
    ,cr.[CountryRegionCode] 
    ,cr.[Name] AS [CountryRegionName]
FROM [Person].[StateProvince] sp 
    INNER JOIN [Person].[CountryRegion] cr 
    ON sp.[CountryRegionCode] = cr.[CountryRegionCode];
vStoreWithDemographics (Sales)
Stores (names and addresses) that sell Adventure Works Cycles products to consumers.
CREATE VIEW [Sales].[vStoreWithDemographics] AS 
SELECT 
    s.[CustomerID] 
    ,s.[Name] 
    ,ct.[Name] AS [ContactType] 
    ,c.[Title] 
    ,c.[FirstName] 
    ,c.[MiddleName] 
    ,c.[LastName] 
    ,c.[Suffix] 
    ,c.[Phone] 
    ,c.[EmailAddress] 
    ,c.[EmailPromotion] 
    ,at.[Name] AS [AddressType]
    ,a.[AddressLine1] 
    ,a.[AddressLine2] 
    ,a.[City] 
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode] 
    ,cr.[Name] AS [CountryRegionName] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/AnnualSales)[1]', 'money') AS [AnnualSales] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/AnnualRevenue)[1]', 'money') AS [AnnualRevenue] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/BankName)[1]', 'nvarchar(50)') AS [BankName] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/BusinessType)[1]', 'nvarchar(5)') AS [BusinessType] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/YearOpened)[1]', 'integer') AS [YearOpened] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Specialty)[1]', 'nvarchar(50)') AS [Specialty] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/SquareFeet)[1]', 'integer') AS [SquareFeet] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Brands)[1]', 'nvarchar(30)') AS [Brands] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Internet)[1]', 'nvarchar(30)') AS [Internet] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/NumberEmployees)[1]', 'integer') AS [NumberEmployees] 
FROM [Sales].[Store] s
    INNER JOIN [Sales].[StoreContact] sc 
    ON sc.[CustomerID] = s.[CustomerID]
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = sc.[ContactID]
    INNER JOIN [Person].[ContactType] ct 
    ON sc.[ContactTypeID] = ct.[ContactTypeID]
    INNER JOIN [Sales].[CustomerAddress] ca 
    ON ca.[CustomerID] = s.[CustomerID]
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = ca.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    INNER JOIN [Person].[AddressType] at 
    ON ca.[AddressTypeID] = at.[AddressTypeID]
WHERE s.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID] 
    FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'S');
vVendor (Purchasing)
Vendor (company) names and addresses and the names of vendor employees to contact.
CREATE VIEW [Purchasing].[vVendor] AS 
SELECT 
    v.[VendorID]
    ,v.[Name]
    ,ct.[Name] AS [ContactType]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
FROM [Purchasing].[Vendor] v
    INNER JOIN [Purchasing].[VendorContact] vc 
    ON vc.[VendorID] = v.[VendorID]
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = vc.[ContactID]
    INNER JOIN [Person].[ContactType] ct 
    ON vc.[ContactTypeID] = ct.[ContactTypeID]
    INNER JOIN [Purchasing].[VendorAddress] va 
    ON va.[VendorID] = v.[VendorID]
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = va.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
Procedures
uspGetBillOfMaterials (dbo)
Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
    @StartProductID [int],
    @CheckDate [datetime]
AS
BEGIN
    SET NOCOUNT ON;

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

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

    -- Use recursive query to list out all Employees required for a particular Manager
    WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], 0 -- Get the initial list of Employees for Manager n
        FROM [HumanResources].[Employee] e 
            INNER JOIN [Person].[Contact] c 
            ON e.[ContactID] = c.[ContactID]
        WHERE [ManagerID] = @ManagerID
        UNION ALL
        SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [HumanResources].[Employee] e 
            INNER JOIN [EMP_cte]
            ON e.[ManagerID] = [EMP_cte].[EmployeeID]
            INNER JOIN [Person].[Contact] c 
            ON e.[ContactID] = c.[ContactID]
        )
    -- Join back to Employee to return the manager name 
    SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName',
        [EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName] -- Outer select from the CTE
    FROM [EMP_cte] 
        INNER JOIN [HumanResources].[Employee] e 
        ON [EMP_cte].[ManagerID] = e.[EmployeeID]
        INNER JOIN [Person].[Contact] c 
        ON e.[ContactID] = c.[ContactID]
    ORDER BY [RecursionLevel], [ManagerID], [EmployeeID]
    OPTION (MAXRECURSION 25) 
END;
uspGetWhereUsedProductID (dbo)
Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.
CREATE PROCEDURE [dbo].[uspGetWhereUsedProductID]
    @StartProductID [int],
    @CheckDate [datetime]
AS
BEGIN
    SET NOCOUNT ON;

    --Use recursive query to generate a multi-level Bill of Material (i.e. all level 1 components of a level 0 assembly, all level 2 components of a level 1 assembly)
    WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
        FROM [Production].[BillOfMaterials] b
            INNER JOIN [Production].[Product] p 
            ON b.[ProductAssemblyID] = p.[ProductID] 
        WHERE b.[ComponentID] = @StartProductID 
            AND @CheckDate >= b.[StartDate] 
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        UNION ALL
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [BOM_cte] cte
            INNER JOIN [Production].[BillOfMaterials] b 
            ON cte.[ProductAssemblyID] = b.[ComponentID]
            INNER JOIN [Production].[Product] p 
            ON b.[ProductAssemblyID] = p.[ProductID] 
        WHERE @CheckDate >= b.[StartDate] 
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        )
    -- Outer select from the CTE
    SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
    FROM [BOM_cte] b
    GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
    ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
    OPTION (MAXRECURSION 25) 
END;
uspLogError (dbo)
Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.
-- uspLogError logs error information in the ErrorLog table about the 
-- error that caused execution to jump to the CATCH block of a 
-- TRY...CATCH construct. This should be executed from within the scope 
-- of a CATCH block otherwise it will return without inserting error 
-- information. 
CREATE PROCEDURE [dbo].[uspLogError] 
    @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS                               -- by uspLogError in the ErrorLog table
BEGIN
    SET NOCOUNT ON;

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

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

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

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

        -- Pass back the ErrorLogID of the row inserted
        SET @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred in stored procedure uspLogError: ';
        EXECUTE [dbo].[uspPrintError];
        RETURN -1;
    END CATCH
END;
uspPrintError (dbo)
Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information.
-- uspPrintError prints error information about the error that caused 
-- execution to jump to the CATCH block of a TRY...CATCH construct. 
-- Should be executed from within the scope of a CATCH block otherwise 
-- it will return without printing any error information.
CREATE PROCEDURE [dbo].[uspPrintError] 
AS
BEGIN
    SET NOCOUNT ON;

    -- Print error information. 
    PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
          ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
          ', State ' + CONVERT(varchar(5), ERROR_STATE()) + 
          ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') + 
          ', Line ' + CONVERT(varchar(5), ERROR_LINE());
    PRINT ERROR_MESSAGE();
END;
uspUpdateEmployeeHireInfo (HumanResources)
Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters.
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
    @EmployeeID [int], 
    @Title [nvarchar](50), 
    @HireDate [datetime], 
    @RateChangeDate [datetime], 
    @Rate [money], 
    @PayFrequency [tinyint], 
    @CurrentFlag [dbo].[Flag] 
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

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

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

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

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
uspUpdateEmployeeLogin (HumanResources)
Updates the Employee table with the values specified in the input parameters for the given EmployeeID.
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeLogin]
    @EmployeeID [int], 
    @ManagerID [int],
    @LoginID [nvarchar](256),
    @Title [nvarchar](50),
    @HireDate [datetime],
    @CurrentFlag [dbo].[Flag]
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        UPDATE [HumanResources].[Employee] 
        SET [ManagerID] = @ManagerID 
            ,[LoginID] = @LoginID 
            ,[Title] = @Title 
            ,[HireDate] = @HireDate 
            ,[CurrentFlag] = @CurrentFlag 
        WHERE [EmployeeID] = @EmployeeID;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
uspUpdateEmployeePersonalInfo (HumanResources)
Updates the Employee table with the values specified in the input parameters for the given EmployeeID.
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo]
    @EmployeeID [int], 
    @NationalIDNumber [nvarchar](15), 
    @BirthDate [datetime], 
    @MaritalStatus [nchar](1), 
    @Gender [nchar](1)
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        UPDATE [HumanResources].[Employee] 
        SET [NationalIDNumber] = @NationalIDNumber 
            ,[BirthDate] = @BirthDate 
            ,[MaritalStatus] = @MaritalStatus 
            ,[Gender] = @Gender 
        WHERE [EmployeeID] = @EmployeeID;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
Functions
ufnGetAccountingEndDate (dbo)
Scalar function used in the uSalesOrderHeader trigger to set the starting account date.
CREATE FUNCTION [dbo].[ufnGetAccountingEndDate]()
RETURNS [datetime] 
AS 
BEGIN
    RETURN DATEADD(millisecond, -2, CONVERT(datetime, '20040701', 112));
END;
ufnGetAccountingStartDate (dbo)
Scalar function used in the uSalesOrderHeader trigger to set the ending account date.
CREATE FUNCTION [dbo].[ufnGetAccountingStartDate]()
RETURNS [datetime] 
AS 
BEGIN
    RETURN CONVERT(datetime, '20030701', 112);
END;
ufnGetDocumentStatusText (dbo)
Scalar function returning the text representation of the Status column in the Document table.
CREATE FUNCTION [dbo].[ufnGetDocumentStatusText](@Status [tinyint])
RETURNS [nvarchar](16) 
AS 
-- Returns the sales order status text representation for the status value.
BEGIN
    DECLARE @ret [nvarchar](16);

    SET @ret = 
        CASE @Status
            WHEN 1 THEN N'Pending approval'
            WHEN 2 THEN N'Approved'
            WHEN 3 THEN N'Obsolete'
            ELSE N'** Invalid **'
        END;
    
    RETURN @ret
END;
ufnGetProductDealerPrice (dbo)
Scalar function returning the dealer price for a given product on a particular order date.
CREATE FUNCTION [dbo].[ufnGetProductDealerPrice](@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
-- Returns the dealer price for the product on a specific date.
BEGIN
    DECLARE @DealerPrice money;
    DECLARE @DealerDiscount money;

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

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

    RETURN @DealerPrice;
END;
ufnGetProductListPrice (dbo)
Scalar function returning the list price for a given product on a particular order date.
CREATE FUNCTION [dbo].[ufnGetProductListPrice](@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
BEGIN
    DECLARE @ListPrice money;

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

    RETURN @ListPrice;
END;
ufnGetProductStandardCost (dbo)
Scalar function returning the standard cost for a given product on a particular order date.
CREATE FUNCTION [dbo].[ufnGetProductStandardCost](@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
-- Returns the standard cost for the product on a specific date.
BEGIN
    DECLARE @StandardCost money;

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

    RETURN @StandardCost;
END;
ufnGetPurchaseOrderStatusText (dbo)
Scalar function returning the text representation of the Status column in the PurchaseOrderHeader table.
CREATE FUNCTION [dbo].[ufnGetPurchaseOrderStatusText](@Status [tinyint])
RETURNS [nvarchar](15) 
AS 
-- Returns the sales order status text representation for the status value.
BEGIN
    DECLARE @ret [nvarchar](15);

    SET @ret = 
        CASE @Status
            WHEN 1 THEN 'Pending'
            WHEN 2 THEN 'Approved'
            WHEN 3 THEN 'Rejected'
            WHEN 4 THEN 'Complete'
            ELSE '** Invalid **'
        END;
    
    RETURN @ret
END;
ufnGetSalesOrderStatusText (dbo)
Scalar function returning the text representation of the Status column in the SalesOrderHeader table.
CREATE FUNCTION [dbo].[ufnGetSalesOrderStatusText](@Status [tinyint])
RETURNS [nvarchar](15) 
AS 
-- Returns the sales order status text representation for the status value.
BEGIN
    DECLARE @ret [nvarchar](15);

    SET @ret = 
        CASE @Status
            WHEN 1 THEN 'In process'
            WHEN 2 THEN 'Approved'
            WHEN 3 THEN 'Backordered'
            WHEN 4 THEN 'Rejected'
            WHEN 5 THEN 'Shipped'
            WHEN 6 THEN 'Cancelled'
            ELSE '** Invalid **'
        END;
    
    RETURN @ret
END;
ufnGetStock (dbo)
Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID.
CREATE FUNCTION [dbo].[ufnGetStock](@ProductID [int])
RETURNS [int] 
AS 
-- Returns the stock level for the product. This function is used internally only
BEGIN
    DECLARE @ret int;
    
    SELECT @ret = SUM(p.[Quantity]) 
    FROM [Production].[ProductInventory] p 
    WHERE p.[ProductID] = @ProductID 
        AND p.[LocationID] = '6'; -- Only look at inventory in the misc storage
    
    IF (@ret IS NULL) 
        SET @ret = 0
    
    RETURN @ret
END;
ufnLeadingZeros (dbo)
Scalar function used by the Sales.Customer table to help set the account number.
CREATE FUNCTION [dbo].[ufnLeadingZeros](
    @Value int
) 
RETURNS varchar(8) 
WITH SCHEMABINDING 
AS 
BEGIN
    DECLARE @ReturnValue varchar(8);

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

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

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

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

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

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

            ELSE NULL 
        END;

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

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

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

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

    -- Return the information to the caller
    IF @ContactID IS NOT NULL 
    BEGIN
        INSERT @retContactInformation
        SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
    END;

    RETURN;
END;
Schemas
HumanResources
Contains objects related to employees and departments.
CREATE SCHEMA HumanResources AUTHORIZATION dbo
Person
Contains objects related to names and addresses of customers, vendors, and employees
CREATE SCHEMA Person AUTHORIZATION dbo
Production
Contains objects related to products, inventory, and manufacturing.
CREATE SCHEMA Production AUTHORIZATION dbo
Purchasing
Contains objects related to vendors and purchase orders.
CREATE SCHEMA Purchasing AUTHORIZATION dbo
Sales
Contains objects related to customers, sales orders, and sales territories.
CREATE SCHEMA Sales AUTHORIZATION dbo
Types
AccountNumber (dbo) 
CREATE TYPE dbo.AccountNumber FROM nvarchar(30) NULL
Flag (dbo) 
CREATE TYPE dbo.Flag FROM bit NOT NULL
Name (dbo) 
CREATE TYPE dbo.[Name] FROM nvarchar(100) NULL
NameStyle (dbo) 
CREATE TYPE dbo.NameStyle FROM bit NOT NULL
OrderNumber (dbo) 
CREATE TYPE dbo.OrderNumber FROM nvarchar(50) NULL
Phone (dbo) 
CREATE TYPE dbo.Phone FROM nvarchar(50) NULL
XML Schema Collections
AdditionalContactInfoSchemaCollection (Person)
Collection of XML schemas for the AdditionalContactInfo column in the Person.Contact table.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"><xsd:element name="AdditionalContactInfo"><xsd:complexType mixed="true"><xsd:complexContent mixed="true"><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:any namespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactRecord http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes" minOccurs="0" maxOccurs="unbounded" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:schema><xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactRecord" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactRecord"><xsd:element name="ContactRecord"><xsd:complexType mixed="true"><xsd:complexContent mixed="true"><xsd:restriction base="xsd:anyType"><xsd:choice minOccurs="0" maxOccurs="unbounded"><xsd:any namespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes" /></xsd:choice><xsd:attribute name="date" type="xsd:date" /></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:schema><xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes" elementFormDefault="qualified"><xsd:element name="eMail" type="t:eMailType" /><xsd:element name="facsimileTelephoneNumber" type="t:phoneNumberType" /><xsd:element name="homePostalAddress" type="t:addressType" /><xsd:element name="internationaliSDNNumber" type="t:phoneNumberType" /><xsd:element name="mobile" type="t:phoneNumberType" /><xsd:element name="pager" type="t:phoneNumberType" /><xsd:element name="physicalDeliveryOfficeName" type="t:addressType" /><xsd:element name="registeredAddress" type="t:addressType" /><xsd:element name="telephoneNumber" type="t:phoneNumberType" /><xsd:element name="telexNumber" type="t:phoneNumberType" /><xsd:complexType name="addressType"><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Street" type="xsd:string" maxOccurs="2" /><xsd:element name="City" type="xsd:string" /><xsd:element name="StateProvince" type="xsd:string" /><xsd:element name="PostalCode" type="xsd:string" minOccurs="0" /><xsd:element name="CountryRegion" type="xsd:string" /><xsd:element name="SpecialInstructions" type="t:specialInstructionsType" minOccurs="0" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="eMailType"><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="eMailAddress" type="xsd:string" /><xsd:element name="SpecialInstructions" type="t:specialInstructionsType" minOccurs="0" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="phoneNumberType"><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="number"><xsd:simpleType><xsd:restriction base="xsd:string"><xsd:pattern value="[0-9\(\)\-]*" /></xsd:restriction></xsd:simpleType></xsd:element><xsd:element name="SpecialInstructions" type="t:specialInstructionsType" minOccurs="0" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="specialInstructionsType" mixed="true"><xsd:complexContent mixed="true"><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:any namespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes" minOccurs="0" maxOccurs="unbounded" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></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 xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume" elementFormDefault="qualified">
  <xsd:element name="Address" type="t:AddressType" />
  <xsd:element name="Education" type="t:EducationType" />
  <xsd:element name="Employment" type="t:EmploymentType" />
  <xsd:element name="Location" type="t:LocationType" />
  <xsd:element name="Name" type="t:NameType" />
  <xsd:element name="Resume" type="t:ResumeType" />
  <xsd:element name="Telephone" type="t:TelephoneType" />
  <xsd:complexType name="AddressType">
    <xsd:complexContent>
      <xsd:restriction base="xsd:anyType">
        <xsd:sequence>
          <xsd:element name="Addr.Type" type="xsd:string" />
          <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:complexContent>
                <xsd:restriction base="xsd:anyType">
                  <xsd:sequence>
                    <xsd:element ref="t:Location" />
                  </xsd:sequence>
                </xsd:restriction>
              </xsd:complexContent>
            </xsd:complexType>
          </xsd:element>
          <xsd:element name="Addr.PostalCode" type="xsd:string" />
          <xsd:element name="Addr.Telephone" minOccurs="0">
            <xsd:complexType>
              <xsd:complexContent>
                <xsd:restriction base="xsd:anyType">
                  <xsd:sequence>
                    <xsd:element ref="t:Telephone" maxOccurs="unbounded" />
                  </xsd:sequence>
                </xsd:restriction>
              </xsd:complexContent>
            </xsd:complexType>
          </xsd:element>
        </xsd:sequence>
      </xsd:restriction>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType name="EducationType">
    <xsd:complexContent>
      <xsd:restriction base="xsd:anyType">
        <xsd:sequence>
          <xsd:element name="Edu.Level" type="xsd:string" />
          <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: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:complexContent>
                <xsd:restriction base="xsd:anyType">
                  <xsd:sequence>
                    <xsd:element ref="t:Location" />
                  </xsd:sequence>
                </xsd:restriction>
              </xsd:complexContent>
            </xsd:complexType>
          </xsd:element>
        </xsd:sequence>
      </xsd:restriction>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType name="EmploymentType">
    <xsd:complexContent>
      <xsd:restriction base="xsd:anyType">
        <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:complexContent>
                <xsd:restriction base="xsd:anyType">
                  <xsd:sequence>
                    <xsd:element ref="t:Location" />
                  </xsd:sequence>
                </xsd:restriction>
              </xsd:complexContent>
            </xsd:complexType>
          </xsd:element>
        </xsd:sequence>
      </xsd:restriction>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType name="LocationType">
    <xsd:complexContent>
      <xsd:restriction base="xsd:anyType">
        <xsd:sequence>
          <xsd:element name="Loc.CountryRegion" type="xsd:string" />
          <xsd:element name="Loc.State" type="xsd:string" minOccurs="0" />
          <xsd:element name="Loc.City" type="xsd:string" minOccurs="0" />
        </xsd:sequence>
      </xsd:restriction>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType name="NameType">
    <xsd:complexContent>
      <xsd:restriction base="xsd:anyType">
        <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:restriction>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType name="ResumeType">
    <xsd:complexContent>
      <xsd:restriction base="xsd:anyType">
        <xsd:sequence>
          <xsd:element ref="t:Name" />
          <xsd:element name="Skills" type="xsd:string" minOccurs="0" />
          <xsd:element ref="t:Employment" maxOccurs="unbounded" />
          <xsd:element ref="t:Education" maxOccurs="unbounded" />
          <xsd:element ref="t:Address" maxOccurs="unbounded" />
          <xsd:element ref="t:Telephone" minOccurs="0" />
          <xsd:element name="EMail" type="xsd:string" minOccurs="0" />
          <xsd:element name="WebSite" type="xsd:string" minOccurs="0" />
        </xsd:sequence>
      </xsd:restriction>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType name="TelephoneType">
    <xsd:complexContent>
      <xsd:restriction base="xsd:anyType">
        <xsd:sequence>
          <xsd:element name="Tel.Type" type="xsd:anyType" minOccurs="0" />
          <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:restriction>
    </xsd:complexContent>
  </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 xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey" elementFormDefault="qualified">
  <xsd:element name="IndividualSurvey">
    <xsd:complexType>
      <xsd:complexContent>
        <xsd:restriction base="xsd:anyType">
          <xsd:sequence>
            <xsd:element name="TotalPurchaseYTD" type="xsd:decimal" minOccurs="0" />
            <xsd:element name="DateFirstPurchase" type="xsd:date" minOccurs="0" />
            <xsd:element name="BirthDate" type="xsd:date" minOccurs="0" />
            <xsd:element name="MaritalStatus" type="xsd:string" minOccurs="0" />
            <xsd:element name="YearlyIncome" type="t:SalaryType" minOccurs="0" />
            <xsd:element name="Gender" type="xsd:string" minOccurs="0" />
            <xsd:element name="TotalChildren" type="xsd:int" minOccurs="0" />
            <xsd:element name="NumberChildrenAtHome" type="xsd:int" minOccurs="0" />
            <xsd:element name="Education" type="xsd:string" minOccurs="0" />
            <xsd:element name="Occupation" type="xsd:string" minOccurs="0" />
            <xsd:element name="HomeOwnerFlag" type="xsd:string" minOccurs="0" />
            <xsd:element name="NumberCarsOwned" type="xsd:int" minOccurs="0" />
            <xsd:element name="Hobby" type="xsd:string" minOccurs="0" maxOccurs="unbounded" />
            <xsd:element name="CommuteDistance" type="t:MileRangeType" minOccurs="0" />
            <xsd:element name="Comments" type="xsd:string" minOccurs="0" />
          </xsd:sequence>
        </xsd:restriction>
      </xsd:complexContent>
    </xsd:complexType>
  </xsd:element>
  <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: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: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 xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" elementFormDefault="qualified">
  <xsd:element name="root">
    <xsd:complexType mixed="true">
      <xsd:complexContent mixed="true">
        <xsd:restriction base="xsd:anyType">
          <xsd:sequence>
            <xsd:element name="Location" maxOccurs="unbounded">
              <xsd:complexType mixed="true">
                <xsd:complexContent mixed="true">
                  <xsd:restriction base="xsd:anyType">
                    <xsd:sequence>
                      <xsd:element name="step" type="t:StepType" maxOccurs="unbounded" />
                    </xsd:sequence>
                    <xsd:attribute name="LocationID" type="xsd:integer" use="required" />
                    <xsd:attribute name="SetupHours" type="xsd:decimal" />
                    <xsd:attribute name="MachineHours" type="xsd:decimal" />
                    <xsd:attribute name="LaborHours" type="xsd:decimal" />
                    <xsd:attribute name="LotSize" type="xsd:decimal" />
                  </xsd:restriction>
                </xsd:complexContent>
              </xsd:complexType>
            </xsd:element>
          </xsd:sequence>
        </xsd:restriction>
      </xsd:complexContent>
    </xsd:complexType>
  </xsd:element>
  <xsd:complexType name="StepType" mixed="true">
    <xsd:complexContent mixed="true">
      <xsd:restriction base="xsd:anyType">
        <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:restriction>
    </xsd:complexContent>
  </xsd:complexType>
</xsd:schema>
ProductDescriptionSchemaCollection (Production)
Collection of XML schemas for the CatalogDescription column in the Production.ProductModel table.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" elementFormDefault="qualified"><xsd:element name="Maintenance"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="NoOfYears" type="xsd:string" /><xsd:element name="Description" type="xsd:string" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element><xsd:element name="Warranty"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="WarrantyPeriod" type="xsd:string" /><xsd:element name="Description" type="xsd:string" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:schema><xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription" elementFormDefault="qualified"><xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" /><xsd:element name="Code" type="xsd:string" /><xsd:element name="Description" type="xsd:string" /><xsd:element name="ProductDescription" type="t:ProductDescription" /><xsd:element name="Taxonomy" type="xsd:string" /><xsd:complexType name="Category"><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element ref="t:Taxonomy" /><xsd:element ref="t:Code" /><xsd:element ref="t:Description" minOccurs="0" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="Features" mixed="true"><xsd:complexContent mixed="true"><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element ref="ns1:Warranty" /><xsd:element ref="ns1:Maintenance" /><xsd:any namespace="##other" processContents="skip" minOccurs="0" maxOccurs="unbounded" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="Manufacturer"><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Name" type="xsd:string" minOccurs="0" /><xsd:element name="CopyrightURL" type="xsd:string" minOccurs="0" /><xsd:element name="Copyright" type="xsd:string" minOccurs="0" /><xsd:element name="ProductURL" type="xsd:string" minOccurs="0" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="Picture"><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Name" type="xsd:string" minOccurs="0" /><xsd:element name="Angle" type="xsd:string" minOccurs="0" /><xsd:element name="Size" type="xsd:string" minOccurs="0" /><xsd:element name="ProductPhotoID" type="xsd:integer" minOccurs="0" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="ProductDescription"><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Summary" type="t:Summary" minOccurs="0" /><xsd:element name="Manufacturer" type="t:Manufacturer" minOccurs="0" /><xsd:element name="Features" type="t:Features" minOccurs="0" maxOccurs="unbounded" /><xsd:element name="Picture" type="t:Picture" minOccurs="0" maxOccurs="unbounded" /><xsd:element name="Category" type="t:Category" minOccurs="0" maxOccurs="unbounded" /><xsd:element name="Specifications" type="t:Specifications" minOccurs="0" maxOccurs="unbounded" /></xsd:sequence><xsd:attribute name="ProductModelID" type="xsd:string" /><xsd:attribute name="ProductModelName" type="xsd:string" /></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="Specifications" mixed="true"><xsd:complexContent mixed="true"><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:any processContents="skip" minOccurs="0" maxOccurs="unbounded" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="Summary" mixed="true"><xsd:complexContent mixed="true"><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:any namespace="http://www.w3.org/1999/xhtml" processContents="skip" minOccurs="0" maxOccurs="unbounded" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></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" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey" elementFormDefault="qualified">
  <xsd:element name="StoreSurvey">
    <xsd:complexType>
      <xsd:complexContent>
        <xsd:restriction base="xsd:anyType">
          <xsd:sequence>
            <xsd:element name="ContactName" type="xsd:string" minOccurs="0" />
            <xsd:element name="JobTitle" type="xsd:string" minOccurs="0" />
            <xsd:element name="AnnualSales" type="xsd:decimal" minOccurs="0" />
            <xsd:element name="AnnualRevenue" type="xsd:decimal" minOccurs="0" />
            <xsd:element name="BankName" type="xsd:string" minOccurs="0" />
            <xsd:element name="BusinessType" type="t:BusinessType" minOccurs="0" />
            <xsd:element name="YearOpened" type="xsd:gYear" minOccurs="0" />
            <xsd:element name="Specialty" type="t:SpecialtyType" minOccurs="0" />
            <xsd:element name="SquareFeet" type="xsd:float" minOccurs="0" />
            <xsd:element name="Brands" type="t:BrandType" minOccurs="0" />
            <xsd:element name="Internet" type="t:InternetType" minOccurs="0" />
            <xsd:element name="NumberEmployees" type="xsd:int" minOccurs="0" />
            <xsd:element name="Comments" type="xsd:string" minOccurs="0" />
          </xsd:sequence>
        </xsd:restriction>
      </xsd:complexContent>
    </xsd:complexType>
  </xsd:element>
  <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="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>
  <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: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>
</xsd:schema>
Database Triggers
ddlDatabaseTriggerLog
Database trigger to audit all of the DDL changes made to the AdventureWorks database.
CREATE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @data XML;
    DECLARE @schema sysname;
    DECLARE @object sysname;
    DECLARE @eventType sysname;

    SET @data = EVENTDATA();
    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') 

    IF @object IS NOT NULL
        PRINT '  ' + @eventType + ' - ' + @schema + '.' + @object;
    ELSE
        PRINT '  ' + @eventType + ' - ' + @schema;

    IF @eventType IS NULL
        PRINT CONVERT(nvarchar(max), @data);

    INSERT [dbo].[DatabaseLog] 
        (
        [PostTime], 
        [DatabaseUser], 
        [Event], 
        [Schema], 
        [Object], 
        [TSQL], 
        [XmlEvent]
        ) 
    VALUES 
        (
        GETDATE(), 
        CONVERT(sysname, CURRENT_USER), 
        @eventType, 
        CONVERT(sysname, @schema), 
        CONVERT(sysname, @object), 
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), 
        @data
        );
END;

generated by dbscript