HumanResources.Employee

Object type: Table

Employee information such as salary, department, and title.

Columns

Name Data type Nullable Description

EmployeeID int No Column has identity seed value of 1 with a step value of 1.
Primary key for Employee records.

NationalIDNumber nvarchar(15) No Unique national identification number such as a social security number.

ContactID int No Identifies the employee in the Contact table. Foreign key to Contact.ContactID.

LoginID nvarchar(256) No Network login.

ManagerID int Yes Manager to whom the employee is assigned. Foreign Key to Employee.M

Title nvarchar(50) No Work title such as Buyer or Sales Representative.

BirthDate datetime No Date of birth.

MaritalStatus nchar(1) No M = Married, S = Single

Gender nchar(1) No M = Male, F = Female

HireDate datetime No Employee hired on this date.

SalariedFlag Flag No Default value: 1
Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.

VacationHours smallint No Default value: 0
Number of available vacation hours.

SickLeaveHours smallint No Default value: 0
Number of available sick leave hours.

CurrentFlag Flag No Default value: 1
0 = Inactive, 1 = Active

rowguid uniqueidentifier No Default value: NEWID()
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.

ModifiedDate datetime No Default value: GETDATE()
Date and time the record was last updated.

Constraints

Name Type Columns Description

DF_Employee_SalariedFlag DEFAULT SalariedFlag Value: 1
Default constraint value of 1 (TRUE)

DF_Employee_VacationHours DEFAULT VacationHours Value: 0
Default constraint value of 0

DF_Employee_SickLeaveHours DEFAULT SickLeaveHours Value: 0
Default constraint value of 0

DF_Employee_CurrentFlag DEFAULT CurrentFlag Value: 1
Default constraint value of 1

DF_Employee_rowguid DEFAULT rowguid Value: NEWID()
Default constraint value of NEWID()

DF_Employee_ModifiedDate DEFAULT ModifiedDate Value: GETDATE()
Default constraint value of GETDATE()

CK_Employee_BirthDate CHECK N/A Value: ([BirthDate] BETWEEN '1930-01-01' AND DATEADD(YEAR, -18, GETDATE() ) )
Check constraint [BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),GETDATE())

CK_Employee_MaritalStatus CHECK N/A Value: (UPPER([MaritalStatus]) IN ('M', 'S') )
Check constraint [MaritalStatus]='s' OR [MaritalStatus]='m' OR [MaritalStatus]='S' OR [MaritalStatus]='M'

CK_Employee_HireDate CHECK N/A Value: ([HireDate] BETWEEN '1996-07-01' AND DATEADD(DAY, 1, GETDATE() ) )
Check constraint [HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),GETDATE())

CK_Employee_Gender CHECK N/A Value: (UPPER([Gender]) IN ('M', 'F') )
Check constraint [Gender]='f' OR [Gender]='m' OR [Gender]='F' OR [Gender]='M'

CK_Employee_VacationHours CHECK N/A Value: ([VacationHours] BETWEEN -40 AND 240)
Check constraint [VacationHours]>=(-40) AND [VacationHours]<=(240)

CK_Employee_SickLeaveHours CHECK N/A Value: ([SickLeaveHours] BETWEEN 0 AND 120)
Check constraint [SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120)

PK_Employee_EmployeeID PRIMARY KEY EmployeeID Clustered index created by a primary key constraint.

FK_Employee_Employee_ManagerID FOREIGN KEY ManagerID Reference table: HumanResources.Employee
Foreign key constraint referencing Employee.ManagerID.

FK_Employee_Contact_ContactID FOREIGN KEY ContactID Reference table: Person.Contact
Foreign key constraint referencing Contact.ContactID.

Indices

Name Description

AK_Employee_LoginID Unique nonclustered index.

AK_Employee_NationalIDNumber Unique nonclustered index.

AK_Employee_rowguid Unique nonclustered index.Used to support replication samples.

IX_Employee_ManagerID Nonclustered index.

Triggers

Name Description

HumanResources.dEmployee INSTEAD OF DELETE trigger which keeps Employees from being deleted.

HumanResources.uEmployee AFTER UPDATE trigger setting the ModifiedDate column in the Employee table to the current date.

Linked tables

Name Description

HumanResources.Employee Employee information such as salary, department, and title.

HumanResources.EmployeeAddress Cross-reference table mapping employees to their address(es).

HumanResources.EmployeeDepartmentHistory Employee department transfers.

HumanResources.EmployeePayHistory Employee pay history.

HumanResources.JobCandidate Résumés submitted to Human Resources by job applicants.

Purchasing.PurchaseOrderHeader General purchase order information. See PurchaseOrderDetail.

Sales.SalesPerson Sales representative current information.

Accessed by

Name Description

dbo.ufnGetContactInformation Table value function returning the first name, last name, job title and contact type for a given contact.

dbo.uspGetEmployeeManagers Stored procedure using a recursive query to return the direct and indirect managers of the specified employee.

dbo.uspGetManagerEmployees Stored procedure using a recursive query to return the direct and indirect employees of the specified manager.

Employee Sales Summary Adventure Works sales for an individual employee. This report includes Sales Comparison and Current Month Sales Comparison charts in addition to a Current Month Order Summary table with drillthrough to individual orders. This report illustrates the use of multiple datasets, charts, tables, drillthrough, and dynamic parameters.

HumanResources.vEmployee Employee names and addresses.

HumanResources.vEmployeeDepartment Returns employee name, title, and current department.

HumanResources.vEmployeeDepartmentHistory Returns employee name and current and previous departments.

Product Line Sales Adventure Works top five sales people and stores. This report illustrates a dataset with queries containing the TOP clause. It also illustrates the use of tables, charts, parameters, calculated fields, and drillthrough links.

Sales Order Detail Detail of an individual Adventure Works order. This report can be accessed as a drillthrough report from the Employee Sales Summary and Territory Sales drilldown report. This report illustrates the use of lists, tables, parameters, and expressions.

Sales.vSalesPerson Sales representiatives (names and addresses) and their sales-related information.

Sales.vSalesPersonSalesByFiscalYears Uses PIVOT to return aggregated sales information for each sales representative.

Territory Sales Drilldown Adventure Works sales by territory. This report drills down through salesperson and order number with drillthrough to individual orders. This report illustrates the use of a table data region that provides drilldown from summary data into detail data by showing and hiding rows. This report also illustrates the use of drillthrough links and conditional formatting.

Modified by

Name Description

HumanResources.uEmployee AFTER UPDATE trigger setting the ModifiedDate column in the Employee table to the current date.

HumanResources.uspUpdateEmployeeHireInfo Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters.

HumanResources.uspUpdateEmployeeLogin Updates the Employee table with the values specified in the input parameters for the given EmployeeID.

HumanResources.uspUpdateEmployeePersonalInfo Updates the Employee table with the values specified in the input parameters for the given EmployeeID.

Applies to

Tables | User

Name	Data type	Nullable	Description
EmployeeID	int	No	Column has identity seed value of 1 with a step value of 1. Primary key for Employee records.
NationalIDNumber	nvarchar(15)	No	Unique national identification number such as a social security number.
ContactID	int	No	Identifies the employee in the Contact table. Foreign key to Contact.ContactID.
LoginID	nvarchar(256)	No	Network login.
ManagerID	int	Yes	Manager to whom the employee is assigned. Foreign Key to Employee.M
Title	nvarchar(50)	No	Work title such as Buyer or Sales Representative.
BirthDate	datetime	No	Date of birth.
MaritalStatus	nchar(1)	No	M = Married, S = Single
Gender	nchar(1)	No	M = Male, F = Female
HireDate	datetime	No	Employee hired on this date.
SalariedFlag	Flag	No	Default value: 1 Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
VacationHours	smallint	No	Default value: 0 Number of available vacation hours.
SickLeaveHours	smallint	No	Default value: 0 Number of available sick leave hours.
CurrentFlag	Flag	No	Default value: 1 0 = Inactive, 1 = Active
rowguid	uniqueidentifier	No	Default value: NEWID() ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime	No	Default value: GETDATE() Date and time the record was last updated.

Name	Type	Columns	Description
DF_Employee_SalariedFlag	DEFAULT	SalariedFlag	Value: 1 Default constraint value of 1 (TRUE)
DF_Employee_VacationHours	DEFAULT	VacationHours	Value: 0 Default constraint value of 0
DF_Employee_SickLeaveHours	DEFAULT	SickLeaveHours	Value: 0 Default constraint value of 0
DF_Employee_CurrentFlag	DEFAULT	CurrentFlag	Value: 1 Default constraint value of 1
DF_Employee_rowguid	DEFAULT	rowguid	Value: NEWID() Default constraint value of NEWID()
DF_Employee_ModifiedDate	DEFAULT	ModifiedDate	Value: GETDATE() Default constraint value of GETDATE()
CK_Employee_BirthDate	CHECK	N/A	Value: ([BirthDate] BETWEEN '1930-01-01' AND DATEADD(YEAR, -18, GETDATE() ) ) Check constraint [BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),GETDATE())
CK_Employee_MaritalStatus	CHECK	N/A	Value: (UPPER([MaritalStatus]) IN ('M', 'S') ) Check constraint [MaritalStatus]='s' OR [MaritalStatus]='m' OR [MaritalStatus]='S' OR [MaritalStatus]='M'
CK_Employee_HireDate	CHECK	N/A	Value: ([HireDate] BETWEEN '1996-07-01' AND DATEADD(DAY, 1, GETDATE() ) ) Check constraint [HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),GETDATE())
CK_Employee_Gender	CHECK	N/A	Value: (UPPER([Gender]) IN ('M', 'F') ) Check constraint [Gender]='f' OR [Gender]='m' OR [Gender]='F' OR [Gender]='M'
CK_Employee_VacationHours	CHECK	N/A	Value: ([VacationHours] BETWEEN -40 AND 240) Check constraint [VacationHours]>=(-40) AND [VacationHours]<=(240)
CK_Employee_SickLeaveHours	CHECK	N/A	Value: ([SickLeaveHours] BETWEEN 0 AND 120) Check constraint [SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120)
PK_Employee_EmployeeID	PRIMARY KEY	EmployeeID	Clustered index created by a primary key constraint.
FK_Employee_Employee_ManagerID	FOREIGN KEY	ManagerID	Reference table: HumanResources.Employee Foreign key constraint referencing Employee.ManagerID.
FK_Employee_Contact_ContactID	FOREIGN KEY	ContactID	Reference table: Person.Contact Foreign key constraint referencing Contact.ContactID.

Name	Description
AK_Employee_LoginID	Unique nonclustered index.
AK_Employee_NationalIDNumber	Unique nonclustered index.
AK_Employee_rowguid	Unique nonclustered index.Used to support replication samples.
IX_Employee_ManagerID	Nonclustered index.

Name	Description
HumanResources.dEmployee	INSTEAD OF DELETE trigger which keeps Employees from being deleted.
HumanResources.uEmployee	AFTER UPDATE trigger setting the ModifiedDate column in the Employee table to the current date.

Name	Description
dbo.ufnGetContactInformation	Table value function returning the first name, last name, job title and contact type for a given contact.
dbo.uspGetEmployeeManagers	Stored procedure using a recursive query to return the direct and indirect managers of the specified employee.
dbo.uspGetManagerEmployees	Stored procedure using a recursive query to return the direct and indirect employees of the specified manager.
Employee Sales Summary	Adventure Works sales for an individual employee. This report includes Sales Comparison and Current Month Sales Comparison charts in addition to a Current Month Order Summary table with drillthrough to individual orders. This report illustrates the use of multiple datasets, charts, tables, drillthrough, and dynamic parameters.
HumanResources.vEmployee	Employee names and addresses.
HumanResources.vEmployeeDepartment	Returns employee name, title, and current department.
HumanResources.vEmployeeDepartmentHistory	Returns employee name and current and previous departments.
Product Line Sales	Adventure Works top five sales people and stores. This report illustrates a dataset with queries containing the TOP clause. It also illustrates the use of tables, charts, parameters, calculated fields, and drillthrough links.
Sales Order Detail	Detail of an individual Adventure Works order. This report can be accessed as a drillthrough report from the Employee Sales Summary and Territory Sales drilldown report. This report illustrates the use of lists, tables, parameters, and expressions.
Sales.vSalesPerson	Sales representiatives (names and addresses) and their sales-related information.
Sales.vSalesPersonSalesByFiscalYears	Uses PIVOT to return aggregated sales information for each sales representative.
Territory Sales Drilldown	Adventure Works sales by territory. This report drills down through salesperson and order number with drillthrough to individual orders. This report illustrates the use of a table data region that provides drilldown from summary data into detail data by showing and hiding rows. This report also illustrates the use of drillthrough links and conditional formatting.