Person.StateProvince Table

Information

Name	StateProvince
Schema	Person
Row Count	0
Data Size
Index Size
Reserved Size
Unused Size
Created	29.3.2010. 21:58:18
Modified	29.3.2010. 21:58:56

Extended Properties

Name	Type	Property Name	Value
StateProvince	TABLE	MS_Description	State and province lookup table.

Columns

Name	Data Type	Collation	References	Default
StateProvinceID	int
StateProvinceCode	nchar(3)	SQL_Latin1_General_CP1_CI_AS
CountryRegionCode	nvarchar(3)	SQL_Latin1_General_CP1_CI_AS	Person.CountryRegion.CountryRegionCode
IsOnlyStateProvinceFlag	bit			((1))
Name	nvarchar(50)	SQL_Latin1_General_CP1_CI_AS
TerritoryID	int		Sales.SalesTerritory.TerritoryID
rowguid	uniqueidentifier			(newid())
ModifiedDate	datetime			(getdate())

Indexes

Name	Type	Columns
PK_StateProvince_StateProvinceID	Clustered	StateProvinceID
AK_StateProvince_Name	NonClustered	Name
AK_StateProvince_StateProvinceCode_CountryRegionCode	NonClustered	StateProvinceCode, CountryRegionCode
AK_StateProvince_rowguid	NonClustered	rowguid

Foreign Key Constraints

Name	Type	Referenced Table
FK_StateProvince_CountryRegion_CountryRegionCode	Foreign key	Person.CountryRegion
FK_StateProvince_SalesTerritory_TerritoryID	Foreign key	Sales.SalesTerritory

SQL Script

CREATE TABLE [Person].[StateProvince] (
    [StateProvinceID] int NOT NULL IDENTITY,
    [StateProvinceCode] nchar(3) NOT NULL,
    [CountryRegionCode] nvarchar(3) NOT NULL,
    [IsOnlyStateProvinceFlag] bit NOT NULL DEFAULT ((1)),
    [Name] nvarchar(50) NOT NULL,
    [TerritoryID] int NOT NULL,
    [rowguid] uniqueidentifier NOT NULL DEFAULT (newid()),
    [ModifiedDate] datetime NOT NULL DEFAULT (getdate())
);
CREATE UNIQUE INDEX [AK_StateProvince_Name] on [Person].[StateProvince]([Name]);
CREATE UNIQUE INDEX [AK_StateProvince_StateProvinceCode_CountryRegionCode] on [Person].[StateProvince]([StateProvinceCode], [CountryRegionCode]);
CREATE UNIQUE INDEX [AK_StateProvince_rowguid] on [Person].[StateProvince]([rowguid]);