AdventureWorks2008 - [Sales].[SpecialOffer]

Description

Sale discounts lookup table.

Table properties

name	value
name	[Sales].[SpecialOffer]
created	Mar 19 2009 9:09PM
modified	Mar 19 2009 9:10PM
ansi nulls	on
quoted identifier	on
row count	16
Size of data	8 kb
Size of indexes	24 kb
Maximum size of a single row	1,476 bytes

Columns

column	datatype	length	bytes	default	nulls	PK	computed	comment
SpecialOfferID	int identity(1,1)	10	4		no	yes	no	Primary key for SpecialOffer records.
Description	nvarchar(255)	255	1020		no		no	Discount description.
DiscountPct	smallmoney	10	4	((0.00))	no		no	Discount precentage.
Type	nvarchar(50)	50	200		no		no	Discount type category.
Category	nvarchar(50)	50	200		no		no	Group the discount applies to such as Reseller or Customer.
StartDate	datetime	23	8		no		no	Discount start date.
EndDate	datetime	23	8		no		no	Discount end date.
MinQty	int	10	4	((0))	no		no	Minimum discount percent allowed.
MaxQty	int	10	4		yes		no	Maximum discount percent allowed.
rowguid	uniqueidentifier	36	16	(newid())	no		no	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime	23	8	(getdate())	no		no	Date and time the record was last updated.

Indexes

name	description	column	comment
AK_SpecialOffer_rowguid	nonclustered, unique located on PRIMARY	rowguid	Unique nonclustered index. Used to support replication samples.
PK_SpecialOffer_SpecialOfferID	clustered, unique, primary key located on PRIMARY	SpecialOfferID	Clustered index created by a primary key constraint.

Referenced by

name
SpecialOfferProduct

Foreign key graph

SpecialOffer SpecialOfferProduct

Check constraints

name	column	comment
CK_SpecialOffer_EndDate ([EndDate]>=[StartDate])	StartDate EndDate	Check constraint [EndDate] >= [StartDate]
CK_SpecialOffer_DiscountPct ([DiscountPct]>=(0.00))	DiscountPct	Check constraint [DiscountPct] >= (0.00)
CK_SpecialOffer_MinQty ([MinQty]>=(0))	MinQty	Check constraint [MinQty] >= (0)
CK_SpecialOffer_MaxQty ([MaxQty]>=(0))	MaxQty	Check constraint [MaxQty] >= (0)

Defaults

default name	column	comment
DF_SpecialOffer_DiscountPct	DiscountPct	Default constraint value of 0.0
DF_SpecialOffer_MinQty	MinQty	Default constraint value of 0.0
DF_SpecialOffer_rowguid	rowguid	Default constraint value of NEWID()
DF_SpecialOffer_ModifiedDate	ModifiedDate	Default constraint value of GETDATE()

Dependency graph

DF_SpecialOffer_DiscountPctDF_SpecialOffer_MinQtyDF_SpecialOffer_ModifiedDateDF_SpecialOffer_rowguidSpecialOffer CK_SpecialOffer_DiscountPctCK_SpecialOffer_EndDateCK_SpecialOffer_MaxQtyCK_SpecialOffer_MinQtyAK_SpecialOffer_rowguidPK_SpecialOffer_SpecialOfferID...

Sample rows

SpecialOfferID	Description	DiscountPct	Type	Category	StartDate	EndDate	MinQty	MaxQty	rowguid	ModifiedDate
1	No Discount	0.0000	No Discount	No Discount	6/1/2001 12:00:00 AM	12/31/2004 12:00:00 AM	0	NULL	0290c4f5-191f-4337-ab6b-0a2dde03cbf9	5/2/2001 12:00:00 AM
2	Volume Discount 11 to 14	0.0200	Volume Discount	Reseller	7/1/2001 12:00:00 AM	6/30/2004 12:00:00 AM	11	14	d7542ee7-15db-4541-985c-5cc27aef26d6	6/1/2001 12:00:00 AM
3	Volume Discount 15 to 24	0.0500	Volume Discount	Reseller	7/1/2001 12:00:00 AM	6/30/2004 12:00:00 AM	15	24	4bdbcc01-8cf7-40a9-b643-40ec5b717491	6/1/2001 12:00:00 AM
4	Volume Discount 25 to 40	0.1000	Volume Discount	Reseller	7/1/2001 12:00:00 AM	6/30/2004 12:00:00 AM	25	40	504b5e85-8f3f-4ebc-9e1d-c1bc5dea9aa8	6/1/2001 12:00:00 AM
5	Volume Discount 41 to 60	0.1500	Volume Discount	Reseller	7/1/2001 12:00:00 AM	6/30/2004 12:00:00 AM	41	60	677e1d9d-944f-4e81-90e8-47eb0a82d48c	6/1/2001 12:00:00 AM
6	Volume Discount over 60	0.2000	Volume Discount	Reseller	7/1/2001 12:00:00 AM	6/30/2004 12:00:00 AM	61	NULL	8157f569-4e8d-46b6-9347-5d0f726a9439	6/1/2001 12:00:00 AM
7	Mountain-100 Clearance Sale	0.3500	Discontinued Product	Reseller	5/15/2002 12:00:00 AM	6/30/2002 12:00:00 AM	0	NULL	7df15bf5-6c05-47e7-80a4-22bd1ce59a72	4/15/2002 12:00:00 AM
8	Sport Helmet Discount-2002	0.1000	Seasonal Discount	Reseller	7/1/2002 12:00:00 AM	7/31/2002 12:00:00 AM	0	NULL	20c5d2cc-a38f-48f8-ac9a-8f15943e52ae	6/1/2002 12:00:00 AM
9	Road-650 Overstock	0.3000	Excess Inventory	Reseller	7/1/2002 12:00:00 AM	8/31/2002 12:00:00 AM	0	NULL	0cf8472b-f9e6-4945-9e09-549d7dde2198	6/1/2002 12:00:00 AM
10	Mountain Tire Sale	0.5000	Excess Inventory	Customer	6/15/2003 12:00:00 AM	8/30/2003 12:00:00 AM	0	NULL	220444ad-2ef3-4e4c-87e9-3aa6ee39a877	5/16/2003 12:00:00 AM

Code

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,
    [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,
    [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]  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]
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]

AdventureWorks2008 - tables - [Sales].[SpecialOffer]

Description

Table properties

Columns

Indexes

Referenced by

Foreign key graph

Check constraints

Defaults

Dependency graph

Sample rows

Code