Sales.SalesPerson Table

Description

Sales representative current information.

Properties

Creation Date	08/01/2010 08:40
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	17
Data Space Used	8.00 KB
Index Space Used	24.00 KB

Columns

Column Name	Description	Datatype	Length	Default
SalesPersonID	Primary key for SalesPerson records.	int	4
TerritoryID	Territory currently assigned to. Foreign key to SalesTerritory.SalesTerritoryID.	int	4
SalesQuota	Projected yearly sales.	money	8
Bonus	Bonus due if quota is met.	money	8	((0.00))
CommissionPct	Commision percent received per sale.	smallmoney	8	((0.00))
SalesYTD	Sales total year to date.	money	8	((0.00))
SalesLastYear	Sales total of previous year.	money	8	((0.00))
rowguid	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	uniqueidentifier	16	(newid())
ModifiedDate	Date and time the record was last updated.	datetime	4	(getdate())

Indexes

Index	Description	Primary	Unique
AK_SalesPerson_rowguid	Unique nonclustered index. Used to support replication samples.
PK_SalesPerson_SalesPersonID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_SalesPerson_Bonus	Check constraint [Bonus] >= (0.00)	([Bonus]>=(0.00))
CK_SalesPerson_CommissionPct	Check constraint [CommissionPct] >= (0.00)	([CommissionPct]>=(0.00))
CK_SalesPerson_SalesLastYear	Check constraint [SalesLastYear] >= (0.00)	([SalesLastYear]>=(0.00))
CK_SalesPerson_SalesQuota	Check constraint [SalesQuota] > (0.00)	([SalesQuota]>(0.00))
CK_SalesPerson_SalesYTD	Check constraint [SalesYTD] >= (0.00)	([SalesYTD]>=(0.00))

Relationships

Relationship	Description
FK_SalesOrderHeader_SalesPerson_SalesPersonID	Foreign key constraint referencing SalesPerson.SalesPersonID.
FK_SalesPerson_Employee_SalesPersonID	Foreign key constraint referencing Employee.EmployeeID.
FK_SalesPerson_SalesTerritory_TerritoryID	Foreign key constraint referencing SalesTerritory.TerritoryID.
FK_SalesPersonQuotaHistory_SalesPerson_SalesPersonID	Foreign key constraint referencing SalesPerson.SalesPersonID.
FK_SalesTerritoryHistory_SalesPerson_SalesPersonID	Foreign key constraint referencing SalesPerson.SalesPersonID.
FK_Store_SalesPerson_SalesPersonID	Foreign key constraint referencing SalesPerson.SalesPersonID

Objects that depend on Sales.SalesPerson

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
Sales.Individual	Table	Demographic data about customers that purchase Adventure Works products online.	2
iStore	Trigger	AFTER INSERT trigger inserting Store only if the Customer does not exist in the Individual table.	2
iuIndividual	Trigger	AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Individual table to the current date.	3
Sales.SalesOrderDetail	Table	Individual products associated with a specific sales order. See SalesOrderHeader.	2
Sales.SalesOrderHeader	Table	General sales order information.	1
Sales.SalesOrderHeaderSalesReason	Table	Cross-reference table mapping sales orders to sales reason codes.	2
Sales.SalesPersonQuotaHistory	Table	Sales performance tracking.	1
Sales.SalesTerritoryHistory	Table	Sales representative transfers to other sales territories.	1
Sales.Store	Table	Customers (resellers) of Adventure Works products.	1
Sales.StoreContact	Table	Cross-reference table mapping stores and their employees.	2
dbo.ufnGetContactInformation	User Defined Function	Table value function returning the first name, last name, job title and contact type for a given contact.	3
uSalesOrderHeader	Trigger	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the SalesOrderHeader table.Updates the SalesYTD column in the SalesPerson and SalesTerritory tables.	2
Sales.vIndividualCustomer	View	Individual customers (names and addresses) that purchase Adventure Works Cycles products online.	3
Sales.vIndividualDemographics	View	Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table.	3
Sales.vSalesPerson	View	Sales representiatives (names and addresses) and their sales-related information.	1
Sales.vSalesPersonSalesByFiscalYears	View	Uses PIVOT to return aggregated sales information for each sales representative.	1
Sales.vStoreWithDemographics	View	Stores (names and addresses) that sell Adventure Works Cycles products to consumers.	2

Objects that Sales.SalesPerson depends on

Database Object	Object Type	Description	Dep Level
Person.Contact	Table	Names of each employee, customer contact, and vendor contact.	2
HumanResources.Employee	Table	Employee information such as salary, department, and title.	1
dbo.Flag	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		3
dbo.Phone	User Defined Data Type		3
Sales.SalesTerritory	Table	Sales territory lookup table.	1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SalesPerson](
	[SalesPersonID] [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,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SalesPerson_SalesPersonID] PRIMARY KEY CLUSTERED 
(
	[SalesPersonID] 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_SalesPersonID] FOREIGN KEY([SalesPersonID])
REFERENCES [Employee] ([EmployeeID])
ALTER TABLE [Sales].[SalesPerson] CHECK CONSTRAINT [FK_SalesPerson_Employee_SalesPersonID]
ALTER TABLE [Sales].[SalesPerson]  WITH CHECK ADD  CONSTRAINT [FK_SalesPerson_SalesTerritory_TerritoryID] FOREIGN KEY([TerritoryID])
REFERENCES [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]

Related Objects

Sales Schema
AdventureWorks Database