Sales.SalesTerritory Table

Description

Sales territory lookup table.

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	10
Data Space Used	8.00 KB
Index Space Used	40.00 KB

Columns

Column Name	Description	Datatype	Length	Default
TerritoryID	Primary key for SalesTerritory records.	Integer	4
Name	Sales territory description	dbo.Name	50
CountryRegionCode	ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.	VarWChar	3
Group	Geographic area to which the sales territory belong.	VarWChar	50
SalesYTD	Sales in the territory year to date.	Currency	8	((0.00))
SalesLastYear	Sales in the territory the previous year.	Currency	8	((0.00))
CostYTD	Business costs in the territory year to date.	Currency	8	((0.00))
CostLastYear	Business costs in the territory the previous year.	Currency	8	((0.00))
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_SalesTerritory_Name	Unique nonclustered index.
AK_SalesTerritory_rowguid	Unique nonclustered index. Used to support replication samples.
PK_SalesTerritory_TerritoryID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_SalesTerritory_CostLastYear	Check constraint [CostLastYear] >= (0.00)	([CostLastYear]>=(0.00))
CK_SalesTerritory_CostYTD	Check constraint [CostYTD] >= (0.00)	([CostYTD]>=(0.00))
CK_SalesTerritory_SalesLastYear	Check constraint [SalesLastYear] >= (0.00)	([SalesLastYear]>=(0.00))
CK_SalesTerritory_SalesYTD	Check constraint [SalesYTD] >= (0.00)	([SalesYTD]>=(0.00))

Relationships

Relationship	Description
FK_SalesTerritory_CountryRegion_CountryRegionCode	Foreign key constraint referencing CountryRegion.CountryRegionCode.

Objects that depend on Sales.SalesTerritory

Database Object	Object Type	Description	Dep Level
Person.Address	Table	Street address information for customers, employees, and vendors.	2
Person.BusinessEntityAddress	Table	Cross-reference table mapping customers, vendors, and employees to their addresses.	3
Sales.Customer	Table	Current customer information. Also see the Person and Store tables.	1
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.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.SalesPerson	Table	Sales representative current information.	1
Sales.SalesPersonQuotaHistory	Table	Sales performance tracking.	2
Sales.SalesTaxRate	Table	Tax rate lookup table.	2
Sales.SalesTerritoryHistory	Table	Sales representative transfers to other sales territories.	1
Person.StateProvince	Table	State and province lookup table.	1
Sales.Store	Table	Customers (resellers) of Adventure Works products.	2
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
HumanResources.vEmployee	View	Employee names and addresses.	2
Sales.vIndividualCustomer	View	Individual customers (names and addresses) that purchase Adventure Works Cycles products online.	2
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
Person.vStateProvinceCountryRegion	View	Joins StateProvince table with CountryRegion table.	2
Sales.vStoreWithContacts	View	Stores (including store contacts) that sell Adventure Works Cycles products to consumers.	3
Sales.vStoreWithDemographics	View	Stores (including demographics) that sell Adventure Works Cycles products to consumers.	3
Purchasing.vVendorWithAddresses	View	Vendor (company) names and addresses .	2

Objects that Sales.SalesTerritory depends on

	Database Object	Object Type	Description	Dep Level
	Person.CountryRegion	Table	Lookup table containing the ISO standard codes for countries and regions.	1
	dbo.Name	User Defined Data Type		1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SalesTerritory](
    [TerritoryID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [CountryRegionCode] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Group] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [SalesYTD] [money] NOT NULL,
    [SalesLastYear] [money] NOT NULL,
    [CostYTD] [money] NOT NULL,
    [CostLastYear] [money] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SalesTerritory_TerritoryID] PRIMARY KEY CLUSTERED 
(
    [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].[SalesTerritory] ADD  CONSTRAINT [DF_SalesTerritory_SalesYTD]  DEFAULT ((0.00)) FOR [SalesYTD]
ALTER TABLE [Sales].[SalesTerritory] ADD  CONSTRAINT [DF_SalesTerritory_SalesLastYear]  DEFAULT ((0.00)) FOR [SalesLastYear]
ALTER TABLE [Sales].[SalesTerritory] ADD  CONSTRAINT [DF_SalesTerritory_CostYTD]  DEFAULT ((0.00)) FOR [CostYTD]
ALTER TABLE [Sales].[SalesTerritory] ADD  CONSTRAINT [DF_SalesTerritory_CostLastYear]  DEFAULT ((0.00)) FOR [CostLastYear]
ALTER TABLE [Sales].[SalesTerritory] ADD  CONSTRAINT [DF_SalesTerritory_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Sales].[SalesTerritory] ADD  CONSTRAINT [DF_SalesTerritory_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Sales].[SalesTerritory]  WITH CHECK ADD  CONSTRAINT [FK_SalesTerritory_CountryRegion_CountryRegionCode] FOREIGN KEY([CountryRegionCode])
REFERENCES [Person].[CountryRegion] ([CountryRegionCode])
ALTER TABLE [Sales].[SalesTerritory] CHECK CONSTRAINT [FK_SalesTerritory_CountryRegion_CountryRegionCode]
ALTER TABLE [Sales].[SalesTerritory]  WITH CHECK ADD  CONSTRAINT [CK_SalesTerritory_CostLastYear] CHECK  (([CostLastYear]>=(0.00)))
ALTER TABLE [Sales].[SalesTerritory] CHECK CONSTRAINT [CK_SalesTerritory_CostLastYear]
ALTER TABLE [Sales].[SalesTerritory]  WITH CHECK ADD  CONSTRAINT [CK_SalesTerritory_CostYTD] CHECK  (([CostYTD]>=(0.00)))
ALTER TABLE [Sales].[SalesTerritory] CHECK CONSTRAINT [CK_SalesTerritory_CostYTD]
ALTER TABLE [Sales].[SalesTerritory]  WITH CHECK ADD  CONSTRAINT [CK_SalesTerritory_SalesLastYear] CHECK  (([SalesLastYear]>=(0.00)))
ALTER TABLE [Sales].[SalesTerritory] CHECK CONSTRAINT [CK_SalesTerritory_SalesLastYear]
ALTER TABLE [Sales].[SalesTerritory]  WITH CHECK ADD  CONSTRAINT [CK_SalesTerritory_SalesYTD] CHECK  (([SalesYTD]>=(0.00)))
ALTER TABLE [Sales].[SalesTerritory] CHECK CONSTRAINT [CK_SalesTerritory_SalesYTD]

Related Objects

Sales Schema
AdventureWorks Database