| Tables | SQL 2005 Adventure Works OLTP |
State and province lookup table.
Columns
Name Data type Nullable Description StateProvinceID int No Column has identity seed value of 1 with a step value of 1.
Primary key for StateProvince records.StateProvinceCode nchar(3) No ISO standard state or province code. CountryRegionCode nvarchar(3) No ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode. IsOnlyStateProvinceFlag Flag No Default value: 1
0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode.Name Name No State or province description. TerritoryID int No ID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID. rowguid uniqueidentifier No Default value: NEWID()
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.ModifiedDate datetime No Default value: GETDATE()
Date and time the record was last updated.
Constraints
Name Type Columns Description DF_StateProvince_IsOnlyStateProvinceFlag DEFAULT IsOnlyStateProvinceFlag Value: 1
Default constraint value of 1 (TRUE)DF_StateProvince_rowguid DEFAULT rowguid Value: NEWID()
Default constraint value of NEWID()DF_StateProvince_ModifiedDate DEFAULT ModifiedDate Value: GETDATE()
Default constraint value of GETDATE()PK_StateProvince_StateProvinceID PRIMARY KEY StateProvinceID Clustered index created by a primary key constraint. FK_StateProvince_CountryRegion_CountryRegionCode FOREIGN KEY CountryRegionCode Reference table: Person.CountryRegion
Foreign key constraint referencing CountryRegion.CountryRegionCode.FK_StateProvince_SalesTerritory_TerritoryID FOREIGN KEY TerritoryID Reference table: Sales.SalesTerritory
Foreign key constraint referencing SalesTerritory.TerritoryID.
Indices
Name Description AK_StateProvince_Name Unique nonclustered index. AK_StateProvince_rowguid Unique nonclustered index.Used to support replication samples. AK_StateProvince_StateProvinceCode_CountryRegionCode Unique nonclustered index.
Triggers
Name Description Person.uStateProvince AFTER UPDATE trigger setting the ModifiedDate column in the StateProvince table to the current date.
Linked tables
Name Description Person.Address Street address information for customers, employees, and vendors. Sales.SalesTaxRate Tax rate lookup table.
Accessed by
Name Description HumanResources.vEmployee Employee names and addresses. Person.vStateProvinceCountryRegion Joins StateProvince table with CountryRegion table. Purchasing.vVendor Vendor (company) names and addresses and the names of vendor employees to contact. Sales Order Detail Detail of an individual Adventure Works order. This report can be accessed as a drillthrough report from the Employee Sales Summary and Territory Sales drilldown report. This report illustrates the use of lists, tables, parameters, and expressions. Sales.vIndividual Individual customers (names and addresses) that purchase Adventure Works Cycles products online. Sales.vSalesPerson Sales representiatives (names and addresses) and their sales-related information. Sales.vStoreWithDemographics Stores (names and addresses) that sell Adventure Works Cycles products to consumers.
Modified by
Name Description Person.uStateProvince AFTER UPDATE trigger setting the ModifiedDate column in the StateProvince table to the current date.
Applies to
Tables | User