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

CollapseAll image

Collapse image Table properties


Name  Value 
 Schema  [Person]
 Owner  [dbo]
 Table is replicated   
 Creation date  23.10.2015
 ID  2085582468
 Located on  PRIMARY
 Data size KB  30552 KB
 Index size KB  53896 KB
 Rows  19972
 ChangeTrackingEnabled   
 FileStreamFileGroup   
 FileStreamPartitionScheme   
 LockEscalation  TABLE
 TrackColumnsUpdatedEnabled   
 Table is filetable   
 Filetable directory   
 Filetable collate   
 Filetable primary key  [PK_Person_BusinessEntityID]
 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 Foreign key BusinessEntityID     int 4        
    PersonType     nchar 2        
    NameStyle     [dbo].[NameStyle] 1   ((0))    
    Title     nvarchar 8      
    FirstName     [dbo].[Name] 50        
    MiddleName     [dbo].[Name] 50      
    LastName     [dbo].[Name] 50        
    Suffix     nvarchar 10      
    EmailPromotion     int 4   ((0))    
    AdditionalContactInfo     xml([Person].[AdditionalContactInfoSchemaCollection]) 2147483647      
    Demographics     xml([Person].[IndividualSurveySchemaCollection]) 2147483647      
    rowguid     uniqueidentifier 16   (newid())  
    ModifiedDate     datetime 8   (getdate())    
Total: 13 column(s)

Collapse image Indexes


Index  Description  Primary  Unique  Size KB 
[PK_Person_BusinessEntityID] BusinessEntityID   32560 0
[AK_Person_rowguid] rowguid     536 0
[IX_Person_LastName_FirstName_MiddleName] LastName       1128 0
[PXML_Person_AddContact]         40 0
[PXML_Person_Demographics]         51768 0
[XMLPATH_Person_Demographics]         0 0
[XMLPROPERTY_Person_Demographics]         0 0
[XMLVALUE_Person_Demographics]         0 0
Total: 8 Index/indexes

Collapse image Columnstore indexes


No columnstore indexes exist

Collapse image Check constraints


Name  Expression 
CK_Person_EmailPromotion ([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))
CK_Person_PersonType ([PersonType] IS NULL OR (upper([PersonType])='GC' OR upper([PersonType])='SP' OR upper([PersonType])='EM' OR upper([PersonType])='IN' OR upper([PersonType])='VC' OR upper([PersonType])='SC'))

Collapse image Referencing tables


Table  Foreign key  Primary key or unique constraint 
[HumanResources].[Employee] FK_Employee_Person_BusinessEntityID PK_Person_BusinessEntityID
[Person].[BusinessEntityContact] FK_BusinessEntityContact_Person_PersonID PK_Person_BusinessEntityID
[Person].[EmailAddress] FK_EmailAddress_Person_BusinessEntityID PK_Person_BusinessEntityID
[Person].[Password] FK_Password_Person_BusinessEntityID PK_Person_BusinessEntityID
[Person].[PersonPhone] FK_PersonPhone_Person_BusinessEntityID PK_Person_BusinessEntityID
[Sales].[Customer] FK_Customer_Person_PersonID PK_Person_BusinessEntityID
[Sales].[PersonCreditCard] FK_PersonCreditCard_Person_BusinessEntityID PK_Person_BusinessEntityID
Total: 7 table(s)

Collapse image Referenced tables


Table  Primary key or unique constraint  Foreign key 
[Person].[BusinessEntity] PK_BusinessEntity_BusinessEntityID FK_Person_BusinessEntity_BusinessEntityID
Total: 1 table(s)

Collapse image Triggers


Name  Schema  Description  Instead 
of 
For 
insert 
For 
update 
For 
delete 
Table/view 
[Person].[iuPerson] [Person]     For Insert For Update   [Person].[Person]
Total: 1 triggers(s)

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


Object name Object type Dep level
[dbo].[uspGetEmployeeManagers] Procedure 1
[dbo].[uspGetManagerEmployees] Procedure 1
[Person].[BusinessEntityContact] Table 1
[Sales].[Customer] Table 1
[Person].[EmailAddress] Table 1
[HumanResources].[Employee] Table 1
[Person].[Password] Table 1
[Sales].[PersonCreditCard] Table 1
[Person].[PersonPhone] Table 1
[Sales].[iduSalesOrderDetail] Trigger 1
[Person].[iuPerson] Trigger 1
[dbo].[ufnGetContactInformation] Function 1
[Person].[vAdditionalContactInfo] View 1
[HumanResources].[vEmployee] View 1
[HumanResources].[vEmployeeDepartment] View 1
[HumanResources].[vEmployeeDepartmentHistory] View 1
[Sales].[vIndividualCustomer] View 1
[Sales].[vPersonDemographics] View 1
[Sales].[vSalesPerson] View 1
[Sales].[vSalesPersonSalesByFiscalYears] View 1
[Sales].[vStoreWithContacts] View 1
[Purchasing].[vVendorWithContacts] View 1
Total 22 object(s)

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


Object name Object type Dep level
[Person] Schema 1
[Person].[BusinessEntity] Table 1
[dbo].[Name] Datatype 1
[dbo].[NameStyle] Datatype 1
[Person].[AdditionalContactInfoSchemaCollection] XML Schema Collection 1
[Person].[IndividualSurveySchemaCollection] XML Schema Collection 1
Total 6 object(s)

Collapse image Extended properties


Name  Value 
MS_Description Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.

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].[Person] (
        [BusinessEntityID]          [int] NOT NULL,
        [PersonType]                [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [NameStyle]                 [dbo].[NameStyle] NOT NULL,
        [Title]                     [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [FirstName]                 [dbo].[Name] NOT NULL,
        [MiddleName]                [dbo].[Name] NULL,
        [LastName]                  [dbo].[Name] NOT NULL,
        [Suffix]                    [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [EmailPromotion]            [int] NOT NULL,
        [AdditionalContactInfo]     [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
        [Demographics]              [xml](CONTENT [Person].[IndividualSurveySchemaCollection]) NULL,
        [rowguid]                   [uniqueidentifier] NOT NULL ROWGUIDCOL,
        [ModifiedDate]              [datetime] NOT NULL,
        CONSTRAINT [PK_Person_BusinessEntityID]
        PRIMARY KEY
        CLUSTERED
        ([BusinessEntityID])
    ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Primary key (clustered) constraint', 'SCHEMA', N'Person', 'TABLE', N'Person', 'CONSTRAINT', N'PK_Person_BusinessEntityID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Clustered index created by a primary key constraint.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'INDEX', N'PK_Person_BusinessEntityID'
GO
ALTER TABLE [Person].[Person]
    ADD
    CONSTRAINT [CK_Person_EmailPromotion]
    CHECK
    ([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))
GO
EXEC sp_addextendedproperty N'MS_Description', N'Check constraint [EmailPromotion] >= (0) AND [EmailPromotion] <= (2)', 'SCHEMA', N'Person', 'TABLE', N'Person', 'CONSTRAINT', N'CK_Person_EmailPromotion'
GO
ALTER TABLE [Person].[Person]
CHECK CONSTRAINT [CK_Person_EmailPromotion]
GO
ALTER TABLE [Person].[Person]
    ADD
    CONSTRAINT [CK_Person_PersonType]
    CHECK
    ([PersonType] IS NULL OR (upper([PersonType])='GC' OR upper([PersonType])='SP' OR upper([PersonType])='EM' OR upper([PersonType])='IN' OR upper([PersonType])='VC' OR upper([PersonType])='SC'))
GO
EXEC sp_addextendedproperty N'MS_Description', N'Check constraint [PersonType] is one of SC, VC, IN, EM or SP.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'CONSTRAINT', N'CK_Person_PersonType'
GO
ALTER TABLE [Person].[Person]
CHECK CONSTRAINT [CK_Person_PersonType]
GO
ALTER TABLE [Person].[Person]
    ADD
    CONSTRAINT [DF_Person_EmailPromotion]
    DEFAULT ((0)) FOR [EmailPromotion]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of 0', 'SCHEMA', N'Person', 'TABLE', N'Person', 'CONSTRAINT', N'DF_Person_EmailPromotion'
GO
ALTER TABLE [Person].[Person]
    ADD
    CONSTRAINT [DF_Person_ModifiedDate]
    DEFAULT (getdate()) FOR [ModifiedDate]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of GETDATE()', 'SCHEMA', N'Person', 'TABLE', N'Person', 'CONSTRAINT', N'DF_Person_ModifiedDate'
GO
ALTER TABLE [Person].[Person]
    ADD
    CONSTRAINT [DF_Person_NameStyle]
    DEFAULT ((0)) FOR [NameStyle]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of 0', 'SCHEMA', N'Person', 'TABLE', N'Person', 'CONSTRAINT', N'DF_Person_NameStyle'
GO
ALTER TABLE [Person].[Person]
    ADD
    CONSTRAINT [DF_Person_rowguid]
    DEFAULT (newid()) FOR [rowguid]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of NEWID()', 'SCHEMA', N'Person', 'TABLE', N'Person', 'CONSTRAINT', N'DF_Person_rowguid'
GO
ALTER TABLE [Person].[Person]
    WITH CHECK
    ADD CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID]
    FOREIGN KEY ([BusinessEntityID]) REFERENCES [Person].[BusinessEntity] ([BusinessEntityID])
ALTER TABLE [Person].[Person]
    CHECK CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID]

GO
EXEC sp_addextendedproperty N'MS_Description', N'Foreign key constraint referencing BusinessEntity.BusinessEntityID.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'CONSTRAINT', N'FK_Person_BusinessEntity_BusinessEntityID'
GO
CREATE UNIQUE NONCLUSTERED INDEX [AK_Person_rowguid]
    ON [Person].[Person] ([rowguid])
    ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Unique nonclustered index. Used to support replication samples.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'INDEX', N'AK_Person_rowguid'
GO
CREATE NONCLUSTERED INDEX [IX_Person_LastName_FirstName_MiddleName]
    ON [Person].[Person] ([LastName], [FirstName], [MiddleName])
    ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Additional contact information about the person stored in xml format. ', 'SCHEMA', N'Person', 'TABLE', N'Person', 'COLUMN', N'AdditionalContactInfo'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Primary key for Person records.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'COLUMN', N'BusinessEntityID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'COLUMN', N'Demographics'
GO
EXEC sp_addextendedproperty N'MS_Description', N'0 = Contact does not wish to receive e-mail promotions, 1 = Contact does wish to receive e-mail promotions from AdventureWorks, 2 = Contact does wish to receive e-mail promotions from AdventureWorks and selected partners. ', 'SCHEMA', N'Person', 'TABLE', N'Person', 'COLUMN', N'EmailPromotion'
GO
EXEC sp_addextendedproperty N'MS_Description', N'First name of the person.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'COLUMN', N'FirstName'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Last name of the person.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'COLUMN', N'LastName'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Middle name or middle initial of the person.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'COLUMN', N'MiddleName'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Date and time the record was last updated.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'COLUMN', N'ModifiedDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'0 = The data in FirstName and LastName are stored in western style (first name, last name) order.  1 = Eastern style (last name, first name) order.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'COLUMN', N'NameStyle'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Primary type of person: SC = Store Contact, IN = Individual (retail) customer, SP = Sales person, EM = Employee (non-sales), VC = Vendor contact, GC = General contact', 'SCHEMA', N'Person', 'TABLE', N'Person', 'COLUMN', N'PersonType'
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'Person', 'COLUMN', N'rowguid'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Surname suffix. For example, Sr. or Jr.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'COLUMN', N'Suffix'
GO
EXEC sp_addextendedproperty N'MS_Description', N'A courtesy title. For example, Mr. or Ms.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'COLUMN', N'Title'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.', 'SCHEMA', N'Person', 'TABLE', N'Person', NULL, NULL
GO
SET ANSI_PADDING ON
GO
CREATE PRIMARY XML INDEX [PXML_Person_AddContact]
    ON [Person].[Person] ([AdditionalContactInfo])
GO
EXEC sp_addextendedproperty N'MS_Description', N'Primary XML index.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'INDEX', N'PXML_Person_AddContact'
GO
SET ANSI_PADDING ON
GO
CREATE PRIMARY XML INDEX [PXML_Person_Demographics]
    ON [Person].[Person] ([Demographics])
GO
EXEC sp_addextendedproperty N'MS_Description', N'Primary XML index.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'INDEX', N'PXML_Person_Demographics'
GO
SET ANSI_PADDING ON
GO
CREATE XML INDEX [XMLPATH_Person_Demographics]
    ON [Person].[Person] ([Demographics])
    USING XML INDEX [PXML_Person_Demographics]
    FOR PATH
GO
EXEC sp_addextendedproperty N'MS_Description', N'Secondary XML index for path.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'INDEX', N'XMLPATH_Person_Demographics'
GO
SET ANSI_PADDING ON
GO
CREATE XML INDEX [XMLPROPERTY_Person_Demographics]
    ON [Person].[Person] ([Demographics])
    USING XML INDEX [PXML_Person_Demographics]
    FOR PROPERTY
GO
EXEC sp_addextendedproperty N'MS_Description', N'Secondary XML index for property.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'INDEX', N'XMLPROPERTY_Person_Demographics'
GO
SET ANSI_PADDING ON
GO
CREATE XML INDEX [XMLVALUE_Person_Demographics]
    ON [Person].[Person] ([Demographics])
    USING XML INDEX [PXML_Person_Demographics]
    FOR VALUE
GO
EXEC sp_addextendedproperty N'MS_Description', N'Secondary XML index for value.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'INDEX', N'XMLVALUE_Person_Demographics'
GO

Collapse image See also


List of tables