Table: [AdventureWorks2016CTP3].[Person].[Address]

CollapseAll image

Collapse image Table properties


Name  Value 
 Schema  [Person]
 Owner  [dbo]
 Table is replicated   
 Creation date  23.10.2015
 ID  693577509
 Located on  PRIMARY
 Data size KB  2904 KB
 Index size KB  2752 KB
 Rows  19614
 ChangeTrackingEnabled   
 FileStreamFileGroup   
 FileStreamPartitionScheme   
 LockEscalation  TABLE
 TrackColumnsUpdatedEnabled   
 Table is filetable   
 Filetable directory   
 Filetable collate   
 Filetable primary key  [PK_Address_AddressID]
 Filetable streamid unique   
 Filetable fullpath unique   
 Filetable is enabled   
 Is memory optimized   
 Durability  SCHEMA_AND_DATA

Collapse image Creation options


Name  Value 
QUOTED_IDENTIFIER ON
ANSI_NULLS ON
ANSI_PADDING ON

Collapse image Columns


      Name  Description  Data type  Max length  Nullable  Default IsGUID  SPARSE Computed value
Primary key Identity   AddressID     int 4        
      AddressLine1     nvarchar 60        
      AddressLine2     nvarchar 60      
      City     nvarchar 30        
    Foreign key StateProvinceID     int 4        
      PostalCode     nvarchar 15        
      SpatialLocation     geography        
      rowguid     uniqueidentifier 16   (newid())  
      ModifiedDate     datetime 8   (getdate())    
Total: 9 column(s)

Collapse image Indexes


Index  Description  Primary  Unique  Size KB 
[PK_Address_AddressID] AddressID   5624 0
[AK_Address_rowguid] rowguid     528 0
[IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] AddressLine1     1728 0
[IX_Address_StateProvinceID] StateProvinceID       288 0
Total: 4 Index/indexes

Collapse image Columnstore indexes


No columnstore indexes exist

Collapse image Identity column


  Name  Seed  Increment  Not for replication 
 AddressID   1   1 

Collapse image Referencing tables


Table  Foreign key  Primary key or unique constraint 
[Person].[BusinessEntityAddress] FK_BusinessEntityAddress_Address_AddressID PK_Address_AddressID
[Sales].[SalesOrderHeader] FK_SalesOrderHeader_Address_BillToAddressID PK_Address_AddressID
[Sales].[SalesOrderHeader] FK_SalesOrderHeader_Address_ShipToAddressID PK_Address_AddressID
Total: 3 table(s)

Collapse image Referenced tables


Table  Primary key or unique constraint  Foreign key 
[Person].[StateProvince] PK_StateProvince_StateProvinceID FK_Address_StateProvince_StateProvinceID
Total: 1 table(s)

Collapse image Objects that depend on [Person].[Address]


Object name Object type Dep level
[Person].[BusinessEntityAddress] Table 1
[Sales].[SalesOrderHeader] Table 1
[HumanResources].[vEmployee] View 1
[Sales].[vIndividualCustomer] View 1
[Sales].[vSalesPerson] View 1
[Sales].[vStoreWithAddresses] View 1
[Purchasing].[vVendorWithAddresses] View 1
Total 7 object(s)

Collapse image Objects that [Person].[Address] depends on


Object name Object type Dep level
[Person] Schema 1
[Person].[StateProvince] Table 1
Total 2 object(s)

Collapse image Extended properties


Name  Value 
MS_Description Street address information for customers, employees, and vendors.

Collapse image Table options


Name  Value 
Pintable OFF
Table lock on bulk load OFF
Insert row lock OFF
Text in row 0
Large value types out of row OFF

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO
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] NOT NULL ROWGUIDCOL,
        [ModifiedDate]        [datetime] NOT NULL,
        CONSTRAINT [PK_Address_AddressID]
        PRIMARY KEY
        CLUSTERED
        ([AddressID])
    ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Primary key (clustered) constraint', 'SCHEMA', N'Person', 'TABLE', N'Address', 'CONSTRAINT', N'PK_Address_AddressID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Clustered index created by a primary key constraint.', 'SCHEMA', N'Person', 'TABLE', N'Address', 'INDEX', N'PK_Address_AddressID'
GO
ALTER TABLE [Person].[Address]
    ADD
    CONSTRAINT [DF_Address_ModifiedDate]
    DEFAULT (getdate()) FOR [ModifiedDate]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of GETDATE()', 'SCHEMA', N'Person', 'TABLE', N'Address', 'CONSTRAINT', N'DF_Address_ModifiedDate'
GO
ALTER TABLE [Person].[Address]
    ADD
    CONSTRAINT [DF_Address_rowguid]
    DEFAULT (newid()) FOR [rowguid]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of NEWID()', 'SCHEMA', N'Person', 'TABLE', N'Address', 'CONSTRAINT', N'DF_Address_rowguid'
GO
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]

GO
EXEC sp_addextendedproperty N'MS_Description', N'Foreign key constraint referencing StateProvince.StateProvinceID.', 'SCHEMA', N'Person', 'TABLE', N'Address', 'CONSTRAINT', N'FK_Address_StateProvince_StateProvinceID'
GO
CREATE UNIQUE NONCLUSTERED INDEX [AK_Address_rowguid]
    ON [Person].[Address] ([rowguid])
    ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Unique nonclustered index. Used to support replication samples.', 'SCHEMA', N'Person', 'TABLE', N'Address', 'INDEX', N'AK_Address_rowguid'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]
    ON [Person].[Address] ([AddressLine1], [AddressLine2], [City], [StateProvinceID], [PostalCode])
    ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Nonclustered index.', 'SCHEMA', N'Person', 'TABLE', N'Address', 'INDEX', N'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode'
GO
CREATE NONCLUSTERED INDEX [IX_Address_StateProvinceID]
    ON [Person].[Address] ([StateProvinceID])
    ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Nonclustered index.', 'SCHEMA', N'Person', 'TABLE', N'Address', 'INDEX', N'IX_Address_StateProvinceID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Primary key for Address records.', 'SCHEMA', N'Person', 'TABLE', N'Address', 'COLUMN', N'AddressID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'First street address line.', 'SCHEMA', N'Person', 'TABLE', N'Address', 'COLUMN', N'AddressLine1'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Second street address line.', 'SCHEMA', N'Person', 'TABLE', N'Address', 'COLUMN', N'AddressLine2'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Name of the city.', 'SCHEMA', N'Person', 'TABLE', N'Address', 'COLUMN', N'City'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Date and time the record was last updated.', 'SCHEMA', N'Person', 'TABLE', N'Address', 'COLUMN', N'ModifiedDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Postal code for the street address.', 'SCHEMA', N'Person', 'TABLE', N'Address', 'COLUMN', N'PostalCode'
GO
EXEC sp_addextendedproperty N'MS_Description', N'ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.', 'SCHEMA', N'Person', 'TABLE', N'Address', 'COLUMN', N'rowguid'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Latitude and longitude of this address.', 'SCHEMA', N'Person', 'TABLE', N'Address', 'COLUMN', N'SpatialLocation'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Unique identification number for the state or province. Foreign key to StateProvince table.', 'SCHEMA', N'Person', 'TABLE', N'Address', 'COLUMN', N'StateProvinceID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Street address information for customers, employees, and vendors.', 'SCHEMA', N'Person', 'TABLE', N'Address', NULL, NULL
GO

Collapse image See also


List of tables