Sales.SalesTerritoryHistory Table

Description

Sales representative transfers to other sales territories.

Properties

Creation Date	27/10/2017 14:33
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
BusinessEntityID	Primary key. The sales rep. Foreign key to SalesPerson.BusinessEntityID.	Integer	4
TerritoryID	Primary key. Territory identification number. Foreign key to SalesTerritory.SalesTerritoryID.	Integer	4
StartDate	Primary key. Date the sales representive started work in the territory.	DBTimeStamp	4
EndDate	Date the sales representative left work in the territory.	DBTimeStamp	4
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_SalesTerritoryHistory_rowguid	Unique nonclustered index. Used to support replication samples.
PK_SalesTerritoryHistory_BusinessEntityID_StartDate_TerritoryID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_SalesTerritoryHistory_EndDate	Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL	([EndDate]>=[StartDate] OR [EndDate] IS NULL)

Relationships

Relationship	Description
FK_SalesTerritoryHistory_SalesPerson_BusinessEntityID	Foreign key constraint referencing SalesPerson.SalesPersonID.
FK_SalesTerritoryHistory_SalesTerritory_TerritoryID	Foreign key constraint referencing SalesTerritory.TerritoryID.

Objects that Sales.SalesTerritoryHistory depends on

Database Object	Object Type	Description	Dep Level
Person.BusinessEntity	Table	Source of the ID that connects vendors, customers, and employees with address and contact information.	4
Person.CountryRegion	Table	Lookup table containing the ISO standard codes for countries and regions.	2
HumanResources.Employee	Table	Employee information such as salary, department, and title.	2
dbo.Flag	User Defined Data Type		3
dbo.NameStyle	User Defined Data Type		4
Person.Person	Table	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.	3
Sales.SalesPerson	Table	Sales representative current information.	1
Sales.SalesTerritory	Table	Sales territory lookup table.	1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SalesTerritoryHistory](
    [BusinessEntityID] [int] NOT NULL,
    [TerritoryID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SalesTerritoryHistory_BusinessEntityID_StartDate_TerritoryID] PRIMARY KEY CLUSTERED 
(
    [BusinessEntityID] ASC,
    [StartDate] ASC,
    [TerritoryID] 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].[SalesTerritoryHistory] ADD  CONSTRAINT [DF_SalesTerritoryHistory_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Sales].[SalesTerritoryHistory] ADD  CONSTRAINT [DF_SalesTerritoryHistory_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Sales].[SalesTerritoryHistory]  WITH CHECK ADD  CONSTRAINT [FK_SalesTerritoryHistory_SalesPerson_BusinessEntityID] FOREIGN KEY([BusinessEntityID])
REFERENCES [Sales].[SalesPerson] ([BusinessEntityID])
ALTER TABLE [Sales].[SalesTerritoryHistory] CHECK CONSTRAINT [FK_SalesTerritoryHistory_SalesPerson_BusinessEntityID]
ALTER TABLE [Sales].[SalesTerritoryHistory]  WITH CHECK ADD  CONSTRAINT [FK_SalesTerritoryHistory_SalesTerritory_TerritoryID] FOREIGN KEY([TerritoryID])
REFERENCES [Sales].[SalesTerritory] ([TerritoryID])
ALTER TABLE [Sales].[SalesTerritoryHistory] CHECK CONSTRAINT [FK_SalesTerritoryHistory_SalesTerritory_TerritoryID]
ALTER TABLE [Sales].[SalesTerritoryHistory]  WITH CHECK ADD  CONSTRAINT [CK_SalesTerritoryHistory_EndDate] CHECK  (([EndDate]>=[StartDate] OR [EndDate] IS NULL))
ALTER TABLE [Sales].[SalesTerritoryHistory] CHECK CONSTRAINT [CK_SalesTerritoryHistory_EndDate]

Related Objects

Sales Schema
AdventureWorks Database