Northwind Database (Document! X Sample)
Sales.SpecialOffer Table
AdventureWorks Database > Sales Schema : Sales.SpecialOffer Table
Description
Sale discounts lookup table.
Properties
Creation Date27/10/2017 14:33
File GroupPRIMARY
Text File Group
System Object
Published for Replication
Rows16
Data Space Used8.00 KB
Index Space Used24.00 KB
Columns
 Column NameDescriptionDatatypeLengthAllow NullsDefaultFormula
Primary Key Primary key for SpecialOffer records.Integer4   
 Discount description.VarWChar255   
 Discount precentage.Currency8 
((0.00))
 
 Discount type category.VarWChar50   
 Group the discount applies to such as Reseller or Customer.VarWChar50   
 Discount start date.DBTimeStamp4   
 Discount end date.DBTimeStamp4   
 Minimum discount percent allowed.Integer4 
((0))
 
 Maximum discount percent allowed.Integer4  
 ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.GUID16 
(newid())
 
 Date and time the record was last updated.DBTimeStamp4 
(getdate())
 
Indexes
IndexDescriptionPrimaryUnique
Unique nonclustered index. Used to support replication samples. 
Primary key (clustered) constraint
Check Constraints
NameDescriptionExpression
Check constraint [DiscountPct] >= (0.00)
([DiscountPct]>=(0.00))
Check constraint [EndDate] >= [StartDate]
([EndDate]>=[StartDate])
Check constraint [MaxQty] >= (0)
([MaxQty]>=(0))
Check constraint [MinQty] >= (0)
([MinQty]>=(0))
Objects that depend on Sales.SpecialOffer
 Database ObjectObject TypeDescriptionDep Level
iduSalesOrderDetail triggeriduSalesOrderDetailTriggerAFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.3
Sales.SalesOrderDetail tableSales.SalesOrderDetailTableIndividual products associated with a specific sales order. See SalesOrderHeader.2
Sales.SpecialOfferProduct tableSales.SpecialOfferProductTableCross-reference table mapping products to special offer discounts.1
SQL
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SpecialOffer](
    [SpecialOfferID] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [DiscountPct] [smallmoney] NOT NULL,
    [Type] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Category] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NOT NULL,
    [MinQty] [int] NOT NULL,
    [MaxQty] [int] NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SpecialOffer_SpecialOfferID] PRIMARY KEY CLUSTERED 
(
    [SpecialOfferID] 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].[SpecialOffer] ADD  CONSTRAINT [DF_SpecialOffer_DiscountPct]  DEFAULT ((0.00)) FOR [DiscountPct]
ALTER TABLE [Sales].[SpecialOffer] ADD  CONSTRAINT [DF_SpecialOffer_MinQty]  DEFAULT ((0)) FOR [MinQty]
ALTER TABLE [Sales].[SpecialOffer] ADD  CONSTRAINT [DF_SpecialOffer_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Sales].[SpecialOffer] ADD  CONSTRAINT [DF_SpecialOffer_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Sales].[SpecialOffer]  WITH CHECK ADD  CONSTRAINT [CK_SpecialOffer_DiscountPct] CHECK  (([DiscountPct]>=(0.00)))
ALTER TABLE [Sales].[SpecialOffer] CHECK CONSTRAINT [CK_SpecialOffer_DiscountPct]
ALTER TABLE [Sales].[SpecialOffer]  WITH CHECK ADD  CONSTRAINT [CK_SpecialOffer_EndDate] CHECK  (([EndDate]>=[StartDate]))
ALTER TABLE [Sales].[SpecialOffer] CHECK CONSTRAINT [CK_SpecialOffer_EndDate]
ALTER TABLE [Sales].[SpecialOffer]  WITH CHECK ADD  CONSTRAINT [CK_SpecialOffer_MaxQty] CHECK  (([MaxQty]>=(0)))
ALTER TABLE [Sales].[SpecialOffer] CHECK CONSTRAINT [CK_SpecialOffer_MaxQty]
ALTER TABLE [Sales].[SpecialOffer]  WITH CHECK ADD  CONSTRAINT [CK_SpecialOffer_MinQty] CHECK  (([MinQty]>=(0)))
ALTER TABLE [Sales].[SpecialOffer] CHECK CONSTRAINT [CK_SpecialOffer_MinQty]
See Also

Related Objects

Sales Schema
AdventureWorks Database