AdventureWorks2008 - [Sales].[SalesPerson]

Description

Sales representative current information.

Table properties

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

Columns

column	datatype	length	bytes	default	nulls	PK	FK	computed	comment
BusinessEntityID	int	10	4		no	yes	Employee.BusinessEntityID	no	Primary key for SalesPerson records. Foreign key to Employee.BusinessEntityID
TerritoryID	int	10	4		yes		SalesTerritory.TerritoryID	no	Territory currently assigned to. Foreign key to SalesTerritory.SalesTerritoryID.
SalesQuota	money	19	8		yes			no	Projected yearly sales.
Bonus	money	19	8	((0.00))	no			no	Bonus due if quota is met.
CommissionPct	smallmoney	10	4	((0.00))	no			no	Commision percent received per sale.
SalesYTD	money	19	8	((0.00))	no			no	Sales total year to date.
SalesLastYear	money	19	8	((0.00))	no			no	Sales total of previous year.
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_SalesPerson_rowguid	nonclustered, unique located on PRIMARY	rowguid	Unique nonclustered index. Used to support replication samples.
PK_SalesPerson_BusinessEntityID	clustered, unique, primary key located on PRIMARY	BusinessEntityID	Clustered index created by a primary key constraint. ddd

References

name
Employee
SalesTerritory

Referenced by

name
SalesOrderHeader
SalesPersonQuotaHistory
SalesTerritoryHistory
Store

Foreign key graph

EmployeeSalesTerritorySalesPerson SalesOrderHeaderSalesPersonQuotaHistorySalesTerritoryHistoryStore

Foreign keys

name	columns	foreign columns	type	comment
FK_SalesPerson_Employee_BusinessEntityID	BusinessEntityID	Employee.BusinessEntityID		Foreign key constraint referencing Employee.EmployeeID.
FK_SalesPerson_SalesTerritory_TerritoryID	TerritoryID	SalesTerritory.TerritoryID		Foreign key constraint referencing SalesTerritory.TerritoryID.

Check constraints

name	column	comment
CK_SalesPerson_SalesQuota ([SalesQuota]>(0.00))	SalesQuota	Check constraint [SalesQuota] > (0.00)
CK_SalesPerson_Bonus ([Bonus]>=(0.00))	Bonus	Check constraint [Bonus] >= (0.00)
CK_SalesPerson_CommissionPct ([CommissionPct]>=(0.00))	CommissionPct	Check constraint [CommissionPct] >= (0.00)
CK_SalesPerson_SalesYTD ([SalesYTD]>=(0.00))	SalesYTD	Check constraint [SalesYTD] >= (0.00)
CK_SalesPerson_SalesLastYear ([SalesLastYear]>=(0.00))	SalesLastYear	Check constraint [SalesLastYear] >= (0.00)

Defaults

default name	column	comment
DF_SalesPerson_Bonus	Bonus	Default constraint value of 0.0
DF_SalesPerson_CommissionPct	CommissionPct	Default constraint value of 0.0
DF_SalesPerson_SalesYTD	SalesYTD	Default constraint value of 0.0
DF_SalesPerson_SalesLastYear	SalesLastYear	Default constraint value of 0.0
DF_SalesPerson_rowguid	rowguid	Default constraint value of NEWID()
DF_SalesPerson_ModifiedDate	ModifiedDate	Default constraint value of GETDATE()

Dependency graph

Objects that depend on [Sales].[SalesPerson]

name	object type	database	server	level
Employee Sales Summary 2008	Report	reportserver_katmai	SPRING	1
Product Line Sales 2008	Report	reportserver_katmai	SPRING	1
Sales Order Detail 2008	Report	reportserver_katmai	SPRING	1
Territory Sales Drilldown 2008	Report	reportserver_katmai	SPRING	1
uSalesOrderHeader	trigger	AdventureWorks2008	SPRING\KATMAI	1
vSalesPerson	view	AdventureWorks2008	SPRING\KATMAI	1
vSalesPersonSalesByFiscalYears	view	AdventureWorks2008	SPRING\KATMAI	1

Sample rows

BusinessEntityID	TerritoryID	SalesQuota	Bonus	CommissionPct	SalesYTD	SalesLastYear	rowguid	ModifiedDate
274	NULL	NULL	0.0000	0.0000	677558.4653	0.0000	48754992-9ee0-4c0e-8c94-9451604e3e02	1/28/2001 12:00:00 AM
275	2	300000.0000	4100.0000	0.0120	4557045.0459	1750406.4785	1e0a7274-3064-4f58-88ee-4c6586c87169	6/24/2001 12:00:00 AM
276	4	250000.0000	2000.0000	0.0150	5200475.2313	1439156.0291	4dd9eee4-8e81-4f8c-af97-683394c1f7c0	6/24/2001 12:00:00 AM
277	3	250000.0000	2500.0000	0.0150	3857163.6332	1997186.2037	39012928-bfec-4242-874d-423162c3f567	6/24/2001 12:00:00 AM
278	6	250000.0000	500.0000	0.0100	1764938.9859	1620276.8966	7a0ae1ab-b283-40f9-91d1-167abf06d720	6/24/2001 12:00:00 AM
279	5	300000.0000	6700.0000	0.0100	2811012.7151	1849640.9418	52a5179d-3239-4157-ae29-17e868296dc0	6/24/2001 12:00:00 AM
280	1	250000.0000	5000.0000	0.0100	0.0000	1927059.1780	be941a4a-fb50-4947-bda4-bb8972365b08	6/24/2001 12:00:00 AM
281	4	250000.0000	3550.0000	0.0100	3018725.4858	2073505.9999	35326ddb-7278-4fef-b3ba-ea137b69094e	6/24/2001 12:00:00 AM
282	6	250000.0000	5000.0000	0.0150	3189356.2465	2038234.6549	31fd7fc1-dc84-4f05-b9a0-762519eacacc	6/24/2001 12:00:00 AM
283	1	250000.0000	3500.0000	0.0120	3587378.4257	1371635.3158	6bac15b2-8ffb-45a9-b6d5-040e16c2073f	6/24/2001 12:00:00 AM

Code

CREATE TABLE [Sales].[SalesPerson](
    [BusinessEntityID] [int] NOT NULL,
    [TerritoryID] [int] NULL,
    [SalesQuota] [money] NULL,
    [Bonus] [money] NOT NULL,
    [CommissionPct] [smallmoney] NOT NULL,
    [SalesYTD] [money] NOT NULL,
    [SalesLastYear] [money] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [BusinessEntityID] [int] NOT NULL,
    [TerritoryID] [int] NULL,
    [SalesQuota] [money] NULL,
    [Bonus] [money] NOT NULL,
    [CommissionPct] [smallmoney] NOT NULL,
    [SalesYTD] [money] NOT NULL,
    [SalesLastYear] [money] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SalesPerson_BusinessEntityID] PRIMARY KEY CLUSTERED 
(
    [BusinessEntityID] 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].[SalesPerson]  WITH CHECK ADD  CONSTRAINT [FK_SalesPerson_Employee_BusinessEntityID] FOREIGN KEY([BusinessEntityID])
REFERENCES [HumanResources].[Employee] ([BusinessEntityID])
ALTER TABLE [Sales].[SalesPerson] CHECK CONSTRAINT [FK_SalesPerson_Employee_BusinessEntityID]
ALTER TABLE [Sales].[SalesPerson]  WITH CHECK ADD  CONSTRAINT [FK_SalesPerson_SalesTerritory_TerritoryID] FOREIGN KEY([TerritoryID])
REFERENCES [Sales].[SalesTerritory] ([TerritoryID])
ALTER TABLE [Sales].[SalesPerson] CHECK CONSTRAINT [FK_SalesPerson_SalesTerritory_TerritoryID]
ALTER TABLE [Sales].[SalesPerson]  WITH CHECK ADD  CONSTRAINT [CK_SalesPerson_Bonus] CHECK  (([Bonus]>=(0.00)))
ALTER TABLE [Sales].[SalesPerson] CHECK CONSTRAINT [CK_SalesPerson_Bonus]
ALTER TABLE [Sales].[SalesPerson]  WITH CHECK ADD  CONSTRAINT [CK_SalesPerson_CommissionPct] CHECK  (([CommissionPct]>=(0.00)))
ALTER TABLE [Sales].[SalesPerson] CHECK CONSTRAINT [CK_SalesPerson_CommissionPct]
ALTER TABLE [Sales].[SalesPerson]  WITH CHECK ADD  CONSTRAINT [CK_SalesPerson_SalesLastYear] CHECK  (([SalesLastYear]>=(0.00)))
ALTER TABLE [Sales].[SalesPerson] CHECK CONSTRAINT [CK_SalesPerson_SalesLastYear]
ALTER TABLE [Sales].[SalesPerson]  WITH CHECK ADD  CONSTRAINT [CK_SalesPerson_SalesQuota] CHECK  (([SalesQuota]>(0.00)))
ALTER TABLE [Sales].[SalesPerson] CHECK CONSTRAINT [CK_SalesPerson_SalesQuota]
ALTER TABLE [Sales].[SalesPerson]  WITH CHECK ADD  CONSTRAINT [CK_SalesPerson_SalesYTD] CHECK  (([SalesYTD]>=(0.00)))
ALTER TABLE [Sales].[SalesPerson] CHECK CONSTRAINT [CK_SalesPerson_SalesYTD]
ALTER TABLE [Sales].[SalesPerson] ADD  CONSTRAINT [DF_SalesPerson_Bonus]  DEFAULT ((0.00)) FOR [Bonus]
ALTER TABLE [Sales].[SalesPerson] ADD  CONSTRAINT [DF_SalesPerson_CommissionPct]  DEFAULT ((0.00)) FOR [CommissionPct]
ALTER TABLE [Sales].[SalesPerson] ADD  CONSTRAINT [DF_SalesPerson_SalesYTD]  DEFAULT ((0.00)) FOR [SalesYTD]
ALTER TABLE [Sales].[SalesPerson] ADD  CONSTRAINT [DF_SalesPerson_SalesLastYear]  DEFAULT ((0.00)) FOR [SalesLastYear]
ALTER TABLE [Sales].[SalesPerson] ADD  CONSTRAINT [DF_SalesPerson_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Sales].[SalesPerson] ADD  CONSTRAINT [DF_SalesPerson_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

AdventureWorks2008 - tables - [Sales].[SalesPerson]

Description

Table properties

Columns

Indexes

References

Referenced by

Foreign key graph

Foreign keys

Check constraints

Defaults

Dependency graph

Objects that depend on [Sales].[SalesPerson]

Sample rows

Code