Adventure Works
Database: Sql Server Last Updated: 11/15/2015   Created: 7/3/2015

Database Dictionary
Filter by Objects:  Filter By Group:     

CORE

AWBuildVersion
Current version number of the AdventureWorks 2012 sample database.
NameData TypeLengthNullDescription
SystemInformationIDTinyIntNPrimary key for AWBuildVersion records.
Database VersionNvarchar25NVersion number of the database in 9.yy.mm.dd.00 format.
VersionDateDateTimeNDate and time the record was last updated.
ModifiedDateDateTimeNDate and time the record was last updated.

DatabaseLog
Audit table tracking all DDL changes made to the AdventureWorks database. Data is captured by the database trigger ddlDatabaseTriggerLog.
NameData TypeLengthNullDescription
DatabaseLogIDIntNPrimary key for DatabaseLog records.
PostTimeDateTimeNThe date and time the DDL change occurred.
DatabaseUserNvarchar128NThe user who implemented the DDL change.
EventNvarchar128NThe type of DDL statement that was executed.
SchemaNvarchar128YThe schema to which the changed object belongs.
ObjectNvarchar128YThe object that was changed by the DDL statment.
TSQLNvarcharMAXNThe exact Transact-SQL statement that was executed.
XmlEventXMLNThe raw XML data generated by database trigger.

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.
NameData TypeLengthNullDescription
ErrorLogIDIntNPrimary key for ErrorLog records.
ErrorTimeDateTimeNThe date and time at which the error occurred.
UserNameNvarchar128NThe user who executed the batch in which the error occurred.
ErrorNumberIntNThe error number of the error that occurred.
ErrorSeverityIntYThe severity of the error that occurred.
ErrorStateIntYThe state number of the error that occurred.
ErrorProcedureNvarchar126YThe name of the stored procedure or trigger where the error occurred.
ErrorLineIntYThe line number at which the error occurred.
ErrorMessageNvarchar4000NThe message text of the error that occurred.

ufnGetAccountingEndDate
Scalar function used in the uSalesOrderHeader trigger to set the starting account date.
NameData TypeDirectionDescription
[ NO FUNCTION PARAMATERS ADDED ]

ufnGetAccountingStartDate
Scalar function used in the uSalesOrderHeader trigger to set the ending account date.
NameData TypeDirectionDescription
[ NO FUNCTION PARAMATERS ADDED ]

ufnGetDocumentStatusText
Scalar function returning the text representation of the Status column in the Document table.
NameData TypeDirectionDescription
@StatusTinyIntINInput parameter for the scalar function ufnGetDocumentStatusText. Enter a valid integer.

ufnLeadingZeros
Scalar function used by the Sales.Customer table to help set the account number.
NameData TypeDirectionDescription
@ValueIntINInput parameter for the scalar function ufnLeadingZeros. Enter a valid integer.

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. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.
NameData TypeDirectionDescription
@ErrorLogIDIntINOUTOutput parameter for the stored procedure uspLogError. Contains the ErrorLogID value corresponding to the row inserted by uspLogError in the ErrorLog table.

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.
NameData TypeDirectionDescription
[ NO PROCEDURE PARAMATERS ADDED ]
CORE
HUMAN RESOURCES

Department
Lookup table containing the departments within the Adventure Works Cycles company.
NameData TypeLengthNullDescription
DepartmentIDSmall IntNPrimary key for Department records.
NameNvarchar50NName of the department.
GroupNameNvarchar50NName of the group to which the department belongs.
ModifiedDateDateTimeNDate and time the record was last updated.

Employee
Employee information such as salary, department, and title.
NameData TypeLengthNullDescription
BusinessEntityIDIntNPrimary key for Employee records. Foreign key to BusinessEntity.BusinessEntityID.
NationalIDNumberNvarchar15NUnique national identification number such as a social security number.
LoginIDNvarchar256NNetwork login.
OrganizationNodeSmall IntYWhere the employee is located in corporate hierarchy.
OrganizationLevelSmall IntYThe depth of the employee in the corporate hierarchy.
JobTitleNvarchar50NWork title such as Buyer or Sales Representative.
BirthDateDateNDate of birth.
MaritalStatusNchar1NM = Married, S = Single
GenderNchar1NM = Male, F = Female
HireDateDateNEmployee hired on this date.
SalariedFlagBitNJob classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
VacationHoursSmall IntNNumber of available vacation hours.
SickLeaveHoursSmall IntNNumber of available sick leave hours.
CurrentFlagBitN0 = Inactive, 1 = Active
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

EmployeeDepartmentHistory
Employee department transfers.
NameData TypeLengthNullDescription
BusinessEntityIDIntNEmployee identification number. Foreign key to Employee.BusinessEntityID.
DepartmentIDSmall IntNDepartment in which the employee worked including currently. Foreign key to Department.DepartmentID.
ShiftIDTinyIntNIdentifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.
StartDateDateNDate the employee started work in the department.
EndDateDateYDate the employee left the department. NULL = Current department.
ModifiedDateDateTimeNDate and time the record was last updated.

EmployeePayHistory
Employee pay history.
NameData TypeLengthNullDescription
BusinessEntityIDIntNEmployee identification number. Foreign key to Employee.BusinessEntityID.
RateChangeDateDateTimeNDate the change in pay is effective
RateMoneyNSalary hourly rate.
PayFrequencyTinyIntN1 = Salary received monthly, 2 = Salary received biweekly
ModifiedDateDateTimeNDate and time the record was last updated.

JobCandidate
Résumés submitted to Human Resources by job applicants.
NameData TypeLengthNullDescription
JobCandidateIDIntNPrimary key for JobCandidate records.
BusinessEntityIDIntYEmployee identification number if applicant was hired. Foreign key to Employee.BusinessEntityID.
ResumeXMLYRésumé in XML format.
ModifiedDateDateTimeNDate and time the record was last updated.

Shift
Work shift lookup table.
NameData TypeLengthNullDescription
ShiftIDTinyIntNPrimary key for Shift records.
NameNvarchar50NShift description.
StartTimeTimeNShift start time.
EndTimeTimeNShift end time.
ModifiedDateDateTimeNDate and time the record was last updated.

vEmployee
Employee names and addresses.
NameDescription
BusinessEntityID
Title
FirstName
MiddleName
LastName
Suffix
JobTitle
PhoneNumber
PhoneNumberType
EmailAddress
EmailPromotion
AddressLine1
AddressLine2
City
StateProvinceName
PostalCode
CountryRegionName
AdditionalContactInfo

vEmployeeDepartment
Returns employee name, title, and current department.
NameDescription
BusinessEntityID
Title
FirstName
MiddleName
LastName
Suffix
JobTitle
Department
GroupName
StartDate

vEmployeeDepartmentHistory
Returns employee name and current and previous departments.
NameDescription
BusinessEntityID
Title
FirstName
MiddleName
LastName
Suffix
Shift
Department
GroupName
StartDate
EndDate

vJobCandidate
Job candidate names and resumes.
NameDescription
JobCandidateID
BusinessEntityID
Name.Prefix
Name.First
Name.Middle
Name.Last
Name.Suffix
Skills
Addr.Type
Addr.Loc.CountryRegion
Addr.Loc.State
Addr.Loc.City
Addr.PostalCode
EMail
WebSite
ModifiedDate

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.
NameDescription
JobCandidateID
Edu.Level
Edu.StartDate
Edu.EndDate
Edu.Degree
Edu.Major
Edu.Minor
Edu.GPA
Edu.GPAScale
Edu.School
Edu.Loc.CountryRegion
Edu.Loc.State
Edu.Loc.City

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.
NameDescription
JobCandidateID
Emp.StartDate
Emp.EndDate
Emp.OrgName
Emp.JobTitle
Emp.Responsibility
Emp.FunctionCategory
Emp.IndustryCategory
Emp.Loc.CountryRegion
Emp.Loc.State
Emp.Loc.City

uspGetEmployeeManagers
Stored procedure using a recursive query to return the direct and indirect managers of the specified employee.
NameData TypeDirectionDescription
@BusinessEntityIDIntINInput parameter for the stored procedure uspGetEmployeeManagers. Enter a valid BusinessEntityID from the HumanResources.Employee table.

uspGetManagerEmployees
Stored procedure using a recursive query to return the direct and indirect employees of the specified manager.
NameData TypeDirectionDescription
@BusinessEntityIDIntINInput parameter for the stored procedure uspGetManagerEmployees. Enter a valid BusinessEntityID of the manager from the HumanResources.Employee table.

uspSearchCandidateResumes
NameData TypeDirectionDescription
@languageIntIN
@searchStringNvarcharIN
@useInflectionalBitIN
@useThesaurusBitIN

uspUpdateEmployeeHireInfo
NameData TypeDirectionDescription
@BusinessEntityIDIntIN
@CurrentFlagBitIN
@HireDateDateTimeIN
@JobTitleNvarcharIN
@PayFrequencyTinyIntIN
@RateMoneyIN
@RateChangeDateDateTimeIN

uspUpdateEmployeeLogin
NameData TypeDirectionDescription
@BusinessEntityIDIntIN
@CurrentFlagBitIN
@HireDateDateTimeIN
@JobTitleNvarcharIN
@LoginIDNvarcharIN
@OrganizationNodeIN

uspUpdateEmployeePersonalInfo
NameData TypeDirectionDescription
@BirthDateDateTimeIN
@BusinessEntityIDIntIN
@GenderNcharIN
@MaritalStatusNcharIN
@NationalIDNumberNvarcharIN
HUMAN RESOURCES
PERSON

Address
Street address information for customers, employees, and vendors.
NameData TypeLengthNullDescription
AddressIDIntNPrimary key for Address records.
AddressLine1Nvarchar60NFirst street address line.
AddressLine2Nvarchar60YSecond street address line.
CityNvarchar30NName of the city.
StateProvinceIDIntNUnique identification number for the state or province. Foreign key to StateProvince table.
PostalCodeNvarchar15NPostal code for the street address.
SpatialLocationGeographyYLatitude and longitude of this address.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

AddressType
Types of addresses stored in the Address table.
NameData TypeLengthNullDescription
AddressTypeIDIntNPrimary key for AddressType records.
NameNvarchar50NAddress type description. For example, Billing, Home, or Shipping.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

BusinessEntity
Source of the ID that connects vendors, customers, and employees with address and contact information.
NameData TypeLengthNullDescription
BusinessEntityIDIntNPrimary key for all customers, vendors, and employees.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

BusinessEntityAddress
Cross-reference table mapping customers, vendors, and employees to their addresses.
NameData TypeLengthNullDescription
BusinessEntityIDIntNPrimary key. Foreign key to BusinessEntity.BusinessEntityID.
AddressIDIntNPrimary key. Foreign key to Address.AddressID.
AddressTypeIDIntNPrimary key. Foreign key to AddressType.AddressTypeID
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

BusinessEntityContact
Cross-reference table mapping stores, vendors, and employees to people
NameData TypeLengthNullDescription
BusinessEntityIDIntNPrimary key. Foreign key to BusinessEntity.BusinessEntityID.
PersonIDIntNPrimary key. Foreign key to Person.BusinessEntityID.
ContactTypeIDIntNPrimary key. Foreign key to ContactType.ContactTypeID.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

ContactType
Lookup table containing the types of business entity contacts.
NameData TypeLengthNullDescription
ContactTypeIDIntNPrimary key for ContactType records.
NameNvarchar50NContact type description.
ModifiedDateDateTimeNDate and time the record was last updated.

CountryRegion
Lookup table containing the ISO standard codes for countries and regions.
NameData TypeLengthNullDescription
CountryRegionCodeNvarchar3NISO standard code for countries and regions.
NameNvarchar50NCountry or region name.
ModifiedDateDateTimeNDate and time the record was last updated.

EmailAddress
Where to send a person email.
NameData TypeLengthNullDescription
BusinessEntityIDIntNPrimary key. Person associated with this email address. Foreign key to Person.BusinessEntityID
EmailAddressIDIntNPrimary key. ID of this email address.
EmailAddressNvarchar50YE-mail address for the person.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

Password
One way hashed authentication information
NameData TypeLengthNullDescription
BusinessEntityIDIntNPasswordHash
PasswordHashVarchar128NPassword for the e-mail account.
PasswordSaltVarchar10NRandom value concatenated with the password string before the password is hashed.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

Person
Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.
NameData TypeLengthNullDescription
BusinessEntityIDIntNPrimary key for Person records.
PersonTypeNchar2NPrimary type of person: SC = Store Contact, IN = Individual (retail) customer, SP = Sales person, EM = Employee (nonsales),
VC = Vendor contact, GC = General contact
NameStyleBitN0 = The data in FirstName and LastName are stored in western style (first name, last name) order. 1 = Eastern style (last name, first name) order
TitleNvarchar8YA courtesy title. For example, Mr. or Ms.
FirstNameNvarchar50NFirst name of the person.
MiddleNameNvarchar50YMiddle name or middle initial of the person.
LastNameNvarchar50NLast name of the person.
SuffixNvarchar10YSurname suffix. For example, Sr. or Jr.
EmailPromotionIntN0 = 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.
AdditionalContactInfoXMLYAdditional contact information about the person stored in xml format.
DemographicsXMLYPersonal information such as hobbies, and income collected from online shoppers. Used for sales analysis.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

PersonPhone
Telephone number and type of a person.
NameData TypeLengthNullDescription
BusinessEntityIDIntNBusiness entity identification number. Foreign key to Person.BusinessEntityID.
PhoneNumberNvarchar25NTelephone number identification number.
PhoneNumberTypeIDIntNKind of phone number. Foreign key to PhoneNumberType.PhoneNumberTypeID.
ModifiedDateDateTimeNDate and time the record was last updated.

PhoneNumberType
Type of phone number of a person.
NameData TypeLengthNullDescription
PhoneNumberTypeIDIntNPrimary key for telephone number type records.
NameNvarchar50NName of the telephone number type
ModifiedDateDateTimeNDate and time the record was last updated.

StateProvince
State and province lookup table.
NameData TypeLengthNullDescription
StateProvinceIDIntNPrimary key for StateProvince records.
StateProvinceCodeNchar3NISO standard state or province code.
CountryRegionCodeNvarchar3NISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.
IsOnlyStateProvinceFlagBitN0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode.
NameNvarchar50NState or province description.
TerritoryIDIntNID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

vAdditionalContactInfo
Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.
NameDescription
BusinessEntityID
FirstName
MiddleName
LastName
TelephoneNumber
TelephoneSpecialInstructions
Street
City
StateProvince
PostalCode
CountryRegion
HomeAddressSpecialInstructions
EMailAddress
EMailSpecialInstructions
EMailTelephoneNumber
rowguid
ModifiedDate

vStateProvinceCountryRegion
Joins StateProvince table with CountryRegion table.
NameDescription
StateProvinceID
StateProvinceCode
IsOnlyStateProvinceFlag
StateProvinceName
TerritoryID
CountryRegionCode
CountryRegionName

ufnGetContactInformation
Table value function returning the first name, last name, job title and contact type for a given contact.
NameData TypeDirectionDescription
@PersonIDIntINInput parameter for the table value function ufnGetContactInformation. Enter a valid PersonID from the Person.Contact table.
PERSON
PRODUCTION

BillOfMaterials
Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical
relationship between a parent product and its components.
NameData TypeLengthNullDescription
BillOfMaterialsIDIntNPrimary key for BillOfMaterials records.
ProductAssemblyIDIntYParent product identification number. Foreign key to Product.ProductID.
ComponentIDIntNComponent identification number. Foreign key to Product.ProductID.
StartDateDateTimeNDate the component started being used in the assembly item.
EndDateDateTimeYDate the component stopped being used in the assembly item.
UnitMeasureCodeNchar3NStandard code identifying the unit of measure for the quantity.
BOMLevelSmall IntNIndicates the depth the component is from its parent (AssemblyID).
PerAssemblyQtyDecimalNQuantity of the component needed to create the assembly.
ModifiedDateDateTimeNDate and time the record was last updated.

Culture
Lookup table containing the languages in which some AdventureWorks data is stored.
NameData TypeLengthNullDescription
CultureIDNchar6NPrimary key for Culture records.
NameNvarchar50NCulture description.
ModifiedDateDateTimeNDate and time the record was last updated.

Document
Product maintenance documents.
NameData TypeLengthNullDescription
DocumentNodeHeirarchyidNPrimary key for Document records.
DocumentLevelSmall IntYDepth in the document hierarchy.
TitleNvarchar50NTitle of the document.
OwnerIntNEmployee who controls the document. Foreign key to Employee.BusinessEntityID
FolderFlagBitN0 = This is a folder, 1 = This is a document.
FileNameNvarchar400NFile name of the document
FileExtensionNvarchar8NFile extension indicating the document type. For example, .doc or .txt.
RevisionNchar5NRevision number of the document.
ChangeNumberIntNEngineering change approval number.
StatusTinyIntN1 = Pending approval, 2 = Approved, 3 = Obsolete
DocumentSummaryNvarcharMAXYDocument abstract.
DocumentVarbinaryYComplete document.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Required for FileStream.
ModifiedDateDateTimeNDate and time the record was last updated.

Illustration
Bicycle assembly diagrams.
NameData TypeLengthNullDescription
IllustrationIDIntNPrimary key for Illustration records.
DiagramXMLYIllustrations used in manufacturing instructions. Stored as XML.
ModifiedDateDateTimeNDate and time the record was last updated.

Location
Product inventory and manufacturing locations.
NameData TypeLengthNullDescription
LocationIDSmall IntNPrimary key for Location records.
NameNvarchar50NLocation description.
CostRateSmall MoneyNStandard hourly cost of the manufacturing location.
AvailabilityDecimalNWork capacity (in hours) of the manufacturing location.
ModifiedDateDateTimeNDate and time the record was last updated.

Product
Products sold or used in the manfacturing of sold products.
NameData TypeLengthNullDescription
ProductIDIntNPrimary key for Product records.
NameNvarchar50NName of the product.
ProductNumberNvarchar25NUnique product identification number.
MakeFlagBitN0 = Product is purchased, 1 = Product is manufactured in-house.
FinishedGoodsFlagBitN0 = Product is not a salable item. 1 = Product is salable.
ColorNvarchar15YProduct color.
SafetyStockLevelSmall IntNMinimum inventory quantity.
ReorderPointSmall IntNInventory level that triggers a purchase order or work order.
StandardCostMoneyNStandard cost of the product.
ListPriceMoneyNSelling price.
SizeNvarchar5YProduct size.
SizeUnitMeasureCodeNchar3YUnit of measure for Size column.
WeightUnitMeasureCodeNchar3YUnit of measure for Weight column.
WeightDecimalYProduct weight.
DaysToManufactureIntNNumber of days required to manufacture the product.
ProductLineNchar2YR = Road, M = Mountain, T = Touring, S = Standard
ClassNchar2YH = High, M = Medium, L = Low
StyleNchar2YW = Womens, M = Mens, U = Universal
ProductSubcategoryIDIntYProduct is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID.
ProductModelIDIntYProduct is a member of this product model. Foreign key to ProductModel.ProductModelID.
SellStartDateDateTimeNDate the product was available for sale.
SellEndDateDateTimeYDate the product was no longer available for sale.
DiscontinuedDateDateTimeYDate the product was discontinued.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

ProductCategory
High-level product categorization.
NameData TypeLengthNullDescription
ProductCategoryIDIntNPrimary key for ProductCategory records.
NameNvarchar50NCategory description.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

ProductCostHistory
Changes in the cost of a product over time.
NameData TypeLengthNullDescription
ProductIDIntNProduct identification number. Foreign key to Product.ProductID
StartDateDateTimeNProduct cost start date.
EndDateDateTimeYProduct cost end date.
StandardCostMoneyNStandard cost of the product.
ModifiedDateDateTimeNDate and time the record was last updated.

ProductDescription
Product descriptions in several languages.
NameData TypeLengthNullDescription
ProductDescriptionIDIntNPrimary key for ProductDescription records.
DescriptionNvarchar400NDescription of the product.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

ProductDocument
Cross-reference table mapping products to related product documents.
NameData TypeLengthNullDescription
ProductIDIntNProduct identification number. Foreign key to Product.ProductID.
DocumentNodeHeirarchyidNDocument identification number. Foreign key to Document.DocumentNode.
ModifiedDateDateTimeNDate and time the record was last updated.

ProductInventory
Product inventory information
NameData TypeLengthNullDescription
ProductIDIntNProduct identification number. Foreign key to Product.ProductID
LocationIDSmall IntNInventory location identification number. Foreign key to Location.LocationID.
ShelfNvarchar10NStorage compartment within an inventory location.
BinTinyIntNStorage container on a shelf in an inventory location
QuantitySmall IntNQuantity of products in the inventory location.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

ProductListPriceHistory
Changes in the list price of a product over time.
NameData TypeLengthNullDescription
ProductIDIntNProduct identification number. Foreign key to Product.ProductID
StartDateDateTimeNList price start date.
EndDateDateTimeYList price end date
ListPriceMoneyNProduct list price.
ModifiedDateDateTimeNDate and time the record was last updated.

ProductModel
Product model classification.
NameData TypeLengthNullDescription
ProductModelIDIntNPrimary key for ProductModel records.
NameNvarchar50NProduct model description.
CatalogDescriptionXMLYDetailed product catalog information in xml format.
InstructionsXMLYManufacturing instructions in xml format.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

ProductModelIllustration
Cross-reference table mapping product models and illustrations.
NameData TypeLengthNullDescription
ProductModelIDIntNPrimary key. Foreign key to ProductModel.ProductModelID.
IllustrationIDIntNPrimary key. Foreign key to Illustration.IllustrationID.
ModifiedDateDateTimeNDate and time the record was last updated.

ProductModelProductDescriptionCulture
Cross-reference table mapping product descriptions and the language the description is written in.
NameData TypeLengthNullDescription
ProductModelIDIntNPrimary key. Foreign key to ProductModel.ProductModelID.
ProductDescriptionIDIntNPrimary key. Foreign key to ProductDescription.ProductDescriptionID.
CultureIDNchar6NCulture identification number. Foreign key to Culture.CultureID.
ModifiedDateDateTimeNDate and time the record was last updated.

ProductPhoto
Product images.
NameData TypeLengthNullDescription
ProductPhotoIDIntNPrimary key for ProductPhoto records.
ThumbNailPhotoVarbinaryYSmall image of the product.
ThumbnailPhotoFileNameNvarchar50YSmall image file name.
LargePhotoVarbinaryYLarge image of the product.
LargePhotoFileNameNvarchar50YLarge image file name.
ModifiedDateDateTimeNDate and time the record was last updated.

ProductProductPhoto
Cross-reference table mapping products and product photos.
NameData TypeLengthNullDescription
ProductIDIntNProduct identification number. Foreign key to Product.ProductID.
ProductPhotoIDIntNProduct photo identification number. Foreign key to ProductPhoto.ProductPhotoID.
PrimaryBitN0 = Photo is not the principal image. 1 = Photo is the principal image.
ModifiedDateDateTimeNDate and time the record was last updated

ProductReview
Customer reviews of products they have purchased.
NameData TypeLengthNullDescription
ProductReviewIDIntNPrimary key for ProductReview records.
ProductIDIntNProduct identification number. Foreign key to Product.ProductID
ReviewerNameNvarchar50NName of the reviewer.
ReviewDateDateTimeNDate review was submitted.
EmailAddressNvarchar50NReviewer's e-mail address.
RatingIntNProduct rating given by the reviewer. Scale is 1 to 5 with 5 as the highest rating.
CommentsNvarchar3850YReviewer's comments
ModifiedDateDateTimeNDate and time the record was last updated.

ProductSubcategory
Product subcategories. See ProductCategory table.
NameData TypeLengthNullDescription
ProductSubcategoryIDIntNPrimary key for ProductSubcategory records.
ProductCategoryIDIntNProduct category identification number. Foreign key to ProductCategory.ProductCategoryID.
NameNvarchar50NSubcategory description.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

ScrapReason
Manufacturing failure reasons lookup table.
NameData TypeLengthNullDescription
ScrapReasonIDSmall IntNPrimary key for ScrapReason records.
NameNvarchar50NFailure description.
ModifiedDateDateTimeNDate and time the record was last updated.

TransactionHistory
Record of each purchase order, sales order, or work order transaction year to date.
NameData TypeLengthNullDescription
TransactionIDIntNPrimary key for TransactionHistory records.
ProductIDIntNProduct identification number. Foreign key to Product.ProductID.
ReferenceOrderIDIntNPurchase order, sales order, or work order identification number.
ReferenceOrderLineIDIntNLine number associated with the purchase order, sales order, or work order.
TransactionDateDateTimeNDate and time of the transaction.
TransactionTypeNchar1NW = WorkOrder, S = SalesOrder, P = PurchaseOrder
QuantityIntNProduct quantity.
ActualCostMoneyNProduct cost.
ModifiedDateDateTimeNDate and time the record was last updated.

TransactionHistoryArchive
Transactions for previous years.
NameData TypeLengthNullDescription
TransactionIDIntNPrimary key for TransactionHistoryArchive records.
ProductIDIntNProduct identification number. Foreign key to Product.ProductID.
ReferenceOrderIDIntNPurchase order, sales order, or work order identification number.
ReferenceOrderLineIDIntNLine number associated with the purchase order, sales order, or work order.
TransactionDateDateTimeNDate and time of the transaction
TransactionTypeNchar1NW = Work Order, S = Sales Order, P = Purchase Order
QuantityIntNProduct quantity.
ActualCostMoneyNProduct cost.
ModifiedDateDateTimeNDate and time the record was last updated.

UnitMeasure
Unit of measure lookup table.
NameData TypeLengthNullDescription
UnitMeasureCodeNchar3NPrimary key.
NameNvarchar50NUnit of measure description
ModifiedDateDateTimeNDate and time the record was last updated.

WorkOrder
Manufacturing work orders.
NameData TypeLengthNullDescription
WorkOrderIDIntNPrimary key for WorkOrder records.
ProductIDIntNProduct identification number. Foreign key to Product.ProductID.
OrderQtyIntNProduct quantity to build.
StockedQtyIntNQuantity built and put in inventory.
ScrappedQtySmall IntNQuantity that failed inspection.
StartDateDateTimeNWork order start date.
EndDateDateTimeYWork order end date.
DueDateDateTimeNWork order due date.
ScrapReasonIDSmall IntYReason for inspection failure.
ModifiedDateDateTimeNDate and time the record was last updated.

WorkOrderRouting
Work order details.
NameData TypeLengthNullDescription
WorkOrderIDIntNPrimary key. Foreign key to WorkOrder.WorkOrderID
ProductIDIntNPrimary key. Foreign key to Product.ProductID
OperationSequenceSmall IntNPrimary key. Indicates the manufacturing process sequence
LocationIDSmall IntNManufacturing location where the part is processed. Foreign key to Location.LocationID.
ScheduledStartDateDateTimeNPlanned manufacturing start date.
ScheduledEndDateDateTimeNPlanned manufacturing end date.
ActualStartDateDateTimeYActual start date.
ActualEndDateDateTimeYActual end date
ActualResourceHrsDecimalYNumber of manufacturing hours used.
PlannedCostMoneyNEstimated manufacturing cost.
ActualCostMoneyYActual manufacturing cost.
ModifiedDateDateTimeNDate and time the record was last updated.

vProductAndDescription
Product names and descriptions. Product descriptions are provided in multiple languages.
NameDescription
ProductID
Name
ProductModel
CultureID
Description

vProductModelCatalogDescription
Displays the content from each element in the xml column CatalogDescription for each product in the
Production.ProductModel table that has catalog data.
NameDescription
ProductModelID
Name
Summary
Manufacturer
Copyright
ProductURL
WarrantyPeriod
WarrantyDescription
NoOfYears
MaintenanceDescription
Wheel
Saddle
Pedal
BikeFrame
Crankset
PictureAngle
PictureSize
ProductPhotoID
Material
Color
ProductLine
Style
RiderExperience
rowguid
ModifiedDate

vProductModelInstructions
Displays the content from each element in the xml column Instructions for each product in the Production.ProductModel
table that has manufacturing instructions.
NameDescription
ProductModelID
Name
Instructions
LocationID
SetupHours
MachineHours
LaborHours
LotSize
Step
rowguid
ModifiedDate

ufnGetProductDealerPrice
Scalar function returning the dealer price for a given product on a particular order date.
NameData TypeDirectionDescription
@OrderDateDateTimeINInput parameter for the scalar function ufnGetProductDealerPrice. Enter a valid order date.
@ProductIDIntINInput parameter for the scalar function ufnGetProductDealerPrice. Enter a valid ProductID from the Production.Product table.

ufnGetProductListPrice
Scalar function returning the list price for a given product on a particular order date.
NameData TypeDirectionDescription
@OrderDateDateTimeINInput parameter for the scalar function ufnGetProductListPrice. Enter a valid order date.
@ProductIDIntINInput parameter for the scalar function ufnGetProductListPrice. Enter a valid ProductID from the Production.Product table.

ufnGetProductStandardCost
Scalar function returning the standard cost for a given product on a particular order date.
NameData TypeDirectionDescription
@OrderDateDateTimeINInput parameter for the scalar function ufnGetProductStandardCost. Enter a valid order date.
@ProductIDIntINInput parameter for the scalar function ufnGetProductStandardCost. Enter a valid ProductID from the Production.Product table.

uspGetBillOfMaterials
Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.
NameData TypeDirectionDescription
@CheckDateDateTimeINInput parameter for the stored procedure uspGetBillOfMaterials used to eliminate components not used after that date. Enter a valid date.
@StartProductIDIntINInput parameter for the stored procedure uspGetBillOfMaterials. Enter a valid ProductID from the Production.Product table.

uspGetWhereUsedProductID
Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.
NameData TypeDirectionDescription
@CheckDateDateTimeINInput parameter for the stored procedure uspGetWhereUsedProductID used to eliminate components not used after that date. Enter a valid date.
@StartProductIDIntINInput parameter for the stored procedure uspGetWhereUsedProductID. Enter a valid ProductID from the Production.Product table.
PRODUCTION
PURCHASING

ProductVendor
Cross-reference table mapping vendors with the products they supply.
NameData TypeLengthNullDescription
ProductIDIntNPrimary key. Foreign key to Product.ProductID.
BusinessEntityIDIntNPrimary key. Foreign key to Vendor.BusinessEntityID
AverageLeadTimeIntNThe average span of time (in days) between placing an order with the vendor and receiving the purchased product.
StandardPriceMoneyNThe vendor's usual selling price
LastReceiptCostMoneyYThe selling price when last purchased
LastReceiptDateDateTimeYDate the product was last received by the vendor.
MinOrderQtyIntNThe maximum quantity that should be ordered.
MaxOrderQtyIntNThe minimum quantity that should be ordered
OnOrderQtyIntYThe quantity currently on order.
UnitMeasureCodeNchar3NThe product's unit of measure.
ModifiedDateDateTimeNDate and time the record was last updated

PurchaseOrderDetail
Individual products associated with a specific purchase order. See PurchaseOrderHeader.
NameData TypeLengthNullDescription
PurchaseOrderIDIntNPrimary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
PurchaseOrderDetailIDIntNPrimary key. One line number per purchased product.
DueDateDateTimeNDate the product is expected to be received.
OrderQtySmall IntNQuantity ordered.
ProductIDIntNProduct identification number. Foreign key to Product.ProductID
UnitPriceMoneyNVendor's selling price of a single product.
LineTotalMoneyNPer product subtotal. Computed as OrderQty * UnitPrice.
ReceivedQtyDecimalNQuantity actually received from the vendor.
RejectedQtyDecimalNQuantity rejected during inspection.
StockedQtyDecimalNQuantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
ModifiedDateDateTimeNDate and time the record was last updated

PurchaseOrderHeader
General purchase order information. See PurchaseOrderDetail.
NameData TypeLengthNullDescription
PurchaseOrderIDIntNPrimary key.
RevisionNumberTinyIntNIncremental number to track changes to the purchase order over time.
StatusTinyIntNOrder current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete
EmployeeIDIntNEmployee who created the purchase order. Foreign key to Employee.BusinessEntityID.
VendorIDIntNVendor with whom the purchase order is placed. Foreign key to Vendor.BusinessEntityID
ShipMethodIDIntNShipping method. Foreign key to ShipMethod.ShipMethodID.
OrderDateDateTimeNPurchase order creation date.
ShipDateDateTimeYEstimated shipment date from the vendor.
SubTotalMoneyNPurchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.
TaxAmtMoneyNTax amount.
FreightMoneyNShipping cost.
TotalDueMoneyNTotal due to vendor. Computed as Subtotal + TaxAmt + Freight.
ModifiedDateDateTimeNDate and time the record was last updated.

ShipMethod
Shipping company lookup table.
NameData TypeLengthNullDescription
ShipMethodIDIntNPrimary key for ShipMethod records.
NameNvarchar50NShipping company name.
ShipBaseMoneyNMinimum shipping charge.
ShipRateMoneyNShipping charge per pound.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

Vendor
Companies from whom Adventure Works Cycles purchases parts or other goods.
NameData TypeLengthNullDescription
BusinessEntityIDIntNPrimary key for Vendor records. Foreign key to BusinessEntity.BusinessEntityID
AccountNumberNvarchar15NVendor account (identification) number.
NameNvarchar50NCompany name.
CreditRatingTinyIntN1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average
PreferredVendorStatusBitN0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.
ActiveFlagBitN0 = Vendor no longer used. 1 = Vendor is actively used.
PurchasingWebServiceURLNvarchar1024YVendor URL.
ModifiedDateDateTimeNDate and time the record was last updated.

vVendorWithAddresses
Vendor (company) names and addresses .
NameDescription
BusinessEntityID
Name
AddressType
AddressLine1
AddressLine2
City
StateProvinceName
PostalCode
CountryRegionName

vVendorWithContacts
Vendor (company) names and the names of vendor employees to contact.
NameDescription
BusinessEntityID
Name
ContactType
Title
FirstName
MiddleName
LastName
Suffix
PhoneNumber
PhoneNumberType
EmailAddress
EmailPromotion

ufnGetPurchaseOrderStatusText
Scalar function returning the text representation of the Status column in the PurchaseOrderHeader table.
NameData TypeDirectionDescription
@StatusTinyIntINInput parameter for the scalar function ufnGetPurchaseOrdertStatusText. Enter a valid integer.

ufnGetStock
Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID.
NameData TypeDirectionDescription
@ProductIDIntINInput parameter for the scalar function ufnGetStock. Enter a valid ProductID from the Production.ProductInventory table.
PURCHASING
SALES

CountryRegionCurrency
Cross-reference table mapping ISO currency codes to a country or region.
NameData TypeLengthNullDescription
CountryRegionCodeNvarchar3NISO code for countries and regions. Foreign key to CountryRegion.CountryRegionCode.
CurrencyCodeNchar3NISO standard currency code. Foreign key to Currency.CurrencyCode.
ModifiedDateDateTimeNDate and time the record was last updated.

CreditCard
Customer credit card information.
NameData TypeLengthNullDescription
CreditCardIDIntNPrimary key for CreditCard records.
CardTypeNvarchar50NCredit card name.
CardNumberNvarchar25NCredit card number.
ExpMonthTinyIntNCredit card expiration month.
ExpYearSmall IntNCredit card expiration year.
ModifiedDateDateTimeNDate and time the record was last updated.

Currency
Lookup table containing standard ISO currencies.
NameData TypeLengthNullDescription
CurrencyCodeNchar3NThe ISO code for the Currency.
NameNvarchar50NCurrency name.
ModifiedDateDateTimeNDate and time the record was last updated.

CurrencyRate
Currency exchange rates.
NameData TypeLengthNullDescription
CurrencyRateIDIntNPrimary key for CurrencyRate records.
CurrencyRateDateDateTimeNDate and time the exchange rate was obtained.
FromCurrencyCodeNchar3NExchange rate was converted from this currency code.
ToCurrencyCodeNchar3NExchange rate was converted to this currency code.
AverageRateMoneyNAverage exchange rate for the day.
EndOfDayRateMoneyNFinal exchange rate for the day.
ModifiedDateDateTimeNDate and time the record was last updated.

Customer
Current customer information. Also see the Person and Store tables.
NameData TypeLengthNullDescription
CustomerIDIntNPrimary key.
PersonIDIntYForeign key to Person.BusinessEntityID
StoreIDIntYForeign key to Store.BusinessEntityID
TerritoryIDIntYID of the territory in which the customer is located. Foreign key to SalesTerritory.SalesTerritoryID.
AccountNumberVarchar10NUnique number identifying the customer assigned by the accounting system.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

PersonCreditCard
Cross-reference table mapping people to their credit card information in the CreditCard table.
NameData TypeLengthNullDescription
BusinessEntityIDIntNBusiness entity identification number. Foreign key to Person.BusinessEntityID.
CreditCardIDIntNCredit card identification number. Foreign key to CreditCard.CreditCardID.
ModifiedDateDateTimeNDate and time the record was last updated.

SalesOrderDetail
Individual products associated with a specific sales order. See SalesOrderHeader.
NameData TypeLengthNullDescription
SalesOrderIDIntNPrimary key. Foreign key to SalesOrderHeader.SalesOrderID.
SalesOrderDetailIDIntNPrimary key. One incremental unique number per product sold.
CarrierTrackingNumberNvarchar25YShipment tracking number supplied by the shipper.
OrderQtySmall IntNQuantity ordered per product.
ProductIDIntNProduct sold to customer. Foreign key to Product.ProductID.
SpecialOfferIDIntNPromotional code. Foreign key to SpecialOffer.SpecialOfferID.
UnitPriceMoneyNSelling price of a single product.
UnitPriceDiscountMoneyNDiscount amount.
LineTotalNumericNPer product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

SalesOrderHeader
General sales order information.
NameData TypeLengthNullDescription
SalesOrderIDIntNPrimary key.
RevisionNumberTinyIntNIncremental number to track changes to the sales order over time.
OrderDateDateTimeNDates the sales order was created.
DueDateDateTimeNDate the order is due to the customer.
ShipDateDateTimeYDate the order was shipped to the customer.
StatusTinyIntNOrder current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled
OnlineOrderFlagBitN0 = Order placed by sales person. 1 = Order placed online by customer.
SalesOrderNumberNvarchar25NUnique sales order identification number.
PurchaseOrderNumberNvarchar25YCustomer purchase order number reference.
AccountNumberNvarchar15YFinancial accounting number reference.
CustomerIDIntNCustomer identification number. Foreign key to Customer.BusinessEntityID.
SalesPersonIDIntYSales person who created the sales order. Foreign key to SalesPerson.BusinessEntityID.
TerritoryIDIntYTerritory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID.
BillToAddressIDIntNCustomer billing address. Foreign key to Address.AddressID.
ShipToAddressIDIntNCustomer shipping address. Foreign key to Address.AddressID.
ShipMethodIDIntNShipping method. Foreign key to ShipMethod.ShipMethodID.
CreditCardIDIntYCredit card identification number. Foreign key to CreditCard.CreditCardID.
CreditCardApprovalCodeVarchar15YApproval code provided by the credit card company.
CurrencyRateIDIntYCurrency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID.
SubTotalMoneyNSales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.
TaxAmtMoneyNTax amount.
FreightMoneyNShipping cost.
TotalDueMoneyNTotal due from customer. Computed as Subtotal + TaxAmt + Freight.
CommentNvarchar128YSales representative comments.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

SalesOrderHeaderSalesReason
Cross-reference table mapping sales orders to sales reason codes.
NameData TypeLengthNullDescription
SalesOrderIDIntNPrimary key. Foreign key to SalesOrderHeader.SalesOrderID.
SalesReasonIDIntNPrimary key. Foreign key to SalesReason.SalesReasonID.
ModifiedDateDateTimeNDate and time the record was last updated.

SalesPerson
Sales representative current information.
NameData TypeLengthNullDescription
BusinessEntityIDIntNPrimary key for SalesPerson records. Foreign key to Employee.BusinessEntityID
TerritoryIDIntYTerritory currently assigned to. Foreign key to SalesTerritory.SalesTerritoryID.
SalesQuotaMoneyYProjected yearly sales.
BonusMoneyNBonus due if quota is met.
CommissionPctSmall MoneyNCommision percent received per sale.
SalesYTDMoneyNSales total year to date.
SalesLastYearMoneyNSales total of previous year.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

SalesPersonQuotaHistory
Sales performance tracking.
NameData TypeLengthNullDescription
BusinessEntityIDIntNSales person identification number. Foreign key to SalesPerson.BusinessEntityID.
QuotaDateDateTimeNSales quota date.
SalesQuotaMoneyNSales quota amount.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

SalesReason
Lookup table of customer purchase reasons.
NameData TypeLengthNullDescription
SalesReasonIDIntNPrimary key for SalesReason records.
NameNvarchar50NSales reason description.
ReasonTypeNvarchar50NCategory the sales reason belongs to.
ModifiedDateDateTimeNDate and time the record was last updated.

SalesTaxRate
Tax rate lookup table.
NameData TypeLengthNullDescription
SalesTaxRateIDIntNPrimary key for SalesTaxRate records
StateProvinceIDIntNState, province, or country/region the sales tax applies to.
TaxTypeTinyIntN1 = Tax applied to retail transactions, 2 = Tax applied to wholesale transactions, 3 = Tax applied to all sales (retail and
wholesale) transactions.
TaxRateSmall MoneyNTax rate amount.
NameNvarchar50NTax rate description.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

SalesTerritory
Sales territory lookup table.
NameData TypeLengthNullDescription
TerritoryIDIntNPrimary key for SalesTerritory records.
NameNvarchar50NSales territory description
CountryRegionCodeNvarchar3NISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.
GroupNvarchar50NGeographic area to which the sales territory belong.
SalesYTDMoneyNSales in the territory year to date.
SalesLastYearMoneyNSales in the territory the previous year.
CostYTDMoneyNBusiness costs in the territory year to date.
CostLastYearMoneyNBusiness costs in the territory the previous year.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

SalesTerritoryHistory
Sales representative transfers to other sales territories.
NameData TypeLengthNullDescription
BusinessEntityIDIntNPrimary key. The sales rep. Foreign key to SalesPerson.BusinessEntityID.
TerritoryIDIntNPrimary key. Territory identification number. Foreign key to SalesTerritory.SalesTerritoryID.
StartDateDateTimeNPrimary key. Date the sales representive started work in the territory.
EndDateDateTimeYDate the sales representative left work in the territory.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

ShoppingCartItem
Contains online customer orders until the order is submitted or cancelled.
NameData TypeLengthNullDescription
ShoppingCartItemIDIntNPrimary key for ShoppingCartItem records.
ShoppingCartIDNvarchar50NShopping cart identification number.
QuantityIntNProduct quantity ordered.
ProductIDIntNProduct ordered. Foreign key to Product.ProductID.
DateCreatedDateTimeNDate the time the record was created.
ModifiedDateDateTimeNDate and time the record was last updated.

SpecialOffer
Sale discounts lookup table.
NameData TypeLengthNullDescription
SpecialOfferIDIntNPrimary key for SpecialOffer records.
DescriptionNvarchar255NDiscount description.
DiscountPctSmall MoneyNDiscount precentage.
TypeNvarchar50NDiscount type category.
CategoryNvarchar50NGroup the discount applies to such as Reseller or Customer.
StartDateDateTimeNDiscount start date.
EndDateDateTimeNDiscount end date.
MinQtyIntNMinimum discount percent allowed.
MaxQtyIntYMaximum discount percent allowed.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

SpecialOfferProduct
Cross-reference table mapping products to special offer discounts.
NameData TypeLengthNullDescription
SpecialOfferIDIntNPrimary key for SpecialOfferProduct records.
ProductIDIntNProduct identification number. Foreign key to Product.ProductID.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

Store
Customers (resellers) of Adventure Works products.
NameData TypeLengthNullDescription
BusinessEntityIDIntNPrimary key. Foreign key to Customer.BusinessEntityID.
NameNvarchar50NName of the store.
SalesPersonIDIntYID of the sales person assigned to the customer. Foreign key to SalesPerson.BusinessEntityID.
DemographicsXMLYDemographic informationg about the store such as the number of employees, annual sales and store type.
rowguidUniqueIdentifierNROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDateDateTimeNDate and time the record was last updated.

vIndividualCustomer
Individual customers (names and addresses) that purchase Adventure Works Cycles products online.
NameDescription
BusinessEntityID
Title
FirstName
MiddleName
LastName
Suffix
PhoneNumber
PhoneNumberType
EmailAddress
EmailPromotion
AddressType
AddressLine1
AddressLine2
City
StateProvinceName
PostalCode
CountryRegionName
Demographics

vPersonDemographics
Displays the content from each element in the xml column Demographics for each customer in the Person.Person table.
NameDescription
BusinessEntityID
TotalPurchaseYTD
DateFirstPurchase
BirthDate
MaritalStatus
YearlyIncome
Gender
TotalChildren
NumberChildrenAtHome
Education
Occupation
HomeOwnerFlag
NumberCarsOwned

vSalesPerson
Sales representiatives (names and addresses) and their sales-related information.
NameDescription
BusinessEntityID
Title
FirstName
MiddleName
LastName
Suffix
JobTitle
PhoneNumber
PhoneNumberType
EmailAddress
EmailPromotion
AddressLine1
AddressLine2
City
StateProvinceName
PostalCode
CountryRegionName
TerritoryName
TerritoryGroup
SalesQuota
SalesYTD
SalesLastYear

vSalesPersonSalesByFiscalYears
Uses PIVOT to return aggregated sales information for each sales representative.
NameDescription
SalesPersonID
FullName
JobTitle
SalesTerritory
2002
2003
2004

vStoreWithAddresses
Stores (including store addresses) that sell Adventure Works Cycles products to consumers.
NameDescription
BusinessEntityID
Name
AddressType
AddressLine1
AddressLine2
City
StateProvinceName
PostalCode
CountryRegionName

vStoreWithContacts
Stores (including store contacts) that sell Adventure Works Cycles products to consumers.
NameDescription
BusinessEntityID
Name
ContactType
Title
FirstName
MiddleName
LastName
Suffix
PhoneNumber
PhoneNumberType
EmailAddress
EmailPromotion

vStoreWithDemographics
Stores (including demographics) that sell Adventure Works Cycles products to consumers.
NameDescription
BusinessEntityID
Name
AnnualSales
AnnualRevenue
BankName
BusinessType
YearOpened
Specialty
SquareFeet
Brands
Internet
NumberEmployees

ufnGetSalesOrderStatusText
Scalar function returning the text representation of the Status column in the SalesOrderHeader table.
NameData TypeDirectionDescription
@StatusTinyIntINInput parameter for the scalar function ufnGetSalesOrderStatusText. Enter a valid integer.