table icon 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 true
Quoted Identifier true
Lock Escalation TABLE
Lock On Bulk Load false
Lob Data Space
Filestream Data Space ID
Has Unchecked Assembly Data false
Text In Row Limit (bytes) Option not set
Large Value Types Out Of Row false
FullText Change Tracking On false
Tracked By Cdc false
Published false
Schema Published false
Replicated false
Replication Filter false
Merge Published false
Sync Tran Subscribed false
MS Shipped false
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 false false false
2 DepartmentID smallint false false false
3 ShiftID tinyint false false false
4 StartDate date false false false
5 EndDate date true false false
6 ModifiedDate datetime false false false

Identity Columns

No identity columns defined.

Primary Key Constraints

Name Is System Named Extended Property Name Extended Property Value
PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID false 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) false false false true false

Default Constraints

Name Column Definition Is System Named Extended Property Name Extended Property Value
DF_EmployeeDepartmentHistory_ModifiedDate ModifiedDate (getdate()) false 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 true true false false false
IX_EmployeeDepartmentHistory_DepartmentID NONCLUSTERED false false false false false
IX_EmployeeDepartmentHistory_ShiftID NONCLUSTERED false false false false false

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 true
Insert
References
Select dbo true
Take Ownership
Update dbo true
View Change Tracking
View Definition

Column Level Permissions

No explicit column permissions granted to this object

Statistics

Detailed Statistics General

NameUpdatedRowsRows SampledStepsDensityAverage Key LengthString IndexFilter ExpressionUnfiltered Rows
PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID Aug 8 2010 5:22PM 296 296 150 1 10 false 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

NameUpdatedRowsRows SampledStepsDensityAverage Key LengthString IndexFilter ExpressionUnfiltered Rows
IX_EmployeeDepartmentHistory_DepartmentID Aug 8 2010 5:23PM 296 296 14 0.1538462 10 false 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

NameUpdatedRowsRows SampledStepsDensityAverage Key LengthString IndexFilter ExpressionUnfiltered Rows
IX_EmployeeDepartmentHistory_ShiftID Aug 8 2010 5:23PM 296 296 3 0 10 false 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]

Objects on which [EmployeeDepartmentHistory] depends

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