Person.Contact Table

Description

Names of each employee, customer contact, and vendor contact.

Properties

Creation Date	08/01/2010 08:40
File Group	PRIMARY
Text File Group	PRIMARY
System Object
Published for Replication
Rows	19972
Data Space Used	4,536.00 KB
Index Space Used	1,896.00 KB

Columns

Column Name	Description	Datatype	Length	Default
ContactID	Primary key for Contact records.	int	4
NameStyle	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.	dbo.NameStyle	1	((0))
Title	A courtesy title. For example, Mr. or Ms.	nvarchar	8
FirstName	First name of the person.	dbo.Name	50
MiddleName	Middle name or middle initial of the person.	dbo.Name	50
LastName	Last name of the person.	dbo.Name	50
Suffix	Surname suffix. For example, Sr. or Jr.	nvarchar	10
EmailAddress	E-mail address for the person.	nvarchar	50
EmailPromotion	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.	int	4	((0))
Phone	Phone number associated with the person.	dbo.Phone	25
PasswordHash	Password for the e-mail account.	varchar	128
PasswordSalt	Random value concatenated with the password string before the password is hashed.	varchar	10
AdditionalContactInfo	Additional contact information about the person stored in xml format.	Person.AdditionalContactInfoSchemaCollection	16
rowguid	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	uniqueidentifier	16	(newid())
ModifiedDate	Date and time the record was last updated.	datetime	4	(getdate())

Indexes

Index	Description	Primary	Unique
AK_Contact_rowguid	Unique nonclustered index. Used to support replication samples.
IX_Contact_EmailAddress	Nonclustered index.
PK_Contact_ContactID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_Contact_EmailPromotion	Check constraint [EmailPromotion] >= (0) AND [EmailPromotion] <= (2)	([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))

Relationships

Relationship	Description
FK_ContactCreditCard_Contact_ContactID	Foreign key constraint referencing Contact.ContactID.
FK_Employee_Contact_ContactID	Foreign key constraint referencing Contact.ContactID.
FK_Individual_Contact_ContactID	Foreign key constraint referencing Contact.ContactID.
FK_SalesOrderHeader_Contact_ContactID	Foreign key constraint referencing Contact.ContactID.
FK_StoreContact_Contact_ContactID	Foreign key constraint referencing Contact.ContactID.
FK_VendorContact_Contact_ContactID	Foreign key constraint referencing Contact.ContactID.

Objects that depend on Person.Contact

Database Object	Object Type	Description	Dep Level
Sales.ContactCreditCard	Table	Cross-reference table mapping customers in the Contact table to their credit card information in the CreditCard table.	1
dEmployee	Trigger	INSTEAD OF DELETE trigger which keeps Employees from being deleted.	2
HumanResources.Employee	Table	Employee information such as salary, department, and title.	1
HumanResources.EmployeeAddress	Table	Cross-reference table mapping employees to their address(es).	2
HumanResources.EmployeeDepartmentHistory	Table	Employee department transfers.	2
HumanResources.EmployeePayHistory	Table	Employee pay history.	2
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.Individual	Table	Demographic data about customers that purchase Adventure Works products online.	1
iPurchaseOrderDetail	Trigger	AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.	4
iuIndividual	Trigger	AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Individual table to the current date.	2
HumanResources.JobCandidate	Table	Résumés submitted to Human Resources by job applicants.	2
Purchasing.PurchaseOrderDetail	Table	Individual products associated with a specific purchase order. See PurchaseOrderHeader.	3
Purchasing.PurchaseOrderHeader	Table	General purchase order information. See PurchaseOrderDetail.	2
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.	2
Sales.SalesPerson	Table	Sales representative current information.	2
Sales.SalesPersonQuotaHistory	Table	Sales performance tracking.	3
Sales.SalesTerritoryHistory	Table	Sales representative transfers to other sales territories.	3
Sales.StoreContact	Table	Cross-reference table mapping stores and their employees.	1
dbo.ufnGetContactInformation	User Defined Function	Table value function returning the first name, last name, job title and contact type for a given contact.	1
uPurchaseOrderDetail	Trigger	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.	4
uPurchaseOrderHeader	Trigger	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.	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.	2
dbo.uspGetEmployeeManagers	Stored Procedure	Stored procedure using a recursive query to return the direct and indirect managers of the specified employee.	1
dbo.uspGetManagerEmployees	Stored Procedure	Stored procedure using a recursive query to return the direct and indirect employees of the specified manager.	1
HumanResources.uspUpdateEmployeeHireInfo	Stored Procedure	Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters.	2
HumanResources.uspUpdateEmployeeLogin	Stored Procedure	Updates the Employee table with the values specified in the input parameters for the given EmployeeID.	2
HumanResources.uspUpdateEmployeePersonalInfo	Stored Procedure	Updates the Employee table with the values specified in the input parameters for the given EmployeeID.	2
Person.vAdditionalContactInfo	View	Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.	1
HumanResources.vEmployee	View	Employee names and addresses.	1
HumanResources.vEmployeeDepartment	View	Returns employee name, title, and current department.	1
HumanResources.vEmployeeDepartmentHistory	View	Returns employee name and current and previous departments.	1
Purchasing.VendorContact	Table	Cross-reference table mapping vendors and their employees.	1
Sales.vIndividualCustomer	View	Individual customers (names and addresses) that purchase Adventure Works Cycles products online.	1
Sales.vIndividualDemographics	View	Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table.	2
HumanResources.vJobCandidate	View	Job candidate names and resumes.	3
HumanResources.vJobCandidateEducation	View	Displays the content from each education related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed.	3
HumanResources.vJobCandidateEmployment	View	Displays the content from each employement history related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed.	3
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.	1
Sales.vStoreWithDemographics	View	Stores (names and addresses) that sell Adventure Works Cycles products to consumers.	1
Purchasing.vVendor	View	Vendor (company) names and addresses and the names of vendor employees to contact.	1

Objects that Person.Contact depends on

Database Object	Object Type	Dep Level
dbo.Name	User Defined Data Type	1
dbo.NameStyle	User Defined Data Type	1
dbo.Phone	User Defined Data Type	1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[Contact](
	[ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION 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,
	[EmailAddress] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EmailPromotion] [int] NOT NULL,
	[Phone] [dbo].[Phone] NULL,
	[PasswordHash] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[PasswordSalt] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Contact_ContactID] PRIMARY KEY CLUSTERED 
(
	[ContactID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [Person].[Contact]  WITH CHECK ADD  CONSTRAINT [CK_Contact_EmailPromotion] CHECK  (([EmailPromotion]>=(0) AND [EmailPromotion]<=(2)))
ALTER TABLE [Person].[Contact] CHECK CONSTRAINT [CK_Contact_EmailPromotion]
ALTER TABLE [Person].[Contact] ADD  CONSTRAINT [DF_Contact_NameStyle]  DEFAULT ((0)) FOR [NameStyle]
ALTER TABLE [Person].[Contact] ADD  CONSTRAINT [DF_Contact_EmailPromotion]  DEFAULT ((0)) FOR [EmailPromotion]
ALTER TABLE [Person].[Contact] ADD  CONSTRAINT [DF_Contact_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Person].[Contact] ADD  CONSTRAINT [DF_Contact_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

Related Objects

Person Schema
AdventureWorks Database