HumanResources.EmployeeDepartmentHistory Table

Information

Name	EmployeeDepartmentHistory
Schema	HumanResources
Row Count	0
Data Size
Index Size
Reserved Size
Unused Size
Created	29.3.2010. 21:58:18
Modified	29.3.2010. 21:58:54

Extended Properties

Name	Type	Property Name	Value
EmployeeDepartmentHistory	TABLE	MS_Description	Employee department transfers.

Columns

Name	Data Type	References	Default
EmployeeID	int	HumanResources.Employee.EmployeeID
DepartmentID	smallint	HumanResources.Department.DepartmentID
ShiftID	tinyint	HumanResources.Shift.ShiftID
StartDate	datetime
EndDate	datetime
ModifiedDate	datetime		(getdate())

Indexes

Name	Type	Columns
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	Clustered	EmployeeID, StartDate, DepartmentID, ShiftID
IX_EmployeeDepartmentHistory_DepartmentID	NonClustered	DepartmentID
IX_EmployeeDepartmentHistory_ShiftID	NonClustered	ShiftID

Check Constraints

Name	Column	Enabled	Trusted	Expression
CK_EmployeeDepartmentHistory_EndDate				([EndDate]>=[StartDate] OR [EndDate] IS NULL)

Foreign Key Constraints

Name	Type	Referenced Table
FK_EmployeeDepartmentHistory_Department_DepartmentID	Foreign key	HumanResources.Department
FK_EmployeeDepartmentHistory_Employee_EmployeeID	Foreign key	HumanResources.Employee
FK_EmployeeDepartmentHistory_Shift_ShiftID	Foreign key	HumanResources.Shift

SQL Script

CREATE TABLE [HumanResources].[EmployeeDepartmentHistory] (
    [EmployeeID] int NOT NULL,
    [DepartmentID] smallint NOT NULL,
    [ShiftID] tinyint NOT NULL,
    [StartDate] datetime NOT NULL,
    [EndDate] datetime NULL,
    [ModifiedDate] datetime NOT NULL DEFAULT (getdate())
);
CREATE INDEX [IX_EmployeeDepartmentHistory_DepartmentID] on [HumanResources].[EmployeeDepartmentHistory]([DepartmentID]);
CREATE INDEX [IX_EmployeeDepartmentHistory_ShiftID] on [HumanResources].[EmployeeDepartmentHistory]([ShiftID]);