SalesTerritory
PK
TerritoryID  
int
NN
Name  
Name
NN
FK
CountryRegionCode  
nvarchar(3)
NN
Group  
nvarchar(50)
NN
SalesYTD  
money
NN
SalesLastYear  
money
NN
CostYTD  
money
NN
CostLastYear  
money
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_SalesTerritory_TerritoryID
IX
AK_SalesTerritory_Name
IX
AK_SalesTerritory_rowguid
ScrapReason
PK
ScrapReasonID  
smallint
NN
Name  
Name
NN
ModifiedDate  
datetime
NN
KEY
PK_ScrapReason_ScrapReasonID
IX
AK_ScrapReason_Name
DatabaseLog
PK
DatabaseLogID  
int
NN
PostTime  
datetime
NN
DatabaseUser  
int
NN
Event  
int
NN
Schema  
int
Object  
int
TSQL  
nvarchar(max)
NN
XmlEvent  
xml
NN
KEY
PK_DatabaseLog_DatabaseLogID
Product
PK
ProductID  
int
NN
Name  
Name
NN
ProductNumber  
nvarchar(25)
NN
MakeFlag  
Flag
NN
FinishedGoodsFlag  
Flag
NN
Color  
nvarchar(15)
SafetyStockLevel  
smallint
NN
ReorderPoint  
smallint
NN
StandardCost  
money
NN
ListPrice  
money
NN
Size  
nvarchar(5)
FK
SizeUnitMeasureCode  
nchar(3)
FK
WeightUnitMeasureCode  
nchar(3)
Weight  
decimal(8,2)
DaysToManufacture  
int
NN
ProductLine  
nchar(2)
Class  
nchar(2)
Style  
nchar(2)
FK
ProductSubcategoryID  
int
FK
ProductModelID  
int
SellStartDate  
datetime
NN
SellEndDate  
datetime
DiscontinuedDate  
datetime
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_Product_ProductID
IX
AK_Product_ProductNumber
IX
AK_Product_Name
IX
AK_Product_rowguid
AWBuildVersion
PK
SystemInformationID  
tinyint
NN
Database Version  
nvarchar(25)
NN
VersionDate  
datetime
NN
ModifiedDate  
datetime
NN
KEY
PK_AWBuildVersion_SystemInformationID
PersonCreditCard
PFK
BusinessEntityID  
int
NN
PFK
CreditCardID  
int
NN
ModifiedDate  
datetime
NN
KEY
PK_PersonCreditCard_BusinessEntityID_CreditCardID
JobCandidate
PK
JobCandidateID  
int
NN
FK
BusinessEntityID  
int
Resume  
xml
ModifiedDate  
datetime
NN
KEY
PK_JobCandidate_JobCandidateID
IX
IX_JobCandidate_BusinessEntityID
ProductModelProductDescriptionCulture
PFK
ProductModelID  
int
NN
PFK
ProductDescriptionID  
int
NN
PFK
CultureID  
nchar(6)
NN
ModifiedDate  
datetime
NN
KEY
PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID
ProductSubcategory
PK
ProductSubcategoryID  
int
NN
FK
ProductCategoryID  
int
NN
Name  
Name
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_ProductSubcategory_ProductSubcategoryID
IX
AK_ProductSubcategory_Name
IX
AK_ProductSubcategory_rowguid
ProductCategory
PK
ProductCategoryID  
int
NN
Name  
Name
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_ProductCategory_ProductCategoryID
IX
AK_ProductCategory_Name
IX
AK_ProductCategory_rowguid
UnitMeasure
PK
UnitMeasureCode  
nchar(3)
NN
Name  
Name
NN
ModifiedDate  
datetime
NN
KEY
PK_UnitMeasure_UnitMeasureCode
IX
AK_UnitMeasure_Name
SalesReason
PK
SalesReasonID  
int
NN
Name  
Name
NN
ReasonType  
Name
NN
ModifiedDate  
datetime
NN
KEY
PK_SalesReason_SalesReasonID
ProductReview
PK
ProductReviewID  
int
NN
FK
ProductID  
int
NN
ReviewerName  
Name
NN
ReviewDate  
datetime
NN
EmailAddress  
nvarchar(50)
NN
Rating  
int
NN
Comments  
nvarchar(3850)
ModifiedDate  
datetime
NN
KEY
PK_ProductReview_ProductReviewID
IX
IX_ProductReview_ProductID_Name
Currency
PK
CurrencyCode  
nchar(3)
NN
Name  
Name
NN
ModifiedDate  
datetime
NN
KEY
PK_Currency_CurrencyCode
IX
AK_Currency_Name
ProductDocument
PFK
ProductID  
int
NN
PFK
DocumentNode  
hierarchyid
NN
ModifiedDate  
datetime
NN
KEY
PK_ProductDocument_ProductID_DocumentNode
ProductVendor
PFK
ProductID  
int
NN
PFK
BusinessEntityID  
int
NN
AverageLeadTime  
int
NN
StandardPrice  
money
NN
LastReceiptCost  
money
LastReceiptDate  
datetime
MinOrderQty  
int
NN
MaxOrderQty  
int
NN
OnOrderQty  
int
FK
UnitMeasureCode  
nchar(3)
NN
ModifiedDate  
datetime
NN
KEY
PK_ProductVendor_ProductID_BusinessEntityID
IX
IX_ProductVendor_UnitMeasureCode
IX
IX_ProductVendor_BusinessEntityID
Culture
PK
CultureID  
nchar(6)
NN
Name  
Name
NN
ModifiedDate  
datetime
NN
KEY
PK_Culture_CultureID
IX
AK_Culture_Name
PersonPhone
PFK
BusinessEntityID  
int
NN
PK
PhoneNumber  
Phone
NN
PFK
PhoneNumberTypeID  
int
NN
ModifiedDate  
datetime
NN
KEY
PK_PersonPhone_BusinessEntityID_PhoneNumber_PhoneNumberTypeID
IX
IX_PersonPhone_PhoneNumber
SpecialOfferProduct
PFK
SpecialOfferID  
int
NN
PFK
ProductID  
int
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_SpecialOfferProduct_SpecialOfferID_ProductID
IX
AK_SpecialOfferProduct_rowguid
IX
IX_SpecialOfferProduct_ProductID
Document
PK
DocumentNode  
hierarchyid
NN
DocumentLevel  
smallint
Title  
nvarchar(50)
NN
FK
Owner  
int
NN
FolderFlag  
bit
NN
FileName  
nvarchar(400)
NN
FileExtension  
nvarchar(8)
NN
Revision  
nchar(5)
NN
ChangeNumber  
int
NN
Status  
tinyint
NN
DocumentSummary  
nvarchar(max)
Document  
varbinary(max)
AK
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_Document_DocumentNode
KEY
UQ__Document__F73921F7C5112C2E
IX
AK_Document_DocumentLevel_DocumentNode
IX
AK_Document_rowguid
IX
IX_Document_FileName_Revision
SalesOrderDetail
PFK
SalesOrderID  
int
NN
PK
SalesOrderDetailID  
int
NN
CarrierTrackingNumber  
nvarchar(25)
OrderQty  
smallint
NN
FK
ProductID  
int
NN
FK
SpecialOfferID  
int
NN
UnitPrice  
money
NN
UnitPriceDiscount  
money
NN
LineTotal  
numeric(38,6)
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
IX
AK_SalesOrderDetail_rowguid
IX
IX_SalesOrderDetail_ProductID
TR
iduSalesOrderDetail
SalesTerritoryHistory
PFK
BusinessEntityID  
int
NN
PFK
TerritoryID  
int
NN
PK
StartDate  
datetime
NN
EndDate  
datetime
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_SalesTerritoryHistory_BusinessEntityID_StartDate_TerritoryID
IX
AK_SalesTerritoryHistory_rowguid
Illustration
PK
IllustrationID  
int
NN
Diagram  
xml
ModifiedDate  
datetime
NN
KEY
PK_Illustration_IllustrationID
ProductModel
PK
ProductModelID  
int
NN
Name  
Name
NN
CatalogDescription  
xml
Instructions  
xml
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_ProductModel_ProductModelID
IX
AK_ProductModel_Name
IX
AK_ProductModel_rowguid
IX
PXML_ProductModel_CatalogDescription
IX
PXML_ProductModel_Instructions
BusinessEntity
PK
BusinessEntityID  
int
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_BusinessEntity_BusinessEntityID
IX
AK_BusinessEntity_rowguid
Password
PFK
BusinessEntityID  
int
NN
PasswordHash  
varchar(128)
NN
PasswordSalt  
varchar(10)
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_Password_BusinessEntityID
EmployeePayHistory
PFK
BusinessEntityID  
int
NN
PK
RateChangeDate  
datetime
NN
Rate  
money
NN
PayFrequency  
tinyint
NN
ModifiedDate  
datetime
NN
KEY
PK_EmployeePayHistory_BusinessEntityID_RateChangeDate
ContactType
PK
ContactTypeID  
int
NN
Name  
Name
NN
ModifiedDate  
datetime
NN
KEY
PK_ContactType_ContactTypeID
IX
AK_ContactType_Name
Department
PK
DepartmentID  
smallint
NN
Name  
Name
NN
GroupName  
Name
NN
ModifiedDate  
datetime
NN
KEY
PK_Department_DepartmentID
IX
AK_Department_Name
WorkOrder
PK
WorkOrderID  
int
NN
FK
ProductID  
int
NN
OrderQty  
int
NN
StockedQty  
int
NN
ScrappedQty  
smallint
NN
StartDate  
datetime
NN
EndDate  
datetime
DueDate  
datetime
NN
FK
ScrapReasonID  
smallint
ModifiedDate  
datetime
NN
KEY
PK_WorkOrder_WorkOrderID
IX
IX_WorkOrder_ScrapReasonID
IX
IX_WorkOrder_ProductID
TR
iWorkOrder
TR
uWorkOrder
CountryRegionCurrency
PFK
CountryRegionCode  
nvarchar(3)
NN
PFK
CurrencyCode  
nchar(3)
NN
ModifiedDate  
datetime
NN
KEY
PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode
IX
IX_CountryRegionCurrency_CurrencyCode
ShoppingCartItem
PK
ShoppingCartItemID  
int
NN
ShoppingCartID  
nvarchar(50)
NN
Quantity  
int
NN
FK
ProductID  
int
NN
DateCreated  
datetime
NN
ModifiedDate  
datetime
NN
KEY
PK_ShoppingCartItem_ShoppingCartItemID
IX
IX_ShoppingCartItem_ShoppingCartID_ProductID
SalesTaxRate
PK
SalesTaxRateID  
int
NN
FK
StateProvinceID  
int
NN
TaxType  
tinyint
NN
TaxRate  
smallmoney
NN
Name  
Name
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_SalesTaxRate_SalesTaxRateID
IX
AK_SalesTaxRate_StateProvinceID_TaxType
IX
AK_SalesTaxRate_rowguid
ShipMethod
PK
ShipMethodID  
int
NN
Name  
Name
NN
ShipBase  
money
NN
ShipRate  
money
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_ShipMethod_ShipMethodID
IX
AK_ShipMethod_Name
IX
AK_ShipMethod_rowguid
ErrorLog
PK
ErrorLogID  
int
NN
ErrorTime  
datetime
NN
UserName  
int
NN
ErrorNumber  
int
NN
ErrorSeverity  
int
ErrorState  
int
ErrorProcedure  
nvarchar(126)
ErrorLine  
int
ErrorMessage  
nvarchar(4000)
NN
KEY
PK_ErrorLog_ErrorLogID
PurchaseOrderDetail
PFK
PurchaseOrderID  
int
NN
PK
PurchaseOrderDetailID  
int
NN
DueDate  
datetime
NN
OrderQty  
smallint
NN
FK
ProductID  
int
NN
UnitPrice  
money
NN
LineTotal  
money
NN
ReceivedQty  
decimal(8,2)
NN
RejectedQty  
decimal(8,2)
NN
StockedQty  
decimal(9,2)
NN
ModifiedDate  
datetime
NN
KEY
PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
IX
IX_PurchaseOrderDetail_ProductID
TR
iPurchaseOrderDetail
TR
uPurchaseOrderDetail
CountryRegion
PK
CountryRegionCode  
nvarchar(3)
NN
Name  
Name
NN
ModifiedDate  
datetime
NN
KEY
PK_CountryRegion_CountryRegionCode
IX
AK_CountryRegion_Name
Vendor
PFK
BusinessEntityID  
int
NN
AccountNumber  
AccountNumber
NN
Name  
Name
NN
CreditRating  
tinyint
NN
PreferredVendorStatus  
Flag
NN
ActiveFlag  
Flag
NN
PurchasingWebServiceURL  
nvarchar(1024)
ModifiedDate  
datetime
NN
KEY
PK_Vendor_BusinessEntityID
IX
AK_Vendor_AccountNumber
TR
dVendor
StateProvince
PK
StateProvinceID  
int
NN
StateProvinceCode  
nchar(3)
NN
FK
CountryRegionCode  
nvarchar(3)
NN
IsOnlyStateProvinceFlag  
Flag
NN
Name  
Name
NN
FK
TerritoryID  
int
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_StateProvince_StateProvinceID
IX
AK_StateProvince_Name
IX
AK_StateProvince_StateProvinceCode_CountryRegionCode
IX
AK_StateProvince_rowguid
CreditCard
PK
CreditCardID  
int
NN
CardType  
nvarchar(50)
NN
CardNumber  
nvarchar(25)
NN
ExpMonth  
tinyint
NN
ExpYear  
smallint
NN
ModifiedDate  
datetime
NN
KEY
PK_CreditCard_CreditCardID
IX
AK_CreditCard_CardNumber
ProductInventory
PFK
ProductID  
int
NN
PFK
LocationID  
smallint
NN
Shelf  
nvarchar(10)
NN
Bin  
tinyint
NN
Quantity  
smallint
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_ProductInventory_ProductID_LocationID
ProductDescription
PK
ProductDescriptionID  
int
NN
Description  
nvarchar(400)
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_ProductDescription_ProductDescriptionID
IX
AK_ProductDescription_rowguid
EmailAddress
PFK
BusinessEntityID  
int
NN
PK
EmailAddressID  
int
NN
EmailAddress  
nvarchar(50)
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_EmailAddress_BusinessEntityID_EmailAddressID
IX
IX_EmailAddress_EmailAddress
CurrencyRate
PK
CurrencyRateID  
int
NN
CurrencyRateDate  
datetime
NN
FK
FromCurrencyCode  
nchar(3)
NN
FK
ToCurrencyCode  
nchar(3)
NN
AverageRate  
money
NN
EndOfDayRate  
money
NN
ModifiedDate  
datetime
NN
KEY
PK_CurrencyRate_CurrencyRateID
IX
AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode
EmployeeDepartmentHistory
PFK
BusinessEntityID  
int
NN
PFK
DepartmentID  
smallint
NN
PFK
ShiftID  
tinyint
NN
PK
StartDate  
date
NN
EndDate  
date
ModifiedDate  
datetime
NN
KEY
PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID
IX
IX_EmployeeDepartmentHistory_DepartmentID
IX
IX_EmployeeDepartmentHistory_ShiftID
SalesPerson
PFK
BusinessEntityID  
int
NN
FK
TerritoryID  
int
SalesQuota  
money
Bonus  
money
NN
CommissionPct  
smallmoney
NN
SalesYTD  
money
NN
SalesLastYear  
money
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_SalesPerson_BusinessEntityID
IX
AK_SalesPerson_rowguid
SpecialOffer
PK
SpecialOfferID  
int
NN
Description  
nvarchar(255)
NN
DiscountPct  
smallmoney
NN
Type  
nvarchar(50)
NN
Category  
nvarchar(50)
NN
StartDate  
datetime
NN
EndDate  
datetime
NN
MinQty  
int
NN
MaxQty  
int
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_SpecialOffer_SpecialOfferID
IX
AK_SpecialOffer_rowguid
PhoneNumberType
PK
PhoneNumberTypeID  
int
NN
Name  
Name
NN
ModifiedDate  
datetime
NN
KEY
PK_PhoneNumberType_PhoneNumberTypeID
SalesPersonQuotaHistory
PFK
BusinessEntityID  
int
NN
PK
QuotaDate  
datetime
NN
SalesQuota  
money
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_SalesPersonQuotaHistory_BusinessEntityID_QuotaDate
IX
AK_SalesPersonQuotaHistory_rowguid
BillOfMaterials
PK
BillOfMaterialsID  
int
NN
FK
ProductAssemblyID  
int
FK
ComponentID  
int
NN
StartDate  
datetime
NN
EndDate  
datetime
FK
UnitMeasureCode  
nchar(3)
NN
BOMLevel  
smallint
NN
PerAssemblyQty  
decimal(8,2)
NN
ModifiedDate  
datetime
NN
KEY
PK_BillOfMaterials_BillOfMaterialsID
IX
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
IX
IX_BillOfMaterials_UnitMeasureCode
Address
PK
AddressID  
int
NN
AddressLine1  
nvarchar(60)
NN
AddressLine2  
nvarchar(60)
City  
nvarchar(30)
NN
FK
StateProvinceID  
int
NN
PostalCode  
nvarchar(15)
NN
SpatialLocation  
geography
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_Address_AddressID
IX
AK_Address_rowguid
IX
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
IX
IX_Address_StateProvinceID
SalesOrderHeader
PK
SalesOrderID  
int
NN
RevisionNumber  
tinyint
NN
OrderDate  
datetime
NN
DueDate  
datetime
NN
ShipDate  
datetime
Status  
tinyint
NN
OnlineOrderFlag  
Flag
NN
SalesOrderNumber  
nvarchar(25)
NN
PurchaseOrderNumber  
OrderNumber
AccountNumber  
AccountNumber
FK
CustomerID  
int
NN
FK
SalesPersonID  
int
FK
TerritoryID  
int
FK
BillToAddressID  
int
NN
FK
ShipToAddressID  
int
NN
FK
ShipMethodID  
int
NN
FK
CreditCardID  
int
CreditCardApprovalCode  
varchar(15)
FK
CurrencyRateID  
int
SubTotal  
money
NN
TaxAmt  
money
NN
Freight  
money
NN
TotalDue  
money
NN
Comment  
nvarchar(128)
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_SalesOrderHeader_SalesOrderID
IX
AK_SalesOrderHeader_rowguid
IX
AK_SalesOrderHeader_SalesOrderNumber
IX
IX_SalesOrderHeader_CustomerID
IX
IX_SalesOrderHeader_SalesPersonID
TR
uSalesOrderHeader
Person
PFK
BusinessEntityID  
int
NN
PersonType  
nchar(2)
NN
NameStyle  
NameStyle
NN
Title  
nvarchar(8)
FirstName  
Name
NN
MiddleName  
Name
LastName  
Name
NN
Suffix  
nvarchar(10)
EmailPromotion  
int
NN
AdditionalContactInfo  
xml
Demographics  
xml
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_Person_BusinessEntityID
IX
IX_Person_LastName_FirstName_MiddleName
IX
AK_Person_rowguid
IX
PXML_Person_AddContact
IX
PXML_Person_Demographics
IX
XMLPATH_Person_Demographics
IX
XMLPROPERTY_Person_Demographics
IX
XMLVALUE_Person_Demographics
TR
iuPerson
ProductPhoto
PK
ProductPhotoID  
int
NN
ThumbNailPhoto  
varbinary(max)
ThumbnailPhotoFileName  
nvarchar(50)
LargePhoto  
varbinary(max)
LargePhotoFileName  
nvarchar(50)
ModifiedDate  
datetime
NN
KEY
PK_ProductPhoto_ProductPhotoID
ProductModelIllustration
PFK
ProductModelID  
int
NN
PFK
IllustrationID  
int
NN
ModifiedDate  
datetime
NN
KEY
PK_ProductModelIllustration_ProductModelID_IllustrationID
Customer
PK
CustomerID  
int
NN
FK
PersonID  
int
FK
StoreID  
int
FK
TerritoryID  
int
AccountNumber  
varchar(10)
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_Customer_CustomerID
IX
AK_Customer_rowguid
IX
AK_Customer_AccountNumber
IX
IX_Customer_TerritoryID
Employee
PFK
BusinessEntityID  
int
NN
NationalIDNumber  
nvarchar(15)
NN
LoginID  
nvarchar(256)
NN
OrganizationNode  
hierarchyid
OrganizationLevel  
smallint
JobTitle  
nvarchar(50)
NN
BirthDate  
date
NN
MaritalStatus  
nchar(1)
NN
Gender  
nchar(1)
NN
HireDate  
date
NN
SalariedFlag  
Flag
NN
VacationHours  
smallint
NN
SickLeaveHours  
smallint
NN
CurrentFlag  
Flag
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_Employee_BusinessEntityID
IX
IX_Employee_OrganizationNode
IX
IX_Employee_OrganizationLevel_OrganizationNode
IX
AK_Employee_LoginID
IX
AK_Employee_NationalIDNumber
IX
AK_Employee_rowguid
TR
dEmployee
ProductProductPhoto
PFK
ProductID  
int
NN
PFK
ProductPhotoID  
int
NN
Primary  
Flag
NN
ModifiedDate  
datetime
NN
KEY
PK_ProductProductPhoto_ProductID_ProductPhotoID
Store
PFK
BusinessEntityID  
int
NN
Name  
Name
NN
FK
SalesPersonID  
int
Demographics  
xml
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_Store_BusinessEntityID
IX
AK_Store_rowguid
IX
IX_Store_SalesPersonID
IX
PXML_Store_Demographics
Shift
PK
ShiftID  
tinyint
NN
Name  
Name
NN
StartTime  
time(7)
NN
EndTime  
time(7)
NN
ModifiedDate  
datetime
NN
KEY
PK_Shift_ShiftID
IX
AK_Shift_Name
IX
AK_Shift_StartTime_EndTime
BusinessEntityAddress
PFK
BusinessEntityID  
int
NN
PFK
AddressID  
int
NN
PFK
AddressTypeID  
int
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID
IX
AK_BusinessEntityAddress_rowguid
IX
IX_BusinessEntityAddress_AddressID
IX
IX_BusinessEntityAddress_AddressTypeID
TransactionHistory
PK
TransactionID  
int
NN
FK
ProductID  
int
NN
ReferenceOrderID  
int
NN
ReferenceOrderLineID  
int
NN
TransactionDate  
datetime
NN
TransactionType  
nchar(1)
NN
Quantity  
int
NN
ActualCost  
money
NN
ModifiedDate  
datetime
NN
KEY
PK_TransactionHistory_TransactionID
IX
IX_TransactionHistory_ProductID
IX
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
Location
PK
LocationID  
smallint
NN
Name  
Name
NN
CostRate  
smallmoney
NN
Availability  
decimal(8,2)
NN
ModifiedDate  
datetime
NN
KEY
PK_Location_LocationID
IX
AK_Location_Name
ProductCostHistory
PFK
ProductID  
int
NN
PK
StartDate  
datetime
NN
EndDate  
datetime
StandardCost  
money
NN
ModifiedDate  
datetime
NN
KEY
PK_ProductCostHistory_ProductID_StartDate
PurchaseOrderHeader
PK
PurchaseOrderID  
int
NN
RevisionNumber  
tinyint
NN
Status  
tinyint
NN
FK
EmployeeID  
int
NN
FK
VendorID  
int
NN
FK
ShipMethodID  
int
NN
OrderDate  
datetime
NN
ShipDate  
datetime
SubTotal  
money
NN
TaxAmt  
money
NN
Freight  
money
NN
TotalDue  
money
NN
ModifiedDate  
datetime
NN
KEY
PK_PurchaseOrderHeader_PurchaseOrderID
IX
IX_PurchaseOrderHeader_VendorID
IX
IX_PurchaseOrderHeader_EmployeeID
TR
uPurchaseOrderHeader
WorkOrderRouting
PFK
WorkOrderID  
int
NN
PK
ProductID  
int
NN
PK
OperationSequence  
smallint
NN
FK
LocationID  
smallint
NN
ScheduledStartDate  
datetime
NN
ScheduledEndDate  
datetime
NN
ActualStartDate  
datetime
ActualEndDate  
datetime
ActualResourceHrs  
decimal(9,4)
PlannedCost  
money
NN
ActualCost  
money
ModifiedDate  
datetime
NN
KEY
PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence
IX
IX_WorkOrderRouting_ProductID
AddressType
PK
AddressTypeID  
int
NN
Name  
Name
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_AddressType_AddressTypeID
IX
AK_AddressType_rowguid
IX
AK_AddressType_Name
SalesOrderHeaderSalesReason
PFK
SalesOrderID  
int
NN
PFK
SalesReasonID  
int
NN
ModifiedDate  
datetime
NN
KEY
PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID
ProductListPriceHistory
PFK
ProductID  
int
NN
PK
StartDate  
datetime
NN
EndDate  
datetime
ListPrice  
money
NN
ModifiedDate  
datetime
NN
KEY
PK_ProductListPriceHistory_ProductID_StartDate
BusinessEntityContact
PFK
BusinessEntityID  
int
NN
PFK
PersonID  
int
NN
PFK
ContactTypeID  
int
NN
rowguid  
uniqueidentifier
NN
ModifiedDate  
datetime
NN
KEY
PK_BusinessEntityContact_BusinessEntityID_PersonID_ContactTypeID
IX
AK_BusinessEntityContact_rowguid
IX
IX_BusinessEntityContact_PersonID
IX
IX_BusinessEntityContact_ContactTypeID
TransactionHistoryArchive
PK
TransactionID  
int
NN
ProductID  
int
NN
ReferenceOrderID  
int
NN
ReferenceOrderLineID  
int
NN
TransactionDate  
datetime
NN
TransactionType  
nchar(1)
NN
Quantity  
int
NN
ActualCost  
money
NN
ModifiedDate  
datetime
NN
KEY
PK_TransactionHistoryArchive_TransactionID
IX
IX_TransactionHistoryArchive_ProductID
IX
IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID

MS SQL Server - Sample

Adventure Works - ER Diagram with subject areas

Human Resources

Luna Modeler

WWW.DATENSEN.COM

Dbo
Production
Person
Sales
Purchasing

View
vEmployeeDepartment
Function
ufnGetDocumentStatusText
Function
ufnGetProductDealerPrice
View
vStoreWithContacts
Procedure
uspUpdateEmployeeHireInfo
User defined type
Name
Trigger
uPurchaseOrderDetail
Function
ufnGetAccountingStartDate
Trigger
dVendor
View
vJobCandidate
Function
ufnGetProductListPrice
Procedure
uspGetEmployeeManagers
View
vSalesPersonSalesByFiscalYears
Function
ufnLeadingZeros
Function
ufnGetContactInformation
Trigger
uPurchaseOrderHeader
Procedure
uspLogError
User defined type
OrderNumber
View
vEmployeeDepartmentHistory
View
vVendorWithAddresses
Procedure
uspPrintError
Trigger
iPurchaseOrderDetail
Trigger
uWorkOrder
User defined type
Phone
Procedure
uspUpdateEmployeeLogin
User defined type
NameStyle
Trigger
dEmployee
Procedure
uspGetBillOfMaterials
Procedure
uspSearchCandidateResumes
Function
ufnGetStock
Function
ufnGetSalesOrderStatusText
Trigger
uSalesOrderHeader
Procedure
uspGetManagerEmployees
View
vProductAndDescription
View
vPersonDemographics
View
vIndividualCustomer
Trigger
iWorkOrder
View
vStoreWithDemographics
Function
ufnGetPurchaseOrderStatusText
View
vStoreWithAddresses
User defined type
AccountNumber
View
vVendorWithContacts
Trigger
iuPerson
View
vJobCandidateEducation
Function
ufnGetAccountingEndDate
View
vProductModelInstructions
Function
ufnGetProductStandardCost
Procedure
uspUpdateEmployeePersonalInfo
View
vStateProvinceCountryRegion
View
vJobCandidateEmployment
Trigger
iduSalesOrderDetail
Procedure
uspGetWhereUsedProductID
User defined type
Flag
View
vProductModelCatalogDescription
View
vAdditionalContactInfo
View
vSalesPerson
View
vEmployee
Documentation for project Adventure Works Sample
Adventure Works Sample
Project
Name
Adventure Works Sample
Diagram
Name
Main Diagram
Report
Generated
11/7/2026 | 1:21:45 AM
tables
Name
EmployeePayHistory
Description
Employee pay history.
Properties
Schema
HumanResources
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [PayFrequency]=(3) OR [PayFrequency]=(2) OR [PayFrequency]=(1)', 'schema', N'HumanResources', 'table', N'EmployeePayHistory', 'constraint', N'CK_EmployeePayHistory_PayFrequency';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [Rate] >= (6.50) AND [Rate] <= (200.00)', 'schema', N'HumanResources', 'table', N'EmployeePayHistory', 'constraint', N'CK_EmployeePayHistory_Rate';
GO
;
Columns
Name
Data type
Param
Key
Not Null
BusinessEntityID
int
Yes
Yes
Description
Employee identification number. Foreign key to Employee.BusinessEntityID.
RateChangeDate
datetime
Yes
Yes
Description
Date the change in pay is effective
Rate
money
Yes
Description
Salary hourly rate.
After script
CONSTRAINT [CK_EmployeePayHistory_Rate] CHECK ([Rate]>=(6.50) AND [Rate]<=(200.00))
PayFrequency
tinyint
Yes
Description
1 = Salary received monthly, 2 = Salary received biweekly
After script
CONSTRAINT [CK_EmployeePayHistory_PayFrequency] CHECK ([PayFrequency]=(2) OR [PayFrequency]=(1))
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Keys
PK_EmployeePayHistory_BusinessEntityID_RateChangeDate
Column in PK_EmployeePayHistory_BusinessEntityID_RateChangeDate
BusinessEntityID
Column in PK_EmployeePayHistory_BusinessEntityID_RateChangeDate
RateChangeDate
Name
SalesOrderHeaderSalesReason
Description
Cross-reference table mapping sales orders to sales reason codes.
Properties
Schema
Sales
Columns
Name
Data type
Param
Key
Not Null
SalesOrderID
int
Yes
Yes
Description
Primary key. Foreign key to SalesOrderHeader.SalesOrderID.
SalesReasonID
int
Yes
Yes
Description
Primary key. Foreign key to SalesReason.SalesReasonID.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Keys
PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID
Column in PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID
SalesOrderID
Column in PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID
SalesReasonID
Name
SalesPerson
Description
Sales representative current information.
Properties
Schema
Sales
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [SalesQuota] > (0.00)', 'schema', N'Sales', 'table', N'SalesPerson', 'constraint', N'CK_SalesPerson_SalesQuota';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [Bonus] >= (0.00)', 'schema', N'Sales', 'table', N'SalesPerson', 'constraint', N'CK_SalesPerson_Bonus';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [CommissionPct] >= (0.00)', 'schema', N'Sales', 'table', N'SalesPerson', 'constraint', N'CK_SalesPerson_CommissionPct';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [SalesYTD] >= (0.00)', 'schema', N'Sales', 'table', N'SalesPerson', 'constraint', N'CK_SalesPerson_SalesYTD';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [SalesLastYear] >= (0.00)', 'schema', N'Sales', 'table', N'SalesPerson', 'constraint', N'CK_SalesPerson_SalesLastYear';
GO
;
Columns
Name
Data type
Param
Key
Not Null
BusinessEntityID
int
Yes
Yes
Description
Primary key for SalesPerson records. Foreign key to Employee.BusinessEntityID
TerritoryID
int
Description
Territory currently assigned to. Foreign key to SalesTerritory.SalesTerritoryID.
SalesQuota
money
Description
Projected yearly sales.
After script
CONSTRAINT [CK_SalesPerson_SalesQuota] CHECK ([SalesQuota]>(0.00))
Bonus
money
Yes
Default value
((0.00))
Description
Bonus due if quota is met.
After script
CONSTRAINT [CK_SalesPerson_Bonus] CHECK ([Bonus]>=(0.00))
CommissionPct
smallmoney
Yes
Default value
((0.00))
Description
Commision percent received per sale.
After script
CONSTRAINT [CK_SalesPerson_CommissionPct] CHECK ([CommissionPct]>=(0.00))
SalesYTD
money
Yes
Default value
((0.00))
Description
Sales total year to date.
After script
CONSTRAINT [CK_SalesPerson_SalesYTD] CHECK ([SalesYTD]>=(0.00))
SalesLastYear
money
Yes
Default value
((0.00))
Description
Sales total of previous year.
After script
CONSTRAINT [CK_SalesPerson_SalesLastYear] CHECK ([SalesLastYear]>=(0.00))
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_SalesPerson_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
Keys
PK_SalesPerson_BusinessEntityID
Column in PK_SalesPerson_BusinessEntityID
BusinessEntityID
Name
Illustration
Description
Bicycle assembly diagrams.
Properties
Schema
Production
Columns
Name
Data type
Param
Key
Not Null
IllustrationID
int
Yes
Yes
Description
Primary key for Illustration records.
After script
IDENTITY(1,1)
Diagram
xml
Description
Illustrations used in manufacturing instructions. Stored as XML.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Keys
PK_Illustration_IllustrationID
Column in PK_Illustration_IllustrationID
IllustrationID
Name
JobCandidate
Description
Résumés submitted to Human Resources by job applicants.
Properties
Schema
HumanResources
Columns
Name
Data type
Param
Key
Not Null
JobCandidateID
int
Yes
Yes
Description
Primary key for JobCandidate records.
After script
IDENTITY(1,1)
BusinessEntityID
int
Description
Employee identification number if applicant was hired. Foreign key to Employee.BusinessEntityID.
Resume
xml
Description
Résumé in XML format.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
IX_JobCandidate_BusinessEntityID
Column name
BusinessEntityID
Description
Nonclustered index.
Keys
PK_JobCandidate_JobCandidateID
Column in PK_JobCandidate_JobCandidateID
JobCandidateID
Name
Location
Description
Product inventory and manufacturing locations.
Properties
Schema
Production
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [CostRate] >= (0.00)', 'schema', N'Production', 'table', N'Location', 'constraint', N'CK_Location_CostRate';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [Availability] >= (0.00)', 'schema', N'Production', 'table', N'Location', 'constraint', N'CK_Location_Availability';
GO
;
Columns
Name
Data type
Param
Key
Not Null
LocationID
smallint
Yes
Yes
Description
Primary key for Location records.
After script
IDENTITY(1,1)
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Location description.
CostRate
smallmoney
Yes
Default value
((0.00))
Description
Standard hourly cost of the manufacturing location.
After script
CONSTRAINT [CK_Location_CostRate] CHECK ([CostRate]>=(0.00))
Availability
decimal
8,2
Yes
Default value
((0.00))
Description
Work capacity (in hours) of the manufacturing location.
After script
CONSTRAINT [CK_Location_Availability] CHECK ([Availability]>=(0.00))
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_Location_Name
Column name
Name
Unique
Yes
Description
Unique nonclustered index.
Keys
PK_Location_LocationID
Column in PK_Location_LocationID
LocationID
Name
Password
Description
One way hashed authentication information
Properties
Schema
Person
Columns
Name
Data type
Param
Key
Not Null
BusinessEntityID
int
Yes
Yes
PasswordHash
varchar
128
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Password for the e-mail account.
PasswordSalt
varchar
10
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Random value concatenated with the password string before the password is hashed.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Keys
PK_Password_BusinessEntityID
Column in PK_Password_BusinessEntityID
BusinessEntityID
Name
SalesPersonQuotaHistory
Description
Sales performance tracking.
Properties
Schema
Sales
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [SalesQuota] > (0.00)', 'schema', N'Sales', 'table', N'SalesPersonQuotaHistory', 'constraint', N'CK_SalesPersonQuotaHistory_SalesQuota';
GO
;
Columns
Name
Data type
Param
Key
Not Null
BusinessEntityID
int
Yes
Yes
Description
Sales person identification number. Foreign key to SalesPerson.BusinessEntityID.
QuotaDate
datetime
Yes
Yes
Description
Sales quota date.
SalesQuota
money
Yes
Description
Sales quota amount.
After script
CONSTRAINT [CK_SalesPersonQuotaHistory_SalesQuota] CHECK ([SalesQuota]>(0.00))
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_SalesPersonQuotaHistory_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
Keys
PK_SalesPersonQuotaHistory_BusinessEntityID_QuotaDate
Column in PK_SalesPersonQuotaHistory_BusinessEntityID_QuotaDate
BusinessEntityID
Column in PK_SalesPersonQuotaHistory_BusinessEntityID_QuotaDate
QuotaDate
Name
Person
Description
Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.
Properties
Schema
Person
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [EmailPromotion] >= (0) AND [EmailPromotion] <= (2)', 'schema', N'Person', 'table', N'Person', 'constraint', N'CK_Person_EmailPromotion';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [PersonType] is one of SC, VC, IN, EM or SP.', 'schema', N'Person', 'table', N'Person', 'constraint', N'CK_Person_PersonType';
GO
;
Columns
Name
Data type
Param
Key
Not Null
BusinessEntityID
int
Yes
Yes
Description
Primary key for Person records.
PersonType
nchar
2
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Primary type of person: SC = Store Contact, IN = Individual (retail) customer, SP = Sales person, EM = Employee (non-sales), VC = Vendor contact, GC = General contact
After script
CONSTRAINT [CK_Person_PersonType] CHECK ([PersonType] IS NULL OR (upper([PersonType])='GC' OR upper([PersonType])='SP' OR upper([PersonType])='EM' OR upper([PersonType])='IN' OR upper([PersonType])='VC' OR upper([PersonType])='SC'))
NameStyle
Yes
Default value
((0))
Description
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
Collation
SQL_Latin1_General_CP1_CI_AS
Description
A courtesy title. For example, Mr. or Ms.
FirstName
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
First name of the person.
MiddleName
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Middle name or middle initial of the person.
LastName
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Last name of the person.
Suffix
nvarchar
10
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Surname suffix. For example, Sr. or Jr.
EmailPromotion
int
Yes
Default value
((0))
Description
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.
After script
CONSTRAINT [CK_Person_EmailPromotion] CHECK ([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))
AdditionalContactInfo
xml
Description
Additional contact information about the person stored in xml format.
Demographics
xml
Description
Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
IX_Person_LastName_FirstName_MiddleName
Column name
LastName
Column name
FirstName
Column name
MiddleName
AK_Person_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
PXML_Person_AddContact
Primary
Yes
Description
Primary XML index.
PXML_Person_Demographics
Primary
Yes
Description
Primary XML index.
XMLPATH_Person_Demographics
Description
Secondary XML index for path.
XMLPROPERTY_Person_Demographics
Description
Secondary XML index for property.
XMLVALUE_Person_Demographics
Description
Secondary XML index for value.
Keys
PK_Person_BusinessEntityID
Column in PK_Person_BusinessEntityID
BusinessEntityID
Name
SalesReason
Description
Lookup table of customer purchase reasons.
Properties
Schema
Sales
Columns
Name
Data type
Param
Key
Not Null
SalesReasonID
int
Yes
Yes
Description
Primary key for SalesReason records.
After script
IDENTITY(1,1)
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Sales reason description.
ReasonType
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Category the sales reason belongs to.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Keys
PK_SalesReason_SalesReasonID
Column in PK_SalesReason_SalesReasonID
SalesReasonID
Name
SalesTaxRate
Description
Tax rate lookup table.
Properties
Schema
Sales
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [TaxType] BETWEEN (1) AND (3)', 'schema', N'Sales', 'table', N'SalesTaxRate', 'constraint', N'CK_SalesTaxRate_TaxType';
GO
;
Columns
Name
Data type
Param
Key
Not Null
SalesTaxRateID
int
Yes
Yes
Description
Primary key for SalesTaxRate records.
After script
IDENTITY(1,1)
StateProvinceID
int
Yes
Description
State, province, or country/region the sales tax applies to.
TaxType
tinyint
Yes
Description
1 = Tax applied to retail transactions, 2 = Tax applied to wholesale transactions, 3 = Tax applied to all sales (retail and wholesale) transactions.
After script
CONSTRAINT [CK_SalesTaxRate_TaxType] CHECK ([TaxType]>=(1) AND [TaxType]<=(3))
TaxRate
smallmoney
Yes
Default value
((0.00))
Description
Tax rate amount.
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Tax rate description.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_SalesTaxRate_StateProvinceID_TaxType
Column name
StateProvinceID
Column name
TaxType
Unique
Yes
Description
Unique nonclustered index.
AK_SalesTaxRate_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
Keys
PK_SalesTaxRate_SalesTaxRateID
Column in PK_SalesTaxRate_SalesTaxRateID
SalesTaxRateID
Name
PersonCreditCard
Description
Cross-reference table mapping people to their credit card information in the CreditCard table.
Properties
Schema
Sales
Columns
Name
Data type
Param
Key
Not Null
BusinessEntityID
int
Yes
Yes
Description
Business entity identification number. Foreign key to Person.BusinessEntityID.
CreditCardID
int
Yes
Yes
Description
Credit card identification number. Foreign key to CreditCard.CreditCardID.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Keys
PK_PersonCreditCard_BusinessEntityID_CreditCardID
Column in PK_PersonCreditCard_BusinessEntityID_CreditCardID
BusinessEntityID
Column in PK_PersonCreditCard_BusinessEntityID_CreditCardID
CreditCardID
Name
PersonPhone
Description
Telephone number and type of a person.
Properties
Schema
Person
Columns
Name
Data type
Param
Key
Not Null
BusinessEntityID
int
Yes
Yes
Description
Business entity identification number. Foreign key to Person.BusinessEntityID.
PhoneNumber
Yes
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Telephone number identification number.
PhoneNumberTypeID
int
Yes
Yes
Description
Kind of phone number. Foreign key to PhoneNumberType.PhoneNumberTypeID.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
IX_PersonPhone_PhoneNumber
Column name
PhoneNumber
Description
Nonclustered index.
Keys
PK_PersonPhone_BusinessEntityID_PhoneNumber_PhoneNumberTypeID
Column in PK_PersonPhone_BusinessEntityID_PhoneNumber_PhoneNumberTypeID
BusinessEntityID
Column in PK_PersonPhone_BusinessEntityID_PhoneNumber_PhoneNumberTypeID
PhoneNumber
Column in PK_PersonPhone_BusinessEntityID_PhoneNumber_PhoneNumberTypeID
PhoneNumberTypeID
Name
SalesTerritory
Description
Sales territory lookup table.
Properties
Schema
Sales
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [SalesYTD] >= (0.00)', 'schema', N'Sales', 'table', N'SalesTerritory', 'constraint', N'CK_SalesTerritory_SalesYTD';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [SalesLastYear] >= (0.00)', 'schema', N'Sales', 'table', N'SalesTerritory', 'constraint', N'CK_SalesTerritory_SalesLastYear';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [CostYTD] >= (0.00)', 'schema', N'Sales', 'table', N'SalesTerritory', 'constraint', N'CK_SalesTerritory_CostYTD';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [CostLastYear] >= (0.00)', 'schema', N'Sales', 'table', N'SalesTerritory', 'constraint', N'CK_SalesTerritory_CostLastYear';
GO
;
Columns
Name
Data type
Param
Key
Not Null
TerritoryID
int
Yes
Yes
Description
Primary key for SalesTerritory records.
After script
IDENTITY(1,1)
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Sales territory description
CountryRegionCode
nvarchar
3
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.
Group
nvarchar
50
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Geographic area to which the sales territory belong.
SalesYTD
money
Yes
Default value
((0.00))
Description
Sales in the territory year to date.
After script
CONSTRAINT [CK_SalesTerritory_SalesYTD] CHECK ([SalesYTD]>=(0.00))
SalesLastYear
money
Yes
Default value
((0.00))
Description
Sales in the territory the previous year.
After script
CONSTRAINT [CK_SalesTerritory_SalesLastYear] CHECK ([SalesLastYear]>=(0.00))
CostYTD
money
Yes
Default value
((0.00))
Description
Business costs in the territory year to date.
After script
CONSTRAINT [CK_SalesTerritory_CostYTD] CHECK ([CostYTD]>=(0.00))
CostLastYear
money
Yes
Default value
((0.00))
Description
Business costs in the territory the previous year.
After script
CONSTRAINT [CK_SalesTerritory_CostLastYear] CHECK ([CostLastYear]>=(0.00))
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_SalesTerritory_Name
Column name
Name
Unique
Yes
Description
Unique nonclustered index.
AK_SalesTerritory_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
Keys
PK_SalesTerritory_TerritoryID
Column in PK_SalesTerritory_TerritoryID
TerritoryID
Name
PhoneNumberType
Description
Type of phone number of a person.
Properties
Schema
Person
Columns
Name
Data type
Param
Key
Not Null
PhoneNumberTypeID
int
Yes
Yes
Description
Primary key for telephone number type records.
After script
IDENTITY(1,1)
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Name of the telephone number type
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Keys
PK_PhoneNumberType_PhoneNumberTypeID
Column in PK_PhoneNumberType_PhoneNumberTypeID
PhoneNumberTypeID
Name
Product
Description
Products sold or used in the manfacturing of sold products.
Properties
Schema
Production
After script
ALTER TABLE [Production].[Product] ADD CONSTRAINT [CK_Product_SellEndDate] CHECK ([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL);
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [SafetyStockLevel] > (0)', 'schema', N'Production', 'table', N'Product', 'constraint', N'CK_Product_SafetyStockLevel';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [ReorderPoint] > (0)', 'schema', N'Production', 'table', N'Product', 'constraint', N'CK_Product_ReorderPoint';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [SafetyStockLevel] > (0)', 'schema', N'Production', 'table', N'Product', 'constraint', N'CK_Product_StandardCost';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [ListPrice] >= (0.00)', 'schema', N'Production', 'table', N'Product', 'constraint', N'CK_Product_ListPrice';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [Weight] > (0.00)', 'schema', N'Production', 'table', N'Product', 'constraint', N'CK_Product_Weight';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [DaysToManufacture] >= (0)', 'schema', N'Production', 'table', N'Product', 'constraint', N'CK_Product_DaysToManufacture';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [ProductLine]=''r'' OR [ProductLine]=''m'' OR [ProductLine]=''t'' OR [ProductLine]=''s'' OR [ProductLine]=''R'' OR [ProductLine]=''M'' OR [ProductLine]=''T'' OR [ProductLine]=''S'' OR [ProductLine] IS NULL', 'schema', N'Production', 'table', N'Product', 'constraint', N'CK_Product_ProductLine';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [Class]=''h'' OR [Class]=''m'' OR [Class]=''l'' OR [Class]=''H'' OR [Class]=''M'' OR [Class]=''L'' OR [Class] IS NULL', 'schema', N'Production', 'table', N'Product', 'constraint', N'CK_Product_Class';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [Style]=''u'' OR [Style]=''m'' OR [Style]=''w'' OR [Style]=''U'' OR [Style]=''M'' OR [Style]=''W'' OR [Style] IS NULL', 'schema', N'Production', 'table', N'Product', 'constraint', N'CK_Product_Style';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [SellEndDate] >= [SellStartDate] OR [SellEndDate] IS NULL', 'schema', N'Production', 'table', N'Product', 'constraint', N'CK_Product_SellEndDate';
GO
;
Columns
Name
Data type
Param
Key
Not Null
ProductID
int
Yes
Yes
Description
Primary key for Product records.
After script
IDENTITY(1,1)
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Name of the product.
ProductNumber
nvarchar
25
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Unique product identification number.
MakeFlag
Yes
Default value
((1))
Description
0 = Product is purchased, 1 = Product is manufactured in-house.
FinishedGoodsFlag
Yes
Default value
((1))
Description
0 = Product is not a salable item. 1 = Product is salable.
Color
nvarchar
15
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Product color.
SafetyStockLevel
smallint
Yes
Description
Minimum inventory quantity.
After script
CONSTRAINT [CK_Product_SafetyStockLevel] CHECK ([SafetyStockLevel]>(0))
ReorderPoint
smallint
Yes
Description
Inventory level that triggers a purchase order or work order.
After script
CONSTRAINT [CK_Product_ReorderPoint] CHECK ([ReorderPoint]>(0))
StandardCost
money
Yes
Description
Standard cost of the product.
After script
CONSTRAINT [CK_Product_StandardCost] CHECK ([StandardCost]>=(0.00))
ListPrice
money
Yes
Description
Selling price.
After script
CONSTRAINT [CK_Product_ListPrice] CHECK ([ListPrice]>=(0.00))
Size
nvarchar
5
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Product size.
SizeUnitMeasureCode
nchar
3
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Unit of measure for Size column.
WeightUnitMeasureCode
nchar
3
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Unit of measure for Weight column.
Weight
decimal
8,2
Description
Product weight.
After script
CONSTRAINT [CK_Product_Weight] CHECK ([Weight]>(0.00))
DaysToManufacture
int
Yes
Description
Number of days required to manufacture the product.
After script
CONSTRAINT [CK_Product_DaysToManufacture] CHECK ([DaysToManufacture]>=(0))
ProductLine
nchar
2
Collation
SQL_Latin1_General_CP1_CI_AS
Description
R = Road, M = Mountain, T = Touring, S = Standard
After script
CONSTRAINT [CK_Product_ProductLine] CHECK (upper([ProductLine])='R' OR upper([ProductLine])='M' OR upper([ProductLine])='T' OR upper([ProductLine])='S' OR [ProductLine] IS NULL)
Class
nchar
2
Collation
SQL_Latin1_General_CP1_CI_AS
Description
H = High, M = Medium, L = Low
After script
CONSTRAINT [CK_Product_Class] CHECK (upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL)
Style
nchar
2
Collation
SQL_Latin1_General_CP1_CI_AS
Description
W = Womens, M = Mens, U = Universal
After script
CONSTRAINT [CK_Product_Style] CHECK (upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL)
ProductSubcategoryID
int
Description
Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID.
ProductModelID
int
Description
Product is a member of this product model. Foreign key to ProductModel.ProductModelID.
SellStartDate
datetime
Yes
Description
Date the product was available for sale.
SellEndDate
datetime
Description
Date the product was no longer available for sale.
DiscontinuedDate
datetime
Description
Date the product was discontinued.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_Product_ProductNumber
Column name
ProductNumber
Unique
Yes
Description
Unique nonclustered index.
AK_Product_Name
Column name
Name
Unique
Yes
Description
Unique nonclustered index.
AK_Product_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
Keys
PK_Product_ProductID
Column in PK_Product_ProductID
ProductID
Name
SalesTerritoryHistory
Description
Sales representative transfers to other sales territories.
Properties
Schema
Sales
After script
ALTER TABLE [Sales].[SalesTerritoryHistory] ADD CONSTRAINT [CK_SalesTerritoryHistory_EndDate] CHECK ([EndDate]>=[StartDate] OR [EndDate] IS NULL);
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL', 'schema', N'Sales', 'table', N'SalesTerritoryHistory', 'constraint', N'CK_SalesTerritoryHistory_EndDate';
GO
;
Columns
Name
Data type
Param
Key
Not Null
BusinessEntityID
int
Yes
Yes
Description
Primary key. The sales rep. Foreign key to SalesPerson.BusinessEntityID.
TerritoryID
int
Yes
Yes
Description
Primary key. Territory identification number. Foreign key to SalesTerritory.SalesTerritoryID.
StartDate
datetime
Yes
Yes
Description
Primary key. Date the sales representive started work in the territory.
EndDate
datetime
Description
Date the sales representative left work in the territory.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_SalesTerritoryHistory_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
Keys
PK_SalesTerritoryHistory_BusinessEntityID_StartDate_TerritoryID
Column in PK_SalesTerritoryHistory_BusinessEntityID_StartDate_TerritoryID
BusinessEntityID
Column in PK_SalesTerritoryHistory_BusinessEntityID_StartDate_TerritoryID
TerritoryID
Column in PK_SalesTerritoryHistory_BusinessEntityID_StartDate_TerritoryID
StartDate
Name
ScrapReason
Description
Manufacturing failure reasons lookup table.
Properties
Schema
Production
Columns
Name
Data type
Param
Key
Not Null
ScrapReasonID
smallint
Yes
Yes
Description
Primary key for ScrapReason records.
After script
IDENTITY(1,1)
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Failure description.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_ScrapReason_Name
Column name
Name
Unique
Yes
Description
Unique nonclustered index.
Keys
PK_ScrapReason_ScrapReasonID
Column in PK_ScrapReason_ScrapReasonID
ScrapReasonID
Name
Shift
Description
Work shift lookup table.
Properties
Schema
HumanResources
Columns
Name
Data type
Param
Key
Not Null
ShiftID
tinyint
Yes
Yes
Description
Primary key for Shift records.
After script
IDENTITY(1,1)
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Shift description.
StartTime
time
7
Yes
Description
Shift start time.
EndTime
time
7
Yes
Description
Shift end time.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_Shift_Name
Column name
Name
Unique
Yes
Description
Unique nonclustered index.
AK_Shift_StartTime_EndTime
Column name
StartTime
Column name
EndTime
Unique
Yes
Description
Unique nonclustered index.
Keys
PK_Shift_ShiftID
Column in PK_Shift_ShiftID
ShiftID
Name
ProductCategory
Description
High-level product categorization.
Properties
Schema
Production
Columns
Name
Data type
Param
Key
Not Null
ProductCategoryID
int
Yes
Yes
Description
Primary key for ProductCategory records.
After script
IDENTITY(1,1)
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Category description.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_ProductCategory_Name
Column name
Name
Unique
Yes
Description
Unique nonclustered index.
AK_ProductCategory_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
Keys
PK_ProductCategory_ProductCategoryID
Column in PK_ProductCategory_ProductCategoryID
ProductCategoryID
Name
ShipMethod
Description
Shipping company lookup table.
Properties
Schema
Purchasing
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [ShipBase] > (0.00)', 'schema', N'Purchasing', 'table', N'ShipMethod', 'constraint', N'CK_ShipMethod_ShipBase';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [ShipRate] > (0.00)', 'schema', N'Purchasing', 'table', N'ShipMethod', 'constraint', N'CK_ShipMethod_ShipRate';
GO
;
Columns
Name
Data type
Param
Key
Not Null
ShipMethodID
int
Yes
Yes
Description
Primary key for ShipMethod records.
After script
IDENTITY(1,1)
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Shipping company name.
ShipBase
money
Yes
Default value
((0.00))
Description
Minimum shipping charge.
After script
CONSTRAINT [CK_ShipMethod_ShipBase] CHECK ([ShipBase]>(0.00))
ShipRate
money
Yes
Default value
((0.00))
Description
Shipping charge per pound.
After script
CONSTRAINT [CK_ShipMethod_ShipRate] CHECK ([ShipRate]>(0.00))
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_ShipMethod_Name
Column name
Name
Unique
Yes
Description
Unique nonclustered index.
AK_ShipMethod_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
Keys
PK_ShipMethod_ShipMethodID
Column in PK_ShipMethod_ShipMethodID
ShipMethodID
Name
ProductCostHistory
Description
Changes in the cost of a product over time.
Properties
Schema
Production
After script
ALTER TABLE [Production].[ProductCostHistory] ADD CONSTRAINT [CK_ProductCostHistory_EndDate] CHECK ([EndDate]>=[StartDate] OR [EndDate] IS NULL);
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL', 'schema', N'Production', 'table', N'ProductCostHistory', 'constraint', N'CK_ProductCostHistory_EndDate';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [StandardCost] >= (0.00)', 'schema', N'Production', 'table', N'ProductCostHistory', 'constraint', N'CK_ProductCostHistory_StandardCost';
GO
;
Columns
Name
Data type
Param
Key
Not Null
ProductID
int
Yes
Yes
Description
Product identification number. Foreign key to Product.ProductID
StartDate
datetime
Yes
Yes
Description
Product cost start date.
EndDate
datetime
Description
Product cost end date.
StandardCost
money
Yes
Description
Standard cost of the product.
After script
CONSTRAINT [CK_ProductCostHistory_StandardCost] CHECK ([StandardCost]>=(0.00))
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Keys
PK_ProductCostHistory_ProductID_StartDate
Column in PK_ProductCostHistory_ProductID_StartDate
ProductID
Column in PK_ProductCostHistory_ProductID_StartDate
StartDate
Name
ProductDescription
Description
Product descriptions in several languages.
Properties
Schema
Production
Columns
Name
Data type
Param
Key
Not Null
ProductDescriptionID
int
Yes
Yes
Description
Primary key for ProductDescription records.
After script
IDENTITY(1,1)
Description
nvarchar
400
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Description of the product.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_ProductDescription_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
Keys
PK_ProductDescription_ProductDescriptionID
Column in PK_ProductDescription_ProductDescriptionID
ProductDescriptionID
Name
ShoppingCartItem
Description
Contains online customer orders until the order is submitted or cancelled.
Properties
Schema
Sales
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [Quantity] >= (1)', 'schema', N'Sales', 'table', N'ShoppingCartItem', 'constraint', N'CK_ShoppingCartItem_Quantity';
GO
;
Columns
Name
Data type
Param
Key
Not Null
ShoppingCartItemID
int
Yes
Yes
Description
Primary key for ShoppingCartItem records.
After script
IDENTITY(1,1)
ShoppingCartID
nvarchar
50
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Shopping cart identification number.
Quantity
int
Yes
Default value
((1))
Description
Product quantity ordered.
After script
CONSTRAINT [CK_ShoppingCartItem_Quantity] CHECK ([Quantity]>=(1))
ProductID
int
Yes
Description
Product ordered. Foreign key to Product.ProductID.
DateCreated
datetime
Yes
Default value
(getdate())
Description
Date the time the record was created.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
IX_ShoppingCartItem_ShoppingCartID_ProductID
Column name
ShoppingCartID
Column name
ProductID
Description
Nonclustered index.
Keys
PK_ShoppingCartItem_ShoppingCartItemID
Column in PK_ShoppingCartItem_ShoppingCartItemID
ShoppingCartItemID
Name
ProductDocument
Description
Cross-reference table mapping products to related product documents.
Properties
Schema
Production
Columns
Name
Data type
Param
Key
Not Null
ProductID
int
Yes
Yes
Description
Product identification number. Foreign key to Product.ProductID.
DocumentNode
hierarchyid
Yes
Yes
Description
Document identification number. Foreign key to Document.DocumentNode.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Keys
PK_ProductDocument_ProductID_DocumentNode
Column in PK_ProductDocument_ProductID_DocumentNode
ProductID
Column in PK_ProductDocument_ProductID_DocumentNode
DocumentNode
Name
DatabaseLog
Description
Audit table tracking all DDL changes made to the AdventureWorks database. Data is captured by the database trigger ddlDatabaseTriggerLog.
Properties
Schema
dbo
Columns
Name
Data type
Param
Key
Not Null
DatabaseLogID
int
Yes
Yes
Description
Primary key for DatabaseLog records.
After script
IDENTITY(1,1)
PostTime
datetime
Yes
Description
The date and time the DDL change occurred.
DatabaseUser
int
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
The user who implemented the DDL change.
Event
int
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
The type of DDL statement that was executed.
Schema
int
Collation
SQL_Latin1_General_CP1_CI_AS
Description
The schema to which the changed object belongs.
Object
int
Collation
SQL_Latin1_General_CP1_CI_AS
Description
The object that was changed by the DDL statment.
TSQL
nvarchar
max
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
The exact Transact-SQL statement that was executed.
XmlEvent
xml
Yes
Description
The raw XML data generated by database trigger.
Keys
PK_DatabaseLog_DatabaseLogID
Column in PK_DatabaseLog_DatabaseLogID
DatabaseLogID
Name
ProductInventory
Description
Product inventory information.
Properties
Schema
Production
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [Shelf] like ''[A-Za-z]'' OR [Shelf]=''N/A''', 'schema', N'Production', 'table', N'ProductInventory', 'constraint', N'CK_ProductInventory_Shelf';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [Bin] BETWEEN (0) AND (100)', 'schema', N'Production', 'table', N'ProductInventory', 'constraint', N'CK_ProductInventory_Bin';
GO
;
Columns
Name
Data type
Param
Key
Not Null
ProductID
int
Yes
Yes
Description
Product identification number. Foreign key to Product.ProductID.
LocationID
smallint
Yes
Yes
Description
Inventory location identification number. Foreign key to Location.LocationID.
Shelf
nvarchar
10
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Storage compartment within an inventory location.
After script
CONSTRAINT [CK_ProductInventory_Shelf] CHECK ([Shelf] like '[A-Za-z]' OR [Shelf]='N/A')
Bin
tinyint
Yes
Description
Storage container on a shelf in an inventory location.
After script
CONSTRAINT [CK_ProductInventory_Bin] CHECK ([Bin]>=(0) AND [Bin]<=(100))
Quantity
smallint
Yes
Default value
((0))
Description
Quantity of products in the inventory location.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Keys
PK_ProductInventory_ProductID_LocationID
Column in PK_ProductInventory_ProductID_LocationID
ProductID
Column in PK_ProductInventory_ProductID_LocationID
LocationID
Name
SpecialOffer
Description
Sale discounts lookup table.
Properties
Schema
Sales
After script
ALTER TABLE [Sales].[SpecialOffer] ADD CONSTRAINT [CK_SpecialOffer_EndDate] CHECK ([EndDate]>=[StartDate]);
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [EndDate] >= [StartDate]', 'schema', N'Sales', 'table', N'SpecialOffer', 'constraint', N'CK_SpecialOffer_EndDate';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [DiscountPct] >= (0.00)', 'schema', N'Sales', 'table', N'SpecialOffer', 'constraint', N'CK_SpecialOffer_DiscountPct';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [MinQty] >= (0)', 'schema', N'Sales', 'table', N'SpecialOffer', 'constraint', N'CK_SpecialOffer_MinQty';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [MaxQty] >= (0)', 'schema', N'Sales', 'table', N'SpecialOffer', 'constraint', N'CK_SpecialOffer_MaxQty';
GO
;
Columns
Name
Data type
Param
Key
Not Null
SpecialOfferID
int
Yes
Yes
Description
Primary key for SpecialOffer records.
After script
IDENTITY(1,1)
Description
nvarchar
255
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Discount description.
DiscountPct
smallmoney
Yes
Default value
((0.00))
Description
Discount precentage.
After script
CONSTRAINT [CK_SpecialOffer_DiscountPct] CHECK ([DiscountPct]>=(0.00))
Type
nvarchar
50
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Discount type category.
Category
nvarchar
50
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Group the discount applies to such as Reseller or Customer.
StartDate
datetime
Yes
Description
Discount start date.
EndDate
datetime
Yes
Description
Discount end date.
MinQty
int
Yes
Default value
((0))
Description
Minimum discount percent allowed.
After script
CONSTRAINT [CK_SpecialOffer_MinQty] CHECK ([MinQty]>=(0))
MaxQty
int
Description
Maximum discount percent allowed.
After script
CONSTRAINT [CK_SpecialOffer_MaxQty] CHECK ([MaxQty]>=(0))
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_SpecialOffer_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
Keys
PK_SpecialOffer_SpecialOfferID
Column in PK_SpecialOffer_SpecialOfferID
SpecialOfferID
Name
ErrorLog
Description
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.
Properties
Schema
dbo
Columns
Name
Data type
Param
Key
Not Null
ErrorLogID
int
Yes
Yes
Description
Primary key for ErrorLog records.
After script
IDENTITY(1,1)
ErrorTime
datetime
Yes
Default value
(getdate())
Description
The date and time at which the error occurred.
UserName
int
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
The user who executed the batch in which the error occurred.
ErrorNumber
int
Yes
Description
The error number of the error that occurred.
ErrorSeverity
int
Description
The severity of the error that occurred.
ErrorState
int
Description
The state number of the error that occurred.
ErrorProcedure
nvarchar
126
Collation
SQL_Latin1_General_CP1_CI_AS
Description
The name of the stored procedure or trigger where the error occurred.
ErrorLine
int
Description
The line number at which the error occurred.
ErrorMessage
nvarchar
4000
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
The message text of the error that occurred.
Keys
PK_ErrorLog_ErrorLogID
Column in PK_ErrorLog_ErrorLogID
ErrorLogID
Name
ProductListPriceHistory
Description
Changes in the list price of a product over time.
Properties
Schema
Production
After script
ALTER TABLE [Production].[ProductListPriceHistory] ADD CONSTRAINT [CK_ProductListPriceHistory_EndDate] CHECK ([EndDate]>=[StartDate] OR [EndDate] IS NULL);
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL', 'schema', N'Production', 'table', N'ProductListPriceHistory', 'constraint', N'CK_ProductListPriceHistory_EndDate';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [ListPrice] > (0.00)', 'schema', N'Production', 'table', N'ProductListPriceHistory', 'constraint', N'CK_ProductListPriceHistory_ListPrice';
GO
;
Columns
Name
Data type
Param
Key
Not Null
ProductID
int
Yes
Yes
Description
Product identification number. Foreign key to Product.ProductID
StartDate
datetime
Yes
Yes
Description
List price start date.
EndDate
datetime
Description
List price end date
ListPrice
money
Yes
Description
Product list price.
After script
CONSTRAINT [CK_ProductListPriceHistory_ListPrice] CHECK ([ListPrice]>(0.00))
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Keys
PK_ProductListPriceHistory_ProductID_StartDate
Column in PK_ProductListPriceHistory_ProductID_StartDate
ProductID
Column in PK_ProductListPriceHistory_ProductID_StartDate
StartDate
Name
Address
Description
Street address information for customers, employees, and vendors.
Properties
Schema
Person
Columns
Name
Data type
Param
Key
Not Null
AddressID
int
Yes
Yes
Description
Primary key for Address records.
After script
IDENTITY(1,1) NOT FOR REPLICATION
AddressLine1
nvarchar
60
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
First street address line.
AddressLine2
nvarchar
60
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Second street address line.
City
nvarchar
30
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Name of the city.
StateProvinceID
int
Yes
Description
Unique identification number for the state or province. Foreign key to StateProvince table.
PostalCode
nvarchar
15
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Postal code for the street address.
SpatialLocation
geography
Description
Latitude and longitude of this address.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_Address_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
Column name
AddressLine1
Column name
AddressLine2
Column name
City
Column name
StateProvinceID
Column name
PostalCode
Unique
Yes
Description
Nonclustered index.
IX_Address_StateProvinceID
Column name
StateProvinceID
Description
Nonclustered index.
Keys
PK_Address_AddressID
Column in PK_Address_AddressID
AddressID
Name
SpecialOfferProduct
Description
Cross-reference table mapping products to special offer discounts.
Properties
Schema
Sales
Columns
Name
Data type
Param
Key
Not Null
SpecialOfferID
int
Yes
Yes
Description
Primary key for SpecialOfferProduct records.
ProductID
int
Yes
Yes
Description
Product identification number. Foreign key to Product.ProductID.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_SpecialOfferProduct_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
IX_SpecialOfferProduct_ProductID
Column name
ProductID
Description
Nonclustered index.
Keys
PK_SpecialOfferProduct_SpecialOfferID_ProductID
Column in PK_SpecialOfferProduct_SpecialOfferID_ProductID
SpecialOfferID
Column in PK_SpecialOfferProduct_SpecialOfferID_ProductID
ProductID
Name
ProductModel
Description
Product model classification.
Properties
Schema
Production
Columns
Name
Data type
Param
Key
Not Null
ProductModelID
int
Yes
Yes
Description
Primary key for ProductModel records.
After script
IDENTITY(1,1)
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Product model description.
CatalogDescription
xml
Description
Detailed product catalog information in xml format.
Instructions
xml
Description
Manufacturing instructions in xml format.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_ProductModel_Name
Column name
Name
Unique
Yes
Description
Unique nonclustered index.
AK_ProductModel_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
PXML_ProductModel_CatalogDescription
Primary
Yes
Description
Primary XML index.
PXML_ProductModel_Instructions
Primary
Yes
Description
Primary XML index.
Keys
PK_ProductModel_ProductModelID
Column in PK_ProductModel_ProductModelID
ProductModelID
Name
AddressType
Description
Types of addresses stored in the Address table.
Properties
Schema
Person
Columns
Name
Data type
Param
Key
Not Null
AddressTypeID
int
Yes
Yes
Description
Primary key for AddressType records.
After script
IDENTITY(1,1)
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Address type description. For example, Billing, Home, or Shipping.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_AddressType_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
AK_AddressType_Name
Column name
Name
Unique
Yes
Description
Unique nonclustered index.
Keys
PK_AddressType_AddressTypeID
Column in PK_AddressType_AddressTypeID
AddressTypeID
Name
StateProvince
Description
State and province lookup table.
Properties
Schema
Person
Columns
Name
Data type
Param
Key
Not Null
StateProvinceID
int
Yes
Yes
Description
Primary key for StateProvince records.
After script
IDENTITY(1,1)
StateProvinceCode
nchar
3
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
ISO standard state or province code.
CountryRegionCode
nvarchar
3
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.
IsOnlyStateProvinceFlag
Yes
Default value
((1))
Description
0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode.
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
State or province description.
TerritoryID
int
Yes
Description
ID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_StateProvince_Name
Column name
Name
Unique
Yes
Description
Unique nonclustered index.
AK_StateProvince_StateProvinceCode_CountryRegionCode
Column name
StateProvinceCode
Column name
CountryRegionCode
Unique
Yes
Description
Unique nonclustered index.
AK_StateProvince_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
Keys
PK_StateProvince_StateProvinceID
Column in PK_StateProvince_StateProvinceID
StateProvinceID
Name
ProductModelIllustration
Description
Cross-reference table mapping product models and illustrations.
Properties
Schema
Production
Columns
Name
Data type
Param
Key
Not Null
ProductModelID
int
Yes
Yes
Description
Primary key. Foreign key to ProductModel.ProductModelID.
IllustrationID
int
Yes
Yes
Description
Primary key. Foreign key to Illustration.IllustrationID.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Keys
PK_ProductModelIllustration_ProductModelID_IllustrationID
Column in PK_ProductModelIllustration_ProductModelID_IllustrationID
ProductModelID
Column in PK_ProductModelIllustration_ProductModelID_IllustrationID
IllustrationID
Name
AWBuildVersion
Description
Current version number of the AdventureWorks 2016 sample database.
Properties
Schema
dbo
Columns
Name
Data type
Param
Key
Not Null
SystemInformationID
tinyint
Yes
Yes
Description
Primary key for AWBuildVersion records.
After script
IDENTITY(1,1)
Database Version
nvarchar
25
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Version number of the database in 9.yy.mm.dd.00 format.
VersionDate
datetime
Yes
Description
Date and time the record was last updated.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Keys
PK_AWBuildVersion_SystemInformationID
Column in PK_AWBuildVersion_SystemInformationID
SystemInformationID
Name
ProductModelProductDescriptionCulture
Description
Cross-reference table mapping product descriptions and the language the description is written in.
Properties
Schema
Production
Columns
Name
Data type
Param
Key
Not Null
ProductModelID
int
Yes
Yes
Description
Primary key. Foreign key to ProductModel.ProductModelID.
ProductDescriptionID
int
Yes
Yes
Description
Primary key. Foreign key to ProductDescription.ProductDescriptionID.
CultureID
nchar
6
Yes
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Culture identification number. Foreign key to Culture.CultureID.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Keys
PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID
Column in PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID
ProductModelID
Column in PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID
ProductDescriptionID
Column in PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID
CultureID
Name
BillOfMaterials
Description
Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.
Properties
Schema
Production
After script
ALTER TABLE [Production].[BillOfMaterials] ADD CONSTRAINT [CK_BillOfMaterials_EndDate] CHECK ([EndDate]>[StartDate] OR [EndDate] IS NULL);
ALTER TABLE [Production].[BillOfMaterials] ADD CONSTRAINT [CK_BillOfMaterials_ProductAssemblyID] CHECK ([ProductAssemblyID]<>[ComponentID]);
ALTER TABLE [Production].[BillOfMaterials] ADD CONSTRAINT [CK_BillOfMaterials_BOMLevel] CHECK ([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1));
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint EndDate] > [StartDate] OR [EndDate] IS NULL', 'schema', N'Production', 'table', N'BillOfMaterials', 'constraint', N'CK_BillOfMaterials_EndDate';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [ProductAssemblyID] <> [ComponentID]', 'schema', N'Production', 'table', N'BillOfMaterials', 'constraint', N'CK_BillOfMaterials_ProductAssemblyID';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [ProductAssemblyID] IS NULL AND [BOMLevel] = (0) AND [PerAssemblyQty] = (1) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel] >= (1)', 'schema', N'Production', 'table', N'BillOfMaterials', 'constraint', N'CK_BillOfMaterials_BOMLevel';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [PerAssemblyQty] >= (1.00)', 'schema', N'Production', 'table', N'BillOfMaterials', 'constraint', N'CK_BillOfMaterials_PerAssemblyQty';
GO
;
Columns
Name
Data type
Param
Key
Not Null
BillOfMaterialsID
int
Yes
Yes
Description
Primary key for BillOfMaterials records.
After script
IDENTITY(1,1)
ProductAssemblyID
int
Description
Parent product identification number. Foreign key to Product.ProductID.
ComponentID
int
Yes
Description
Component identification number. Foreign key to Product.ProductID.
StartDate
datetime
Yes
Default value
(getdate())
Description
Date the component started being used in the assembly item.
EndDate
datetime
Description
Date the component stopped being used in the assembly item.
UnitMeasureCode
nchar
3
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Standard code identifying the unit of measure for the quantity.
BOMLevel
smallint
Yes
Description
Indicates the depth the component is from its parent (AssemblyID).
PerAssemblyQty
decimal
8,2
Yes
Default value
((1.00))
Description
Quantity of the component needed to create the assembly.
After script
CONSTRAINT [CK_BillOfMaterials_PerAssemblyQty] CHECK ([PerAssemblyQty]>=(1.00))
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
Column name
ProductAssemblyID
Column name
ComponentID
Column name
StartDate
Unique
Yes
Clustered
Yes
Description
Clustered index.
IX_BillOfMaterials_UnitMeasureCode
Column name
UnitMeasureCode
Description
Nonclustered index.
Keys
PK_BillOfMaterials_BillOfMaterialsID
Column in PK_BillOfMaterials_BillOfMaterialsID
BillOfMaterialsID
Name
Store
Description
Customers (resellers) of Adventure Works products.
Properties
Schema
Sales
Columns
Name
Data type
Param
Key
Not Null
BusinessEntityID
int
Yes
Yes
Description
Primary key. Foreign key to Customer.BusinessEntityID.
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Name of the store.
SalesPersonID
int
Description
ID of the sales person assigned to the customer. Foreign key to SalesPerson.BusinessEntityID.
Demographics
xml
Description
Demographic informationg about the store such as the number of employees, annual sales and store type.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_Store_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
IX_Store_SalesPersonID
Column name
SalesPersonID
Description
Nonclustered index.
PXML_Store_Demographics
Primary
Yes
Description
Primary XML index.
Keys
PK_Store_BusinessEntityID
Column in PK_Store_BusinessEntityID
BusinessEntityID
Name
ProductPhoto
Description
Product images.
Properties
Schema
Production
Columns
Name
Data type
Param
Key
Not Null
ProductPhotoID
int
Yes
Yes
Description
Primary key for ProductPhoto records.
After script
IDENTITY(1,1)
ThumbNailPhoto
varbinary
max
Description
Small image of the product.
ThumbnailPhotoFileName
nvarchar
50
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Small image file name.
LargePhoto
varbinary
max
Description
Large image of the product.
LargePhotoFileName
nvarchar
50
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Large image file name.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Keys
PK_ProductPhoto_ProductPhotoID
Column in PK_ProductPhoto_ProductPhotoID
ProductPhotoID
Name
ProductProductPhoto
Description
Cross-reference table mapping products and product photos.
Properties
Schema
Production
Columns
Name
Data type
Param
Key
Not Null
ProductID
int
Yes
Yes
Description
Product identification number. Foreign key to Product.ProductID.
ProductPhotoID
int
Yes
Yes
Description
Product photo identification number. Foreign key to ProductPhoto.ProductPhotoID.
Primary
Yes
Default value
((0))
Description
0 = Photo is not the principal image. 1 = Photo is the principal image.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Keys
PK_ProductProductPhoto_ProductID_ProductPhotoID
Column in PK_ProductProductPhoto_ProductID_ProductPhotoID
ProductID
Column in PK_ProductProductPhoto_ProductID_ProductPhotoID
ProductPhotoID
Name
TransactionHistory
Description
Record of each purchase order, sales order, or work order transaction year to date.
Properties
Schema
Production
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [TransactionType]=''p'' OR [TransactionType]=''s'' OR [TransactionType]=''w'' OR [TransactionType]=''P'' OR [TransactionType]=''S'' OR [TransactionType]=''W'')', 'schema', N'Production', 'table', N'TransactionHistory', 'constraint', N'CK_TransactionHistory_TransactionType';
GO
;
Columns
Name
Data type
Param
Key
Not Null
TransactionID
int
Yes
Yes
Description
Primary key for TransactionHistory records.
After script
IDENTITY(100000,1)
ProductID
int
Yes
Description
Product identification number. Foreign key to Product.ProductID.
ReferenceOrderID
int
Yes
Description
Purchase order, sales order, or work order identification number.
ReferenceOrderLineID
int
Yes
Default value
((0))
Description
Line number associated with the purchase order, sales order, or work order.
TransactionDate
datetime
Yes
Default value
(getdate())
Description
Date and time of the transaction.
TransactionType
nchar
1
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
W = WorkOrder, S = SalesOrder, P = PurchaseOrder
After script
CONSTRAINT [CK_TransactionHistory_TransactionType] CHECK (upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')
Quantity
int
Yes
Description
Product quantity.
ActualCost
money
Yes
Description
Product cost.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
IX_TransactionHistory_ProductID
Column name
ProductID
Description
Nonclustered index.
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
Column name
ReferenceOrderID
Column name
ReferenceOrderLineID
Description
Nonclustered index.
Keys
PK_TransactionHistory_TransactionID
Column in PK_TransactionHistory_TransactionID
TransactionID
Name
ProductReview
Description
Customer reviews of products they have purchased.
Properties
Schema
Production
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [Rating] BETWEEN (1) AND (5)', 'schema', N'Production', 'table', N'ProductReview', 'constraint', N'CK_ProductReview_Rating';
GO
;
Columns
Name
Data type
Param
Key
Not Null
ProductReviewID
int
Yes
Yes
Description
Primary key for ProductReview records.
After script
IDENTITY(1,1)
ProductID
int
Yes
Description
Product identification number. Foreign key to Product.ProductID.
ReviewerName
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Name of the reviewer.
ReviewDate
datetime
Yes
Default value
(getdate())
Description
Date review was submitted.
EmailAddress
nvarchar
50
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Reviewer's e-mail address.
Rating
int
Yes
Description
Product rating given by the reviewer. Scale is 1 to 5 with 5 as the highest rating.
After script
CONSTRAINT [CK_ProductReview_Rating] CHECK ([Rating]>=(1) AND [Rating]<=(5))
Comments
nvarchar
3850
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Reviewer's comments
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
IX_ProductReview_ProductID_Name
Column name
Comments
Column name
ProductID
Column name
ReviewerName
Description
Nonclustered index.
Keys
PK_ProductReview_ProductReviewID
Column in PK_ProductReview_ProductReviewID
ProductReviewID
Name
BusinessEntity
Description
Source of the ID that connects vendors, customers, and employees with address and contact information.
Properties
Schema
Person
Columns
Name
Data type
Param
Key
Not Null
BusinessEntityID
int
Yes
Yes
Description
Primary key for all customers, vendors, and employees.
After script
IDENTITY(1,1) NOT FOR REPLICATION
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_BusinessEntity_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
Keys
PK_BusinessEntity_BusinessEntityID
Column in PK_BusinessEntity_BusinessEntityID
BusinessEntityID
Name
TransactionHistoryArchive
Description
Transactions for previous years.
Properties
Schema
Production
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [TransactionType]=''p'' OR [TransactionType]=''s'' OR [TransactionType]=''w'' OR [TransactionType]=''P'' OR [TransactionType]=''S'' OR [TransactionType]=''W''', 'schema', N'Production', 'table', N'TransactionHistoryArchive', 'constraint', N'CK_TransactionHistoryArchive_TransactionType';
GO
;
Columns
Name
Data type
Param
Key
Not Null
TransactionID
int
Yes
Yes
Description
Primary key for TransactionHistoryArchive records.
ProductID
int
Yes
Description
Product identification number. Foreign key to Product.ProductID.
ReferenceOrderID
int
Yes
Description
Purchase order, sales order, or work order identification number.
ReferenceOrderLineID
int
Yes
Default value
((0))
Description
Line number associated with the purchase order, sales order, or work order.
TransactionDate
datetime
Yes
Default value
(getdate())
Description
Date and time of the transaction.
TransactionType
nchar
1
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
W = Work Order, S = Sales Order, P = Purchase Order
After script
CONSTRAINT [CK_TransactionHistoryArchive_TransactionType] CHECK (upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')
Quantity
int
Yes
Description
Product quantity.
ActualCost
money
Yes
Description
Product cost.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
IX_TransactionHistoryArchive_ProductID
Column name
ProductID
Description
Nonclustered index.
IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID
Column name
ReferenceOrderID
Column name
ReferenceOrderLineID
Description
Nonclustered index.
Keys
PK_TransactionHistoryArchive_TransactionID
Column in PK_TransactionHistoryArchive_TransactionID
TransactionID
Name
ProductSubcategory
Description
Product subcategories. See ProductCategory table.
Properties
Schema
Production
Columns
Name
Data type
Param
Key
Not Null
ProductSubcategoryID
int
Yes
Yes
Description
Primary key for ProductSubcategory records.
After script
IDENTITY(1,1)
ProductCategoryID
int
Yes
Description
Product category identification number. Foreign key to ProductCategory.ProductCategoryID.
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Subcategory description.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_ProductSubcategory_Name
Column name
Name
Unique
Yes
Description
Unique nonclustered index.
AK_ProductSubcategory_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
Keys
PK_ProductSubcategory_ProductSubcategoryID
Column in PK_ProductSubcategory_ProductSubcategoryID
ProductSubcategoryID
Name
BusinessEntityAddress
Description
Cross-reference table mapping customers, vendors, and employees to their addresses.
Properties
Schema
Person
Columns
Name
Data type
Param
Key
Not Null
BusinessEntityID
int
Yes
Yes
Description
Primary key. Foreign key to BusinessEntity.BusinessEntityID.
AddressID
int
Yes
Yes
Description
Primary key. Foreign key to Address.AddressID.
AddressTypeID
int
Yes
Yes
Description
Primary key. Foreign key to AddressType.AddressTypeID.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_BusinessEntityAddress_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
IX_BusinessEntityAddress_AddressID
Column name
AddressID
Description
Nonclustered index.
IX_BusinessEntityAddress_AddressTypeID
Column name
AddressTypeID
Description
Nonclustered index.
Keys
PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID
Column in PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID
BusinessEntityID
Column in PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID
AddressID
Column in PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID
AddressTypeID
Name
ProductVendor
Description
Cross-reference table mapping vendors with the products they supply.
Properties
Schema
Purchasing
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [AverageLeadTime] >= (1)', 'schema', N'Purchasing', 'table', N'ProductVendor', 'constraint', N'CK_ProductVendor_AverageLeadTime';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [StandardPrice] > (0.00)', 'schema', N'Purchasing', 'table', N'ProductVendor', 'constraint', N'CK_ProductVendor_StandardPrice';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [LastReceiptCost] > (0.00)', 'schema', N'Purchasing', 'table', N'ProductVendor', 'constraint', N'CK_ProductVendor_LastReceiptCost';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [MinOrderQty] >= (1)', 'schema', N'Purchasing', 'table', N'ProductVendor', 'constraint', N'CK_ProductVendor_MinOrderQty';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [MaxOrderQty] >= (1)', 'schema', N'Purchasing', 'table', N'ProductVendor', 'constraint', N'CK_ProductVendor_MaxOrderQty';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [OnOrderQty] >= (0)', 'schema', N'Purchasing', 'table', N'ProductVendor', 'constraint', N'CK_ProductVendor_OnOrderQty';
GO
;
Columns
Name
Data type
Param
Key
Not Null
ProductID
int
Yes
Yes
Description
Primary key. Foreign key to Product.ProductID.
BusinessEntityID
int
Yes
Yes
Description
Primary key. Foreign key to Vendor.BusinessEntityID.
AverageLeadTime
int
Yes
Description
The average span of time (in days) between placing an order with the vendor and receiving the purchased product.
After script
CONSTRAINT [CK_ProductVendor_AverageLeadTime] CHECK ([AverageLeadTime]>=(1))
StandardPrice
money
Yes
Description
The vendor's usual selling price.
After script
CONSTRAINT [CK_ProductVendor_StandardPrice] CHECK ([StandardPrice]>(0.00))
LastReceiptCost
money
Description
The selling price when last purchased.
After script
CONSTRAINT [CK_ProductVendor_LastReceiptCost] CHECK ([LastReceiptCost]>(0.00))
LastReceiptDate
datetime
Description
Date the product was last received by the vendor.
MinOrderQty
int
Yes
Description
The maximum quantity that should be ordered.
After script
CONSTRAINT [CK_ProductVendor_MinOrderQty] CHECK ([MinOrderQty]>=(1))
MaxOrderQty
int
Yes
Description
The minimum quantity that should be ordered.
After script
CONSTRAINT [CK_ProductVendor_MaxOrderQty] CHECK ([MaxOrderQty]>=(1))
OnOrderQty
int
Description
The quantity currently on order.
After script
CONSTRAINT [CK_ProductVendor_OnOrderQty] CHECK ([OnOrderQty]>=(0))
UnitMeasureCode
nchar
3
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
The product's unit of measure.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
IX_ProductVendor_UnitMeasureCode
Column name
UnitMeasureCode
Description
Nonclustered index.
IX_ProductVendor_BusinessEntityID
Column name
BusinessEntityID
Description
Nonclustered index.
Keys
PK_ProductVendor_ProductID_BusinessEntityID
Column in PK_ProductVendor_ProductID_BusinessEntityID
ProductID
Column in PK_ProductVendor_ProductID_BusinessEntityID
BusinessEntityID
Name
BusinessEntityContact
Description
Cross-reference table mapping stores, vendors, and employees to people
Properties
Schema
Person
Columns
Name
Data type
Param
Key
Not Null
BusinessEntityID
int
Yes
Yes
Description
Primary key. Foreign key to BusinessEntity.BusinessEntityID.
PersonID
int
Yes
Yes
Description
Primary key. Foreign key to Person.BusinessEntityID.
ContactTypeID
int
Yes
Yes
Description
Primary key. Foreign key to ContactType.ContactTypeID.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_BusinessEntityContact_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
IX_BusinessEntityContact_PersonID
Column name
PersonID
Description
Nonclustered index.
IX_BusinessEntityContact_ContactTypeID
Column name
ContactTypeID
Description
Nonclustered index.
Keys
PK_BusinessEntityContact_BusinessEntityID_PersonID_ContactTypeID
Column in PK_BusinessEntityContact_BusinessEntityID_PersonID_ContactTypeID
BusinessEntityID
Column in PK_BusinessEntityContact_BusinessEntityID_PersonID_ContactTypeID
PersonID
Column in PK_BusinessEntityContact_BusinessEntityID_PersonID_ContactTypeID
ContactTypeID
Name
UnitMeasure
Description
Unit of measure lookup table.
Properties
Schema
Production
Columns
Name
Data type
Param
Key
Not Null
UnitMeasureCode
nchar
3
Yes
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Primary key.
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Unit of measure description.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_UnitMeasure_Name
Column name
Name
Unique
Yes
Description
Unique nonclustered index.
Keys
PK_UnitMeasure_UnitMeasureCode
Column in PK_UnitMeasure_UnitMeasureCode
UnitMeasureCode
Name
Vendor
Description
Companies from whom Adventure Works Cycles purchases parts or other goods.
Properties
Schema
Purchasing
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [CreditRating] BETWEEN (1) AND (5)', 'schema', N'Purchasing', 'table', N'Vendor', 'constraint', N'CK_Vendor_CreditRating';
GO
;
Columns
Name
Data type
Param
Key
Not Null
BusinessEntityID
int
Yes
Yes
Description
Primary key for Vendor records. Foreign key to BusinessEntity.BusinessEntityID
AccountNumber
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Vendor account (identification) number.
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Company name.
CreditRating
tinyint
Yes
Description
1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average
After script
CONSTRAINT [CK_Vendor_CreditRating] CHECK ([CreditRating]>=(1) AND [CreditRating]<=(5))
PreferredVendorStatus
Yes
Default value
((1))
Description
0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.
ActiveFlag
Yes
Default value
((1))
Description
0 = Vendor no longer used. 1 = Vendor is actively used.
PurchasingWebServiceURL
nvarchar
1024
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Vendor URL.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_Vendor_AccountNumber
Column name
AccountNumber
Unique
Yes
Description
Unique nonclustered index.
Keys
PK_Vendor_BusinessEntityID
Column in PK_Vendor_BusinessEntityID
BusinessEntityID
Lines
Name
ContactType
Description
Lookup table containing the types of business entity contacts.
Properties
Schema
Person
Columns
Name
Data type
Param
Key
Not Null
ContactTypeID
int
Yes
Yes
Description
Primary key for ContactType records.
After script
IDENTITY(1,1)
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Contact type description.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_ContactType_Name
Column name
Name
Unique
Yes
Description
Unique nonclustered index.
Keys
PK_ContactType_ContactTypeID
Column in PK_ContactType_ContactTypeID
ContactTypeID
Name
CountryRegionCurrency
Description
Cross-reference table mapping ISO currency codes to a country or region.
Properties
Schema
Sales
Columns
Name
Data type
Param
Key
Not Null
CountryRegionCode
nvarchar
3
Yes
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
ISO code for countries and regions. Foreign key to CountryRegion.CountryRegionCode.
CurrencyCode
nchar
3
Yes
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
ISO standard currency code. Foreign key to Currency.CurrencyCode.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
IX_CountryRegionCurrency_CurrencyCode
Column name
CurrencyCode
Description
Nonclustered index.
Keys
PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode
Column in PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode
CountryRegionCode
Column in PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode
CurrencyCode
Name
CountryRegion
Description
Lookup table containing the ISO standard codes for countries and regions.
Properties
Schema
Person
Columns
Name
Data type
Param
Key
Not Null
CountryRegionCode
nvarchar
3
Yes
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
ISO standard code for countries and regions.
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Country or region name.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_CountryRegion_Name
Column name
Name
Unique
Yes
Description
Unique nonclustered index.
Keys
PK_CountryRegion_CountryRegionCode
Column in PK_CountryRegion_CountryRegionCode
CountryRegionCode
Name
WorkOrder
Description
Manufacturing work orders.
Properties
Schema
Production
After script
ALTER TABLE [Production].[WorkOrder] ADD CONSTRAINT [CK_WorkOrder_EndDate] CHECK ([EndDate]>=[StartDate] OR [EndDate] IS NULL);
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [OrderQty] > (0)', 'schema', N'Production', 'table', N'WorkOrder', 'constraint', N'CK_WorkOrder_OrderQty';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [ScrappedQty] >= (0)', 'schema', N'Production', 'table', N'WorkOrder', 'constraint', N'CK_WorkOrder_ScrappedQty';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL', 'schema', N'Production', 'table', N'WorkOrder', 'constraint', N'CK_WorkOrder_EndDate';
GO
;
Columns
Name
Data type
Param
Key
Not Null
WorkOrderID
int
Yes
Yes
Description
Primary key for WorkOrder records.
After script
IDENTITY(1,1)
ProductID
int
Yes
Description
Product identification number. Foreign key to Product.ProductID.
OrderQty
int
Yes
Description
Product quantity to build.
After script
CONSTRAINT [CK_WorkOrder_OrderQty] CHECK ([OrderQty]>(0))
StockedQty
int
Yes
Computed
(isnull([OrderQty]-[ScrappedQty],(0)))
Description
Quantity built and put in inventory.
ScrappedQty
smallint
Yes
Description
Quantity that failed inspection.
After script
CONSTRAINT [CK_WorkOrder_ScrappedQty] CHECK ([ScrappedQty]>=(0))
StartDate
datetime
Yes
Description
Work order start date.
EndDate
datetime
Description
Work order end date.
DueDate
datetime
Yes
Description
Work order due date.
ScrapReasonID
smallint
Description
Reason for inspection failure.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
IX_WorkOrder_ScrapReasonID
Column name
ScrapReasonID
Description
Nonclustered index.
IX_WorkOrder_ProductID
Column name
ProductID
Description
Nonclustered index.
Keys
PK_WorkOrder_WorkOrderID
Column in PK_WorkOrder_WorkOrderID
WorkOrderID
Name
PurchaseOrderDetail
Description
Individual products associated with a specific purchase order. See PurchaseOrderHeader.
Properties
Schema
Purchasing
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [OrderQty] > (0)', 'schema', N'Purchasing', 'table', N'PurchaseOrderDetail', 'constraint', N'CK_PurchaseOrderDetail_OrderQty';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [UnitPrice] >= (0.00)', 'schema', N'Purchasing', 'table', N'PurchaseOrderDetail', 'constraint', N'CK_PurchaseOrderDetail_UnitPrice';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [ReceivedQty] >= (0.00)', 'schema', N'Purchasing', 'table', N'PurchaseOrderDetail', 'constraint', N'CK_PurchaseOrderDetail_ReceivedQty';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [RejectedQty] >= (0.00)', 'schema', N'Purchasing', 'table', N'PurchaseOrderDetail', 'constraint', N'CK_PurchaseOrderDetail_RejectedQty';
GO
;
Columns
Name
Data type
Param
Key
Not Null
PurchaseOrderID
int
Yes
Yes
Description
Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
PurchaseOrderDetailID
int
Yes
Yes
Description
Primary key. One line number per purchased product.
After script
IDENTITY(1,1)
DueDate
datetime
Yes
Description
Date the product is expected to be received.
OrderQty
smallint
Yes
Description
Quantity ordered.
After script
CONSTRAINT [CK_PurchaseOrderDetail_OrderQty] CHECK ([OrderQty]>(0))
ProductID
int
Yes
Description
Product identification number. Foreign key to Product.ProductID.
UnitPrice
money
Yes
Description
Vendor's selling price of a single product.
After script
CONSTRAINT [CK_PurchaseOrderDetail_UnitPrice] CHECK ([UnitPrice]>=(0.00))
LineTotal
money
Yes
Computed
(isnull([OrderQty]*[UnitPrice],(0.00)))
Description
Per product subtotal. Computed as OrderQty * UnitPrice.
ReceivedQty
decimal
8,2
Yes
Description
Quantity actually received from the vendor.
After script
CONSTRAINT [CK_PurchaseOrderDetail_ReceivedQty] CHECK ([ReceivedQty]>=(0.00))
RejectedQty
decimal
8,2
Yes
Description
Quantity rejected during inspection.
After script
CONSTRAINT [CK_PurchaseOrderDetail_RejectedQty] CHECK ([RejectedQty]>=(0.00))
StockedQty
decimal
9,2
Yes
Computed
(isnull([ReceivedQty]-[RejectedQty],(0.00)))
Description
Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
IX_PurchaseOrderDetail_ProductID
Column name
ProductID
Description
Nonclustered index.
Keys
PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
Column in PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
PurchaseOrderID
Column in PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
PurchaseOrderDetailID
Name
CreditCard
Description
Customer credit card information.
Properties
Schema
Sales
Columns
Name
Data type
Param
Key
Not Null
CreditCardID
int
Yes
Yes
Description
Primary key for CreditCard records.
After script
IDENTITY(1,1)
CardType
nvarchar
50
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Credit card name.
CardNumber
nvarchar
25
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Credit card number.
ExpMonth
tinyint
Yes
Description
Credit card expiration month.
ExpYear
smallint
Yes
Description
Credit card expiration year.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_CreditCard_CardNumber
Column name
CardNumber
Unique
Yes
Description
Unique nonclustered index.
Keys
PK_CreditCard_CreditCardID
Column in PK_CreditCard_CreditCardID
CreditCardID
Name
Culture
Description
Lookup table containing the languages in which some AdventureWorks data is stored.
Properties
Schema
Production
Columns
Name
Data type
Param
Key
Not Null
CultureID
nchar
6
Yes
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Primary key for Culture records.
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Culture description.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_Culture_Name
Column name
Name
Unique
Yes
Description
Unique nonclustered index.
Keys
PK_Culture_CultureID
Column in PK_Culture_CultureID
CultureID
Name
WorkOrderRouting
Description
Work order details.
Properties
Schema
Production
After script
ALTER TABLE [Production].[WorkOrderRouting] ADD CONSTRAINT [CK_WorkOrderRouting_ScheduledEndDate] CHECK ([ScheduledEndDate]>=[ScheduledStartDate]);
ALTER TABLE [Production].[WorkOrderRouting] ADD CONSTRAINT [CK_WorkOrderRouting_ActualEndDate] CHECK ([ActualEndDate]>=[ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL);
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [ScheduledEndDate] >= [ScheduledStartDate]', 'schema', N'Production', 'table', N'WorkOrderRouting', 'constraint', N'CK_WorkOrderRouting_ScheduledEndDate';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [ActualEndDate] >= [ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL', 'schema', N'Production', 'table', N'WorkOrderRouting', 'constraint', N'CK_WorkOrderRouting_ActualEndDate';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [ActualResourceHrs] >= (0.0000)', 'schema', N'Production', 'table', N'WorkOrderRouting', 'constraint', N'CK_WorkOrderRouting_ActualResourceHrs';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [PlannedCost] > (0.00)', 'schema', N'Production', 'table', N'WorkOrderRouting', 'constraint', N'CK_WorkOrderRouting_PlannedCost';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [ActualCost] > (0.00)', 'schema', N'Production', 'table', N'WorkOrderRouting', 'constraint', N'CK_WorkOrderRouting_ActualCost';
GO
;
Columns
Name
Data type
Param
Key
Not Null
WorkOrderID
int
Yes
Yes
Description
Primary key. Foreign key to WorkOrder.WorkOrderID.
ProductID
int
Yes
Yes
Description
Primary key. Foreign key to Product.ProductID.
OperationSequence
smallint
Yes
Yes
Description
Primary key. Indicates the manufacturing process sequence.
LocationID
smallint
Yes
Description
Manufacturing location where the part is processed. Foreign key to Location.LocationID.
ScheduledStartDate
datetime
Yes
Description
Planned manufacturing start date.
ScheduledEndDate
datetime
Yes
Description
Planned manufacturing end date.
ActualStartDate
datetime
Description
Actual start date.
ActualEndDate
datetime
Description
Actual end date.
ActualResourceHrs
decimal
9,4
Description
Number of manufacturing hours used.
After script
CONSTRAINT [CK_WorkOrderRouting_ActualResourceHrs] CHECK ([ActualResourceHrs]>=(0.0000))
PlannedCost
money
Yes
Description
Estimated manufacturing cost.
After script
CONSTRAINT [CK_WorkOrderRouting_PlannedCost] CHECK ([PlannedCost]>(0.00))
ActualCost
money
Description
Actual manufacturing cost.
After script
CONSTRAINT [CK_WorkOrderRouting_ActualCost] CHECK ([ActualCost]>(0.00))
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
IX_WorkOrderRouting_ProductID
Column name
ProductID
Description
Nonclustered index.
Keys
PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence
Column in PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence
WorkOrderID
Column in PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence
ProductID
Column in PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence
OperationSequence
Name
Currency
Description
Lookup table containing standard ISO currencies.
Properties
Schema
Sales
Columns
Name
Data type
Param
Key
Not Null
CurrencyCode
nchar
3
Yes
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
The ISO code for the Currency.
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Currency name.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_Currency_Name
Column name
Name
Unique
Yes
Description
Unique nonclustered index.
Keys
PK_Currency_CurrencyCode
Column in PK_Currency_CurrencyCode
CurrencyCode
Name
PurchaseOrderHeader
Description
General purchase order information. See PurchaseOrderDetail.
Properties
Schema
Purchasing
After script
ALTER TABLE [Purchasing].[PurchaseOrderHeader] ADD CONSTRAINT [CK_PurchaseOrderHeader_ShipDate] CHECK ([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL);
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [Status] BETWEEN (1) AND (4)', 'schema', N'Purchasing', 'table', N'PurchaseOrderHeader', 'constraint', N'CK_PurchaseOrderHeader_Status';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL', 'schema', N'Purchasing', 'table', N'PurchaseOrderHeader', 'constraint', N'CK_PurchaseOrderHeader_ShipDate';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [SubTotal] >= (0.00)', 'schema', N'Purchasing', 'table', N'PurchaseOrderHeader', 'constraint', N'CK_PurchaseOrderHeader_SubTotal';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [TaxAmt] >= (0.00)', 'schema', N'Purchasing', 'table', N'PurchaseOrderHeader', 'constraint', N'CK_PurchaseOrderHeader_TaxAmt';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [Freight] >= (0.00)', 'schema', N'Purchasing', 'table', N'PurchaseOrderHeader', 'constraint', N'CK_PurchaseOrderHeader_Freight';
GO
;
Columns
Name
Data type
Param
Key
Not Null
PurchaseOrderID
int
Yes
Yes
Description
Primary key.
After script
IDENTITY(1,1)
RevisionNumber
tinyint
Yes
Default value
((0))
Description
Incremental number to track changes to the purchase order over time.
Status
tinyint
Yes
Default value
((1))
Description
Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete
After script
CONSTRAINT [CK_PurchaseOrderHeader_Status] CHECK ([Status]>=(1) AND [Status]<=(4))
EmployeeID
int
Yes
Description
Employee who created the purchase order. Foreign key to Employee.BusinessEntityID.
VendorID
int
Yes
Description
Vendor with whom the purchase order is placed. Foreign key to Vendor.BusinessEntityID.
ShipMethodID
int
Yes
Description
Shipping method. Foreign key to ShipMethod.ShipMethodID.
OrderDate
datetime
Yes
Default value
(getdate())
Description
Purchase order creation date.
ShipDate
datetime
Description
Estimated shipment date from the vendor.
SubTotal
money
Yes
Default value
((0.00))
Description
Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.
After script
CONSTRAINT [CK_PurchaseOrderHeader_SubTotal] CHECK ([SubTotal]>=(0.00))
TaxAmt
money
Yes
Default value
((0.00))
Description
Tax amount.
After script
CONSTRAINT [CK_PurchaseOrderHeader_TaxAmt] CHECK ([TaxAmt]>=(0.00))
Freight
money
Yes
Default value
((0.00))
Description
Shipping cost.
After script
CONSTRAINT [CK_PurchaseOrderHeader_Freight] CHECK ([Freight]>=(0.00))
TotalDue
money
Yes
Computed
(isnull(([SubTotal]+[TaxAmt])+[Freight],(0))) PERSISTED
Description
Total due to vendor. Computed as Subtotal + TaxAmt + Freight.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
IX_PurchaseOrderHeader_VendorID
Column name
VendorID
Description
Nonclustered index.
IX_PurchaseOrderHeader_EmployeeID
Column name
EmployeeID
Description
Nonclustered index.
Keys
PK_PurchaseOrderHeader_PurchaseOrderID
Column in PK_PurchaseOrderHeader_PurchaseOrderID
PurchaseOrderID
Name
CurrencyRate
Description
Currency exchange rates.
Properties
Schema
Sales
Columns
Name
Data type
Param
Key
Not Null
CurrencyRateID
int
Yes
Yes
Description
Primary key for CurrencyRate records.
After script
IDENTITY(1,1)
CurrencyRateDate
datetime
Yes
Description
Date and time the exchange rate was obtained.
FromCurrencyCode
nchar
3
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Exchange rate was converted from this currency code.
ToCurrencyCode
nchar
3
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Exchange rate was converted to this currency code.
AverageRate
money
Yes
Description
Average exchange rate for the day.
EndOfDayRate
money
Yes
Description
Final exchange rate for the day.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode
Column name
CurrencyRateDate
Column name
FromCurrencyCode
Column name
ToCurrencyCode
Unique
Yes
Description
Unique nonclustered index.
Keys
PK_CurrencyRate_CurrencyRateID
Column in PK_CurrencyRate_CurrencyRateID
CurrencyRateID
Name
Customer
Description
Current customer information. Also see the Person and Store tables.
Properties
Schema
Sales
Columns
Name
Data type
Param
Key
Not Null
CustomerID
int
Yes
Yes
Description
Primary key.
After script
IDENTITY(1,1) NOT FOR REPLICATION
PersonID
int
Description
Foreign key to Person.BusinessEntityID
StoreID
int
Description
Foreign key to Store.BusinessEntityID
TerritoryID
int
Description
ID of the territory in which the customer is located. Foreign key to SalesTerritory.SalesTerritoryID.
AccountNumber
varchar
10
Yes
Computed
(isnull('AW'+[dbo].[ufnLeadingZeros]([CustomerID]),''))
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Unique number identifying the customer assigned by the accounting system.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_Customer_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
AK_Customer_AccountNumber
Column name
AccountNumber
Unique
Yes
Description
Unique nonclustered index.
IX_Customer_TerritoryID
Column name
TerritoryID
Description
Nonclustered index.
Keys
PK_Customer_CustomerID
Column in PK_Customer_CustomerID
CustomerID
Name
Department
Description
Lookup table containing the departments within the Adventure Works Cycles company.
Properties
Schema
HumanResources
Columns
Name
Data type
Param
Key
Not Null
DepartmentID
smallint
Yes
Yes
Description
Primary key for Department records.
After script
IDENTITY(1,1)
Name
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Name of the department.
GroupName
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Name of the group to which the department belongs.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_Department_Name
Column name
Name
Unique
Yes
Description
Unique nonclustered index.
Keys
PK_Department_DepartmentID
Column in PK_Department_DepartmentID
DepartmentID
Name
Document
Description
Product maintenance documents.
Properties
Schema
Production
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [Status] BETWEEN (1) AND (3)', 'schema', N'Production', 'table', N'Document', 'constraint', N'CK_Document_Status';
GO
;
Columns
Name
Data type
Param
Key
Not Null
DocumentNode
hierarchyid
Yes
Yes
Description
Primary key for Document records.
DocumentLevel
smallint
Computed
([DocumentNode].[GetLevel]())
Description
Depth in the document hierarchy.
Title
nvarchar
50
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Title of the document.
Owner
int
Yes
Description
Employee who controls the document. Foreign key to Employee.BusinessEntityID
FolderFlag
bit
Yes
Default value
((0))
Description
0 = This is a folder, 1 = This is a document.
FileName
nvarchar
400
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
File name of the document
FileExtension
nvarchar
8
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
File extension indicating the document type. For example, .doc or .txt.
Revision
nchar
5
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Revision number of the document.
ChangeNumber
int
Yes
Default value
((0))
Description
Engineering change approval number.
Status
tinyint
Yes
Description
1 = Pending approval, 2 = Approved, 3 = Obsolete
After script
CONSTRAINT [CK_Document_Status] CHECK ([Status]>=(1) AND [Status]<=(3))
DocumentSummary
nvarchar
max
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Document abstract.
Document
varbinary
max
Description
Complete document.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Required for FileStream.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_Document_DocumentLevel_DocumentNode
Column name
DocumentLevel
Column name
DocumentNode
Unique
Yes
Description
Unique nonclustered index.
AK_Document_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support FileStream.
IX_Document_FileName_Revision
Column name
FileName
Column name
Revision
Description
Unique nonclustered index.
Keys
PK_Document_DocumentNode
Column in PK_Document_DocumentNode
DocumentNode
UQ__Document__F73921F7C5112C2E
Column in UQ__Document__F73921F7C5112C2E
rowguid
Name
SalesOrderDetail
Description
Individual products associated with a specific sales order. See SalesOrderHeader.
Properties
Schema
Sales
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [OrderQty] > (0)', 'schema', N'Sales', 'table', N'SalesOrderDetail', 'constraint', N'CK_SalesOrderDetail_OrderQty';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [UnitPrice] >= (0.00)', 'schema', N'Sales', 'table', N'SalesOrderDetail', 'constraint', N'CK_SalesOrderDetail_UnitPrice';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [UnitPriceDiscount] >= (0.00)', 'schema', N'Sales', 'table', N'SalesOrderDetail', 'constraint', N'CK_SalesOrderDetail_UnitPriceDiscount';
GO
;
Columns
Name
Data type
Param
Key
Not Null
SalesOrderID
int
Yes
Yes
Description
Primary key. Foreign key to SalesOrderHeader.SalesOrderID.
SalesOrderDetailID
int
Yes
Yes
Description
Primary key. One incremental unique number per product sold.
After script
IDENTITY(1,1)
CarrierTrackingNumber
nvarchar
25
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Shipment tracking number supplied by the shipper.
OrderQty
smallint
Yes
Description
Quantity ordered per product.
After script
CONSTRAINT [CK_SalesOrderDetail_OrderQty] CHECK ([OrderQty]>(0))
ProductID
int
Yes
Description
Product sold to customer. Foreign key to Product.ProductID.
SpecialOfferID
int
Yes
Description
Promotional code. Foreign key to SpecialOffer.SpecialOfferID.
UnitPrice
money
Yes
Description
Selling price of a single product.
After script
CONSTRAINT [CK_SalesOrderDetail_UnitPrice] CHECK ([UnitPrice]>=(0.00))
UnitPriceDiscount
money
Yes
Default value
((0.0))
Description
Discount amount.
After script
CONSTRAINT [CK_SalesOrderDetail_UnitPriceDiscount] CHECK ([UnitPriceDiscount]>=(0.00))
LineTotal
numeric
38,6
Yes
Computed
(isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0)))
Description
Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_SalesOrderDetail_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
IX_SalesOrderDetail_ProductID
Column name
ProductID
Description
Nonclustered index.
Keys
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
Column in PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
SalesOrderID
Column in PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
SalesOrderDetailID
Name
EmailAddress
Description
Where to send a person email.
Properties
Schema
Person
Columns
Name
Data type
Param
Key
Not Null
BusinessEntityID
int
Yes
Yes
Description
Primary key. Person associated with this email address. Foreign key to Person.BusinessEntityID
EmailAddressID
int
Yes
Yes
Description
Primary key. ID of this email address.
After script
IDENTITY(1,1)
EmailAddress
nvarchar
50
Collation
SQL_Latin1_General_CP1_CI_AS
Description
E-mail address for the person.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
IX_EmailAddress_EmailAddress
Column name
EmailAddress
Description
Nonclustered index.
Keys
PK_EmailAddress_BusinessEntityID_EmailAddressID
Column in PK_EmailAddress_BusinessEntityID_EmailAddressID
BusinessEntityID
Column in PK_EmailAddress_BusinessEntityID_EmailAddressID
EmailAddressID
Name
Employee
Description
Employee information such as salary, department, and title.
Properties
Schema
HumanResources
After script
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [BirthDate] >= ''1930-01-01'' AND [BirthDate] <= dateadd(year,(-18),GETDATE())', 'schema', N'HumanResources', 'table', N'Employee', 'constraint', N'CK_Employee_BirthDate';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [MaritalStatus]=''s'' OR [MaritalStatus]=''m'' OR [MaritalStatus]=''S'' OR [MaritalStatus]=''M''', 'schema', N'HumanResources', 'table', N'Employee', 'constraint', N'CK_Employee_MaritalStatus';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [HireDate] >= ''1996-07-01'' AND [HireDate] <= dateadd(day,(1),GETDATE())', 'schema', N'HumanResources', 'table', N'Employee', 'constraint', N'CK_Employee_HireDate';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [Gender]=''f'' OR [Gender]=''m'' OR [Gender]=''F'' OR [Gender]=''M''', 'schema', N'HumanResources', 'table', N'Employee', 'constraint', N'CK_Employee_Gender';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [VacationHours] >= (-40) AND [VacationHours] <= (240)', 'schema', N'HumanResources', 'table', N'Employee', 'constraint', N'CK_Employee_VacationHours';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [SickLeaveHours] >= (0) AND [SickLeaveHours] <= (120)', 'schema', N'HumanResources', 'table', N'Employee', 'constraint', N'CK_Employee_SickLeaveHours';
GO
;
Columns
Name
Data type
Param
Key
Not Null
BusinessEntityID
int
Yes
Yes
Description
Primary key for Employee records. Foreign key to BusinessEntity.BusinessEntityID.
NationalIDNumber
nvarchar
15
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Unique national identification number such as a social security number.
LoginID
nvarchar
256
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Network login.
OrganizationNode
hierarchyid
Description
Where the employee is located in corporate hierarchy.
OrganizationLevel
smallint
Computed
([OrganizationNode].[GetLevel]())
Description
The depth of the employee in the corporate hierarchy.
JobTitle
nvarchar
50
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Work title such as Buyer or Sales Representative.
BirthDate
date
Yes
Description
Date of birth.
After script
CONSTRAINT [CK_Employee_BirthDate] CHECK ([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate()))
MaritalStatus
nchar
1
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
M = Married, S = Single
After script
CONSTRAINT [CK_Employee_MaritalStatus] CHECK (upper([MaritalStatus])='S' OR upper([MaritalStatus])='M')
Gender
nchar
1
Yes
Collation
SQL_Latin1_General_CP1_CI_AS
Description
M = Male, F = Female
After script
CONSTRAINT [CK_Employee_Gender] CHECK (upper([Gender])='F' OR upper([Gender])='M')
HireDate
date
Yes
Description
Employee hired on this date.
After script
CONSTRAINT [CK_Employee_HireDate] CHECK ([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate()))
SalariedFlag
Yes
Default value
((1))
Description
Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
VacationHours
smallint
Yes
Default value
((0))
Description
Number of available vacation hours.
After script
CONSTRAINT [CK_Employee_VacationHours] CHECK ([VacationHours]>=(-40) AND [VacationHours]<=(240))
SickLeaveHours
smallint
Yes
Default value
((0))
Description
Number of available sick leave hours.
After script
CONSTRAINT [CK_Employee_SickLeaveHours] CHECK ([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120))
CurrentFlag
Yes
Default value
((1))
Description
0 = Inactive, 1 = Active
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
IX_Employee_OrganizationNode
Column name
OrganizationNode
Description
Unique nonclustered index.
IX_Employee_OrganizationLevel_OrganizationNode
Column name
OrganizationLevel
Column name
OrganizationNode
Description
Unique nonclustered index.
AK_Employee_LoginID
Column name
LoginID
Unique
Yes
Description
Unique nonclustered index.
AK_Employee_NationalIDNumber
Column name
NationalIDNumber
Unique
Yes
Description
Unique nonclustered index.
AK_Employee_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
Keys
PK_Employee_BusinessEntityID
Column in PK_Employee_BusinessEntityID
BusinessEntityID
Name
SalesOrderHeader
Description
General sales order information.
Properties
Schema
Sales
After script
ALTER TABLE [Sales].[SalesOrderHeader] ADD CONSTRAINT [CK_SalesOrderHeader_DueDate] CHECK ([DueDate]>=[OrderDate]);
ALTER TABLE [Sales].[SalesOrderHeader] ADD CONSTRAINT [CK_SalesOrderHeader_ShipDate] CHECK ([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL);
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [Freight] >= (0.00)', 'schema', N'Sales', 'table', N'SalesOrderHeader', 'constraint', N'CK_SalesOrderHeader_Freight';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [Status] BETWEEN (0) AND (8)', 'schema', N'Sales', 'table', N'SalesOrderHeader', 'constraint', N'CK_SalesOrderHeader_Status';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [DueDate] >= [OrderDate]', 'schema', N'Sales', 'table', N'SalesOrderHeader', 'constraint', N'CK_SalesOrderHeader_DueDate';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL', 'schema', N'Sales', 'table', N'SalesOrderHeader', 'constraint', N'CK_SalesOrderHeader_ShipDate';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [SubTotal] >= (0.00)', 'schema', N'Sales', 'table', N'SalesOrderHeader', 'constraint', N'CK_SalesOrderHeader_SubTotal';
GO
;
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [TaxAmt] >= (0.00)', 'schema', N'Sales', 'table', N'SalesOrderHeader', 'constraint', N'CK_SalesOrderHeader_TaxAmt';
GO
;
Columns
Name
Data type
Param
Key
Not Null
SalesOrderID
int
Yes
Yes
Description
Primary key.
After script
IDENTITY(1,1) NOT FOR REPLICATION
RevisionNumber
tinyint
Yes
Default value
((0))
Description
Incremental number to track changes to the sales order over time.
OrderDate
datetime
Yes
Default value
(getdate())
Description
Dates the sales order was created.
DueDate
datetime
Yes
Description
Date the order is due to the customer.
ShipDate
datetime
Description
Date the order was shipped to the customer.
Status
tinyint
Yes
Default value
((1))
Description
Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled
After script
CONSTRAINT [CK_SalesOrderHeader_Status] CHECK ([Status]>=(0) AND [Status]<=(8))
OnlineOrderFlag
Yes
Default value
((1))
Description
0 = Order placed by sales person. 1 = Order placed online by customer.
SalesOrderNumber
nvarchar
25
Yes
Computed
(isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ERROR ***'))
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Unique sales order identification number.
PurchaseOrderNumber
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Customer purchase order number reference.
AccountNumber
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Financial accounting number reference.
CustomerID
int
Yes
Description
Customer identification number. Foreign key to Customer.BusinessEntityID.
SalesPersonID
int
Description
Sales person who created the sales order. Foreign key to SalesPerson.BusinessEntityID.
TerritoryID
int
Description
Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID.
BillToAddressID
int
Yes
Description
Customer billing address. Foreign key to Address.AddressID.
ShipToAddressID
int
Yes
Description
Customer shipping address. Foreign key to Address.AddressID.
ShipMethodID
int
Yes
Description
Shipping method. Foreign key to ShipMethod.ShipMethodID.
CreditCardID
int
Description
Credit card identification number. Foreign key to CreditCard.CreditCardID.
CreditCardApprovalCode
varchar
15
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Approval code provided by the credit card company.
CurrencyRateID
int
Description
Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID.
SubTotal
money
Yes
Default value
((0.00))
Description
Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.
After script
CONSTRAINT [CK_SalesOrderHeader_SubTotal] CHECK ([SubTotal]>=(0.00))
TaxAmt
money
Yes
Default value
((0.00))
Description
Tax amount.
After script
CONSTRAINT [CK_SalesOrderHeader_TaxAmt] CHECK ([TaxAmt]>=(0.00))
Freight
money
Yes
Default value
((0.00))
Description
Shipping cost.
After script
CONSTRAINT [CK_SalesOrderHeader_Freight] CHECK ([Freight]>=(0.00))
TotalDue
money
Yes
Computed
(isnull(([SubTotal]+[TaxAmt])+[Freight],(0)))
Description
Total due from customer. Computed as Subtotal + TaxAmt + Freight.
Comment
nvarchar
128
Collation
SQL_Latin1_General_CP1_CI_AS
Description
Sales representative comments.
rowguid
uniqueidentifier
Yes
Default value
(newid())
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
AK_SalesOrderHeader_rowguid
Column name
rowguid
Unique
Yes
Description
Unique nonclustered index. Used to support replication samples.
AK_SalesOrderHeader_SalesOrderNumber
Column name
SalesOrderNumber
Unique
Yes
Description
Unique nonclustered index.
IX_SalesOrderHeader_CustomerID
Column name
CustomerID
Description
Nonclustered index.
IX_SalesOrderHeader_SalesPersonID
Column name
SalesPersonID
Description
Nonclustered index.
Keys
PK_SalesOrderHeader_SalesOrderID
Column in PK_SalesOrderHeader_SalesOrderID
SalesOrderID
Name
EmployeeDepartmentHistory
Description
Employee department transfers.
Properties
Schema
HumanResources
After script
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] ADD CONSTRAINT [CK_EmployeeDepartmentHistory_EndDate] CHECK ([EndDate]>=[StartDate] OR [EndDate] IS NULL);
EXEC sys.sp_addextendedproperty 'MS_Description', N'Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NUL', 'schema', N'HumanResources', 'table', N'EmployeeDepartmentHistory', 'constraint', N'CK_EmployeeDepartmentHistory_EndDate';
GO
;
Columns
Name
Data type
Param
Key
Not Null
BusinessEntityID
int
Yes
Yes
Description
Employee identification number. Foreign key to Employee.BusinessEntityID.
DepartmentID
smallint
Yes
Yes
Description
Department in which the employee worked including currently. Foreign key to Department.DepartmentID.
ShiftID
tinyint
Yes
Yes
Description
Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.
StartDate
date
Yes
Yes
Description
Date the employee started work in the department.
EndDate
date
Description
Date the employee left the department. NULL = Current department.
ModifiedDate
datetime
Yes
Default value
(getdate())
Description
Date and time the record was last updated.
Indexes
IX_EmployeeDepartmentHistory_DepartmentID
Column name
DepartmentID
Description
Nonclustered index.
IX_EmployeeDepartmentHistory_ShiftID
Column name
ShiftID
Description
Nonclustered index.
Keys
PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID
Column in PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID
BusinessEntityID
Column in PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID
DepartmentID
Column in PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID
ShiftID
Column in PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID
StartDate
relationships
Name
FK_EmployeePayHistory_Employee_BusinessEntityID
Description
Foreign key constraint referencing Employee.EmployeeID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Employee
Column in EmployeePayHistory
PK_Employee_BusinessEntityID
BusinessEntityID
BusinessEntityID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SalesOrderHeaderSalesReason_SalesReason_SalesReasonID
Description
Foreign key constraint referencing SalesReason.SalesReasonID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in SalesReason
Column in SalesOrderHeaderSalesReason
PK_SalesReason_SalesReasonID
SalesReasonID
SalesReasonID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID
Description
Foreign key constraint referencing SalesOrderHeader.SalesOrderID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in SalesOrderHeader
Column in SalesOrderHeaderSalesReason
PK_SalesOrderHeader_SalesOrderID
SalesOrderID
SalesOrderID
Properties
Rerefential integrity - parent delete
Cascade
Referential integrity - parent update
No action
Name
FK_SalesPerson_Employee_BusinessEntityID
Description
Foreign key constraint referencing Employee.EmployeeID.
Name
Source
Target
Cardinality type:
One to
one
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Employee
Column in SalesPerson
PK_Employee_BusinessEntityID
BusinessEntityID
BusinessEntityID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SalesPerson_SalesTerritory_TerritoryID
Description
Foreign key constraint referencing SalesTerritory.TerritoryID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Optional
Key and columns
Key name
Column in SalesTerritory
Column in SalesPerson
PK_SalesTerritory_TerritoryID
TerritoryID
TerritoryID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_JobCandidate_Employee_BusinessEntityID
Description
Foreign key constraint referencing Employee.EmployeeID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Optional
Key and columns
Key name
Column in Employee
Column in JobCandidate
PK_Employee_BusinessEntityID
BusinessEntityID
BusinessEntityID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_Password_Person_BusinessEntityID
Description
Foreign key constraint referencing Person.BusinessEntityID.
Name
Source
Target
Cardinality type:
One to
one
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Person
Column in Password
PK_Person_BusinessEntityID
BusinessEntityID
BusinessEntityID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SalesPersonQuotaHistory_SalesPerson_BusinessEntityID
Description
Foreign key constraint referencing SalesPerson.SalesPersonID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in SalesPerson
Column in SalesPersonQuotaHistory
PK_SalesPerson_BusinessEntityID
BusinessEntityID
BusinessEntityID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_Person_BusinessEntity_BusinessEntityID
Description
Foreign key constraint referencing BusinessEntity.BusinessEntityID.
Name
Source
Target
Cardinality type:
One to
one
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in BusinessEntity
Column in Person
PK_BusinessEntity_BusinessEntityID
BusinessEntityID
BusinessEntityID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SalesTaxRate_StateProvince_StateProvinceID
Description
Foreign key constraint referencing StateProvince.StateProvinceID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in StateProvince
Column in SalesTaxRate
PK_StateProvince_StateProvinceID
StateProvinceID
StateProvinceID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_PersonCreditCard_Person_BusinessEntityID
Description
Foreign key constraint referencing Person.BusinessEntityID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Person
Column in PersonCreditCard
PK_Person_BusinessEntityID
BusinessEntityID
BusinessEntityID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_PersonCreditCard_CreditCard_CreditCardID
Description
Foreign key constraint referencing CreditCard.CreditCardID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in CreditCard
Column in PersonCreditCard
PK_CreditCard_CreditCardID
CreditCardID
CreditCardID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_PersonPhone_Person_BusinessEntityID
Description
Foreign key constraint referencing Person.BusinessEntityID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Person
Column in PersonPhone
PK_Person_BusinessEntityID
BusinessEntityID
BusinessEntityID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_PersonPhone_PhoneNumberType_PhoneNumberTypeID
Description
Foreign key constraint referencing PhoneNumberType.PhoneNumberTypeID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in PhoneNumberType
Column in PersonPhone
PK_PhoneNumberType_PhoneNumberTypeID
PhoneNumberTypeID
PhoneNumberTypeID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SalesTerritory_CountryRegion_CountryRegionCode
Description
Foreign key constraint referencing CountryRegion.CountryRegionCode.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in CountryRegion
Column in SalesTerritory
PK_CountryRegion_CountryRegionCode
CountryRegionCode
CountryRegionCode
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_Product_UnitMeasure_SizeUnitMeasureCode
Description
Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Optional
Key and columns
Key name
Column in UnitMeasure
Column in Product
PK_UnitMeasure_UnitMeasureCode
UnitMeasureCode
SizeUnitMeasureCode
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_Product_UnitMeasure_WeightUnitMeasureCode
Description
Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Optional
Key and columns
Key name
Column in UnitMeasure
Column in Product
PK_UnitMeasure_UnitMeasureCode
UnitMeasureCode
WeightUnitMeasureCode
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_Product_ProductModel_ProductModelID
Description
Foreign key constraint referencing ProductModel.ProductModelID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Optional
Key and columns
Key name
Column in ProductModel
Column in Product
PK_ProductModel_ProductModelID
ProductModelID
ProductModelID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_Product_ProductSubcategory_ProductSubcategoryID
Description
Foreign key constraint referencing ProductSubcategory.ProductSubcategoryID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Optional
Key and columns
Key name
Column in ProductSubcategory
Column in Product
PK_ProductSubcategory_ProductSubcategoryID
ProductSubcategoryID
ProductSubcategoryID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SalesTerritoryHistory_SalesPerson_BusinessEntityID
Description
Foreign key constraint referencing SalesPerson.SalesPersonID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in SalesPerson
Column in SalesTerritoryHistory
PK_SalesPerson_BusinessEntityID
BusinessEntityID
BusinessEntityID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SalesTerritoryHistory_SalesTerritory_TerritoryID
Description
Foreign key constraint referencing SalesTerritory.TerritoryID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in SalesTerritory
Column in SalesTerritoryHistory
PK_SalesTerritory_TerritoryID
TerritoryID
TerritoryID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductCostHistory_Product_ProductID
Description
Foreign key constraint referencing Product.ProductID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Product
Column in ProductCostHistory
PK_Product_ProductID
ProductID
ProductID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ShoppingCartItem_Product_ProductID
Description
Foreign key constraint referencing Product.ProductID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Product
Column in ShoppingCartItem
PK_Product_ProductID
ProductID
ProductID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductDocument_Product_ProductID
Description
Foreign key constraint referencing Product.ProductID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Product
Column in ProductDocument
PK_Product_ProductID
ProductID
ProductID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductDocument_Document_DocumentNode
Description
Foreign key constraint referencing Document.DocumentNode.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Document
Column in ProductDocument
PK_Document_DocumentNode
DocumentNode
DocumentNode
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductInventory_Location_LocationID
Description
Foreign key constraint referencing Location.LocationID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Location
Column in ProductInventory
PK_Location_LocationID
LocationID
LocationID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductInventory_Product_ProductID
Description
Foreign key constraint referencing Product.ProductID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Product
Column in ProductInventory
PK_Product_ProductID
ProductID
ProductID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductListPriceHistory_Product_ProductID
Description
Foreign key constraint referencing Product.ProductID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Product
Column in ProductListPriceHistory
PK_Product_ProductID
ProductID
ProductID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_Address_StateProvince_StateProvinceID
Description
Foreign key constraint referencing StateProvince.StateProvinceID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in StateProvince
Column in Address
PK_StateProvince_StateProvinceID
StateProvinceID
StateProvinceID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SpecialOfferProduct_Product_ProductID
Description
Foreign key constraint referencing Product.ProductID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Product
Column in SpecialOfferProduct
PK_Product_ProductID
ProductID
ProductID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SpecialOfferProduct_SpecialOffer_SpecialOfferID
Description
Foreign key constraint referencing SpecialOffer.SpecialOfferID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in SpecialOffer
Column in SpecialOfferProduct
PK_SpecialOffer_SpecialOfferID
SpecialOfferID
SpecialOfferID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_StateProvince_CountryRegion_CountryRegionCode
Description
Foreign key constraint referencing CountryRegion.CountryRegionCode.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in CountryRegion
Column in StateProvince
PK_CountryRegion_CountryRegionCode
CountryRegionCode
CountryRegionCode
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_StateProvince_SalesTerritory_TerritoryID
Description
Foreign key constraint referencing SalesTerritory.TerritoryID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in SalesTerritory
Column in StateProvince
PK_SalesTerritory_TerritoryID
TerritoryID
TerritoryID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductModelIllustration_ProductModel_ProductModelID
Description
Foreign key constraint referencing ProductModel.ProductModelID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in ProductModel
Column in ProductModelIllustration
PK_ProductModel_ProductModelID
ProductModelID
ProductModelID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductModelIllustration_Illustration_IllustrationID
Description
Foreign key constraint referencing Illustration.IllustrationID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Illustration
Column in ProductModelIllustration
PK_Illustration_IllustrationID
IllustrationID
IllustrationID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductModelProductDescriptionCulture_ProductDescription_ProductDescriptionID
Description
Foreign key constraint referencing ProductDescription.ProductDescriptionID.
Key and columns
Key name
Column in ProductDescription
Column in ProductModelProductDescriptionCulture
PK_ProductDescription_ProductDescriptionID
ProductDescriptionID
ProductDescriptionID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductModelProductDescriptionCulture_Culture_CultureID
Description
Foreign key constraint referencing Culture.CultureID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Culture
Column in ProductModelProductDescriptionCulture
PK_Culture_CultureID
CultureID
CultureID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductModelProductDescriptionCulture_ProductModel_ProductModelID
Description
Foreign key constraint referencing ProductModel.ProductModelID.
Key and columns
Key name
Column in ProductModel
Column in ProductModelProductDescriptionCulture
PK_ProductModel_ProductModelID
ProductModelID
ProductModelID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_BillOfMaterials_Product_ProductAssemblyID
Description
Foreign key constraint referencing Product.ProductAssemblyID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Optional
Key and columns
Key name
Column in Product
Column in BillOfMaterials
PK_Product_ProductID
ProductID
ProductAssemblyID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_BillOfMaterials_Product_ComponentID
Description
Foreign key constraint referencing Product.ComponentID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Product
Column in BillOfMaterials
PK_Product_ProductID
ProductID
ComponentID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_BillOfMaterials_UnitMeasure_UnitMeasureCode
Description
Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in UnitMeasure
Column in BillOfMaterials
PK_UnitMeasure_UnitMeasureCode
UnitMeasureCode
UnitMeasureCode
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_Store_BusinessEntity_BusinessEntityID
Description
Foreign key constraint referencing BusinessEntity.BusinessEntityID
Name
Source
Target
Cardinality type:
One to
one
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in BusinessEntity
Column in Store
PK_BusinessEntity_BusinessEntityID
BusinessEntityID
BusinessEntityID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_Store_SalesPerson_SalesPersonID
Description
Foreign key constraint referencing SalesPerson.SalesPersonID
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Optional
Key and columns
Key name
Column in SalesPerson
Column in Store
PK_SalesPerson_BusinessEntityID
BusinessEntityID
SalesPersonID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductProductPhoto_Product_ProductID
Description
Foreign key constraint referencing Product.ProductID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Product
Column in ProductProductPhoto
PK_Product_ProductID
ProductID
ProductID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductProductPhoto_ProductPhoto_ProductPhotoID
Description
Foreign key constraint referencing ProductPhoto.ProductPhotoID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in ProductPhoto
Column in ProductProductPhoto
PK_ProductPhoto_ProductPhotoID
ProductPhotoID
ProductPhotoID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_TransactionHistory_Product_ProductID
Description
Foreign key constraint referencing Product.ProductID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Product
Column in TransactionHistory
PK_Product_ProductID
ProductID
ProductID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductReview_Product_ProductID
Description
Foreign key constraint referencing Product.ProductID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Product
Column in ProductReview
PK_Product_ProductID
ProductID
ProductID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductSubcategory_ProductCategory_ProductCategoryID
Description
Foreign key constraint referencing ProductCategory.ProductCategoryID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in ProductCategory
Column in ProductSubcategory
PK_ProductCategory_ProductCategoryID
ProductCategoryID
ProductCategoryID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_BusinessEntityAddress_Address_AddressID
Description
Foreign key constraint referencing Address.AddressID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Address
Column in BusinessEntityAddress
PK_Address_AddressID
AddressID
AddressID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_BusinessEntityAddress_AddressType_AddressTypeID
Description
Foreign key constraint referencing AddressType.AddressTypeID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in AddressType
Column in BusinessEntityAddress
PK_AddressType_AddressTypeID
AddressTypeID
AddressTypeID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_BusinessEntityAddress_BusinessEntity_BusinessEntityID
Description
Foreign key constraint referencing BusinessEntity.BusinessEntityID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in BusinessEntity
Column in BusinessEntityAddress
PK_BusinessEntity_BusinessEntityID
BusinessEntityID
BusinessEntityID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductVendor_Product_ProductID
Description
Foreign key constraint referencing Product.ProductID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Product
Column in ProductVendor
PK_Product_ProductID
ProductID
ProductID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductVendor_UnitMeasure_UnitMeasureCode
Description
Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in UnitMeasure
Column in ProductVendor
PK_UnitMeasure_UnitMeasureCode
UnitMeasureCode
UnitMeasureCode
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_ProductVendor_Vendor_BusinessEntityID
Description
Foreign key constraint referencing Vendor.BusinessEntityID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Vendor
Column in ProductVendor
PK_Vendor_BusinessEntityID
BusinessEntityID
BusinessEntityID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_BusinessEntityContact_Person_PersonID
Description
Foreign key constraint referencing Person.BusinessEntityID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Person
Column in BusinessEntityContact
PK_Person_BusinessEntityID
BusinessEntityID
PersonID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_BusinessEntityContact_ContactType_ContactTypeID
Description
Foreign key constraint referencing ContactType.ContactTypeID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in ContactType
Column in BusinessEntityContact
PK_ContactType_ContactTypeID
ContactTypeID
ContactTypeID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_BusinessEntityContact_BusinessEntity_BusinessEntityID
Description
Foreign key constraint referencing BusinessEntity.BusinessEntityID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in BusinessEntity
Column in BusinessEntityContact
PK_BusinessEntity_BusinessEntityID
BusinessEntityID
BusinessEntityID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_Vendor_BusinessEntity_BusinessEntityID
Description
Foreign key constraint referencing BusinessEntity.BusinessEntityID
Name
Source
Target
Cardinality type:
One to
one
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in BusinessEntity
Column in Vendor
PK_BusinessEntity_BusinessEntityID
BusinessEntityID
BusinessEntityID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_CountryRegionCurrency_CountryRegion_CountryRegionCode
Description
Foreign key constraint referencing CountryRegion.CountryRegionCode.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in CountryRegion
Column in CountryRegionCurrency
PK_CountryRegion_CountryRegionCode
CountryRegionCode
CountryRegionCode
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_CountryRegionCurrency_Currency_CurrencyCode
Description
Foreign key constraint referencing Currency.CurrencyCode.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Currency
Column in CountryRegionCurrency
PK_Currency_CurrencyCode
CurrencyCode
CurrencyCode
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_WorkOrder_Product_ProductID
Description
Foreign key constraint referencing Product.ProductID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Product
Column in WorkOrder
PK_Product_ProductID
ProductID
ProductID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_WorkOrder_ScrapReason_ScrapReasonID
Description
Foreign key constraint referencing ScrapReason.ScrapReasonID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Optional
Key and columns
Key name
Column in ScrapReason
Column in WorkOrder
PK_ScrapReason_ScrapReasonID
ScrapReasonID
ScrapReasonID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_PurchaseOrderDetail_Product_ProductID
Description
Foreign key constraint referencing Product.ProductID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Product
Column in PurchaseOrderDetail
PK_Product_ProductID
ProductID
ProductID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID
Description
Foreign key constraint referencing PurchaseOrderHeader.PurchaseOrderID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in PurchaseOrderHeader
Column in PurchaseOrderDetail
PK_PurchaseOrderHeader_PurchaseOrderID
PurchaseOrderID
PurchaseOrderID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_WorkOrderRouting_Location_LocationID
Description
Foreign key constraint referencing Location.LocationID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Location
Column in WorkOrderRouting
PK_Location_LocationID
LocationID
LocationID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_WorkOrderRouting_WorkOrder_WorkOrderID
Description
Foreign key constraint referencing WorkOrder.WorkOrderID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in WorkOrder
Column in WorkOrderRouting
PK_WorkOrder_WorkOrderID
WorkOrderID
WorkOrderID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_PurchaseOrderHeader_Employee_EmployeeID
Description
Foreign key constraint referencing Employee.EmployeeID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Employee
Column in PurchaseOrderHeader
PK_Employee_BusinessEntityID
BusinessEntityID
EmployeeID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_PurchaseOrderHeader_Vendor_VendorID
Description
Foreign key constraint referencing Vendor.VendorID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Vendor
Column in PurchaseOrderHeader
PK_Vendor_BusinessEntityID
BusinessEntityID
VendorID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_PurchaseOrderHeader_ShipMethod_ShipMethodID
Description
Foreign key constraint referencing ShipMethod.ShipMethodID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in ShipMethod
Column in PurchaseOrderHeader
PK_ShipMethod_ShipMethodID
ShipMethodID
ShipMethodID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_CurrencyRate_Currency_FromCurrencyCode
Description
Foreign key constraint referencing Currency.FromCurrencyCode.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Currency
Column in CurrencyRate
PK_Currency_CurrencyCode
CurrencyCode
FromCurrencyCode
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_CurrencyRate_Currency_ToCurrencyCode
Description
Foreign key constraint referencing Currency.ToCurrencyCode.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Currency
Column in CurrencyRate
PK_Currency_CurrencyCode
CurrencyCode
ToCurrencyCode
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_Customer_Person_PersonID
Description
Foreign key constraint referencing Person.BusinessEntityID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Optional
Key and columns
Key name
Column in Person
Column in Customer
PK_Person_BusinessEntityID
BusinessEntityID
PersonID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_Customer_Store_StoreID
Description
Foreign key constraint referencing Store.BusinessEntityID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Optional
Key and columns
Key name
Column in Store
Column in Customer
PK_Store_BusinessEntityID
BusinessEntityID
StoreID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_Customer_SalesTerritory_TerritoryID
Description
Foreign key constraint referencing SalesTerritory.TerritoryID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Optional
Key and columns
Key name
Column in SalesTerritory
Column in Customer
PK_SalesTerritory_TerritoryID
TerritoryID
TerritoryID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_Document_Employee_Owner
Description
Foreign key constraint referencing Employee.BusinessEntityID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Employee
Column in Document
PK_Employee_BusinessEntityID
BusinessEntityID
Owner
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID
Description
Foreign key constraint referencing SalesOrderHeader.PurchaseOrderID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in SalesOrderHeader
Column in SalesOrderDetail
PK_SalesOrderHeader_SalesOrderID
SalesOrderID
SalesOrderID
Properties
Rerefential integrity - parent delete
Cascade
Referential integrity - parent update
No action
Name
FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID
Description
Foreign key constraint referencing SpecialOfferProduct.SpecialOfferIDProductID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in SpecialOfferProduct
Column in SalesOrderDetail
PK_SpecialOfferProduct_SpecialOfferID_ProductID
SpecialOfferID
SpecialOfferID
ProductID
ProductID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_EmailAddress_Person_BusinessEntityID
Description
Foreign key constraint referencing Person.BusinessEntityID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Person
Column in EmailAddress
PK_Person_BusinessEntityID
BusinessEntityID
BusinessEntityID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_Employee_Person_BusinessEntityID
Description
Foreign key constraint referencing Person.BusinessEntityID.
Name
Source
Target
Cardinality type:
One to
one
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Person
Column in Employee
PK_Person_BusinessEntityID
BusinessEntityID
BusinessEntityID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SalesOrderHeader_Address_BillToAddressID
Description
Foreign key constraint referencing Address.AddressID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Address
Column in SalesOrderHeader
PK_Address_AddressID
AddressID
BillToAddressID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SalesOrderHeader_Address_ShipToAddressID
Description
Foreign key constraint referencing Address.AddressID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Address
Column in SalesOrderHeader
PK_Address_AddressID
AddressID
ShipToAddressID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SalesOrderHeader_CreditCard_CreditCardID
Description
Foreign key constraint referencing CreditCard.CreditCardID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Optional
Key and columns
Key name
Column in CreditCard
Column in SalesOrderHeader
PK_CreditCard_CreditCardID
CreditCardID
CreditCardID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SalesOrderHeader_CurrencyRate_CurrencyRateID
Description
Foreign key constraint referencing CurrencyRate.CurrencyRateID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Optional
Key and columns
Key name
Column in CurrencyRate
Column in SalesOrderHeader
PK_CurrencyRate_CurrencyRateID
CurrencyRateID
CurrencyRateID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SalesOrderHeader_Customer_CustomerID
Description
Foreign key constraint referencing Customer.CustomerID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Customer
Column in SalesOrderHeader
PK_Customer_CustomerID
CustomerID
CustomerID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SalesOrderHeader_SalesPerson_SalesPersonID
Description
Foreign key constraint referencing SalesPerson.SalesPersonID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Optional
Key and columns
Key name
Column in SalesPerson
Column in SalesOrderHeader
PK_SalesPerson_BusinessEntityID
BusinessEntityID
SalesPersonID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SalesOrderHeader_ShipMethod_ShipMethodID
Description
Foreign key constraint referencing ShipMethod.ShipMethodID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in ShipMethod
Column in SalesOrderHeader
PK_ShipMethod_ShipMethodID
ShipMethodID
ShipMethodID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_SalesOrderHeader_SalesTerritory_TerritoryID
Description
Foreign key constraint referencing SalesTerritory.TerritoryID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Optional
Key and columns
Key name
Column in SalesTerritory
Column in SalesOrderHeader
PK_SalesTerritory_TerritoryID
TerritoryID
TerritoryID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_EmployeeDepartmentHistory_Department_DepartmentID
Description
Foreign key constraint referencing Department.DepartmentID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Department
Column in EmployeeDepartmentHistory
PK_Department_DepartmentID
DepartmentID
DepartmentID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_EmployeeDepartmentHistory_Employee_BusinessEntityID
Description
Foreign key constraint referencing Employee.EmployeeID.
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Employee
Column in EmployeeDepartmentHistory
PK_Employee_BusinessEntityID
BusinessEntityID
BusinessEntityID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Name
FK_EmployeeDepartmentHistory_Shift_ShiftID
Description
Foreign key constraint referencing Shift.ShiftID
Name
Source
Target
Cardinality type:
One to
many
Ordinality:
Mandatory
Mandatory
Key and columns
Key name
Column in Shift
Column in EmployeeDepartmentHistory
PK_Shift_ShiftID
ShiftID
ShiftID
Properties
Rerefential integrity - parent delete
No action
Referential integrity - parent update
No action
Lines
Line name
vStateProvinceCountryRegion-StateProvince
Line name
vStateProvinceCountryRegion-CountryRegion
Line name
iuPerson-Person
Lines
Line name
vAdditionalContactInfo-Person
Line name
uWorkOrder-TransactionHistory
Line name
iWorkOrder-TransactionHistory
Line name
uPurchaseOrderDetail-PurchaseOrderDetail
Line name
uPurchaseOrderDetail-PurchaseOrderHeader
Line name
uPurchaseOrderHeader-PurchaseOrderHeader
Line name
iPurchaseOrderDetail-PurchaseOrderHeader
Line name
dVendor-Vendor
Lines
Line name
dEmployee-Employee
Line name
vEmployeeDepartment-Employee
Line name
vEmployee-Employee
Line name
vEmployeeDepartmentHistory-Employee
Line name
vJobCandidate-Employee
Other objects
Name
vEmployee
Type
View
Description
Employee names and addresses.
Schema
HumanResources
Code
CREATE VIEW [HumanResources].[vEmployee] 
AS 
SELECT 
    e.[BusinessEntityID]
    ,p.[Title]
    ,p.[FirstName]
    ,p.[MiddleName]
    ,p.[LastName]
    ,p.[Suffix]
    ,e.[JobTitle]  
    ,pp.[PhoneNumber]
    ,pnt.[Name] AS [PhoneNumberType]
    ,ea.[EmailAddress]
    ,p.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode]
    ,cr.[Name] AS [CountryRegionName] 
    ,p.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
	INNER JOIN [Person].[Person] p
	ON p.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [Person].[BusinessEntityAddress] bea 
    ON bea.[BusinessEntityID] = e.[BusinessEntityID] 
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    LEFT OUTER JOIN [Person].[PersonPhone] pp
    ON pp.BusinessEntityID = p.[BusinessEntityID]
    LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
    ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
    LEFT OUTER JOIN [Person].[EmailAddress] ea
    ON p.[BusinessEntityID] = ea.[BusinessEntityID];
Name
vEmployeeDepartment
Type
View
Description
Returns employee name, title, and current department.
Schema
HumanResources
Code
CREATE VIEW [HumanResources].[vEmployeeDepartment] 
AS 
SELECT 
    e.[BusinessEntityID] 
    ,p.[Title] 
    ,p.[FirstName] 
    ,p.[MiddleName] 
    ,p.[LastName] 
    ,p.[Suffix] 
    ,e.[JobTitle]
    ,d.[Name] AS [Department] 
    ,d.[GroupName] 
    ,edh.[StartDate] 
FROM [HumanResources].[Employee] e
	INNER JOIN [Person].[Person] p
	ON p.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
    ON e.[BusinessEntityID] = edh.[BusinessEntityID] 
    INNER JOIN [HumanResources].[Department] d 
    ON edh.[DepartmentID] = d.[DepartmentID] 
WHERE edh.EndDate IS NULL
Name
vEmployeeDepartmentHistory
Type
View
Description
Returns employee name and current and previous departments.
Schema
HumanResources
Code
CREATE VIEW [HumanResources].[vEmployeeDepartmentHistory] 
AS 
SELECT 
    e.[BusinessEntityID] 
    ,p.[Title] 
    ,p.[FirstName] 
    ,p.[MiddleName] 
    ,p.[LastName] 
    ,p.[Suffix] 
    ,s.[Name] AS [Shift]
    ,d.[Name] AS [Department] 
    ,d.[GroupName] 
    ,edh.[StartDate] 
    ,edh.[EndDate]
FROM [HumanResources].[Employee] e
	INNER JOIN [Person].[Person] p
	ON p.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
    ON e.[BusinessEntityID] = edh.[BusinessEntityID] 
    INNER JOIN [HumanResources].[Department] d 
    ON edh.[DepartmentID] = d.[DepartmentID] 
    INNER JOIN [HumanResources].[Shift] s
    ON s.[ShiftID] = edh.[ShiftID];
Name
vJobCandidate
Type
View
Description
Job candidate names and resumes.
Schema
HumanResources
Code
CREATE VIEW [HumanResources].[vJobCandidate] 
AS 
SELECT 
    jc.[JobCandidateID] 
    ,jc.[BusinessEntityID] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Name/Name.Prefix)[1]', 'nvarchar(30)') AS [Name.Prefix] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
        (/Resume/Name/Name.First)[1]', 'nvarchar(30)') AS [Name.First] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Name/Name.Middle)[1]', 'nvarchar(30)') AS [Name.Middle] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Name/Name.Last)[1]', 'nvarchar(30)') AS [Name.Last] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Name/Name.Suffix)[1]', 'nvarchar(30)') AS [Name.Suffix] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Skills)[1]', 'nvarchar(max)') AS [Skills] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.Type)[1]', 'nvarchar(30)') AS [Addr.Type]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(100)') AS [Addr.Loc.CountryRegion]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.Location/Location/Loc.State)[1]', 'nvarchar(100)') AS [Addr.Loc.State]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.Location/Location/Loc.City)[1]', 'nvarchar(100)') AS [Addr.Loc.City]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.PostalCode)[1]', 'nvarchar(20)') AS [Addr.PostalCode]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/EMail)[1]', 'nvarchar(max)') AS [EMail] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/WebSite)[1]', 'nvarchar(max)') AS [WebSite] 
    ,jc.[ModifiedDate] 
FROM [HumanResources].[JobCandidate] jc 
CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
    /Resume') AS Resume(ref);
Name
vJobCandidateEducation
Type
View
Description
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.
Schema
HumanResources
Code
CREATE VIEW [HumanResources].[vJobCandidateEducation] 
AS 
SELECT 
    jc.[JobCandidateID] 
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Level)[1]', 'nvarchar(max)') AS [Edu.Level]
    ,CONVERT(datetime, REPLACE([Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.StartDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Edu.StartDate] 
    ,CONVERT(datetime, REPLACE([Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.EndDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Edu.EndDate] 
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Degree)[1]', 'nvarchar(50)') AS [Edu.Degree]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Major)[1]', 'nvarchar(50)') AS [Edu.Major]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Minor)[1]', 'nvarchar(50)') AS [Edu.Minor]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.GPA)[1]', 'nvarchar(5)') AS [Edu.GPA]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.GPAScale)[1]', 'nvarchar(5)') AS [Edu.GPAScale]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.School)[1]', 'nvarchar(100)') AS [Edu.School]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(100)') AS [Edu.Loc.CountryRegion]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Location/Location/Loc.State)[1]', 'nvarchar(100)') AS [Edu.Loc.State]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Location/Location/Loc.City)[1]', 'nvarchar(100)') AS [Edu.Loc.City]
FROM [HumanResources].[JobCandidate] jc 
CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
    /Resume/Education') AS [Education](ref);
Name
vJobCandidateEmployment
Type
View
Description
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.
Schema
HumanResources
Code
CREATE VIEW [HumanResources].[vJobCandidateEmployment] 
AS 
SELECT 
    jc.[JobCandidateID] 
    ,CONVERT(datetime, REPLACE([Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.StartDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Emp.StartDate] 
    ,CONVERT(datetime, REPLACE([Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.EndDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Emp.EndDate] 
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.OrgName)[1]', 'nvarchar(100)') AS [Emp.OrgName]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.JobTitle)[1]', 'nvarchar(100)') AS [Emp.JobTitle]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.Responsibility)[1]', 'nvarchar(max)') AS [Emp.Responsibility]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.FunctionCategory)[1]', 'nvarchar(max)') AS [Emp.FunctionCategory]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.IndustryCategory)[1]', 'nvarchar(max)') AS [Emp.IndustryCategory]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(max)') AS [Emp.Loc.CountryRegion]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.Location/Location/Loc.State)[1]', 'nvarchar(max)') AS [Emp.Loc.State]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.Location/Location/Loc.City)[1]', 'nvarchar(max)') AS [Emp.Loc.City]
FROM [HumanResources].[JobCandidate] jc 
CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
    /Resume/Employment') AS Employment(ref);
Name
vAdditionalContactInfo
Type
View
Description
Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.
Schema
Person
Code
CREATE VIEW [Person].[vAdditionalContactInfo] 
AS 
SELECT 
    [BusinessEntityID] 
    ,[FirstName]
    ,[MiddleName]
    ,[LastName]
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:telephoneNumber)[1]/act:number', 'nvarchar(50)') AS [TelephoneNumber] 
    ,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:telephoneNumber/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [TelephoneSpecialInstructions] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
        (act:homePostalAddress/act:Street)[1]', 'nvarchar(50)') AS [Street] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:City)[1]', 'nvarchar(50)') AS [City] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:StateProvince)[1]', 'nvarchar(50)') AS [StateProvince] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:PostalCode)[1]', 'nvarchar(50)') AS [PostalCode] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:CountryRegion)[1]', 'nvarchar(50)') AS [CountryRegion] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:SpecialInstructions/text())[1]', 'nvarchar(max)') AS [HomeAddressSpecialInstructions] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:eMail/act:eMailAddress)[1]', 'nvarchar(128)') AS [EMailAddress] 
    ,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:eMail/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [EMailSpecialInstructions] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:eMail/act:SpecialInstructions/act:telephoneNumber/act:number)[1]', 'nvarchar(50)') AS [EMailTelephoneNumber] 
    ,[rowguid] 
    ,[ModifiedDate]
FROM [Person].[Person]
OUTER APPLY [AdditionalContactInfo].nodes(
    'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
    /ci:AdditionalContactInfo') AS ContactInfo(ref) 
WHERE [AdditionalContactInfo] IS NOT NULL;
Name
vStateProvinceCountryRegion
Type
View
Description
Joins StateProvince table with CountryRegion table.
Schema
Person
Code
CREATE VIEW [Person].[vStateProvinceCountryRegion] 
WITH SCHEMABINDING 
AS 
SELECT 
    sp.[StateProvinceID] 
    ,sp.[StateProvinceCode] 
    ,sp.[IsOnlyStateProvinceFlag] 
    ,sp.[Name] AS [StateProvinceName] 
    ,sp.[TerritoryID] 
    ,cr.[CountryRegionCode] 
    ,cr.[Name] AS [CountryRegionName]
FROM [Person].[StateProvince] sp 
    INNER JOIN [Person].[CountryRegion] cr 
    ON sp.[CountryRegionCode] = cr.[CountryRegionCode];
Name
vProductAndDescription
Type
View
Description
Product names and descriptions. Product descriptions are provided in multiple languages.
Schema
Production
Code
CREATE VIEW [Production].[vProductAndDescription] 
WITH SCHEMABINDING 
AS 
-- View (indexed or standard) to display products and product descriptions by language.
SELECT 
    p.[ProductID] 
    ,p.[Name] 
    ,pm.[Name] AS [ProductModel] 
    ,pmx.[CultureID] 
    ,pd.[Description] 
FROM [Production].[Product] p 
    INNER JOIN [Production].[ProductModel] pm 
    ON p.[ProductModelID] = pm.[ProductModelID] 
    INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx 
    ON pm.[ProductModelID] = pmx.[ProductModelID] 
    INNER JOIN [Production].[ProductDescription] pd 
    ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID];
Name
vProductModelCatalogDescription
Type
View
Description
Displays the content from each element in the xml column CatalogDescription for each product in the Production.ProductModel table that has catalog data.
Schema
Production
Code
CREATE VIEW [Production].[vProductModelCatalogDescription] 
AS 
SELECT 
    [ProductModelID] 
    ,[Name] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace html="http://www.w3.org/1999/xhtml"; 
        (/p1:ProductDescription/p1:Summary/html:p)[1]', 'nvarchar(max)') AS [Summary] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Manufacturer/p1:Name)[1]', 'nvarchar(max)') AS [Manufacturer] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Manufacturer/p1:Copyright)[1]', 'nvarchar(30)') AS [Copyright] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Manufacturer/p1:ProductURL)[1]', 'nvarchar(256)') AS [ProductURL] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; 
        (/p1:ProductDescription/p1:Features/wm:Warranty/wm:WarrantyPeriod)[1]', 'nvarchar(256)') AS [WarrantyPeriod] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; 
        (/p1:ProductDescription/p1:Features/wm:Warranty/wm:Description)[1]', 'nvarchar(256)') AS [WarrantyDescription] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; 
        (/p1:ProductDescription/p1:Features/wm:Maintenance/wm:NoOfYears)[1]', 'nvarchar(256)') AS [NoOfYears] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; 
        (/p1:ProductDescription/p1:Features/wm:Maintenance/wm:Description)[1]', 'nvarchar(256)') AS [MaintenanceDescription] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:wheel)[1]', 'nvarchar(256)') AS [Wheel] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:saddle)[1]', 'nvarchar(256)') AS [Saddle] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:pedal)[1]', 'nvarchar(256)') AS [Pedal] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:BikeFrame)[1]', 'nvarchar(max)') AS [BikeFrame] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:crankset)[1]', 'nvarchar(256)') AS [Crankset] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Picture/p1:Angle)[1]', 'nvarchar(256)') AS [PictureAngle] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Picture/p1:Size)[1]', 'nvarchar(256)') AS [PictureSize] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Picture/p1:ProductPhotoID)[1]', 'nvarchar(256)') AS [ProductPhotoID] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/Material)[1]', 'nvarchar(256)') AS [Material] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/Color)[1]', 'nvarchar(256)') AS [Color] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/ProductLine)[1]', 'nvarchar(256)') AS [ProductLine] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/Style)[1]', 'nvarchar(256)') AS [Style] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/RiderExperience)[1]', 'nvarchar(1024)') AS [RiderExperience] 
    ,[rowguid] 
    ,[ModifiedDate]
FROM [Production].[ProductModel] 
WHERE [CatalogDescription] IS NOT NULL;
Name
vProductModelInstructions
Type
View
Description
Displays the content from each element in the xml column Instructions for each product in the Production.ProductModel table that has manufacturing instructions.
Schema
Production
Code
CREATE VIEW [Production].[vProductModelInstructions] 
AS 
SELECT 
    [ProductModelID] 
    ,[Name] 
    ,[Instructions].value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
        (/root/text())[1]', 'nvarchar(max)') AS [Instructions] 
    ,[MfgInstructions].ref.value('@LocationID[1]', 'int') AS [LocationID] 
    ,[MfgInstructions].ref.value('@SetupHours[1]', 'decimal(9, 4)') AS [SetupHours] 
    ,[MfgInstructions].ref.value('@MachineHours[1]', 'decimal(9, 4)') AS [MachineHours] 
    ,[MfgInstructions].ref.value('@LaborHours[1]', 'decimal(9, 4)') AS [LaborHours] 
    ,[MfgInstructions].ref.value('@LotSize[1]', 'int') AS [LotSize] 
    ,[Steps].ref.value('string(.)[1]', 'nvarchar(1024)') AS [Step] 
    ,[rowguid] 
    ,[ModifiedDate]
FROM [Production].[ProductModel] 
CROSS APPLY [Instructions].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
    /root/Location') MfgInstructions(ref)
CROSS APPLY [MfgInstructions].ref.nodes('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
    step') Steps(ref);
Name
vVendorWithAddresses
Type
View
Description
Vendor (company) names and addresses .
Schema
Purchasing
Code
CREATE VIEW [Purchasing].[vVendorWithAddresses] AS 
SELECT 
    v.[BusinessEntityID]
    ,v.[Name]
    ,at.[Name] AS [AddressType]
    ,a.[AddressLine1] 
    ,a.[AddressLine2] 
    ,a.[City] 
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode] 
    ,cr.[Name] AS [CountryRegionName] 
FROM [Purchasing].[Vendor] v
    INNER JOIN [Person].[BusinessEntityAddress] bea 
    ON bea.[BusinessEntityID] = v.[BusinessEntityID] 
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    INNER JOIN [Person].[AddressType] at 
    ON at.[AddressTypeID] = bea.[AddressTypeID];
Name
vVendorWithContacts
Type
View
Description
Vendor (company) names and the names of vendor employees to contact.
Schema
Purchasing
Code
CREATE VIEW [Purchasing].[vVendorWithContacts] AS 
SELECT 
    v.[BusinessEntityID]
    ,v.[Name]
    ,ct.[Name] AS [ContactType] 
    ,p.[Title] 
    ,p.[FirstName] 
    ,p.[MiddleName] 
    ,p.[LastName] 
    ,p.[Suffix] 
    ,pp.[PhoneNumber] 
	,pnt.[Name] AS [PhoneNumberType]
    ,ea.[EmailAddress] 
    ,p.[EmailPromotion] 
FROM [Purchasing].[Vendor] v
    INNER JOIN [Person].[BusinessEntityContact] bec 
    ON bec.[BusinessEntityID] = v.[BusinessEntityID]
	INNER JOIN [Person].ContactType ct
	ON ct.[ContactTypeID] = bec.[ContactTypeID]
	INNER JOIN [Person].[Person] p
	ON p.[BusinessEntityID] = bec.[PersonID]
	LEFT OUTER JOIN [Person].[EmailAddress] ea
	ON ea.[BusinessEntityID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[PersonPhone] pp
	ON pp.[BusinessEntityID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
	ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];
Name
vIndividualCustomer
Type
View
Description
Individual customers (names and addresses) that purchase Adventure Works Cycles products online.
Schema
Sales
Code
CREATE VIEW [Sales].[vIndividualCustomer] 
AS 
SELECT 
    p.[BusinessEntityID]
    ,p.[Title]
    ,p.[FirstName]
    ,p.[MiddleName]
    ,p.[LastName]
    ,p.[Suffix]
    ,pp.[PhoneNumber]
	,pnt.[Name] AS [PhoneNumberType]
    ,ea.[EmailAddress]
    ,p.[EmailPromotion]
    ,at.[Name] AS [AddressType]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
    ,p.[Demographics]
FROM [Person].[Person] p
    INNER JOIN [Person].[BusinessEntityAddress] bea 
    ON bea.[BusinessEntityID] = p.[BusinessEntityID] 
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    INNER JOIN [Person].[AddressType] at 
    ON at.[AddressTypeID] = bea.[AddressTypeID]
	INNER JOIN [Sales].[Customer] c
	ON c.[PersonID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[EmailAddress] ea
	ON ea.[BusinessEntityID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[PersonPhone] pp
	ON pp.[BusinessEntityID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
	ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID]
WHERE c.StoreID IS NULL;
Name
vPersonDemographics
Type
View
Description
Displays the content from each element in the xml column Demographics for each customer in the Person.Person table.
Schema
Sales
Code
CREATE VIEW [Sales].[vPersonDemographics] 
AS 
SELECT 
    p.[BusinessEntityID] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        TotalPurchaseYTD[1]', 'money') AS [TotalPurchaseYTD] 
    ,CONVERT(datetime, REPLACE([IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        DateFirstPurchase[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [DateFirstPurchase] 
    ,CONVERT(datetime, REPLACE([IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        BirthDate[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [BirthDate] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        MaritalStatus[1]', 'nvarchar(1)') AS [MaritalStatus] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        YearlyIncome[1]', 'nvarchar(30)') AS [YearlyIncome] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        Gender[1]', 'nvarchar(1)') AS [Gender] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        TotalChildren[1]', 'integer') AS [TotalChildren] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        NumberChildrenAtHome[1]', 'integer') AS [NumberChildrenAtHome] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        Education[1]', 'nvarchar(30)') AS [Education] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        Occupation[1]', 'nvarchar(30)') AS [Occupation] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        HomeOwnerFlag[1]', 'bit') AS [HomeOwnerFlag] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        NumberCarsOwned[1]', 'integer') AS [NumberCarsOwned] 
FROM [Person].[Person] p 
CROSS APPLY p.[Demographics].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
    /IndividualSurvey') AS [IndividualSurvey](ref) 
WHERE [Demographics] IS NOT NULL;
Name
vSalesPerson
Type
View
Description
Sales representiatives (names and addresses) and their sales-related information.
Schema
Sales
Code
CREATE VIEW [Sales].[vSalesPerson] 
AS 
SELECT 
    s.[BusinessEntityID]
    ,p.[Title]
    ,p.[FirstName]
    ,p.[MiddleName]
    ,p.[LastName]
    ,p.[Suffix]
    ,e.[JobTitle]
    ,pp.[PhoneNumber]
	,pnt.[Name] AS [PhoneNumberType]
    ,ea.[EmailAddress]
    ,p.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
    ,[TerritoryName] = st.[Name]
    ,[TerritoryGroup] = st.[Group]
    ,s.[SalesQuota]
    ,s.[SalesYTD]
    ,s.[SalesLastYear]
FROM [Sales].[SalesPerson] s
    INNER JOIN [HumanResources].[Employee] e 
    ON e.[BusinessEntityID] = s.[BusinessEntityID]
	INNER JOIN [Person].[Person] p
	ON p.[BusinessEntityID] = s.[BusinessEntityID]
    INNER JOIN [Person].[BusinessEntityAddress] bea 
    ON bea.[BusinessEntityID] = s.[BusinessEntityID] 
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    LEFT OUTER JOIN [Sales].[SalesTerritory] st 
    ON st.[TerritoryID] = s.[TerritoryID]
	LEFT OUTER JOIN [Person].[EmailAddress] ea
	ON ea.[BusinessEntityID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[PersonPhone] pp
	ON pp.[BusinessEntityID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
	ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];
Name
vSalesPersonSalesByFiscalYears
Type
View
Description
Uses PIVOT to return aggregated sales information for each sales representative.
Schema
Sales
Code
CREATE VIEW [Sales].[vSalesPersonSalesByFiscalYears] 
AS 
SELECT 
    pvt.[SalesPersonID]
    ,pvt.[FullName]
    ,pvt.[JobTitle]
    ,pvt.[SalesTerritory]
    ,pvt.[2002]
    ,pvt.[2003]
    ,pvt.[2004] 
FROM (SELECT 
        soh.[SalesPersonID]
        ,p.[FirstName] + ' ' + COALESCE(p.[MiddleName], '') + ' ' + p.[LastName] AS [FullName]
        ,e.[JobTitle]
        ,st.[Name] AS [SalesTerritory]
        ,soh.[SubTotal]
        ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
    FROM [Sales].[SalesPerson] sp 
        INNER JOIN [Sales].[SalesOrderHeader] soh 
        ON sp.[BusinessEntityID] = soh.[SalesPersonID]
        INNER JOIN [Sales].[SalesTerritory] st 
        ON sp.[TerritoryID] = st.[TerritoryID] 
        INNER JOIN [HumanResources].[Employee] e 
        ON soh.[SalesPersonID] = e.[BusinessEntityID] 
		INNER JOIN [Person].[Person] p
		ON p.[BusinessEntityID] = sp.[BusinessEntityID]
	 ) AS soh 
PIVOT 
(
    SUM([SubTotal]) 
    FOR [FiscalYear] 
    IN ([2002], [2003], [2004])
) AS pvt;
Name
vStoreWithAddresses
Type
View
Description
Stores (including store addresses) that sell Adventure Works Cycles products to consumers.
Schema
Sales
Code
CREATE VIEW [Sales].[vStoreWithAddresses] AS 
SELECT 
    s.[BusinessEntityID] 
    ,s.[Name] 
    ,at.[Name] AS [AddressType]
    ,a.[AddressLine1] 
    ,a.[AddressLine2] 
    ,a.[City] 
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode] 
    ,cr.[Name] AS [CountryRegionName] 
FROM [Sales].[Store] s
    INNER JOIN [Person].[BusinessEntityAddress] bea 
    ON bea.[BusinessEntityID] = s.[BusinessEntityID] 
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    INNER JOIN [Person].[AddressType] at 
    ON at.[AddressTypeID] = bea.[AddressTypeID];
Name
vStoreWithContacts
Type
View
Description
Stores (including store contacts) that sell Adventure Works Cycles products to consumers.
Schema
Sales
Code
CREATE VIEW [Sales].[vStoreWithContacts] AS 
SELECT 
    s.[BusinessEntityID] 
    ,s.[Name] 
    ,ct.[Name] AS [ContactType] 
    ,p.[Title] 
    ,p.[FirstName] 
    ,p.[MiddleName] 
    ,p.[LastName] 
    ,p.[Suffix] 
    ,pp.[PhoneNumber] 
	,pnt.[Name] AS [PhoneNumberType]
    ,ea.[EmailAddress] 
    ,p.[EmailPromotion] 
FROM [Sales].[Store] s
    INNER JOIN [Person].[BusinessEntityContact] bec 
    ON bec.[BusinessEntityID] = s.[BusinessEntityID]
	INNER JOIN [Person].[ContactType] ct
	ON ct.[ContactTypeID] = bec.[ContactTypeID]
	INNER JOIN [Person].[Person] p
	ON p.[BusinessEntityID] = bec.[PersonID]
	LEFT OUTER JOIN [Person].[EmailAddress] ea
	ON ea.[BusinessEntityID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[PersonPhone] pp
	ON pp.[BusinessEntityID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
	ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];
Name
vStoreWithDemographics
Type
View
Description
Stores (including demographics) that sell Adventure Works Cycles products to consumers.
Schema
Sales
Code
CREATE VIEW [Sales].[vStoreWithDemographics] AS 
SELECT 
    s.[BusinessEntityID] 
    ,s.[Name] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/AnnualSales)[1]', 'money') AS [AnnualSales] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/AnnualRevenue)[1]', 'money') AS [AnnualRevenue] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/BankName)[1]', 'nvarchar(50)') AS [BankName] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/BusinessType)[1]', 'nvarchar(5)') AS [BusinessType] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/YearOpened)[1]', 'integer') AS [YearOpened] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Specialty)[1]', 'nvarchar(50)') AS [Specialty] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/SquareFeet)[1]', 'integer') AS [SquareFeet] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Brands)[1]', 'nvarchar(30)') AS [Brands] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Internet)[1]', 'nvarchar(30)') AS [Internet] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/NumberEmployees)[1]', 'integer') AS [NumberEmployees] 
FROM [Sales].[Store] s;
Name
ufnGetAccountingEndDate
Type
Function
Description
Scalar function used in the uSalesOrderHeader trigger to set the starting account date.
Schema
dbo
Code
CREATE FUNCTION [dbo].[ufnGetAccountingEndDate]()
RETURNS [datetime] 
AS 
BEGIN
    RETURN DATEADD(millisecond, -2, CONVERT(datetime, '20040701', 112));
END;
Name
ufnGetAccountingStartDate
Type
Function
Description
Scalar function used in the uSalesOrderHeader trigger to set the ending account date.
Schema
dbo
Code
CREATE FUNCTION [dbo].[ufnGetAccountingStartDate]()
RETURNS [datetime] 
AS 
BEGIN
    RETURN CONVERT(datetime, '20030701', 112);
END;
Name
ufnGetContactInformation
Type
Function
Description
Table value function returning the first name, last name, job title and contact type for a given contact.
Schema
dbo
Code
CREATE FUNCTION [dbo].[ufnGetContactInformation](@PersonID int)
RETURNS @retContactInformation TABLE 
(
    -- Columns returned by the function
    [PersonID] int NOT NULL, 
    [FirstName] [nvarchar](50) NULL, 
    [LastName] [nvarchar](50) NULL, 
	[JobTitle] [nvarchar](50) NULL,
    [BusinessEntityType] [nvarchar](50) NULL
)
AS 
-- Returns the first name, last name, job title and business entity type for the specified contact.
-- Since a contact can serve multiple roles, more than one row may be returned.
BEGIN
	IF @PersonID IS NOT NULL 
		BEGIN
		IF EXISTS(SELECT * FROM [HumanResources].[Employee] e 
					WHERE e.[BusinessEntityID] = @PersonID) 
			INSERT INTO @retContactInformation
				SELECT @PersonID, p.FirstName, p.LastName, e.[JobTitle], 'Employee'
				FROM [HumanResources].[Employee] AS e
					INNER JOIN [Person].[Person] p
					ON p.[BusinessEntityID] = e.[BusinessEntityID]
				WHERE e.[BusinessEntityID] = @PersonID;

		IF EXISTS(SELECT * FROM [Purchasing].[Vendor] AS v
					INNER JOIN [Person].[BusinessEntityContact] bec 
					ON bec.[BusinessEntityID] = v.[BusinessEntityID]
					WHERE bec.[PersonID] = @PersonID)
			INSERT INTO @retContactInformation
				SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Vendor Contact' 
				FROM [Purchasing].[Vendor] AS v
					INNER JOIN [Person].[BusinessEntityContact] bec 
					ON bec.[BusinessEntityID] = v.[BusinessEntityID]
					INNER JOIN [Person].ContactType ct
					ON ct.[ContactTypeID] = bec.[ContactTypeID]
					INNER JOIN [Person].[Person] p
					ON p.[BusinessEntityID] = bec.[PersonID]
				WHERE bec.[PersonID] = @PersonID;
		
		IF EXISTS(SELECT * FROM [Sales].[Store] AS s
					INNER JOIN [Person].[BusinessEntityContact] bec 
					ON bec.[BusinessEntityID] = s.[BusinessEntityID]
					WHERE bec.[PersonID] = @PersonID)
			INSERT INTO @retContactInformation
				SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Store Contact' 
				FROM [Sales].[Store] AS s
					INNER JOIN [Person].[BusinessEntityContact] bec 
					ON bec.[BusinessEntityID] = s.[BusinessEntityID]
					INNER JOIN [Person].ContactType ct
					ON ct.[ContactTypeID] = bec.[ContactTypeID]
					INNER JOIN [Person].[Person] p
					ON p.[BusinessEntityID] = bec.[PersonID]
				WHERE bec.[PersonID] = @PersonID;

		IF EXISTS(SELECT * FROM [Person].[Person] AS p
					INNER JOIN [Sales].[Customer] AS c
					ON c.[PersonID] = p.[BusinessEntityID]
					WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL) 
			INSERT INTO @retContactInformation
				SELECT @PersonID, p.FirstName, p.LastName, NULL, 'Consumer' 
				FROM [Person].[Person] AS p
					INNER JOIN [Sales].[Customer] AS c
					ON c.[PersonID] = p.[BusinessEntityID]
					WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL; 
		END

	RETURN;
END;
Name
ufnGetDocumentStatusText
Type
Function
Description
Scalar function returning the text representation of the Status column in the Document table.
Schema
dbo
Code
CREATE FUNCTION [dbo].[ufnGetDocumentStatusText](@Status [tinyint])
RETURNS [nvarchar](16) 
AS 
-- Returns the sales order status text representation for the status value.
BEGIN
    DECLARE @ret [nvarchar](16);

    SET @ret = 
        CASE @Status
            WHEN 1 THEN N'Pending approval'
            WHEN 2 THEN N'Approved'
            WHEN 3 THEN N'Obsolete'
            ELSE N'** Invalid **'
        END;
    
    RETURN @ret
END;
Name
ufnGetProductDealerPrice
Type
Function
Description
Scalar function returning the dealer price for a given product on a particular order date.
Schema
dbo
Code


CREATE FUNCTION [dbo].[ufnGetProductDealerPrice](@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
-- Returns the dealer price for the product on a specific date.
BEGIN
    DECLARE @DealerPrice money;
    DECLARE @DealerDiscount money;

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

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

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

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

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

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

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

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

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

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

    RETURN (@ReturnValue);
END;
Name
uspGetBillOfMaterials
Type
Procedure
Description
Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.
Schema
dbo
Code
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
    @StartProductID [int],
    @CheckDate [datetime]
AS
BEGIN
    SET NOCOUNT ON;

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

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

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

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

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

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

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

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

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

    -- Print error information. 
    PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
          ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
          ', State ' + CONVERT(varchar(5), ERROR_STATE()) + 
          ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') + 
          ', Line ' + CONVERT(varchar(5), ERROR_LINE());
    PRINT ERROR_MESSAGE();
END;
Name
uspSearchCandidateResumes
Type
Procedure
Schema
dbo
Code
--A stored procedure which demonstrates integrated full text search

CREATE PROCEDURE [dbo].[uspSearchCandidateResumes]
    @searchString [nvarchar](1000),   
    @useInflectional [bit]=0,
    @useThesaurus [bit]=0,
    @language[int]=0


WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

      DECLARE @string nvarchar(1050)
      --setting the lcid to the default instance LCID if needed
      IF @language = NULL OR @language = 0 
      BEGIN 
            SELECT @language =CONVERT(int, serverproperty('lcid'))  
      END
      

            --FREETEXTTABLE case as inflectional and Thesaurus were required
      IF @useThesaurus = 1 AND @useInflectional = 1  
        BEGIN
                  SELECT FT_TBL.[JobCandidateID], KEY_TBL.[RANK] FROM [HumanResources].[JobCandidate] AS FT_TBL 
                        INNER JOIN FREETEXTTABLE([HumanResources].[JobCandidate],*, @searchString,LANGUAGE @language) AS KEY_TBL
                   ON  FT_TBL.[JobCandidateID] =KEY_TBL.[KEY]
            END

      ELSE IF @useThesaurus = 1
            BEGIN
                  SELECT @string ='FORMSOF(THESAURUS,"'+@searchString +'"'+')'      
                  SELECT FT_TBL.[JobCandidateID], KEY_TBL.[RANK] FROM [HumanResources].[JobCandidate] AS FT_TBL 
                        INNER JOIN CONTAINSTABLE([HumanResources].[JobCandidate],*, @string,LANGUAGE @language) AS KEY_TBL
                   ON  FT_TBL.[JobCandidateID] =KEY_TBL.[KEY]
        END

      ELSE IF @useInflectional = 1
            BEGIN
                  SELECT @string ='FORMSOF(INFLECTIONAL,"'+@searchString +'"'+')'
                  SELECT FT_TBL.[JobCandidateID], KEY_TBL.[RANK] FROM [HumanResources].[JobCandidate] AS FT_TBL 
                        INNER JOIN CONTAINSTABLE([HumanResources].[JobCandidate],*, @string,LANGUAGE @language) AS KEY_TBL
                   ON  FT_TBL.[JobCandidateID] =KEY_TBL.[KEY]
        END
  
      ELSE --base case, plain CONTAINSTABLE
            BEGIN
                  SELECT @string='"'+@searchString +'"'
                  SELECT FT_TBL.[JobCandidateID],KEY_TBL.[RANK] FROM [HumanResources].[JobCandidate] AS FT_TBL 
                        INNER JOIN CONTAINSTABLE([HumanResources].[JobCandidate],*,@string,LANGUAGE @language) AS KEY_TBL
                   ON  FT_TBL.[JobCandidateID] =KEY_TBL.[KEY]
            END

END;
Name
uspUpdateEmployeeHireInfo
Type
Procedure
Description
Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters.
Schema
HumanResources
Code
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
    @BusinessEntityID [int], 
    @JobTitle [nvarchar](50), 
    @HireDate [datetime], 
    @RateChangeDate [datetime], 
    @Rate [money], 
    @PayFrequency [tinyint], 
    @CurrentFlag [dbo].[Flag] 
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE [HumanResources].[Employee] 
        SET [JobTitle] = @JobTitle 
            ,[HireDate] = @HireDate 
            ,[CurrentFlag] = @CurrentFlag 
        WHERE [BusinessEntityID] = @BusinessEntityID;

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

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

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
Name
uspUpdateEmployeeLogin
Type
Procedure
Description
Updates the Employee table with the values specified in the input parameters for the given BusinessEntityID.
Schema
HumanResources
Code
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeLogin]
    @BusinessEntityID [int], 
    @OrganizationNode [hierarchyid],
    @LoginID [nvarchar](256),
    @JobTitle [nvarchar](50),
    @HireDate [datetime],
    @CurrentFlag [dbo].[Flag]
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

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

    BEGIN TRY
        UPDATE [HumanResources].[Employee] 
        SET [NationalIDNumber] = @NationalIDNumber 
            ,[BirthDate] = @BirthDate 
            ,[MaritalStatus] = @MaritalStatus 
            ,[Gender] = @Gender 
        WHERE [BusinessEntityID] = @BusinessEntityID;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
Name
dEmployee
Type
Trigger
Description
INSTEAD OF DELETE trigger which keeps Employees from being deleted.
Schema
HumanResources
Code
CREATE TRIGGER [HumanResources].[dEmployee] ON [HumanResources].[Employee] 
INSTEAD OF DELETE NOT FOR REPLICATION AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN
        RAISERROR
            (N'Employees cannot be deleted. They can only be marked as not current.', -- Message
            10, -- Severity.
            1); -- State.

        -- Rollback any active or uncommittable transactions
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
    END;
END;
Name
iuPerson
Type
Trigger
Description
AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Person table to the current date.
Schema
Person
Code
CREATE TRIGGER [Person].[iuPerson] ON [Person].[Person] 
AFTER INSERT, UPDATE NOT FOR REPLICATION AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    IF UPDATE([BusinessEntityID]) OR UPDATE([Demographics]) 
    BEGIN
        UPDATE [Person].[Person] 
        SET [Person].[Person].[Demographics] = N' 
            0.00 
            ' 
        FROM inserted 
        WHERE [Person].[Person].[BusinessEntityID] = inserted.[BusinessEntityID] 
            AND inserted.[Demographics] IS NULL;
        
        UPDATE [Person].[Person] 
        SET [Demographics].modify(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
            insert 0.00 
            as first 
            into (/IndividualSurvey)[1]') 
        FROM inserted 
        WHERE [Person].[Person].[BusinessEntityID] = inserted.[BusinessEntityID] 
            AND inserted.[Demographics] IS NOT NULL 
            AND inserted.[Demographics].exist(N'declare default element namespace 
                "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
                /IndividualSurvey/TotalPurchaseYTD') <> 1;
    END;
END;
Lines
Name
iWorkOrder
Type
Trigger
Description
AFTER INSERT trigger that inserts a row in the TransactionHistory table.
Schema
Production
Code
CREATE TRIGGER [Production].[iWorkOrder] ON [Production].[WorkOrder] 
AFTER INSERT AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        INSERT INTO [Production].[TransactionHistory](
            [ProductID]
            ,[ReferenceOrderID]
            ,[TransactionType]
            ,[TransactionDate]
            ,[Quantity]
            ,[ActualCost])
        SELECT 
            inserted.[ProductID]
            ,inserted.[WorkOrderID]
            ,'W'
            ,GETDATE()
            ,inserted.[OrderQty]
            ,0
        FROM inserted;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

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

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
Name
uWorkOrder
Type
Trigger
Description
AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.
Schema
Production
Code
CREATE TRIGGER [Production].[uWorkOrder] ON [Production].[WorkOrder] 
AFTER UPDATE AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        IF UPDATE([ProductID]) OR UPDATE([OrderQty])
        BEGIN
            INSERT INTO [Production].[TransactionHistory](
                [ProductID]
                ,[ReferenceOrderID]
                ,[TransactionType]
                ,[TransactionDate]
                ,[Quantity])
            SELECT 
                inserted.[ProductID]
                ,inserted.[WorkOrderID]
                ,'W'
                ,GETDATE()
                ,inserted.[OrderQty]
            FROM inserted;
        END;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

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

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
Name
iPurchaseOrderDetail
Type
Trigger
Description
AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.
Schema
Purchasing
Code
CREATE TRIGGER [Purchasing].[iPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail] 
AFTER INSERT AS
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        INSERT INTO [Production].[TransactionHistory]
            ([ProductID]
            ,[ReferenceOrderID]
            ,[ReferenceOrderLineID]
            ,[TransactionType]
            ,[TransactionDate]
            ,[Quantity]
            ,[ActualCost])
        SELECT 
            inserted.[ProductID]
            ,inserted.[PurchaseOrderID]
            ,inserted.[PurchaseOrderDetailID]
            ,'P'
            ,GETDATE()
            ,inserted.[OrderQty]
            ,inserted.[UnitPrice]
        FROM inserted 
            INNER JOIN [Purchasing].[PurchaseOrderHeader] 
            ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID];

        -- Update SubTotal in PurchaseOrderHeader record. Note that this causes the 
        -- PurchaseOrderHeader trigger to fire which will update the RevisionNumber.
        UPDATE [Purchasing].[PurchaseOrderHeader]
        SET [Purchasing].[PurchaseOrderHeader].[SubTotal] = 
            (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal])
                FROM [Purchasing].[PurchaseOrderDetail]
                WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID])
        WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN (SELECT inserted.[PurchaseOrderID] FROM inserted);
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

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

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
Name
uPurchaseOrderDetail
Type
Trigger
Description
AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.
Schema
Purchasing
Code
CREATE TRIGGER [Purchasing].[uPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail] 
AFTER UPDATE AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice])
        -- Insert record into TransactionHistory 
        BEGIN
            INSERT INTO [Production].[TransactionHistory]
                ([ProductID]
                ,[ReferenceOrderID]
                ,[ReferenceOrderLineID]
                ,[TransactionType]
                ,[TransactionDate]
                ,[Quantity]
                ,[ActualCost])
            SELECT 
                inserted.[ProductID]
                ,inserted.[PurchaseOrderID]
                ,inserted.[PurchaseOrderDetailID]
                ,'P'
                ,GETDATE()
                ,inserted.[OrderQty]
                ,inserted.[UnitPrice]
            FROM inserted 
                INNER JOIN [Purchasing].[PurchaseOrderDetail] 
                ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID];

            -- Update SubTotal in PurchaseOrderHeader record. Note that this causes the 
            -- PurchaseOrderHeader trigger to fire which will update the RevisionNumber.
            UPDATE [Purchasing].[PurchaseOrderHeader]
            SET [Purchasing].[PurchaseOrderHeader].[SubTotal] = 
                (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal])
                    FROM [Purchasing].[PurchaseOrderDetail]
                    WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] 
                        = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID])
            WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] 
                IN (SELECT inserted.[PurchaseOrderID] FROM inserted);

            UPDATE [Purchasing].[PurchaseOrderDetail]
            SET [Purchasing].[PurchaseOrderDetail].[ModifiedDate] = GETDATE()
            FROM inserted
            WHERE inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]
                AND inserted.[PurchaseOrderDetailID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderDetailID];
        END;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

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

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
Name
uPurchaseOrderHeader
Type
Trigger
Description
AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.
Schema
Purchasing
Code
CREATE TRIGGER [Purchasing].[uPurchaseOrderHeader] ON [Purchasing].[PurchaseOrderHeader] 
AFTER UPDATE AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        -- Update RevisionNumber for modification of any field EXCEPT the Status.
        IF NOT UPDATE([Status])
        BEGIN
            UPDATE [Purchasing].[PurchaseOrderHeader]
            SET [Purchasing].[PurchaseOrderHeader].[RevisionNumber] = 
                [Purchasing].[PurchaseOrderHeader].[RevisionNumber] + 1
            WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN 
                (SELECT inserted.[PurchaseOrderID] FROM inserted);
        END;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

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

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
Name
dVendor
Type
Trigger
Description
INSTEAD OF DELETE trigger which keeps Vendors from being deleted.
Schema
Purchasing
Code
CREATE TRIGGER [Purchasing].[dVendor] ON [Purchasing].[Vendor] 
INSTEAD OF DELETE NOT FOR REPLICATION AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        DECLARE @DeleteCount int;

        SELECT @DeleteCount = COUNT(*) FROM deleted;
        IF @DeleteCount > 0 
        BEGIN
            RAISERROR
                (N'Vendors cannot be deleted. They can only be marked as not active.', -- Message
                10, -- Severity.
                1); -- State.

        -- Rollback any active or uncommittable transactions
            IF @@TRANCOUNT > 0
            BEGIN
                ROLLBACK TRANSACTION;
            END
        END;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

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

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
Lines
Name
iduSalesOrderDetail
Type
Trigger
Description
AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.
Schema
Sales
Code
CREATE TRIGGER [Sales].[iduSalesOrderDetail] ON [Sales].[SalesOrderDetail] 
AFTER INSERT, DELETE, UPDATE AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        -- If inserting or updating these columns
        IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) OR UPDATE([UnitPriceDiscount]) 
        -- Insert record into TransactionHistory
        BEGIN
            INSERT INTO [Production].[TransactionHistory]
                ([ProductID]
                ,[ReferenceOrderID]
                ,[ReferenceOrderLineID]
                ,[TransactionType]
                ,[TransactionDate]
                ,[Quantity]
                ,[ActualCost])
            SELECT 
                inserted.[ProductID]
                ,inserted.[SalesOrderID]
                ,inserted.[SalesOrderDetailID]
                ,'S'
                ,GETDATE()
                ,inserted.[OrderQty]
                ,inserted.[UnitPrice]
            FROM inserted 
                INNER JOIN [Sales].[SalesOrderHeader] 
                ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID];

            UPDATE [Person].[Person] 
            SET [Demographics].modify('declare default element namespace 
                "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
                replace value of (/IndividualSurvey/TotalPurchaseYTD)[1] 
                with data(/IndividualSurvey/TotalPurchaseYTD)[1] + sql:column ("inserted.LineTotal")') 
            FROM inserted 
                INNER JOIN [Sales].[SalesOrderHeader] AS SOH
                ON inserted.[SalesOrderID] = SOH.[SalesOrderID] 
                INNER JOIN [Sales].[Customer] AS C
                ON SOH.[CustomerID] = C.[CustomerID]
            WHERE C.[PersonID] = [Person].[Person].[BusinessEntityID];
        END;

        -- Update SubTotal in SalesOrderHeader record. Note that this causes the 
        -- SalesOrderHeader trigger to fire which will update the RevisionNumber.
        UPDATE [Sales].[SalesOrderHeader]
        SET [Sales].[SalesOrderHeader].[SubTotal] = 
            (SELECT SUM([Sales].[SalesOrderDetail].[LineTotal])
                FROM [Sales].[SalesOrderDetail]
                WHERE [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID])
        WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN (SELECT inserted.[SalesOrderID] FROM inserted);

        UPDATE [Person].[Person] 
        SET [Demographics].modify('declare default element namespace 
            "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
            replace value of (/IndividualSurvey/TotalPurchaseYTD)[1] 
            with data(/IndividualSurvey/TotalPurchaseYTD)[1] - sql:column("deleted.LineTotal")') 
        FROM deleted 
            INNER JOIN [Sales].[SalesOrderHeader] 
            ON deleted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID] 
            INNER JOIN [Sales].[Customer]
            ON [Sales].[Customer].[CustomerID] = [Sales].[SalesOrderHeader].[CustomerID]
        WHERE [Sales].[Customer].[PersonID] = [Person].[Person].[BusinessEntityID];
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

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

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
Name
uSalesOrderHeader
Type
Trigger
Description
AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the SalesOrderHeader table.Updates the SalesYTD column in the SalesPerson and SalesTerritory tables.
Schema
Sales
Code
CREATE TRIGGER [Sales].[uSalesOrderHeader] ON [Sales].[SalesOrderHeader] 
AFTER UPDATE NOT FOR REPLICATION AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        -- Update RevisionNumber for modification of any field EXCEPT the Status.
        IF NOT UPDATE([Status])
        BEGIN
            UPDATE [Sales].[SalesOrderHeader]
            SET [Sales].[SalesOrderHeader].[RevisionNumber] = 
                [Sales].[SalesOrderHeader].[RevisionNumber] + 1
            WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN 
                (SELECT inserted.[SalesOrderID] FROM inserted);
        END;

        -- Update the SalesPerson SalesYTD when SubTotal is updated
        IF UPDATE([SubTotal])
        BEGIN
            DECLARE @StartDate datetime,
                    @EndDate datetime

            SET @StartDate = [dbo].[ufnGetAccountingStartDate]();
            SET @EndDate = [dbo].[ufnGetAccountingEndDate]();

            UPDATE [Sales].[SalesPerson]
            SET [Sales].[SalesPerson].[SalesYTD] = 
                (SELECT SUM([Sales].[SalesOrderHeader].[SubTotal])
                FROM [Sales].[SalesOrderHeader] 
                WHERE [Sales].[SalesPerson].[BusinessEntityID] = [Sales].[SalesOrderHeader].[SalesPersonID]
                    AND ([Sales].[SalesOrderHeader].[Status] = 5) -- Shipped
                    AND [Sales].[SalesOrderHeader].[OrderDate] BETWEEN @StartDate AND @EndDate)
            WHERE [Sales].[SalesPerson].[BusinessEntityID] 
                IN (SELECT DISTINCT inserted.[SalesPersonID] FROM inserted 
                    WHERE inserted.[OrderDate] BETWEEN @StartDate AND @EndDate);

            -- Update the SalesTerritory SalesYTD when SubTotal is updated
            UPDATE [Sales].[SalesTerritory]
            SET [Sales].[SalesTerritory].[SalesYTD] = 
                (SELECT SUM([Sales].[SalesOrderHeader].[SubTotal])
                FROM [Sales].[SalesOrderHeader] 
                WHERE [Sales].[SalesTerritory].[TerritoryID] = [Sales].[SalesOrderHeader].[TerritoryID]
                    AND ([Sales].[SalesOrderHeader].[Status] = 5) -- Shipped
                    AND [Sales].[SalesOrderHeader].[OrderDate] BETWEEN @StartDate AND @EndDate)
            WHERE [Sales].[SalesTerritory].[TerritoryID] 
                IN (SELECT DISTINCT inserted.[TerritoryID] FROM inserted 
                    WHERE inserted.[OrderDate] BETWEEN @StartDate AND @EndDate);
        END;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

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

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
Name
Flag
Type
UserDefinedType
Schema
dbo
Base type
bit
Not null
Yes
Name
NameStyle
Type
UserDefinedType
Schema
dbo
Base type
bit
Not null
Yes
Name
Name
Type
UserDefinedType
Schema
dbo
Base type
nvarchar
Params
4000
Name
OrderNumber
Type
UserDefinedType
Schema
dbo
Base type
nvarchar
Params
4000
Name
Phone
Type
UserDefinedType
Schema
dbo
Base type
nvarchar
Params
4000
Name
AccountNumber
Type
UserDefinedType
Schema
dbo
Base type
nvarchar
Params
4000
Notes
Name
Project
Note

MS SQL Server - Sample

Adventure Works - Schema "Person"

Name
Luna
Note

Luna Modeler

WWW.DATENSEN.COM

Name
note3
Note

MS SQL Server - Sample

Adventure Works - Schema "Person"

Name
logo
Note

Name
Purchasing
Name
Person
Name
Production
Name
Human Resources
Name
Sales
Name
Dbo
Name
Project1
Note

MS SQL Server - Sample

Adventure Works - ER Diagram with subject areas