Description
State and province lookup table.
Table properties
| name | value |
|---|
| name | [Person].[StateProvince] |
| created | Mar 19 2009 9:08PM |
| modified | Mar 19 2009 9:08PM
|
| ansi nulls | on
|
| quoted identifier | on
|
| row count | 181 |
| Size of data | 16 kb |
| Size of indexes | 64 kb |
| Maximum size of a single row | 256 bytes |
Columns
| column | datatype | length | bytes | default | nulls | PK | FK | UQ | computed | comment |
|---|
| StateProvinceID | int identity(1,1) | 10 | 4 | | no
| yes
| |
| no
| Primary key for StateProvince records.
|
| StateProvinceCode | nchar(3) | 3 | 12 | | no
|
| |
| no
| ISO standard state or province code.
|
| CountryRegionCode | nvarchar(3) | 3 | 12 | | no
|
| CountryRegion.CountryRegionCode
|
| no
| ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.
|
| IsOnlyStateProvinceFlag | Flag | 1 | 0 | ((1)) | no
|
| |
| no
| 0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode.
|
| Name | Name | 50 | 200 | | no
|
| |
| no
| State or province description.
|
| TerritoryID | int | 10 | 4 | | no
|
| SalesTerritory.TerritoryID
|
| no
| ID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID.
|
| rowguid | uniqueidentifier | 36 | 16 | (newid()) | no
|
| |
| no
| ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
|
| ModifiedDate | datetime | 23 | 8 | (getdate()) | no
|
| |
| no
| Date and time the record was last updated.
|
Indexes
References
Referenced by
Foreign key graph
Foreign keys
| name | columns | foreign columns | type | comment |
|---|
| FK_StateProvince_CountryRegion_CountryRegionCode | CountryRegionCode | CountryRegion.CountryRegionCode |
| Foreign key constraint referencing CountryRegion.CountryRegionCode.
|
| FK_StateProvince_SalesTerritory_TerritoryID | TerritoryID | SalesTerritory.TerritoryID |
| Foreign key constraint referencing SalesTerritory.TerritoryID.
|
Defaults
Dependency graph
Objects that [Person].[StateProvince] depends on
Objects that depend on [Person].[StateProvince]
Sample rows
| StateProvinceID | StateProvinceCode | CountryRegionCode | IsOnlyStateProvinceFlag | Name | TerritoryID | rowguid | ModifiedDate |
|---|
| 1
| AB
| CA
| False
| Alberta
| 6
| 298c2880-ab1c-4982-a5ad-a36eb4ba0d34
| 3/11/2004 10:17:21 AM
|
| 2
| AK
| US
| False
| Alaska
| 1
| 5b7b8462-a888-4e0b-a3e1-7278f8af107e
| 3/11/2004 10:17:21 AM
|
| 3
| AL
| US
| False
| Alabama
| 5
| 41b328be-21ae-45d0-841d-6f8dd71ce626
| 3/11/2004 10:17:21 AM
|
| 4
| AR
| US
| False
| Arkansas
| 3
| 54656a80-06f2-4c70-ba10-247179fc246e
| 3/11/2004 10:17:21 AM
|
| 5
| AS
| AS
| True
| American Samoa
| 1
| 255d15e1-9f6e-4cf8-9e5f-6b3858ad9b6a
| 3/11/2004 10:17:21 AM
|
| 6
| AZ
| US
| False
| Arizona
| 4
| fb8be18e-f441-44f0-a4a9-1d0f204cb701
| 3/11/2004 10:17:21 AM
|
| 7
| BC
| CA
| False
| British Columbia
| 6
| d27fcc6e-bb99-438b-ba86-285ceeb2fa53
| 3/11/2004 10:17:21 AM
|
| 8
| BY
| DE
| False
| Bayern
| 8
| d54e5000-a0da-46d1-86b0-b8fe16c9f781
| 3/11/2004 10:17:21 AM
|
| 9
| CA
| US
| False
| California
| 4
| 3b2ff23c-1c75-40ae-9093-f4eb42263f4e
| 3/11/2004 10:17:21 AM
|
| 10
| CO
| US
| False
| Colorado
| 3
| 292df595-7d3c-41fb-a040-7c184d379fce
| 3/11/2004 10:17:21 AM
|
Code
CREATE TABLE [Person].[StateProvince](
[StateProvinceID] [int] IDENTITY(1,1) NOT NULL,
[StateProvinceCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CountryRegionCode] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IsOnlyStateProvinceFlag] [dbo].[Flag] NOT NULL,
[Name] [dbo].[Name] NOT NULL,
[StateProvinceID] [int] IDENTITY(1,1) NOT NULL,
[StateProvinceCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CountryRegionCode] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IsOnlyStateProvinceFlag] [dbo].[Flag] NOT NULL,
[TerritoryID] [int] NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_StateProvince_StateProvinceID] PRIMARY KEY CLUSTERED
(
[StateProvinceID] 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 [Person].[StateProvince] WITH CHECK ADD CONSTRAINT [FK_StateProvince_CountryRegion_CountryRegionCode] FOREIGN KEY([CountryRegionCode])
REFERENCES [Person].[CountryRegion] ([CountryRegionCode])
ALTER TABLE [Person].[StateProvince] CHECK CONSTRAINT [FK_StateProvince_CountryRegion_CountryRegionCode]
ALTER TABLE [Person].[StateProvince] WITH CHECK ADD CONSTRAINT [FK_StateProvince_SalesTerritory_TerritoryID] FOREIGN KEY([TerritoryID])
REFERENCES [Sales].[SalesTerritory] ([TerritoryID])
ALTER TABLE [Person].[StateProvince] CHECK CONSTRAINT [FK_StateProvince_SalesTerritory_TerritoryID]
ALTER TABLE [Person].[StateProvince] ADD CONSTRAINT [DF_StateProvince_IsOnlyStateProvinceFlag] DEFAULT ((1)) FOR [IsOnlyStateProvinceFlag]
ALTER TABLE [Person].[StateProvince] ADD CONSTRAINT [DF_StateProvince_rowguid] DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Person].[StateProvince] ADD CONSTRAINT [DF_StateProvince_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]