Person.Address Table

Information

Name	Address
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
Address	TABLE	MS_Description	Street address information for customers, employees, and vendors.

Columns

Name	Data Type	Collation	References	Default
AddressID	int
AddressLine1	nvarchar(60)	SQL_Latin1_General_CP1_CI_AS
AddressLine2	nvarchar(60)	SQL_Latin1_General_CP1_CI_AS
City	nvarchar(30)	SQL_Latin1_General_CP1_CI_AS
StateProvinceID	int		Person.StateProvince.StateProvinceID
PostalCode	nvarchar(15)	SQL_Latin1_General_CP1_CI_AS
rowguid	uniqueidentifier			(newid())
ModifiedDate	datetime			(getdate())

Indexes

Name	Type	Columns
PK_Address_AddressID	Clustered	AddressID
AK_Address_rowguid	NonClustered	rowguid
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	NonClustered	AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
IX_Address_StateProvinceID	NonClustered	StateProvinceID

Foreign Key Constraints

Name	Type	Referenced Table
FK_Address_StateProvince_StateProvinceID	Foreign key	Person.StateProvince

SQL Script

CREATE TABLE [Person].[Address] (
    [AddressID] int NOT NULL IDENTITY,
    [AddressLine1] nvarchar(60) NOT NULL,
    [AddressLine2] nvarchar(60) NULL,
    [City] nvarchar(30) NOT NULL,
    [StateProvinceID] int NOT NULL,
    [PostalCode] nvarchar(15) NOT NULL,
    [rowguid] uniqueidentifier NOT NULL DEFAULT (newid()),
    [ModifiedDate] datetime NOT NULL DEFAULT (getdate())
);
CREATE UNIQUE INDEX [AK_Address_rowguid] on [Person].[Address]([rowguid]);
CREATE UNIQUE INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] on [Person].[Address]([AddressLine1], [AddressLine2], [City], [StateProvinceID], [PostalCode]);
CREATE INDEX [IX_Address_StateProvinceID] on [Person].[Address]([StateProvinceID]);