Object type: Table

Employee department transfers.

Columns 

Name	Data type	Nullable	Description
EmployeeID	int	No	Employee identification number. Foreign key to Employee.EmployeeID.
DepartmentID	smallint	No	Department in which the employee worked including currently. Foreign key to Department.DepartmentID.
ShiftID	tinyint	No	Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.
StartDate	datetime	No	Date the employee started work in the department.
EndDate	datetime	Yes	Date the employee left the department. NULL = Current department.
ModifiedDate	datetime	No	Default value: GETDATE() Date and time the record was last updated.

Constraints 

Name	Type	Columns	Description
DF_EmployeeDepartmentHistory_ModifiedDate	DEFAULT	ModifiedDate	Value: GETDATE() Default constraint value of GETDATE()
CK_EmployeeDepartmentHistory_EndDate	CHECK	N/A	Value: (([EndDate] >= [StartDate]) OR ([EndDate] IS NULL) ) Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NUL
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	PRIMARY KEY	EmployeeID StartDate DepartmentID ShiftID	Clustered index created by a primary key constraint.
FK_EmployeeDepartmentHistory_Department_DepartmentID	FOREIGN KEY	DepartmentID	Reference table: HumanResources.Department Foreign key constraint referencing Department.DepartmentID.
FK_EmployeeDepartmentHistory_Employee_EmployeeID	FOREIGN KEY	EmployeeID	Reference table: HumanResources.Employee Foreign key constraint referencing Employee.EmployeeID.
FK_EmployeeDepartmentHistory_Shift_ShiftID	FOREIGN KEY	ShiftID	Reference table: HumanResources.Shift Foreign key constraint referencing Shift.ShiftID

Indices 

Name	Description
IX_EmployeeDepartmentHistory_DepartmentID	Nonclustered index.
IX_EmployeeDepartmentHistory_ShiftID	Nonclustered index.

Triggers 

Name	Description
HumanResources.uEmployeeDepartmentHistory	AFTER UPDATE trigger setting the ModifiedDate column in the EmployeeDepartmentHistory table to the current date.

Accessed by 

Name	Description
HumanResources.vEmployeeDepartment	Returns employee name, title, and current department.
HumanResources.vEmployeeDepartmentHistory	Returns employee name and current and previous departments.

Modified by 

Name	Description
HumanResources.uEmployeeDepartmentHistory	AFTER UPDATE trigger setting the ModifiedDate column in the EmployeeDepartmentHistory table to the current date.

Applies to

Tables | User

The AdventureWorks database sample was developed by Microsoft Corporation, copyright 2005. SQL Server is a trademark of Microsoft Corporation