Person.Address Table

In This Topic

Description

Street address information for customers, employees, and vendors.

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group	PRIMARY
System Object
Published for Replication
Rows	19614
Data Space Used	2,784.00 KB
Index Space Used	2,624.00 KB

Columns

Column Name	Description	Datatype	Length	Default
AddressID	Primary key for Address records.	Integer	4
AddressLine1	First street address line.	VarWChar	60
AddressLine2	Second street address line.	VarWChar	60
City	Name of the city.	VarWChar	30
StateProvinceID	Unique identification number for the state or province. Foreign key to StateProvince table.	Integer	4
PostalCode	Postal code for the street address.	VarWChar	15
SpatialLocation	Latitude and longitude of this address.	UserDefined	0
rowguid	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	GUID	16	(newid())
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	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_BusinessEntityAddress_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.

Objects that depend on Person.Address

Database Object	Object Type	Description	Dep Level
Person.BusinessEntityAddress	Table	Cross-reference table mapping customers, vendors, and employees to their addresses.	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.	3
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.	3
HumanResources.vEmployee	View	Employee names and addresses.	2
Sales.vIndividualCustomer	View	Individual customers (names and addresses) that purchase Adventure Works Cycles products online.	2
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.	3
Sales.vStoreWithAddresses	View	Stores (including store addresses) that sell Adventure Works Cycles products to consumers.	1
Purchasing.vVendorWithAddresses	View	Vendor (company) names and addresses .	2

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.	4
dbo.Flag	User Defined Data Type		4
dbo.Name	User Defined Data Type		2
Sales.SalesTerritory	Table	Sales territory lookup table.	3
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,
    [SpatialLocation] [geography] 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] TEXTIMAGE_ON [PRIMARY]

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]
ALTER TABLE [Person].[Address]  WITH CHECK ADD  CONSTRAINT [FK_Address_StateProvince_StateProvinceID] FOREIGN KEY([StateProvinceID])
REFERENCES [Person].[StateProvince] ([StateProvinceID])
ALTER TABLE [Person].[Address] CHECK CONSTRAINT [FK_Address_StateProvince_StateProvinceID]

Related Objects

Person Schema
AdventureWorks Database

Send comments on this topic.