AdventureWorks

Basic Index   Expanded Index

Table Name:Sales.SalesOrderHeader
Description:General sales order information.
Primary Keys:SalesOrderID
FieldTypeDefaultsNulls?Comments
SalesOrderIDint - IdentityNoneNoPrimary key.
RevisionNumbertinyint((0))NoIncremental number to track changes to the sales order over time.
OrderDatedatetime(getdate())NoDates the sales order was created.
DueDatedatetimeNoneNoDate the order is due to the customer.
ShipDatedatetimeNoneYesDate the order was shipped to the customer.
Statustinyint((1))NoOrder current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled
OnlineOrderFlagbit((1))No0 = Order placed by sales person. 1 = Order placed online by customer.
SalesOrderNumbernvarchar (25)NoneNoUnique sales order identification number.
PurchaseOrderNumbernvarchar (25)NoneYesCustomer purchase order number reference.
AccountNumbernvarchar (15)NoneYesFinancial accounting number reference.
CustomerIDintNoneNoCustomer identification number. Foreign key to Customer.CustomerID.
ContactIDintNoneNoCustomer contact identification number. Foreign key to Contact.ContactID.
SalesPersonIDintNoneYesSales person who created the sales order. Foreign key to SalesPerson.SalePersonID.
TerritoryIDintNoneYesTerritory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID.
BillToAddressIDintNoneNoCustomer billing address. Foreign key to Address.AddressID.
ShipToAddressIDintNoneNoCustomer shipping address. Foreign key to Address.AddressID.
ShipMethodIDintNoneNoShipping method. Foreign key to ShipMethod.ShipMethodID.
CreditCardIDintNoneYesCredit card identification number. Foreign key to CreditCard.CreditCardID.
CreditCardApprovalCodevarchar (15)NoneYesApproval code provided by the credit card company.
CurrencyRateIDintNoneYesCurrency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID.
SubTotalmoney((0.00))NoSales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.
TaxAmtmoney((0.00))NoTax amount.
Freightmoney((0.00))NoShipping cost.
TotalDuemoneyNoneNoTotal due from customer. Computed as Subtotal + TaxAmt + Freight.
Commentnvarchar (128)NoneYesSales representative comments.
rowguiduniqueidentifier(newid())NoROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDatedatetime(getdate())NoDate and time the record was last updated.

IndexClustered?Unique?Fields
PK_SalesOrderHeader_SalesOrderIDYesYesSalesOrderID
AK_SalesOrderHeader_rowguidNoYesrowguid
AK_SalesOrderHeader_SalesOrderNumberNoYesSalesOrderNumber
IX_SalesOrderHeader_CustomerIDNoNoCustomerID
IX_SalesOrderHeader_SalesPersonIDNoNoSalesPersonID

Check ConstraintText
CK_SalesOrderHeader_Status([Status]>=(0) AND [Status]<=(8))
CK_SalesOrderHeader_DueDate([DueDate]>=[OrderDate])
CK_SalesOrderHeader_ShipDate([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)
CK_SalesOrderHeader_SubTotal([SubTotal]>=(0.00))
CK_SalesOrderHeader_TaxAmt([TaxAmt]>=(0.00))
CK_SalesOrderHeader_Freight([Freight]>=(0.00))

Internal Foreign Key ConstraintAffected FieldSource Table
FK_SalesOrderHeader_Address_BillToAddressIDBillToAddressIDPerson.Address
FK_SalesOrderHeader_Address_ShipToAddressIDShipToAddressIDPerson.Address
FK_SalesOrderHeader_Contact_ContactIDContactIDPerson.Contact
FK_SalesOrderHeader_CreditCard_CreditCardIDCreditCardIDSales.CreditCard
FK_SalesOrderHeader_CurrencyRate_CurrencyRateIDCurrencyRateIDSales.CurrencyRate
FK_SalesOrderHeader_Customer_CustomerIDCustomerIDSales.Customer
FK_SalesOrderHeader_SalesPerson_SalesPersonIDSalesPersonIDSales.SalesPerson
FK_SalesOrderHeader_SalesTerritory_TerritoryIDTerritoryIDSales.SalesTerritory
FK_SalesOrderHeader_ShipMethod_ShipMethodIDShipMethodIDPurchasing.ShipMethod

Primary Key as Foreign Key ConstraintAffected TableAffected Field
FK_SalesOrderDetail_SalesOrderHeader_SalesOrderIDSales.SalesOrderDetailSalesOrderID
FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderIDSales.SalesOrderHeaderSalesReasonSalesOrderID

TriggerText
uSalesOrderHeaderCREATE 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
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;
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].[SalesPersonID] = [Sales].[SalesOrderHeader].[SalesPersonID]
AND ([Sales].[SalesOrderHeader].[Status] = 5) -- Shipped
AND [Sales].[SalesOrderHeader].[OrderDate] BETWEEN @StartDate AND @EndDate)
WHERE [Sales].[SalesPerson].[SalesPersonID]
IN (SELECT DISTINCT inserted.[SalesPersonID] FROM inserted
WHERE inserted.[OrderDate] BETWEEN @StartDate AND @EndDate);
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];
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspLogError];
END CATCH;
END;