# Table: EmployeeDepartmentHistory

## Properties

Property | Value
--- | ---
Name | EmployeeDepartmentHistory
ID | 1189579276
Owner | dbo
Schema | HumanResources
Filegroup | PRIMARY
Data Size (KB) | 16 KB
Index Size (KB) | 48 KB
Rows | 296
Unused (KB) | 0 KB
ANSI Null On |
Quoted Identifier |
Lock Escalation | TABLE
Lock On Bulk Load |
Lob Data Space |
Filestream Data Space ID |
Has Unchecked Assembly Data |
Text In Row Limit (bytes) | Option not set
Large Value Types Out Of Row |
FullText Change Tracking On |
Tracked By Cdc |
Published |
Schema Published |
Replicated |
Replication Filter |
Merge Published |
Sync Tran Subscribed |
MS Shipped |
Modify Date | 8/8/2010 5:23:39 PM
Create Date | 8/8/2010 5:21:51 PM

## Extended Properties

Name | Value
--- | ---
MS_Description | Employee department transfers.

## Columns

Column ID | Column Name | Datatype | Nullable | Is Sparse | Is Column Set
--- | --- | --- | --- | --- | ---
1 | BusinessEntityID | int |  |  |
2 | DepartmentID | smallint |  |  |
3 | ShiftID | tinyint |  |  |
4 | StartDate | date |  |  |
5 | EndDate | date |  |  |
6 | ModifiedDate | datetime |  |  |

## Identity Columns

No identity columns defined.

## Primary Key Constraints

Name | Is System Named | Extended Property Name | Extended Property Value
--- | --- | --- | ---
PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID |  | MS_Description | Primary key (clustered) constraint

## Primary Key Columns

Name
BusinessEntityID
DepartmentID
ShiftID
StartDate

## Foreign Keys

Name | Referenced Object | Update Action | Delete Action
--- | --- | --- | ---
FK_EmployeeDepartmentHistory_Department_DepartmentID | HumanResources.Department | No action | No action
FK_EmployeeDepartmentHistory_Employee_BusinessEntityID | HumanResources.Employee | No action | No action
FK_EmployeeDepartmentHistory_Shift_ShiftID | HumanResources.Shift | No action | No action

## Column Level Check Constraints

No column constraints defined.

## Table Level Check Constraints

Name | Definition | Is Disabled | Is Not For Replication | Is Not Trusted | Uses Database Collation | Is System Named
--- | --- | --- | --- | --- | --- | ---
CK_EmployeeDepartmentHistory_EndDate | ([EndDate]>=[StartDate] OR [EndDate] IS NULL) |  |  |  |  |

## Default Constraints

Name | Column | Definition | Is System Named | Extended Property Name | Extended Property Value
--- | --- | --- | --- | --- | ---
DF_EmployeeDepartmentHistory_ModifiedDate | ModifiedDate | (getdate()) |  | MS_Description | Default constraint value of GETDATE()

## Indexes

Name | Index Type | Primary Key | Unique | Unique Constraint | Ignore Duplicate Key | Disabled
--- | --- | --- | --- | --- | --- | ---
PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID | CLUSTERED |  |  |  |  |
IX_EmployeeDepartmentHistory_DepartmentID | NONCLUSTERED |  |  |  |  |
IX_EmployeeDepartmentHistory_ShiftID | NONCLUSTERED |  |  |  |  |

## FullText Indexes

No fulltext indexes defined.

## Referencing Tables

No referencing tables defined.

## Object Level Permissions

#### Permissions for: weekly_receipts

Permission | Grantor | Grant | With Grant Option | Deny
--- | --- | --- | --- | ---
Alter |  |  |  |
Control |  |  |  |
Delete | dbo |  |  |
Insert |  |  |  |
References |  |  |  |
Select | dbo |  |  |
Take Ownership |  |  |  |
Update | dbo |  |  |
View Change Tracking |  |  |  |
View Definition |  |  |  |

## Column Level Permissions

No explicit column permissions granted to this object

## Statistics

### Detailed Statistics General

Name | Updated | Rows | Rows Sampled | Steps | Density | Average Key Length | String Index | Filter Expression | Unfiltered Rows
--- | --- | --- | --- | --- | --- | --- | --- | --- | ---
PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID | Aug 8 2010 5:22PM | 296 | 296 | 150 | 1 | 10 |  |  | 296

### Detailed Statistics Density

All Density | Average Length | Columns
--- | --- | ---
0.003448276 | 4 | BusinessEntityID
0.003378378 | 7 | BusinessEntityID, StartDate
0.003378378 | 9 | BusinessEntityID, StartDate, DepartmentID
0.003378378 | 10 | BusinessEntityID, StartDate, DepartmentID, ShiftID

### Detailed Statistics Histogram

Range High Key | Range Rows | Estimated Rows | Distinct Range Rows | Avg Range Rows
--- | --- | --- | --- | ---
1 | 0 | 1 | 0 | 1
3 | 1 | 1 | 1 | 1
4 | 0 | 2 | 0 | 1
6 | 1 | 1 | 1 | 1
8 | 1 | 1 | 1 | 1
10 | 1 | 1 | 1 | 1
12 | 1 | 1 | 1 | 1
14 | 1 | 1 | 1 | 1
15 | 0 | 1 | 0 | 1
16 | 0 | 2 | 0 | 1
18 | 1 | 1 | 1 | 1
20 | 1 | 1 | 1 | 1
22 | 1 | 1 | 1 | 1
24 | 1 | 1 | 1 | 1
26 | 1 | 1 | 1 | 1
28 | 1 | 1 | 1 | 1
30 | 1 | 1 | 1 | 1
32 | 1 | 1 | 1 | 1
34 | 1 | 1 | 1 | 1
36 | 1 | 1 | 1 | 1
38 | 1 | 1 | 1 | 1
40 | 1 | 1 | 1 | 1
42 | 1 | 1 | 1 | 1
44 | 1 | 1 | 1 | 1
46 | 1 | 1 | 1 | 1
48 | 1 | 1 | 1 | 1
50 | 1 | 1 | 1 | 1
52 | 1 | 1 | 1 | 1
54 | 1 | 1 | 1 | 1
56 | 1 | 1 | 1 | 1
58 | 1 | 1 | 1 | 1
60 | 1 | 1 | 1 | 1
62 | 1 | 1 | 1 | 1
64 | 1 | 1 | 1 | 1
66 | 1 | 1 | 1 | 1
68 | 1 | 1 | 1 | 1
70 | 1 | 1 | 1 | 1
72 | 1 | 1 | 1 | 1
74 | 1 | 1 | 1 | 1
76 | 1 | 1 | 1 | 1
78 | 1 | 1 | 1 | 1
80 | 1 | 1 | 1 | 1
82 | 1 | 1 | 1 | 1
84 | 1 | 1 | 1 | 1
86 | 1 | 1 | 1 | 1
88 | 1 | 1 | 1 | 1
90 | 1 | 1 | 1 | 1
92 | 1 | 1 | 1 | 1
94 | 1 | 1 | 1 | 1
96 | 1 | 1 | 1 | 1
98 | 1 | 1 | 1 | 1
100 | 1 | 1 | 1 | 1
102 | 1 | 1 | 1 | 1
104 | 1 | 1 | 1 | 1
106 | 1 | 1 | 1 | 1
108 | 1 | 1 | 1 | 1
110 | 1 | 1 | 1 | 1
112 | 1 | 1 | 1 | 1
114 | 1 | 1 | 1 | 1
116 | 1 | 1 | 1 | 1
118 | 1 | 1 | 1 | 1
120 | 1 | 1 | 1 | 1
122 | 1 | 1 | 1 | 1
124 | 1 | 1 | 1 | 1
126 | 1 | 1 | 1 | 1
128 | 1 | 1 | 1 | 1
130 | 1 | 1 | 1 | 1
132 | 1 | 1 | 1 | 1
134 | 1 | 1 | 1 | 1
136 | 1 | 1 | 1 | 1
138 | 1 | 1 | 1 | 1
140 | 1 | 1 | 1 | 1
142 | 1 | 1 | 1 | 1
144 | 1 | 1 | 1 | 1
146 | 1 | 1 | 1 | 1
148 | 1 | 1 | 1 | 1
150 | 1 | 1 | 1 | 1
152 | 1 | 1 | 1 | 1
154 | 1 | 1 | 1 | 1
156 | 1 | 1 | 1 | 1
158 | 1 | 1 | 1 | 1
160 | 1 | 1 | 1 | 1
162 | 1 | 1 | 1 | 1
164 | 1 | 1 | 1 | 1
166 | 1 | 1 | 1 | 1
168 | 1 | 1 | 1 | 1
170 | 1 | 1 | 1 | 1
172 | 1 | 1 | 1 | 1
174 | 1 | 1 | 1 | 1
176 | 1 | 1 | 1 | 1
178 | 1 | 1 | 1 | 1
180 | 1 | 1 | 1 | 1
182 | 1 | 1 | 1 | 1
184 | 1 | 1 | 1 | 1
186 | 1 | 1 | 1 | 1
188 | 1 | 1 | 1 | 1
190 | 1 | 1 | 1 | 1
192 | 1 | 1 | 1 | 1
194 | 1 | 1 | 1 | 1
196 | 1 | 1 | 1 | 1
198 | 1 | 1 | 1 | 1
200 | 1 | 1 | 1 | 1
202 | 1 | 1 | 1 | 1
204 | 1 | 1 | 1 | 1
206 | 1 | 1 | 1 | 1
208 | 1 | 1 | 1 | 1
210 | 1 | 1 | 1 | 1
212 | 1 | 1 | 1 | 1
214 | 1 | 1 | 1 | 1
216 | 1 | 1 | 1 | 1
218 | 1 | 1 | 1 | 1
220 | 1 | 1 | 1 | 1
222 | 1 | 1 | 1 | 1
223 | 0 | 1 | 0 | 1
224 | 0 | 2 | 0 | 1
226 | 1 | 1 | 1 | 1
228 | 1 | 1 | 1 | 1
230 | 1 | 1 | 1 | 1
232 | 1 | 1 | 1 | 1
233 | 0 | 1 | 0 | 1
234 | 0 | 2 | 0 | 1
236 | 1 | 1 | 1 | 1
238 | 1 | 1 | 1 | 1
240 | 1 | 1 | 1 | 1
242 | 1 | 1 | 1 | 1
244 | 1 | 1 | 1 | 1
246 | 1 | 1 | 1 | 1
248 | 1 | 1 | 1 | 1
249 | 0 | 1 | 0 | 1
250 | 0 | 3 | 0 | 1
252 | 1 | 1 | 1 | 1
254 | 1 | 1 | 1 | 1
256 | 1 | 1 | 1 | 1
258 | 1 | 1 | 1 | 1
260 | 1 | 1 | 1 | 1
262 | 1 | 1 | 1 | 1
264 | 1 | 1 | 1 | 1
266 | 1 | 1 | 1 | 1
268 | 1 | 1 | 1 | 1
270 | 1 | 1 | 1 | 1
272 | 1 | 1 | 1 | 1
274 | 1 | 1 | 1 | 1
276 | 1 | 1 | 1 | 1
278 | 1 | 1 | 1 | 1
280 | 1 | 1 | 1 | 1
282 | 1 | 1 | 1 | 1
284 | 1 | 1 | 1 | 1
286 | 1 | 1 | 1 | 1
288 | 1 | 1 | 1 | 1
290 | 1 | 1 | 1 | 1

### Detailed Statistics General

Name | Updated | Rows | Rows Sampled | Steps | Density | Average Key Length | String Index | Filter Expression | Unfiltered Rows
--- | --- | --- | --- | --- | --- | --- | --- | --- | ---
IX_EmployeeDepartmentHistory_DepartmentID | Aug 8 2010 5:23PM | 296 | 296 | 14 | 0.1538462 | 10 |  |  | 296

### Detailed Statistics Density

All Density | Average Length | Columns
--- | --- | ---
0.0625 | 2 | DepartmentID
0.003378378 | 6 | DepartmentID, BusinessEntityID
0.003378378 | 9 | DepartmentID, BusinessEntityID, StartDate
0.003378378 | 10 | DepartmentID, BusinessEntityID, StartDate, ShiftID

### Detailed Statistics Histogram

Range High Key | Range Rows | Estimated Rows | Distinct Range Rows | Avg Range Rows
--- | --- | --- | --- | ---
1 | 0 | 7 | 0 | 1
2 | 0 | 4 | 0 | 1
3 | 0 | 18 | 0 | 1
4 | 0 | 10 | 0 | 1
5 | 0 | 13 | 0 | 1
6 | 0 | 4 | 0 | 1
7 | 0 | 180 | 0 | 1
9 | 6 | 6 | 1 | 6
10 | 0 | 11 | 0 | 1
11 | 0 | 10 | 0 | 1
12 | 0 | 5 | 0 | 1
14 | 7 | 7 | 1 | 7
15 | 0 | 6 | 0 | 1
16 | 0 | 2 | 0 | 1

### Detailed Statistics General

Name | Updated | Rows | Rows Sampled | Steps | Density | Average Key Length | String Index | Filter Expression | Unfiltered Rows
--- | --- | --- | --- | --- | --- | --- | --- | --- | ---
IX_EmployeeDepartmentHistory_ShiftID | Aug 8 2010 5:23PM | 296 | 296 | 3 | 0 | 10 |  |  | 296

### Detailed Statistics Density

All Density | Average Length | Columns
--- | --- | ---
0.3333333 | 1 | ShiftID
0.003448276 | 5 | ShiftID, BusinessEntityID
0.003378378 | 8 | ShiftID, BusinessEntityID, StartDate
0.003378378 | 10 | ShiftID, BusinessEntityID, StartDate, DepartmentID

### Detailed Statistics Histogram

Range High Key | Range Rows | Estimated Rows | Distinct Range Rows | Avg Range Rows
--- | --- | --- | --- | ---
1 | 0 | 182 | 0 | 1
2 | 0 | 62 | 0 | 1
3 | 0 | 52 | 0 | 1

## Objects that depend on [EmployeeDepartmentHistory]

- HumanResources.EmployeeDepartmentHistory
  - HumanResources.vEmployeeDepartment
  - HumanResources.vEmployeeDepartmentHistory

## Objects on which [EmployeeDepartmentHistory] depends

- HumanResources.EmployeeDepartmentHistory
  - HumanResources.Employee
    - Person.Person
      - Person.AdditionalContactInfoSchemaCollection
      - Person.BusinessEntity
      - Demographics.exist
      - Person.IndividualSurveySchemaCollection
      - dbo.Name
      - dbo.NameStyle
    - dbo.Flag
  - HumanResources.Shift
  - HumanResources.Department

## SQL

Script
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE TABLE [HumanResources].[EmployeeDepartmentHistory]( [BusinessEntityID] [int] NOT NULL, [DepartmentID] [smallint] NOT NULL, [ShiftID] [tinyint] NOT NULL, [StartDate] [date] NOT NULL, [EndDate] [date] NULL, [ModifiedDate] [datetime] NOT NULL ) ON [PRIMARY] EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee identification number. Foreign key to Employee.BusinessEntityID.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'EmployeeDepartmentHistory', @level2type=N'COLUMN',@level2name=N'BusinessEntityID' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Department in which the employee worked including currently. Foreign key to Department.DepartmentID.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'EmployeeDepartmentHistory', @level2type=N'COLUMN',@level2name=N'DepartmentID' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'EmployeeDepartmentHistory', @level2type=N'COLUMN',@level2name=N'ShiftID' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date the employee started work in the department.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'EmployeeDepartmentHistory', @level2type=N'COLUMN',@level2name=N'StartDate' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date the employee left the department. NULL = Current department.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'EmployeeDepartmentHistory', @level2type=N'COLUMN',@level2name=N'EndDate' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the record was last updated.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'EmployeeDepartmentHistory', @level2type=N'COLUMN',@level2name=N'ModifiedDate' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee department transfers.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'EmployeeDepartmentHistory'

#### See also

List of Tables