AdventureWorks Database Schema

Table dbo.AWBuildVersion

Current version number of the AdventureWorks sample database.

Name Type Nullable Comment
SystemInformationID PK tinyint NOT NULL IDENTITY 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 DEFAULT (getdate()) Date and time the record was last updated.


Table dbo.DatabaseLog

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

Name Type Nullable Comment
DatabaseLogID PK int NOT NULL IDENTITY Primary key for DatabaseLog records.
PostTime datetime NOT NULL The date and time the DDL change occurred.
DatabaseUser nvarchar (128) NOT NULL The user who implemented the DDL change.
Event nvarchar (128) NOT NULL The type of DDL statement that was executed.
Schema nvarchar (128) NULL The schema to which the changed object belongs.
Object nvarchar (128) 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 (max) NOT NULL The raw XML data generated by database trigger.


Table dbo.ErrorLog

Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.

Name Type Nullable Comment
ErrorLogID PK int NOT NULL IDENTITY Primary key for ErrorLog records.
ErrorTime datetime NOT NULL DEFAULT (getdate()) The date and time at which the error occurred.
UserName nvarchar (128) 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.


Table HumanResources.Department

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

Name Type Nullable Comment
DepartmentID PK smallint NOT NULL IDENTITY Primary key for Department records.
Name nvarchar (50) NOT NULL Name of the department.
GroupName nvarchar (50) NOT NULL Name of the group to which the department belongs.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table HumanResources.Employee

Employee information such as salary, department, and title.

Name Type Nullable Comment
EmployeeID PK int NOT NULL IDENTITY Primary key for Employee records.
NationalIDNumber nvarchar (15) NOT NULL Unique national identification number such as a social security number.
ContactID int NOT NULL -> Person.Contact.ContactID Identifies the employee in the Contact table. Foreign key to Contact.ContactID.
LoginID nvarchar (256) NOT NULL Network login.
ManagerID int NULL -> HumanResources.Employee.EmployeeID 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 bit NOT NULL DEFAULT ((1)) Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
VacationHours smallint NOT NULL DEFAULT ((0)) Number of available vacation hours.
SickLeaveHours smallint NOT NULL DEFAULT ((0)) Number of available sick leave hours.
CurrentFlag bit NOT NULL DEFAULT ((1)) 0 = Inactive, 1 = Active
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table HumanResources.EmployeeAddress

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

Name Type Nullable Comment
EmployeeID PK int NOT NULL -> HumanResources.Employee.EmployeeID Primary key. Foreign key to Employee.EmployeeID.
AddressID PK int NOT NULL -> Person.Address.AddressID Primary key. Foreign key to Address.AddressID.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table HumanResources.EmployeeDepartmentHistory

Employee department transfers.

Name Type Nullable Comment
EmployeeID PK int NOT NULL -> HumanResources.Employee.EmployeeID Employee identification number. Foreign key to Employee.EmployeeID.
DepartmentID PK smallint NOT NULL -> HumanResources.Department.DepartmentID Department in which the employee worked including currently. Foreign key to Department.DepartmentID.
ShiftID PK tinyint NOT NULL -> HumanResources.Shift.ShiftID Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.
StartDate PK 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 DEFAULT (getdate()) Date and time the record was last updated.


Table HumanResources.EmployeePayHistory

Employee pay history.

Name Type Nullable Comment
EmployeeID PK int NOT NULL -> HumanResources.Employee.EmployeeID Employee identification number. Foreign key to Employee.EmployeeID.
RateChangeDate PK datetime NOT NULL Date the change in pay is effective
Rate money (19, 4) NOT NULL Salary hourly rate.
PayFrequency tinyint NOT NULL 1 = Salary received monthly, 2 = Salary received biweekly
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table HumanResources.JobCandidate

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

Name Type Nullable Comment
JobCandidateID PK int NOT NULL IDENTITY Primary key for JobCandidate records.
EmployeeID int NULL -> HumanResources.Employee.EmployeeID Employee identification number if applicant was hired. Foreign key to Employee.EmployeeID.
Resume xml (max) NULL Résumé in XML format.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table HumanResources.Shift

Work shift lookup table.

Name Type Nullable Comment
ShiftID PK tinyint NOT NULL IDENTITY Primary key for Shift records.
Name nvarchar (50) NOT NULL Shift description.
StartTime datetime NOT NULL Shift start time.
EndTime datetime NOT NULL Shift end time.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


View HumanResources.vEmployee

Employee names and addresses.

Name Type Nullable Comment
EmployeeID int NOT NULL
Title nvarchar (8) NULL
FirstName nvarchar (50) NOT NULL
MiddleName nvarchar (50) NULL
LastName nvarchar (50) NOT NULL
Suffix nvarchar (10) NULL
JobTitle nvarchar (50) NOT NULL
Phone nvarchar (25) NULL
EmailAddress nvarchar (50) NULL
EmailPromotion int NOT NULL
AddressLine1 nvarchar (60) NOT NULL
AddressLine2 nvarchar (60) NULL
City nvarchar (30) NOT NULL
StateProvinceName nvarchar (50) NOT NULL
PostalCode nvarchar (15) NOT NULL
CountryRegionName nvarchar (50) NOT NULL
AdditionalContactInfo xml (max) NULL


View HumanResources.vEmployeeDepartment

Returns employee name, title, and current department.

Name Type Nullable Comment
EmployeeID int NOT NULL
Title nvarchar (8) NULL
FirstName nvarchar (50) NOT NULL
MiddleName nvarchar (50) NULL
LastName nvarchar (50) NOT NULL
Suffix nvarchar (10) NULL
JobTitle nvarchar (50) NOT NULL
Department nvarchar (50) NOT NULL
GroupName nvarchar (50) NOT NULL
StartDate datetime NOT NULL


View HumanResources.vEmployeeDepartmentHistory

Returns employee name and current and previous departments.

Name Type Nullable Comment
EmployeeID int NOT NULL
Title nvarchar (8) NULL
FirstName nvarchar (50) NOT NULL
MiddleName nvarchar (50) NULL
LastName nvarchar (50) NOT NULL
Suffix nvarchar (10) NULL
Shift nvarchar (50) NOT NULL
Department nvarchar (50) NOT NULL
GroupName nvarchar (50) NOT NULL
StartDate datetime NOT NULL
EndDate datetime NULL


View HumanResources.vJobCandidate

Job candidate names and resumes.

Name Type Nullable Comment
JobCandidateID int NOT NULL IDENTITY
EmployeeID int NULL
Name.Prefix nvarchar (30) NULL
Name.First nvarchar (30) NULL
Name.Middle nvarchar (30) NULL
Name.Last nvarchar (30) NULL
Name.Suffix nvarchar (30) NULL
Skills nvarchar (max) NULL
Addr.Type nvarchar (30) NULL
Addr.Loc.CountryRegion nvarchar (100) NULL
Addr.Loc.State nvarchar (100) NULL
Addr.Loc.City nvarchar (100) NULL
Addr.PostalCode nvarchar (20) NULL
EMail nvarchar (max) NULL
WebSite nvarchar (max) NULL
ModifiedDate datetime NOT NULL


View HumanResources.vJobCandidateEducation

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.

Name Type Nullable Comment
JobCandidateID int NOT NULL IDENTITY
Edu.Level nvarchar (max) NULL
Edu.StartDate datetime NULL
Edu.EndDate datetime NULL
Edu.Degree nvarchar (50) NULL
Edu.Major nvarchar (50) NULL
Edu.Minor nvarchar (50) NULL
Edu.GPA nvarchar (5) NULL
Edu.GPAScale nvarchar (5) NULL
Edu.School nvarchar (100) NULL
Edu.Loc.CountryRegion nvarchar (100) NULL
Edu.Loc.State nvarchar (100) NULL
Edu.Loc.City nvarchar (100) NULL


View HumanResources.vJobCandidateEmployment

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.

Name Type Nullable Comment
JobCandidateID int NOT NULL IDENTITY
Emp.StartDate datetime NULL
Emp.EndDate datetime NULL
Emp.OrgName nvarchar (100) NULL
Emp.JobTitle nvarchar (100) NULL
Emp.Responsibility nvarchar (max) NULL
Emp.FunctionCategory nvarchar (max) NULL
Emp.IndustryCategory nvarchar (max) NULL
Emp.Loc.CountryRegion nvarchar (max) NULL
Emp.Loc.State nvarchar (max) NULL
Emp.Loc.City nvarchar (max) NULL


Table Person.Address

Street address information for customers, employees, and vendors.

Name Type Nullable Comment
AddressID PK int NOT NULL IDENTITY 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 -> Person.StateProvince.StateProvinceID 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 DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Person.AddressType

Types of addresses stored in the Address table.

Name Type Nullable Comment
AddressTypeID PK int NOT NULL IDENTITY Primary key for AddressType records.
Name nvarchar (50) NOT NULL Address type description. For example, Billing, Home, or Shipping.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Person.Contact

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

Name Type Nullable Comment
ContactID PK int NOT NULL IDENTITY Primary key for Contact records.
NameStyle bit NOT NULL DEFAULT ((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 nvarchar (50) NOT NULL First name of the person.
MiddleName nvarchar (50) NULL Middle name or middle initial of the person.
LastName nvarchar (50) 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 DEFAULT ((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 nvarchar (25) 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 (max) NULL Additional contact information about the person stored in xml format.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Person.ContactType

Lookup table containing the types of contacts stored in Contact.

Name Type Nullable Comment
ContactTypeID PK int NOT NULL IDENTITY Primary key for ContactType records.
Name nvarchar (50) NOT NULL Contact type description.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Person.CountryRegion

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

Name Type Nullable Comment
CountryRegionCode PK nvarchar (3) NOT NULL ISO standard code for countries and regions.
Name nvarchar (50) NOT NULL Country or region name.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Person.StateProvince

State and province lookup table.

Name Type Nullable Comment
StateProvinceID PK int NOT NULL IDENTITY Primary key for StateProvince records.
StateProvinceCode nchar (3) NOT NULL ISO standard state or province code.
CountryRegionCode nvarchar (3) NOT NULL -> Person.CountryRegion.CountryRegionCode ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.
IsOnlyStateProvinceFlag bit NOT NULL DEFAULT ((1)) 0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode.
Name nvarchar (50) NOT NULL State or province description.
TerritoryID int NOT NULL -> Sales.SalesTerritory.TerritoryID ID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


View Person.vAdditionalContactInfo

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

Name Type Nullable Comment
ContactID int NOT NULL IDENTITY
FirstName nvarchar (50) NOT NULL
MiddleName nvarchar (50) NULL
LastName nvarchar (50) NOT NULL
TelephoneNumber nvarchar (50) NULL
TelephoneSpecialInstructions nvarchar (max) NULL
Street nvarchar (50) NULL
City nvarchar (50) NULL
StateProvince nvarchar (50) NULL
PostalCode nvarchar (50) NULL
CountryRegion nvarchar (50) NULL
HomeAddressSpecialInstructions nvarchar (max) NULL
EMailAddress nvarchar (128) NULL
EMailSpecialInstructions nvarchar (max) NULL
EMailTelephoneNumber nvarchar (50) NULL
rowguid uniqueidentifier NOT NULL
ModifiedDate datetime NOT NULL


View Person.vStateProvinceCountryRegion

Joins StateProvince table with CountryRegion table.

Name Type Nullable Comment
StateProvinceID int NOT NULL
StateProvinceCode nchar (3) NOT NULL
IsOnlyStateProvinceFlag bit NOT NULL
StateProvinceName nvarchar (50) NOT NULL
TerritoryID int NOT NULL
CountryRegionCode nvarchar (3) NOT NULL
CountryRegionName nvarchar (50) NOT NULL


Table Production.BillOfMaterials

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

Name Type Nullable Comment
BillOfMaterialsID PK int NOT NULL IDENTITY Primary key for BillOfMaterials records.
ProductAssemblyID int NULL -> Production.Product.ProductID Parent product identification number. Foreign key to Product.ProductID.
ComponentID int NOT NULL -> Production.Product.ProductID Component identification number. Foreign key to Product.ProductID.
StartDate datetime NOT NULL DEFAULT (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 -> Production.UnitMeasure.UnitMeasureCode 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 DEFAULT ((1.00)) Quantity of the component needed to create the assembly.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.Culture

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

Name Type Nullable Comment
CultureID PK nchar (6) NOT NULL Primary key for Culture records.
Name nvarchar (50) NOT NULL Culture description.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.Document

Product maintenance documents.

Name Type Nullable Comment
DocumentID PK int NOT NULL IDENTITY 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 DEFAULT ((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 DEFAULT (getdate()) Date and time the record was last updated.


Table Production.Illustration

Bicycle assembly diagrams.

Name Type Nullable Comment
IllustrationID PK int NOT NULL IDENTITY Primary key for Illustration records.
Diagram xml (max) NULL Illustrations used in manufacturing instructions. Stored as XML.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.Location

Product inventory and manufacturing locations.

Name Type Nullable Comment
LocationID PK smallint NOT NULL IDENTITY Primary key for Location records.
Name nvarchar (50) NOT NULL Location description.
CostRate smallmoney (10, 4) NOT NULL DEFAULT ((0.00)) Standard hourly cost of the manufacturing location.
Availability decimal (8, 2) NOT NULL DEFAULT ((0.00)) Work capacity (in hours) of the manufacturing location.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.Product

Products sold or used in the manfacturing of sold products.

Name Type Nullable Comment
ProductID PK int NOT NULL IDENTITY Primary key for Product records.
Name nvarchar (50) NOT NULL Name of the product.
ProductNumber nvarchar (25) NOT NULL Unique product identification number.
MakeFlag bit NOT NULL DEFAULT ((1)) 0 = Product is purchased, 1 = Product is manufactured in-house.
FinishedGoodsFlag bit NOT NULL DEFAULT ((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 (19, 4) NOT NULL Standard cost of the product.
ListPrice money (19, 4) NOT NULL Selling price.
Size nvarchar (5) NULL Product size.
SizeUnitMeasureCode nchar (3) NULL -> Production.UnitMeasure.UnitMeasureCode Unit of measure for Size column.
WeightUnitMeasureCode nchar (3) NULL -> Production.UnitMeasure.UnitMeasureCode 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 -> Production.ProductSubcategory.ProductSubcategoryID Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID.
ProductModelID int NULL -> Production.ProductModel.ProductModelID 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 DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.ProductCategory

High-level product categorization.

Name Type Nullable Comment
ProductCategoryID PK int NOT NULL IDENTITY Primary key for ProductCategory records.
Name nvarchar (50) NOT NULL Category description.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.ProductCostHistory

Changes in the cost of a product over time.

Name Type Nullable Comment
ProductID PK int NOT NULL -> Production.Product.ProductID Product identification number. Foreign key to Product.ProductID
StartDate PK datetime NOT NULL Product cost start date.
EndDate datetime NULL Product cost end date.
StandardCost money (19, 4) NOT NULL Standard cost of the product.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.ProductDescription

Product descriptions in several languages.

Name Type Nullable Comment
ProductDescriptionID PK int NOT NULL IDENTITY Primary key for ProductDescription records.
Description nvarchar (400) NOT NULL Description of the product.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.ProductDocument

Cross-reference table mapping products to related product documents.

Name Type Nullable Comment
ProductID PK int NOT NULL -> Production.Product.ProductID Product identification number. Foreign key to Product.ProductID.
DocumentID PK int NOT NULL -> Production.Document.DocumentID Document identification number. Foreign key to Document.DocumentID.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.ProductInventory

Product inventory information.

Name Type Nullable Comment
ProductID PK int NOT NULL -> Production.Product.ProductID Product identification number. Foreign key to Product.ProductID.
LocationID PK smallint NOT NULL -> Production.Location.LocationID 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 DEFAULT ((0)) Quantity of products in the inventory location.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.ProductListPriceHistory

Changes in the list price of a product over time.

Name Type Nullable Comment
ProductID PK int NOT NULL -> Production.Product.ProductID Product identification number. Foreign key to Product.ProductID
StartDate PK datetime NOT NULL List price start date.
EndDate datetime NULL List price end date
ListPrice money (19, 4) NOT NULL Product list price.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.ProductModel

Product model classification.

Name Type Nullable Comment
ProductModelID PK int NOT NULL IDENTITY Primary key for ProductModel records.
Name nvarchar (50) NOT NULL Product model description.
CatalogDescription xml (max) NULL Detailed product catalog information in xml format.
Instructions xml (max) NULL Manufacturing instructions in xml format.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.ProductModelIllustration

Cross-reference table mapping product models and illustrations.

Name Type Nullable Comment
ProductModelID PK int NOT NULL -> Production.ProductModel.ProductModelID Primary key. Foreign key to ProductModel.ProductModelID.
IllustrationID PK int NOT NULL -> Production.Illustration.IllustrationID Primary key. Foreign key to Illustration.IllustrationID.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.ProductModelProductDescriptionCulture

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

Name Type Nullable Comment
ProductModelID PK int NOT NULL -> Production.ProductModel.ProductModelID Primary key. Foreign key to ProductModel.ProductModelID.
ProductDescriptionID PK int NOT NULL -> Production.ProductDescription.ProductDescriptionID Primary key. Foreign key to ProductDescription.ProductDescriptionID.
CultureID PK nchar (6) NOT NULL -> Production.Culture.CultureID Culture identification number. Foreign key to Culture.CultureID.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.ProductPhoto

Product images.

Name Type Nullable Comment
ProductPhotoID PK int NOT NULL IDENTITY 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 DEFAULT (getdate()) Date and time the record was last updated.


Table Production.ProductProductPhoto

Cross-reference table mapping products and product photos.

Name Type Nullable Comment
ProductID PK int NOT NULL -> Production.Product.ProductID Product identification number. Foreign key to Product.ProductID.
ProductPhotoID PK int NOT NULL -> Production.ProductPhoto.ProductPhotoID Product photo identification number. Foreign key to ProductPhoto.ProductPhotoID.
Primary bit NOT NULL DEFAULT ((0)) 0 = Photo is not the principal image. 1 = Photo is the principal image.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.ProductReview

Customer reviews of products they have purchased.

Name Type Nullable Comment
ProductReviewID PK int NOT NULL IDENTITY Primary key for ProductReview records.
ProductID int NOT NULL -> Production.Product.ProductID Product identification number. Foreign key to Product.ProductID.
ReviewerName nvarchar (50) NOT NULL Name of the reviewer.
ReviewDate datetime NOT NULL DEFAULT (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 DEFAULT (getdate()) Date and time the record was last updated.


Table Production.ProductSubcategory

Product subcategories. See ProductCategory table.

Name Type Nullable Comment
ProductSubcategoryID PK int NOT NULL IDENTITY Primary key for ProductSubcategory records.
ProductCategoryID int NOT NULL -> Production.ProductCategory.ProductCategoryID Product category identification number. Foreign key to ProductCategory.ProductCategoryID.
Name nvarchar (50) NOT NULL Subcategory description.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.ScrapReason

Manufacturing failure reasons lookup table.

Name Type Nullable Comment
ScrapReasonID PK smallint NOT NULL IDENTITY Primary key for ScrapReason records.
Name nvarchar (50) NOT NULL Failure description.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.TransactionHistory

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

Name Type Nullable Comment
TransactionID PK int NOT NULL IDENTITY Primary key for TransactionHistory records.
ProductID int NOT NULL -> Production.Product.ProductID 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 DEFAULT ((0)) Line number associated with the purchase order, sales order, or work order.
TransactionDate datetime NOT NULL DEFAULT (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 (19, 4) NOT NULL Product cost.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.TransactionHistoryArchive

Transactions for previous years.

Name Type Nullable Comment
TransactionID PK 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 DEFAULT ((0)) Line number associated with the purchase order, sales order, or work order.
TransactionDate datetime NOT NULL DEFAULT (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 (19, 4) NOT NULL Product cost.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.UnitMeasure

Unit of measure lookup table.

Name Type Nullable Comment
UnitMeasureCode PK nchar (3) NOT NULL Primary key.
Name nvarchar (50) NOT NULL Unit of measure description.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


View Production.vProductAndDescription

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

Name Type Nullable Comment
ProductID int NOT NULL
Name nvarchar (50) NOT NULL
ProductModel nvarchar (50) NOT NULL
CultureID nchar (6) NOT NULL
Description nvarchar (400) NOT NULL


View Production.vProductModelCatalogDescription

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

Name Type Nullable Comment
ProductModelID int NOT NULL IDENTITY
Name nvarchar (50) NOT NULL
Summary nvarchar (max) NULL
Manufacturer nvarchar (max) NULL
Copyright nvarchar (30) NULL
ProductURL nvarchar (256) NULL
WarrantyPeriod nvarchar (256) NULL
WarrantyDescription nvarchar (256) NULL
NoOfYears nvarchar (256) NULL
MaintenanceDescription nvarchar (256) NULL
Wheel nvarchar (256) NULL
Saddle nvarchar (256) NULL
Pedal nvarchar (256) NULL
BikeFrame nvarchar (max) NULL
Crankset nvarchar (256) NULL
PictureAngle nvarchar (256) NULL
PictureSize nvarchar (256) NULL
ProductPhotoID nvarchar (256) NULL
Material nvarchar (256) NULL
Color nvarchar (256) NULL
ProductLine nvarchar (256) NULL
Style nvarchar (256) NULL
RiderExperience nvarchar (1024) NULL
rowguid uniqueidentifier NOT NULL
ModifiedDate datetime NOT NULL


View Production.vProductModelInstructions

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

Name Type Nullable Comment
ProductModelID int NOT NULL IDENTITY
Name nvarchar (50) NOT NULL
Instructions nvarchar (max) NULL
LocationID int NULL
SetupHours decimal (9, 4) NULL
MachineHours decimal (9, 4) NULL
LaborHours decimal (9, 4) NULL
LotSize int NULL
Step nvarchar (1024) NULL
rowguid uniqueidentifier NOT NULL
ModifiedDate datetime NOT NULL


Table Production.WorkOrder

Manufacturing work orders.

Name Type Nullable Comment
WorkOrderID PK int NOT NULL IDENTITY Primary key for WorkOrder records.
ProductID int NOT NULL -> Production.Product.ProductID Product identification number. Foreign key to Product.ProductID.
OrderQty int NOT NULL Product quantity to build.
StockedQty int NOT NULL COMPUTED 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 -> Production.ScrapReason.ScrapReasonID Reason for inspection failure.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Production.WorkOrderRouting

Work order details.

Name Type Nullable Comment
WorkOrderID PK int NOT NULL -> Production.WorkOrder.WorkOrderID Primary key. Foreign key to WorkOrder.WorkOrderID.
ProductID PK int NOT NULL Primary key. Foreign key to Product.ProductID.
OperationSequence PK smallint NOT NULL Primary key. Indicates the manufacturing process sequence.
LocationID smallint NOT NULL -> Production.Location.LocationID 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 (19, 4) NOT NULL Estimated manufacturing cost.
ActualCost money (19, 4) NULL Actual manufacturing cost.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Purchasing.ProductVendor

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

Name Type Nullable Comment
ProductID PK int NOT NULL -> Production.Product.ProductID Primary key. Foreign key to Product.ProductID.
VendorID PK int NOT NULL -> Purchasing.Vendor.VendorID 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 (19, 4) NOT NULL The vendor's usual selling price.
LastReceiptCost money (19, 4) 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 -> Production.UnitMeasure.UnitMeasureCode The product's unit of measure.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Purchasing.PurchaseOrderDetail

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

Name Type Nullable Comment
PurchaseOrderID PK int NOT NULL -> Purchasing.PurchaseOrderHeader.PurchaseOrderID Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
PurchaseOrderDetailID PK int NOT NULL IDENTITY 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 -> Production.Product.ProductID Product identification number. Foreign key to Product.ProductID.
UnitPrice money (19, 4) NOT NULL Vendor's selling price of a single product.
LineTotal money (19, 4) NOT NULL COMPUTED 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 decimal (9, 2) NOT NULL COMPUTED Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Purchasing.PurchaseOrderHeader

General purchase order information. See PurchaseOrderDetail.

Name Type Nullable Comment
PurchaseOrderID PK int NOT NULL IDENTITY Primary key.
RevisionNumber tinyint NOT NULL DEFAULT ((0)) Incremental number to track changes to the purchase order over time.
Status tinyint NOT NULL DEFAULT ((1)) Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete
EmployeeID int NOT NULL -> HumanResources.Employee.EmployeeID Employee who created the purchase order. Foreign key to Employee.EmployeeID.
VendorID int NOT NULL -> Purchasing.Vendor.VendorID Vendor with whom the purchase order is placed. Foreign key to Vendor.VendorID.
ShipMethodID int NOT NULL -> Purchasing.ShipMethod.ShipMethodID Shipping method. Foreign key to ShipMethod.ShipMethodID.
OrderDate datetime NOT NULL DEFAULT (getdate()) Purchase order creation date.
ShipDate datetime NULL Estimated shipment date from the vendor.
SubTotal money (19, 4) NOT NULL DEFAULT ((0.00)) Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.
TaxAmt money (19, 4) NOT NULL DEFAULT ((0.00)) Tax amount.
Freight money (19, 4) NOT NULL DEFAULT ((0.00)) Shipping cost.
TotalDue money (19, 4) NOT NULL COMPUTED Total due to vendor. Computed as Subtotal TaxAmt Freight.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Purchasing.ShipMethod

Shipping company lookup table.

Name Type Nullable Comment
ShipMethodID PK int NOT NULL IDENTITY Primary key for ShipMethod records.
Name nvarchar (50) NOT NULL Shipping company name.
ShipBase money (19, 4) NOT NULL DEFAULT ((0.00)) Minimum shipping charge.
ShipRate money (19, 4) NOT NULL DEFAULT ((0.00)) Shipping charge per pound.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Purchasing.Vendor

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

Name Type Nullable Comment
VendorID PK int NOT NULL IDENTITY Primary key for Vendor records.
AccountNumber nvarchar (15) NOT NULL Vendor account (identification) number.
Name nvarchar (50) NOT NULL Company name.
CreditRating tinyint NOT NULL 1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average
PreferredVendorStatus bit NOT NULL DEFAULT ((1)) 0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.
ActiveFlag bit NOT NULL DEFAULT ((1)) 0 = Vendor no longer used. 1 = Vendor is actively used.
PurchasingWebServiceURL nvarchar (1024) NULL Vendor URL.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Purchasing.VendorAddress

Cross-reference mapping vendors and addresses.

Name Type Nullable Comment
VendorID PK int NOT NULL -> Purchasing.Vendor.VendorID Primary key. Foreign key to Vendor.VendorID.
AddressID PK int NOT NULL -> Person.Address.AddressID Primary key. Foreign key to Address.AddressID.
AddressTypeID int NOT NULL -> Person.AddressType.AddressTypeID Address type. Foreign key to AddressType.AddressTypeID.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Purchasing.VendorContact

Cross-reference table mapping vendors and their employees.

Name Type Nullable Comment
VendorID PK int NOT NULL -> Purchasing.Vendor.VendorID Primary key.
ContactID PK int NOT NULL -> Person.Contact.ContactID Contact (Vendor employee) identification number. Foreign key to Contact.ContactID.
ContactTypeID int NOT NULL -> Person.ContactType.ContactTypeID Contact type such as sales manager, or sales agent.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


View Purchasing.vVendor

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

Name Type Nullable Comment
VendorID int NOT NULL
Name nvarchar (50) NOT NULL
ContactType nvarchar (50) NOT NULL
Title nvarchar (8) NULL
FirstName nvarchar (50) NOT NULL
MiddleName nvarchar (50) NULL
LastName nvarchar (50) NOT NULL
Suffix nvarchar (10) NULL
Phone nvarchar (25) NULL
EmailAddress nvarchar (50) NULL
EmailPromotion int NOT NULL
AddressLine1 nvarchar (60) NOT NULL
AddressLine2 nvarchar (60) NULL
City nvarchar (30) NOT NULL
StateProvinceName nvarchar (50) NOT NULL
PostalCode nvarchar (15) NOT NULL
CountryRegionName nvarchar (50) NOT NULL


Table Sales.ContactCreditCard

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

Name Type Nullable Comment
ContactID PK int NOT NULL -> Person.Contact.ContactID Customer identification number. Foreign key to Contact.ContactID.
CreditCardID PK int NOT NULL -> Sales.CreditCard.CreditCardID Credit card identification number. Foreign key to CreditCard.CreditCardID.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.CountryRegionCurrency

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

Name Type Nullable Comment
CountryRegionCode PK nvarchar (3) NOT NULL -> Person.CountryRegion.CountryRegionCode ISO code for countries and regions. Foreign key to CountryRegion.CountryRegionCode.
CurrencyCode PK nchar (3) NOT NULL -> Sales.Currency.CurrencyCode ISO standard currency code. Foreign key to Currency.CurrencyCode.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.CreditCard

Customer credit card information.

Name Type Nullable Comment
CreditCardID PK int NOT NULL IDENTITY 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 DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.Currency

Lookup table containing standard ISO currencies.

Name Type Nullable Comment
CurrencyCode PK nchar (3) NOT NULL The ISO code for the Currency.
Name nvarchar (50) NOT NULL Currency name.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.CurrencyRate

Currency exchange rates.

Name Type Nullable Comment
CurrencyRateID PK int NOT NULL IDENTITY Primary key for CurrencyRate records.
CurrencyRateDate datetime NOT NULL Date and time the exchange rate was obtained.
FromCurrencyCode nchar (3) NOT NULL -> Sales.Currency.CurrencyCode Exchange rate was converted from this currency code.
ToCurrencyCode nchar (3) NOT NULL -> Sales.Currency.CurrencyCode Exchange rate was converted to this currency code.
AverageRate money (19, 4) NOT NULL Average exchange rate for the day.
EndOfDayRate money (19, 4) NOT NULL Final exchange rate for the day.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.Customer

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

Name Type Nullable Comment
CustomerID PK int NOT NULL IDENTITY Primary key for Customer records.
TerritoryID int NULL -> Sales.SalesTerritory.TerritoryID ID of the territory in which the customer is located. Foreign key to SalesTerritory.SalesTerritoryID.
AccountNumber varchar (10) NOT NULL COMPUTED 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 DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.CustomerAddress

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

Name Type Nullable Comment
CustomerID PK int NOT NULL -> Sales.Customer.CustomerID Primary key. Foreign key to Customer.CustomerID.
AddressID PK int NOT NULL -> Person.Address.AddressID Primary key. Foreign key to Address.AddressID.
AddressTypeID int NOT NULL -> Person.AddressType.AddressTypeID Address type. Foreign key to AddressType.AddressTypeID.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.Individual

Demographic data about customers that purchase Adventure Works products online.

Name Type Nullable Comment
CustomerID PK int NOT NULL -> Sales.Customer.CustomerID Unique customer identification number. Foreign key to Customer.CustomerID.
ContactID int NOT NULL -> Person.Contact.ContactID Identifies the customer in the Contact table. Foreign key to Contact.ContactID.
Demographics xml (max) NULL Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.SalesOrderDetail

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

Name Type Nullable Comment
SalesOrderID PK int NOT NULL -> Sales.SalesOrderHeader.SalesOrderID Primary key. Foreign key to SalesOrderHeader.SalesOrderID.
SalesOrderDetailID PK int NOT NULL IDENTITY 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 -> Sales.SpecialOfferProduct.ProductID Product sold to customer. Foreign key to Product.ProductID.
SpecialOfferID int NOT NULL -> Sales.SpecialOfferProduct.SpecialOfferID Promotional code. Foreign key to SpecialOffer.SpecialOfferID.
UnitPrice money (19, 4) NOT NULL Selling price of a single product.
UnitPriceDiscount money (19, 4) NOT NULL DEFAULT ((0.0)) Discount amount.
LineTotal numeric (38, 6) NOT NULL COMPUTED Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.SalesOrderHeader

General sales order information.

Name Type Nullable Comment
SalesOrderID PK int NOT NULL IDENTITY Primary key.
RevisionNumber tinyint NOT NULL DEFAULT ((0)) Incremental number to track changes to the sales order over time.
OrderDate datetime NOT NULL DEFAULT (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 DEFAULT ((1)) Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled
OnlineOrderFlag bit NOT NULL DEFAULT ((1)) 0 = Order placed by sales person. 1 = Order placed online by customer.
SalesOrderNumber nvarchar (25) NOT NULL COMPUTED Unique sales order identification number.
PurchaseOrderNumber nvarchar (25) NULL Customer purchase order number reference.
AccountNumber nvarchar (15) NULL Financial accounting number reference.
CustomerID int NOT NULL -> Sales.Customer.CustomerID Customer identification number. Foreign key to Customer.CustomerID.
ContactID int NOT NULL -> Person.Contact.ContactID Customer contact identification number. Foreign key to Contact.ContactID.
SalesPersonID int NULL -> Sales.SalesPerson.SalesPersonID Sales person who created the sales order. Foreign key to SalesPerson.SalePersonID.
TerritoryID int NULL -> Sales.SalesTerritory.TerritoryID Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID.
BillToAddressID int NOT NULL -> Person.Address.AddressID Customer billing address. Foreign key to Address.AddressID.
ShipToAddressID int NOT NULL -> Person.Address.AddressID Customer shipping address. Foreign key to Address.AddressID.
ShipMethodID int NOT NULL -> Purchasing.ShipMethod.ShipMethodID Shipping method. Foreign key to ShipMethod.ShipMethodID.
CreditCardID int NULL -> Sales.CreditCard.CreditCardID Credit card identification number. Foreign key to CreditCard.CreditCardID.
CreditCardApprovalCode varchar (15) NULL Approval code provided by the credit card company.
CurrencyRateID int NULL -> Sales.CurrencyRate.CurrencyRateID Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID.
SubTotal money (19, 4) NOT NULL DEFAULT ((0.00)) Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.
TaxAmt money (19, 4) NOT NULL DEFAULT ((0.00)) Tax amount.
Freight money (19, 4) NOT NULL DEFAULT ((0.00)) Shipping cost.
TotalDue money (19, 4) NOT NULL COMPUTED Total due from customer. Computed as Subtotal TaxAmt Freight.
Comment nvarchar (128) NULL Sales representative comments.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.SalesOrderHeaderSalesReason

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

Name Type Nullable Comment
SalesOrderID PK int NOT NULL -> Sales.SalesOrderHeader.SalesOrderID Primary key. Foreign key to SalesOrderHeader.SalesOrderID.
SalesReasonID PK int NOT NULL -> Sales.SalesReason.SalesReasonID Primary key. Foreign key to SalesReason.SalesReasonID.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.SalesPerson

Sales representative current information.

Name Type Nullable Comment
SalesPersonID PK int NOT NULL -> HumanResources.Employee.EmployeeID Primary key for SalesPerson records.
TerritoryID int NULL -> Sales.SalesTerritory.TerritoryID Territory currently assigned to. Foreign key to SalesTerritory.SalesTerritoryID.
SalesQuota money (19, 4) NULL Projected yearly sales.
Bonus money (19, 4) NOT NULL DEFAULT ((0.00)) Bonus due if quota is met.
CommissionPct smallmoney (10, 4) NOT NULL DEFAULT ((0.00)) Commision percent received per sale.
SalesYTD money (19, 4) NOT NULL DEFAULT ((0.00)) Sales total year to date.
SalesLastYear money (19, 4) NOT NULL DEFAULT ((0.00)) Sales total of previous year.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.SalesPersonQuotaHistory

Sales performance tracking.

Name Type Nullable Comment
SalesPersonID PK int NOT NULL -> Sales.SalesPerson.SalesPersonID Sales person identification number. Foreign key to SalesPerson.SalesPersonID.
QuotaDate PK datetime NOT NULL Sales quota date.
SalesQuota money (19, 4) NOT NULL Sales quota amount.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.SalesReason

Lookup table of customer purchase reasons.

Name Type Nullable Comment
SalesReasonID PK int NOT NULL IDENTITY Primary key for SalesReason records.
Name nvarchar (50) NOT NULL Sales reason description.
ReasonType nvarchar (50) NOT NULL Category the sales reason belongs to.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.SalesTaxRate

Tax rate lookup table.

Name Type Nullable Comment
SalesTaxRateID PK int NOT NULL IDENTITY Primary key for SalesTaxRate records.
StateProvinceID int NOT NULL -> Person.StateProvince.StateProvinceID 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 (10, 4) NOT NULL DEFAULT ((0.00)) Tax rate amount.
Name nvarchar (50) NOT NULL Tax rate description.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.SalesTerritory

Sales territory lookup table.

Name Type Nullable Comment
TerritoryID PK int NOT NULL IDENTITY Primary key for SalesTerritory records.
Name nvarchar (50) 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 (19, 4) NOT NULL DEFAULT ((0.00)) Sales in the territory year to date.
SalesLastYear money (19, 4) NOT NULL DEFAULT ((0.00)) Sales in the territory the previous year.
CostYTD money (19, 4) NOT NULL DEFAULT ((0.00)) Business costs in the territory year to date.
CostLastYear money (19, 4) NOT NULL DEFAULT ((0.00)) Business costs in the territory the previous year.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.SalesTerritoryHistory

Sales representative transfers to other sales territories.

Name Type Nullable Comment
SalesPersonID PK int NOT NULL -> Sales.SalesPerson.SalesPersonID Primary key for SalesTerritoryHistory records.
TerritoryID PK int NOT NULL -> Sales.SalesTerritory.TerritoryID Territory identification number. Foreign key to SalesTerritory.SalesTerritoryID.
StartDate PK 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 DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.ShoppingCartItem

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

Name Type Nullable Comment
ShoppingCartItemID PK int NOT NULL IDENTITY Primary key for ShoppingCartItem records.
ShoppingCartID nvarchar (50) NOT NULL Shopping cart identification number.
Quantity int NOT NULL DEFAULT ((1)) Product quantity ordered.
ProductID int NOT NULL -> Production.Product.ProductID Product ordered. Foreign key to Product.ProductID.
DateCreated datetime NOT NULL DEFAULT (getdate()) Date the time the record was created.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.SpecialOffer

Sale discounts lookup table.

Name Type Nullable Comment
SpecialOfferID PK int NOT NULL IDENTITY Primary key for SpecialOffer records.
Description nvarchar (255) NOT NULL Discount description.
DiscountPct smallmoney (10, 4) NOT NULL DEFAULT ((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 DEFAULT ((0)) Minimum discount percent allowed.
MaxQty int NULL Maximum discount percent allowed.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.SpecialOfferProduct

Cross-reference table mapping products to special offer discounts.

Name Type Nullable Comment
SpecialOfferID PK int NOT NULL -> Sales.SpecialOffer.SpecialOfferID Primary key for SpecialOfferProduct records.
ProductID PK int NOT NULL -> Production.Product.ProductID Product identification number. Foreign key to Product.ProductID.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.Store

Customers (resellers) of Adventure Works products.

Name Type Nullable Comment
CustomerID PK int NOT NULL -> Sales.Customer.CustomerID Primary key. Foreign key to Customer.CustomerID.
Name nvarchar (50) NOT NULL Name of the store.
SalesPersonID int NULL -> Sales.SalesPerson.SalesPersonID ID of the sales person assigned to the customer. Foreign key to SalesPerson.SalesPersonID.
Demographics xml (max) NULL Demographic informationg about the store such as the number of employees, annual sales and store type.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


Table Sales.StoreContact

Cross-reference table mapping stores and their employees.

Name Type Nullable Comment
CustomerID PK int NOT NULL -> Sales.Store.CustomerID Store identification number. Foreign key to Customer.CustomerID.
ContactID PK int NOT NULL -> Person.Contact.ContactID Contact (store employee) identification number. Foreign key to Contact.ContactID.
ContactTypeID int NOT NULL -> Person.ContactType.ContactTypeID Contact type such as owner or purchasing agent. Foreign key to ContactType.ContactTypeID.
rowguid uniqueidentifier NOT NULL DEFAULT (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime NOT NULL DEFAULT (getdate()) Date and time the record was last updated.


View Sales.vIndividualCustomer

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

Name Type Nullable Comment
CustomerID int NOT NULL
Title nvarchar (8) NULL
FirstName nvarchar (50) NOT NULL
MiddleName nvarchar (50) NULL
LastName nvarchar (50) NOT NULL
Suffix nvarchar (10) NULL
Phone nvarchar (25) NULL
EmailAddress nvarchar (50) NULL
EmailPromotion int NOT NULL
AddressType nvarchar (50) NOT NULL
AddressLine1 nvarchar (60) NOT NULL
AddressLine2 nvarchar (60) NULL
City nvarchar (30) NOT NULL
StateProvinceName nvarchar (50) NOT NULL
PostalCode nvarchar (15) NOT NULL
CountryRegionName nvarchar (50) NOT NULL
Demographics xml (max) NULL


View Sales.vIndividualDemographics

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

Name Type Nullable Comment
CustomerID int NOT NULL
TotalPurchaseYTD money (19, 4) NULL
DateFirstPurchase datetime NULL
BirthDate datetime NULL
MaritalStatus nvarchar (1) NULL
YearlyIncome nvarchar (30) NULL
Gender nvarchar (1) NULL
TotalChildren int NULL
NumberChildrenAtHome int NULL
Education nvarchar (30) NULL
Occupation nvarchar (30) NULL
HomeOwnerFlag bit NULL
NumberCarsOwned int NULL


View Sales.vSalesPerson

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

Name Type Nullable Comment
SalesPersonID int NOT NULL
Title nvarchar (8) NULL
FirstName nvarchar (50) NOT NULL
MiddleName nvarchar (50) NULL
LastName nvarchar (50) NOT NULL
Suffix nvarchar (10) NULL
JobTitle nvarchar (50) NOT NULL
Phone nvarchar (25) NULL
EmailAddress nvarchar (50) NULL
EmailPromotion int NOT NULL
AddressLine1 nvarchar (60) NOT NULL
AddressLine2 nvarchar (60) NULL
City nvarchar (30) NOT NULL
StateProvinceName nvarchar (50) NOT NULL
PostalCode nvarchar (15) NOT NULL
CountryRegionName nvarchar (50) NOT NULL
TerritoryName nvarchar (50) NULL
TerritoryGroup nvarchar (50) NULL
SalesQuota money (19, 4) NULL
SalesYTD money (19, 4) NOT NULL
SalesLastYear money (19, 4) NOT NULL


View Sales.vSalesPersonSalesByFiscalYears

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

Name Type Nullable Comment
SalesPersonID int NULL
FullName nvarchar (152) NULL
Title nvarchar (50) NOT NULL
SalesTerritory nvarchar (50) NOT NULL
2002 money (19, 4) NULL
2003 money (19, 4) NULL
2004 money (19, 4) NULL


View Sales.vStoreWithDemographics

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

Name Type Nullable Comment
CustomerID int NOT NULL
Name nvarchar (50) NOT NULL
ContactType nvarchar (50) NOT NULL
Title nvarchar (8) NULL
FirstName nvarchar (50) NOT NULL
MiddleName nvarchar (50) NULL
LastName nvarchar (50) NOT NULL
Suffix nvarchar (10) NULL
Phone nvarchar (25) NULL
EmailAddress nvarchar (50) NULL
EmailPromotion int NOT NULL
AddressType nvarchar (50) NOT NULL
AddressLine1 nvarchar (60) NOT NULL
AddressLine2 nvarchar (60) NULL
City nvarchar (30) NOT NULL
StateProvinceName nvarchar (50) NOT NULL
PostalCode nvarchar (15) NOT NULL
CountryRegionName nvarchar (50) NOT NULL
AnnualSales money (19, 4) NULL
AnnualRevenue money (19, 4) NULL
BankName nvarchar (50) NULL
BusinessType nvarchar (5) NULL
YearOpened int NULL
Specialty nvarchar (50) NULL
SquareFeet int NULL
Brands nvarchar (30) NULL
Internet nvarchar (30) NULL
NumberEmployees int NULL


Last edited Jan 26, 2011 at 9:33 PM by altair, version 1

Comments

No comments yet.