Sales.SalesOrderDetail Table

In This Topic

Description

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

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	121317
Data Space Used	9,912.00 KB
Index Space Used	6,160.00 KB

Columns

Column Name	Description	Datatype	Length	Default	Formula
SalesOrderID	Primary key. Foreign key to SalesOrderHeader.SalesOrderID.	Integer	4
SalesOrderDetailID	Primary key. One incremental unique number per product sold.	Integer	4
CarrierTrackingNumber	Shipment tracking number supplied by the shipper.	VarWChar	25
OrderQty	Quantity ordered per product.	SmallInt	2
ProductID	Product sold to customer. Foreign key to Product.ProductID.	Integer	4
SpecialOfferID	Promotional code. Foreign key to SpecialOffer.SpecialOfferID.	Integer	4
UnitPrice	Selling price of a single product.	Currency	8
UnitPriceDiscount	Discount amount.	Currency	8	((0.0))
LineTotal	Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.	Numeric	9 (38,6)		(isnull(([UnitPrice]((1.0)-[UnitPriceDiscount]))[OrderQty],(0.0)))
rowguid	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	GUID	16	(newid())
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
AK_SalesOrderDetail_rowguid	Unique nonclustered index. Used to support replication samples.
IX_SalesOrderDetail_ProductID	Nonclustered index.
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_SalesOrderDetail_OrderQty	Check constraint [OrderQty] > (0)	([OrderQty]>(0))
CK_SalesOrderDetail_UnitPrice	Check constraint [UnitPrice] >= (0.00)	([UnitPrice]>=(0.00))
CK_SalesOrderDetail_UnitPriceDiscount	Check constraint [UnitPriceDiscount] >= (0.00)	([UnitPriceDiscount]>=(0.00))

Triggers

Trigger	Description
iduSalesOrderDetail	AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.

Relationships

Relationship	Description
FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID	Foreign key constraint referencing SalesOrderHeader.PurchaseOrderID.
FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID	Foreign key constraint referencing SpecialOfferProduct.SpecialOfferIDProductID.

Objects that depend on Sales.SalesOrderDetail

	Database Object	Object Type	Description	Dep Level
	iduSalesOrderDetail	Trigger	AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.	3

Objects that Sales.SalesOrderDetail depends on

Database Object	Object Type	Description	Dep Level
dbo.AccountNumber	User Defined Data Type		2
Person.Address	Table	Street address information for customers, employees, and vendors.	1
Sales.CreditCard	Table	Customer credit card information.	2
Sales.Currency	Table	Lookup table containing standard ISO currencies.	3
Sales.CurrencyRate	Table	Currency exchange rates.	2
Sales.Customer	Table	Current customer information. Also see the Person and Store tables.	2
HumanResources.Employee	Table	Employee information such as salary, department, and title.	3
dbo.ErrorLog	Table	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.	3
dbo.Flag	User Defined Data Type		4
dbo.Name	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		5
dbo.OrderNumber	User Defined Data Type		2
Person.Person	Table	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.	4
Production.Product	Table	Products sold or used in the manfacturing of sold products.	1
Production.ProductCategory	Table	High-level product categorization.	3
Production.ProductModel	Table	Product model classification.	2
Production.ProductSubcategory	Table	Product subcategories. See ProductCategory table.	2
Sales.SalesOrderHeader	Table	General sales order information.	1
Sales.SalesPerson	Table	Sales representative current information.	2
Sales.SalesTerritory	Table	Sales territory lookup table.	3
Purchasing.ShipMethod	Table	Shipping company lookup table.	2
Sales.SpecialOffer	Table	Sale discounts lookup table.	1
Sales.SpecialOfferProduct	Table	Cross-reference table mapping products to special offer discounts.	1
Person.StateProvince	Table	State and province lookup table.	1
Sales.Store	Table	Customers (resellers) of Adventure Works products.	1
Production.TransactionHistory	Table	Record of each purchase order, sales order, or work order transaction year to date.	1
dbo.ufnGetAccountingEndDate	User Defined Function	Scalar function used in the uSalesOrderHeader trigger to set the starting account date.	2
dbo.ufnGetAccountingStartDate	User Defined Function	Scalar function used in the uSalesOrderHeader trigger to set the ending account date.	2
dbo.ufnLeadingZeros	User Defined Function	Scalar function used by the Sales.Customer table to help set the account number.	3
Production.UnitMeasure	Table	Unit of measure lookup table.	2
dbo.uspLogError	Stored Procedure	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.	2
dbo.uspPrintError	Stored Procedure	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.	2

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SalesOrderDetail](
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal]  AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED 
(
    [SalesOrderID] ASC,
    [SalesOrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [Sales].[SalesOrderDetail] ADD  CONSTRAINT [DF_SalesOrderDetail_UnitPriceDiscount]  DEFAULT ((0.0)) FOR [UnitPriceDiscount]
ALTER TABLE [Sales].[SalesOrderDetail] ADD  CONSTRAINT [DF_SalesOrderDetail_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Sales].[SalesOrderDetail] ADD  CONSTRAINT [DF_SalesOrderDetail_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Sales].[SalesOrderDetail]  WITH CHECK ADD  CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID] FOREIGN KEY([SalesOrderID])
REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])
ON DELETE CASCADE
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID]
ALTER TABLE [Sales].[SalesOrderDetail]  WITH CHECK ADD  CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID] FOREIGN KEY([SpecialOfferID], [ProductID])
REFERENCES [Sales].[SpecialOfferProduct] ([SpecialOfferID], [ProductID])
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]
ALTER TABLE [Sales].[SalesOrderDetail]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderDetail_OrderQty] CHECK  (([OrderQty]>(0)))
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_OrderQty]
ALTER TABLE [Sales].[SalesOrderDetail]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderDetail_UnitPrice] CHECK  (([UnitPrice]>=(0.00)))
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_UnitPrice]
ALTER TABLE [Sales].[SalesOrderDetail]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderDetail_UnitPriceDiscount] CHECK  (([UnitPriceDiscount]>=(0.00)))
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_UnitPriceDiscount]

Related Objects

Sales Schema
AdventureWorks Database

Send comments on this topic.