• lsrep17 Documentation
  • Tables [Person].[Person]
    Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.
    PropertyValue
    CollationSQL_Latin1_General_CP1_CI_AS
    Row Count (~)19972
    Created13:14:19 14 marca 2012
    Last Modified15:26:58 22 marca 2017
    KeyNameData TypeMax Length (Bytes)Allow NullsDefaultDescription
    Cluster Primary Key PK_Person_BusinessEntityID: BusinessEntityIDForeign Keys FK_Person_BusinessEntity_BusinessEntityID: [Person].[BusinessEntity].BusinessEntityIDBusinessEntityIDint4
    False
    Primary key for Person records.
    Check Constraints 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')PersonTypenchar(2)4
    False
    Primary type of person: SC = Store Contact, IN = Individual (retail) customer, SP = Sales person, EM = Employee (non-sales), VC = Vendor contact, GC = General contact
    NameStyle[dbo].[NameStyle]1
    False
    ((0))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.
    Titlenvarchar(8)16
    True
    A courtesy title. For example, Mr. or Ms.
    Indexes IX_Person_LastName_FirstName_MiddleNameFirstName[dbo].[Name]100
    False
    First name of the person.
    Indexes IX_Person_LastName_FirstName_MiddleNameMiddleName[dbo].[Name]100
    True
    Middle name or middle initial of the person.
    Indexes IX_Person_LastName_FirstName_MiddleNameLastName[dbo].[Name]100
    False
    Last name of the person.
    Suffixnvarchar(10)20
    True
    Surname suffix. For example, Sr. or Jr.
    Check Constraints CK_Person_EmailPromotion : ([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))EmailPromotionint4
    False
    ((0))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.
    Indexes PXML_Person_AddContactAdditionalContactInfoxml([Person].[AdditionalContactInfoSchemaCollection])max
    True
    Additional contact information about the person stored in xml format.
    Indexes PXML_Person_Demographics
XMLPATH_Person_Demographics
XMLPROPERTY_Person_Demographics
XMLVALUE_Person_Demographics(4)Demographicsxml([Person].[IndividualSurveySchemaCollection])max
    True
    Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.
    Indexes AK_Person_rowguidrowguiduniqueidentifier16
    False
    (newid())ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
    ModifiedDatedatetime8
    False
    (getdate())Date and time the record was last updated.
    KeyNameKey ColumnsTypeUniqueXML TypeDescription
    Cluster Primary Key PK_Person_BusinessEntityID: BusinessEntityIDPK_Person_BusinessEntityIDBusinessEntityID
    True
    Primary key (clustered) constraint
    AK_Person_rowguidrowguid
    True
    Unique nonclustered index. Used to support replication samples.
    IX_Person_LastName_FirstName_MiddleNameLastName, FirstName, MiddleName
    PXML_Person_AddContactAdditionalContactInfoxmlPrimaryPrimary XML index.
    PXML_Person_DemographicsDemographicsxmlPrimaryPrimary XML index.
    XMLPATH_Person_DemographicsDemographicsxmlSecondarySecondary XML index for path.
    XMLPROPERTY_Person_DemographicsDemographicsxmlSecondarySecondary XML index for property.
    XMLVALUE_Person_DemographicsDemographicsxmlSecondarySecondary XML index for value.
    NameANSI Nulls OnQuoted Identifier OnOnNot For ReplicationDescription
    iuPerson
    True
    True
    After Insert Update
    True
    AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Person table to the current date.
    NameOn ColumnConstraintDescription
    CK_Person_EmailPromotionEmailPromotion([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))Check constraint [EmailPromotion] >= (0) AND [EmailPromotion] <= (2)
    CK_Person_PersonTypePersonType([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')Check constraint [PersonType] is one of SC, VC, IN, EM or SP.
    NameColumnsDescription
    FK_Person_BusinessEntity_BusinessEntityIDBusinessEntityID->[Person].[BusinessEntity].[BusinessEntityID]Foreign key constraint referencing BusinessEntity.BusinessEntityID.
    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 CONSTRAINT [DF_Person_NameStyle] DEFAULT ((0)),
    [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 CONSTRAINT [DF_Person_EmailPromotion] DEFAULT ((0)),
    [AdditionalContactInfo] [xml] (CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
    [Demographics] [xml] (CONTENT [Person].[IndividualSurveySchemaCollection]) NULL,
    [rowguid] [uniqueidentifier] NOT NULL ROWGUIDCOL CONSTRAINT [DF_Person_rowguid] DEFAULT (newid()),
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Person_ModifiedDate] DEFAULT (getdate())
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    CREATE TRIGGER [Person].[iuPerson] ON [Person].[Person]
    AFTER INSERT, UPDATE NOT FOR REPLICATION AS
    BEGIN
       DECLARE @Count int;

       SET @Count = @@ROWCOUNT;
       IF @Count = 0
           RETURN;

       SET NOCOUNT ON;

       IF UPDATE([BusinessEntityID]) OR UPDATE([Demographics])
       BEGIN
           UPDATE [Person].[Person]
           SET [Person].[Person].[Demographics] = N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
               <TotalPurchaseYTD>0.00</TotalPurchaseYTD>
               </IndividualSurvey>'

           FROM inserted
           WHERE [Person].[Person].[BusinessEntityID] = inserted.[BusinessEntityID]
               AND inserted.[Demographics] IS NULL;
           
           UPDATE [Person].[Person]
           SET [Demographics].modify(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
               insert <TotalPurchaseYTD>0.00</TotalPurchaseYTD>
               as first
               into (/IndividualSurvey)[1]'
    )
           FROM inserted
           WHERE [Person].[Person].[BusinessEntityID] = inserted.[BusinessEntityID]
               AND inserted.[Demographics] IS NOT NULL
               AND inserted.[Demographics].exist(N'declare default element namespace
                   "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
                   /IndividualSurvey/TotalPurchaseYTD'
    ) <> 1;
       END;
    END;
    GO
    ALTER TABLE [Person].[Person] ADD CONSTRAINT [CK_Person_EmailPromotion] CHECK (([EmailPromotion]>=(0) AND [EmailPromotion]<=(2)))
    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
    ALTER TABLE [Person].[Person] ADD CONSTRAINT [PK_Person_BusinessEntityID] PRIMARY KEY CLUSTERED  ([BusinessEntityID]) ON [PRIMARY]
    GO
    CREATE NONCLUSTERED INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person] ([LastName], [FirstName], [MiddleName]) ON [PRIMARY]
    GO
    CREATE UNIQUE NONCLUSTERED INDEX [AK_Person_rowguid] ON [Person].[Person] ([rowguid]) ON [PRIMARY]
    GO
    CREATE PRIMARY XML INDEX [PXML_Person_AddContact]
    ON [Person].[Person] ([AdditionalContactInfo])
    GO
    CREATE PRIMARY XML INDEX [PXML_Person_Demographics]
    ON [Person].[Person] ([Demographics])
    GO
    CREATE XML INDEX [XMLPATH_Person_Demographics]
    ON [Person].[Person] ([Demographics])
    USING XML INDEX [PXML_Person_Demographics]
    FOR PATH
    GO
    CREATE XML INDEX [XMLPROPERTY_Person_Demographics]
    ON [Person].[Person] ([Demographics])
    USING XML INDEX [PXML_Person_Demographics]
    FOR PROPERTY
    GO
    CREATE XML INDEX [XMLVALUE_Person_Demographics]
    ON [Person].[Person] ([Demographics])
    USING XML INDEX [PXML_Person_Demographics]
    FOR VALUE
    GO
    ALTER TABLE [Person].[Person] ADD CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID] FOREIGN KEY ([BusinessEntityID]) REFERENCES [Person].[BusinessEntity] ([BusinessEntityID])
    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
    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'Check constraint [EmailPromotion] >= (0) AND [EmailPromotion] <= (2)', 'SCHEMA', N'Person', 'TABLE', N'Person', 'CONSTRAINT', N'CK_Person_EmailPromotion'
    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
    EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of 0', 'SCHEMA', N'Person', 'TABLE', N'Person', 'CONSTRAINT', N'DF_Person_EmailPromotion'
    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
    EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of 0', 'SCHEMA', N'Person', 'TABLE', N'Person', 'CONSTRAINT', N'DF_Person_NameStyle'
    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
    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
    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'Unique nonclustered index. Used to support replication samples.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'INDEX', N'AK_Person_rowguid'
    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
    EXEC sp_addextendedproperty N'MS_Description', N'Primary XML index.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'INDEX', N'PXML_Person_AddContact'
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Primary XML index.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'INDEX', N'PXML_Person_Demographics'
    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
    EXEC sp_addextendedproperty N'MS_Description', N'Secondary XML index for property.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'INDEX', N'XMLPROPERTY_Person_Demographics'
    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
    EXEC sp_addextendedproperty N'MS_Description', N'AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Person table to the current date.', 'SCHEMA', N'Person', 'TABLE', N'Person', 'TRIGGER', N'iuPerson'
    GO