Database reference - AdventureWorks2008

AdventureWorks2008 -  tables -  [Sales].[SalesTerritoryHistory]

Description

Sales representative transfers to other sales territories. 

Table properties

namevalue
name[Sales].[SalesTerritoryHistory]
createdMar 19 2009 9:09PM
modifiedMar 19 2009 9:10PM 
ansi nullson 
quoted identifieron 
row count17
Size of data8 kb
Size of indexes24 kb
Maximum size of a single row48 bytes

Columns

columndatatypelengthbytesdefaultnullsPKFKUQcomputedcomment
BusinessEntityIDint104 no  composite PK  SalesPerson.BusinessEntityID      no  Primary key. The sales rep. Foreign key to SalesPerson.BusinessEntityID. 
TerritoryIDint104 no  composite PK  SalesTerritory.TerritoryID      no  Primary key. Territory identification number. Foreign key to SalesTerritory.SalesTerritoryID. 
StartDatedatetime238 no  composite PK    no  Primary key. Date the sales representive started work in the territory. 
EndDatedatetime238 yes      no  Date the sales representative left work in the territory. 
rowguiduniqueidentifier3616(newid())no      no  ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. 
ModifiedDatedatetime238(getdate())no      no  Date and time the record was last updated. 

Indexes

namedescriptioncolumncomment
AK_SalesTerritoryHistory_rowguidnonclustered, unique located on PRIMARYrowguidUnique nonclustered index. Used to support replication samples. 
PK_SalesTerritoryHistory_BusinessEntityID_StartDate_TerritoryIDclustered, unique, primary key located on PRIMARYBusinessEntityID, StartDate, TerritoryIDClustered index created by a primary key constraint. 

References

name
SalesPerson
SalesTerritory

Foreign key graph

SalesPersonSalesTerritorySalesTerritoryHistory

Foreign keys

namecolumnsforeign columnstypecomment
FK_SalesTerritoryHistory_SalesPerson_BusinessEntityIDBusinessEntityIDSalesPerson.BusinessEntityID   Foreign key constraint referencing SalesPerson.SalesPersonID. 
FK_SalesTerritoryHistory_SalesTerritory_TerritoryIDTerritoryIDSalesTerritory.TerritoryID   Foreign key constraint referencing SalesTerritory.TerritoryID. 

Check constraints

namecolumncomment
CK_SalesTerritoryHistory_EndDateStartDate  EndDate    Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL 

Defaults

default namecolumncomment
DF_SalesTerritoryHistory_rowguidrowguidDefault constraint value of NEWID() 
DF_SalesTerritoryHistory_ModifiedDateModifiedDateDefault constraint value of GETDATE() 

Dependency graph

DF_SalesTerritoryHistory_ModifiedDateDF_SalesTerritoryHistory_rowguidSalesTerritoryHistoryCK_SalesTerritoryHistory_EndDateAK_SalesTerritoryHistory_rowguid...PK_SalesTerritoryHistory_BusinessEntityID_StartDat...

Sample rows

BusinessEntityIDTerritoryIDStartDateEndDaterowguidModifiedDate
275  2  7/1/2001 12:00:00 AM  12/31/2002 12:00:00 AM  8563ce6a-00ff-47d7-ba4d-3c3e1cdef531  12/24/2002 12:00:00 AM 
275  3  1/1/2003 12:00:00 AM  NULL  2f44304c-ee87-4c72-813e-ca75c5f61f4c  12/25/2002 12:00:00 AM 
276  4  7/1/2001 12:00:00 AM  NULL  64bcb1b3-a793-40ba-9859-d90f78c3f167  6/24/2001 12:00:00 AM 
277  3  7/1/2001 12:00:00 AM  12/31/2002 12:00:00 AM  3e9f893d-5142-46c9-a76a-867d1e3d6f90  12/24/2002 12:00:00 AM 
277  2  1/1/2003 12:00:00 AM  NULL  132e4721-32dd-4a73-b556-1837f3a2b9ae  12/25/2002 12:00:00 AM 
278  6  7/1/2001 12:00:00 AM  NULL  b7c8f9f5-5fb8-47b3-be73-1b9a14bdf8b9  6/24/2001 12:00:00 AM 
279  5  7/1/2001 12:00:00 AM  NULL  57d1cdcf-62ce-499f-8be8-1bb71c4bb7ef  6/24/2001 12:00:00 AM 
280  1  7/1/2001 12:00:00 AM  10/31/2002 12:00:00 AM  fd3f5566-10e2-4960-be12-0365e5665881  10/24/2002 12:00:00 AM 
281  4  7/1/2001 12:00:00 AM  NULL  9d8754b2-c320-40db-a77f-ff5a1bc0f46b  6/24/2001 12:00:00 AM 
282  6  7/1/2001 12:00:00 AM  6/30/2002 12:00:00 AM  2c9f5240-d8bf-4f85-897d-6083146dbc4b  6/23/2002 12:00:00 AM 

Code

CREATE TABLE [Sales].[SalesTerritoryHistory](
    [BusinessEntityID] [int] NOT NULL,
    [TerritoryID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [BusinessEntityID] [int] NOT NULL,
    [TerritoryID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] 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]  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]
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]



Documentation generated by SqlSpec