Database reference - AdventureWorks

AdventureWorks -  tables -  [Sales].[SalesTerritory]

Description

Sales territory lookup table. 

Table properties

namevalue
name[Sales].[SalesTerritory]
createdMar 19 2009 9:08PM
modifiedMar 19 2009 9:08PM 
ansi nullson 
quoted identifieron 
row count10
Size of data8 kb
Size of indexes40 kb
Maximum size of a single row472 bytes

Columns

columndatatypelengthbytesdefaultnullsPKFKUQcomputedcomment
TerritoryIDint identity(1,1)104 no  yes    no  Primary key for SalesTerritory records. 
NameName50200 no      no  Sales territory description 
CountryRegionCodenvarchar(3)312 no      no  ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.  
Groupnvarchar(50)50200 no      no  Geographic area to which the sales territory belong. 
SalesYTDmoney198((0.00))no      no  Sales in the territory year to date. 
SalesLastYearmoney198((0.00))no      no  Sales in the territory the previous year. 
CostYTDmoney198((0.00))no      no  Business costs in the territory year to date. 
CostLastYearmoney198((0.00))no      no  Business costs in the territory the previous year. 
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_SalesTerritory_Namenonclustered, unique located on PRIMARYNameUnique nonclustered index. 
AK_SalesTerritory_rowguidnonclustered, unique located on PRIMARYrowguidUnique nonclustered index. Used to support replication samples. 
PK_SalesTerritory_TerritoryIDclustered, unique, primary key located on PRIMARYTerritoryIDClustered index created by a primary key constraint. 

Referenced by

name
StateProvince
Customer
SalesOrderHeader
SalesPerson
SalesTerritoryHistory

Foreign key graph

SalesTerritoryStateProvinceCustomerSalesOrderHeaderSalesPersonSalesTerritoryHistory

Check constraints

namecolumncomment
CK_SalesTerritory_SalesYTDSalesYTD    Check constraint [SalesYTD] >= (0.00) 
CK_SalesTerritory_SalesLastYearSalesLastYear    Check constraint [SalesLastYear] >= (0.00) 
CK_SalesTerritory_CostYTDCostYTD    Check constraint [CostYTD] >= (0.00) 
CK_SalesTerritory_CostLastYearCostLastYear    Check constraint [CostLastYear] >= (0.00) 

Defaults

default namecolumncomment
DF_SalesTerritory_SalesYTDSalesYTDDefault constraint value of 0.0 
DF_SalesTerritory_SalesLastYearSalesLastYearDefault constraint value of 0.0 
DF_SalesTerritory_CostYTDCostYTDDefault constraint value of 0.0 
DF_SalesTerritory_CostLastYearCostLastYearDefault constraint value of 0.0 
DF_SalesTerritory_rowguidrowguidDefault constraint value of NEWID() 
DF_SalesTerritory_ModifiedDateModifiedDateDefault constraint value of GETDATE() 

Dependency graph

DF_SalesTerritory_CostLastYearDF_SalesTerritory_CostYTDDF_SalesTerritory_ModifiedDateDF_SalesTerritory_rowguidDF_SalesTerritory_SalesLastYearDF_SalesTerritory_SalesYTDNameSalesTerritoryCK_SalesTerritory_CostLastYearCK_SalesTerritory_CostYTDCK_SalesTerritory_SalesLastYearCK_SalesTerritory_SalesYTDAK_SalesTerritory_NameAK_SalesTerritory_rowguid...PK_SalesTerritory_TerritoryID...uSalesOrderHeadervSalesPersonvSalesPersonSalesByFiscalYears

Objects that [Sales].[SalesTerritory] depends on

nameobject typelevel
Nametype1

Objects that depend on [Sales].[SalesTerritory]

nameobject typelevel
uSalesOrderHeadertrigger1
vSalesPersonview1
vSalesPersonSalesByFiscalYearsview1

Sample rows

TerritoryIDNameCountryRegionCodeGroupSalesYTDSalesLastYearCostYTDCostLastYearrowguidModifiedDate
1  Northwest  US  North America  5767341.9752  3298694.4938  0.0000  0.0000  43689a10-e30b-497f-b0de-11de20267ff7  6/1/1998 12:00:00 AM 
2  Northeast  US  North America  3857163.6331  3607148.9371  0.0000  0.0000  00fb7309-96cc-49e2-8363-0a1ba72486f2  6/1/1998 12:00:00 AM 
3  Central  US  North America  4677108.2690  3205014.0767  0.0000  0.0000  df6e7fd8-1a8d-468c-b103-ed8addb452c1  6/1/1998 12:00:00 AM 
4  Southwest  US  North America  8351296.7411  5366575.7098  0.0000  0.0000  dc3e9ea0-7950-4431-9428-99dbcbc33865  6/1/1998 12:00:00 AM 
5  Southeast  US  North America  2851419.0435  3925071.4318  0.0000  0.0000  6dc4165a-5e4c-42d2-809d-4344e0ac75e7  6/1/1998 12:00:00 AM 
6  Canada  CA  North America  6917270.8842  5693988.8600  0.0000  0.0000  06b4af8a-1639-476e-9266-110461d66b00  6/1/1998 12:00:00 AM 
7  France  FR  Europe  3899045.6940  2396539.7601  0.0000  0.0000  bf806804-9b4c-4b07-9d19-706f2e689552  6/1/1998 12:00:00 AM 
8  Germany  DE  Europe  2481039.1786  1307949.7917  0.0000  0.0000  6d2450db-8159-414f-a917-e73ee91c38a9  6/1/1998 12:00:00 AM 
9  Australia  AU  Pacific  1977474.8096  2278548.9776  0.0000  0.0000  602e612e-dfe9-41d9-b894-27e489747885  6/1/1998 12:00:00 AM 
10  United Kingdom  GB  Europe  3514865.9051  1635823.3967  0.0000  0.0000  05fc7e1f-2dea-414e-9ecd-09d150516fb5  6/1/1998 12:00:00 AM 

Code

CREATE TABLE [Sales].[SalesTerritory](
    [TerritoryID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [TerritoryID] [int] IDENTITY(1,1) 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]  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]
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]

Powered by Yoyodyne © 1971