Table: Sales.SalesOrderHeader

Properties

Owner:	Sales
Creation Date:	04/26/2006
Located On:	PRIMARY
Data Size KB:	5592
Index Size KB:	2384
Rows:	31465
Description:	General sales order information.

Columns

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

Total: 27 column(s)

Identity column

	Name	Seed	Increment	Not for replication
	SalesOrderID	1	1

Indexes

Index	Primary	Unique	Description
PK_SalesOrderHeader_SalesOrderID			Primary key (clustered) constraint
IX_SalesOrderHeader_CustomerID			Nonclustered index.
IX_SalesOrderHeader_SalesPersonID			Nonclustered index.
AK_SalesOrderHeader_SalesOrderNumber			Unique nonclustered index.
AK_SalesOrderHeader_rowguid			Unique nonclustered index. Used to support replication samples.

Total: 5 index(es)

Triggers

	Name	Owner	Instead Of	Disabled	Table/View	Description
	uSalesOrderHeader	Sales			Sales.SalesOrderHeader	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the SalesOrderHeader table.Updates the SalesYTD column in the SalesPerson and SalesTerritory tables.

Total: 1 trigger(s)

Check Constraints

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

Total: 6 constraint(s)

Referencing Tables

Table	Foreign Key	Primary Key or Unique Constraint
Sales.SalesOrderDetail	FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID	PK_SalesOrderHeader_SalesOrderID
Sales.SalesOrderHeaderSalesReason	FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID	PK_SalesOrderHeader_SalesOrderID

Total: 2 table(s)

Referenced Tables

Table	Foreign Key	Primary Key or Unique Constraint
Person.Address	FK_SalesOrderHeader_Address_BillToAddressID	PK_Address_AddressID
Person.Address	FK_SalesOrderHeader_Address_ShipToAddressID	PK_Address_AddressID
Person.Contact	FK_SalesOrderHeader_Contact_ContactID	PK_Contact_ContactID
Sales.CreditCard	FK_SalesOrderHeader_CreditCard_CreditCardID	PK_CreditCard_CreditCardID
Sales.CurrencyRate	FK_SalesOrderHeader_CurrencyRate_CurrencyRateID	PK_CurrencyRate_CurrencyRateID
Sales.Customer	FK_SalesOrderHeader_Customer_CustomerID	PK_Customer_CustomerID
Sales.SalesPerson	FK_SalesOrderHeader_SalesPerson_SalesPersonID	PK_SalesPerson_SalesPersonID
Sales.SalesTerritory	FK_SalesOrderHeader_SalesTerritory_TerritoryID	PK_SalesTerritory_TerritoryID
Purchasing.ShipMethod	FK_SalesOrderHeader_ShipMethod_ShipMethodID	PK_ShipMethod_ShipMethodID

Total: 9 table(s)

Objects that [Sales].[SalesOrderHeader] depends on

Object Name	Owner	Object Type	Dep Level
AccountNumber	dbo	User Defined type	1
Flag	dbo	User Defined type	1
Name	dbo	User Defined type	1
NameStyle	dbo	User Defined type	1
OrderNumber	dbo	User Defined type	1
Phone	dbo	User Defined type	1
ufnLeadingZeros	dbo	Function	2
Contact	Person	Table	2
CountryRegion	Person	Table	2
CreditCard	Sales	Table	2
Currency	Sales	Table	2
SalesTerritory	Sales	Table	2
ShipMethod	Purchasing	Table	2
CurrencyRate	Sales	Table	3
Customer	Sales	Table	3
Employee	HumanResources	Table	3
StateProvince	Person	Table	3
Address	Person	Table	4
SalesPerson	Sales	Table	4

Total: 19 object(s)

Objects that depend on [Sales].[SalesOrderHeader]

Object Name	Owner	Object Type	Dep Level
vSalesPersonSalesByFiscalYears	Sales	View	1
SalesOrderDetail	Sales	Table	1
SalesOrderHeaderSalesReason	Sales	Table	1
uSalesOrderHeader	Sales	Trigger	1
iduSalesOrderDetail	Sales	Trigger	2

Total: 5 object(s)

SQL

CREATE TABLE [SalesOrderHeader] (
    [SalesOrderID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
    [RevisionNumber] [tinyint] NOT NULL CONSTRAINT [DF_SalesOrderHeader_RevisionNumber] DEFAULT ((0)),
    [OrderDate] [datetime] NOT NULL CONSTRAINT [DF_SalesOrderHeader_OrderDate] DEFAULT (getdate()),
    [DueDate] [datetime] NOT NULL ,
    [ShipDate] [datetime] NULL ,
    [Status] [tinyint] NOT NULL CONSTRAINT [DF_SalesOrderHeader_Status] DEFAULT ((1)),
    [OnlineOrderFlag] [Flag] NOT NULL CONSTRAINT [DF_SalesOrderHeader_OnlineOrderFlag] DEFAULT ((1)),
    [SalesOrderNumber] AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID],0),N'*** ERROR ***')) ,
    [PurchaseOrderNumber] [OrderNumber] NULL ,
    [AccountNumber] [AccountNumber] NULL ,
    [CustomerID] [int] NOT NULL ,
    [ContactID] [int] NOT NULL ,
    [SalesPersonID] [int] NULL ,
    [TerritoryID] [int] NULL ,
    [BillToAddressID] [int] NOT NULL ,
    [ShipToAddressID] [int] NOT NULL ,
    [ShipMethodID] [int] NOT NULL ,
    [CreditCardID] [int] NULL ,
    [CreditCardApprovalCode] [varchar] (15) COLLATE Latin1_General_CS_AS NULL ,
    [CurrencyRateID] [int] NULL ,
    [SubTotal] [money] NOT NULL CONSTRAINT [DF_SalesOrderHeader_SubTotal] DEFAULT ((0.00)),
    [TaxAmt] [money] NOT NULL CONSTRAINT [DF_SalesOrderHeader_TaxAmt] DEFAULT ((0.00)),
    [Freight] [money] NOT NULL CONSTRAINT [DF_SalesOrderHeader_Freight] DEFAULT ((0.00)),
    [TotalDue] AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))) ,
    [Comment] [nvarchar] (128) COLLATE Latin1_General_CS_AS NULL ,
    [rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL CONSTRAINT [DF_SalesOrderHeader_rowguid] DEFAULT (newid()),
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_SalesOrderHeader_ModifiedDate] DEFAULT (getdate()),
    CONSTRAINT [PK_SalesOrderHeader_SalesOrderID] PRIMARY KEY  CLUSTERED
    (
        [SalesOrderID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_SalesOrderHeader_Address_BillToAddressID] FOREIGN KEY
    (
        [BillToAddressID]
    ) REFERENCES [Address] (
        [AddressID]
    ),
    CONSTRAINT [FK_SalesOrderHeader_Address_ShipToAddressID] FOREIGN KEY
    (
        [ShipToAddressID]
    ) REFERENCES [Address] (
        [AddressID]
    ),
    CONSTRAINT [FK_SalesOrderHeader_Contact_ContactID] FOREIGN KEY
    (
        [ContactID]
    ) REFERENCES [Contact] (
        [ContactID]
    ),
    CONSTRAINT [FK_SalesOrderHeader_CreditCard_CreditCardID] FOREIGN KEY
    (
        [CreditCardID]
    ) REFERENCES [CreditCard] (
        [CreditCardID]
    ),
    CONSTRAINT [FK_SalesOrderHeader_CurrencyRate_CurrencyRateID] FOREIGN KEY
    (
        [CurrencyRateID]
    ) REFERENCES [CurrencyRate] (
        [CurrencyRateID]
    ),
    CONSTRAINT [FK_SalesOrderHeader_Customer_CustomerID] FOREIGN KEY
    (
        [CustomerID]
    ) REFERENCES [Customer] (
        [CustomerID]
    ),
    CONSTRAINT [FK_SalesOrderHeader_SalesPerson_SalesPersonID] FOREIGN KEY
    (
        [SalesPersonID]
    ) REFERENCES [SalesPerson] (
        [SalesPersonID]
    ),
    CONSTRAINT [FK_SalesOrderHeader_SalesTerritory_TerritoryID] FOREIGN KEY
    (
        [TerritoryID]
    ) REFERENCES [SalesTerritory] (
        [TerritoryID]
    ),
    CONSTRAINT [FK_SalesOrderHeader_ShipMethod_ShipMethodID] FOREIGN KEY
    (
        [ShipMethodID]
    ) REFERENCES [ShipMethod] (
        [ShipMethodID]
    ),
    CONSTRAINT [CK_SalesOrderHeader_DueDate] CHECK ([DueDate]>=[OrderDate]),
    CONSTRAINT [CK_SalesOrderHeader_Freight] CHECK ([Freight]>=(0.00)),
    CONSTRAINT [CK_SalesOrderHeader_ShipDate] CHECK ([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL),
    CONSTRAINT [CK_SalesOrderHeader_Status] CHECK ([Status]>=(0) AND [Status]<=(8)),
    CONSTRAINT [CK_SalesOrderHeader_SubTotal] CHECK ([SubTotal]>=(0.00)),
    CONSTRAINT [CK_SalesOrderHeader_TaxAmt] CHECK ([TaxAmt]>=(0.00))
) ON [PRIMARY]
GO