Person.Address Table

Description

Street address information for customers, employees, and vendors.

Properties

Creation Date	08/01/2010 08:40
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	19614
Data Space Used	2,224.00 KB
Index Space Used	2,440.00 KB

Columns

Column Name	Description	Datatype	Length	Default
AddressID	Primary key for Address records.	int	4
AddressLine1	First street address line.	nvarchar	60
AddressLine2	Second street address line.	nvarchar	60
City	Name of the city.	nvarchar	30
StateProvinceID	Unique identification number for the state or province. Foreign key to StateProvince table.	int	4
PostalCode	Postal code for the street address.	nvarchar	15
rowguid	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	uniqueidentifier	16	(newid())
ModifiedDate	Date and time the record was last updated.	datetime	4	(getdate())

Indexes

Index	Description	Primary	Unique
AK_Address_rowguid	Unique nonclustered index. Used to support replication samples.
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	Nonclustered index.
IX_Address_StateProvinceID	Nonclustered index.
PK_Address_AddressID	Primary key (clustered) constraint

Relationships

Relationship	Description
FK_Address_StateProvince_StateProvinceID	Foreign key constraint referencing StateProvince.StateProvinceID.
FK_CustomerAddress_Address_AddressID	Foreign key constraint referencing Address.AddressID.
FK_EmployeeAddress_Address_AddressID	Foreign key constraint referencing Address.AddressID.
FK_SalesOrderHeader_Address_BillToAddressID	Foreign key constraint referencing Address.AddressID.
FK_SalesOrderHeader_Address_ShipToAddressID	Foreign key constraint referencing Address.AddressID.
FK_VendorAddress_Address_AddressID	Foreign key constraint referencing Address.AddressID.

Objects that depend on Person.Address

Database Object	Object Type	Description	Dep Level
Sales.CustomerAddress	Table	Cross-reference table mapping customers to their address(es).	1
HumanResources.EmployeeAddress	Table	Cross-reference table mapping employees to their address(es).	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
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.	1
Purchasing.VendorAddress	Table	Cross-reference mapping vendors and addresses.	1
Sales.vIndividualCustomer	View	Individual customers (names and addresses) that purchase Adventure Works Cycles products online.	1
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.	2
Sales.vStoreWithDemographics	View	Stores (names and addresses) that sell Adventure Works Cycles products to consumers.	1
Purchasing.vVendor	View	Vendor (company) names and addresses and the names of vendor employees to contact.	1

Objects that Person.Address depends on

Database Object	Object Type	Description	Dep Level
Person.CountryRegion	Table	Lookup table containing the ISO standard codes for countries and regions.	2
dbo.Flag	User Defined Data Type		2
dbo.Name	User Defined Data Type		2
Sales.SalesTerritory	Table	Sales territory lookup table.	2
Person.StateProvince	Table	State and province lookup table.	1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[Address](
	[AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[AddressLine1] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[AddressLine2] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[City] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[StateProvinceID] [int] NOT NULL,
	[PostalCode] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED 
(
	[AddressID] 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].[Address]  WITH CHECK ADD  CONSTRAINT [FK_Address_StateProvince_StateProvinceID] FOREIGN KEY([StateProvinceID])
REFERENCES [StateProvince] ([StateProvinceID])
ALTER TABLE [Person].[Address] CHECK CONSTRAINT [FK_Address_StateProvince_StateProvinceID]
ALTER TABLE [Person].[Address] ADD  CONSTRAINT [DF_Address_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Person].[Address] ADD  CONSTRAINT [DF_Address_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

Related Objects

Person Schema
AdventureWorks Database