Description
Employee information such as salary, department, and title.
Table properties
| name | value |
|---|
| name | [HumanResources].[Employee] |
| created | Mar 19 2009 9:08PM |
| modified | Mar 19 2009 9:08PM
|
| ansi nulls | on
|
| quoted identifier | on
|
| row count | 290 |
| Size of data | 56 kb |
| Size of indexes | 120 kb |
| Maximum size of a single row | 1,348 bytes |
Columns
| column | datatype | length | bytes | default | nulls | PK | FK | UQ | computed | comment |
|---|
| EmployeeID | int identity(1,1) | 10 | 4 | | no
| yes
| |
| no
| Primary key for Employee records.
|
| NationalIDNumber | nvarchar(15) | 15 | 60 | | no
|
| |
| no
| Unique national identification number such as a social security number.
|
| ContactID | int | 10 | 4 | | no
|
| Contact.ContactID
|
| no
| Identifies the employee in the Contact table. Foreign key to Contact.ContactID.
|
| LoginID | nvarchar(256) | 256 | 1024 | | no
|
| |
| no
| Network login.
|
| ManagerID | int | 10 | 4 | | yes
|
| Employee.EmployeeID
|
| no
| Manager to whom the employee is assigned. Foreign Key to Employee.M
|
| Title | nvarchar(50) | 50 | 200 | | no
|
| |
| no
| Work title such as Buyer or Sales Representative.
|
| BirthDate | datetime | 23 | 8 | | no
|
| |
| no
| Date of birth.
|
| MaritalStatus | nchar(1) | 1 | 4 | | no
|
| |
| no
| M = Married, S = Single
|
| Gender | nchar(1) | 1 | 4 | | no
|
| |
| no
| M = Male, F = Female
|
| HireDate | datetime | 23 | 8 | | no
|
| |
| no
| Employee hired on this date.
|
| SalariedFlag | Flag | 1 | 0 | ((1)) | no
|
| |
| no
| Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
|
| VacationHours | smallint | 5 | 2 | ((0)) | no
|
| |
| no
| Number of available vacation hours.
|
| SickLeaveHours | smallint | 5 | 2 | ((0)) | no
|
| |
| no
| Number of available sick leave hours.
|
| CurrentFlag | Flag | 1 | 0 | ((1)) | no
|
| |
| no
| 0 = Inactive, 1 = Active
|
| rowguid | uniqueidentifier | 36 | 16 | (newid()) | no
|
| |
| no
| ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
|
| ModifiedDate | datetime | 23 | 8 | (getdate()) | no
|
| |
| no
| Date and time the record was last updated.
|
Indexes
| name | description | column | comment |
|---|
| AK_Employee_LoginID | nonclustered, unique located on PRIMARY | LoginID | Unique nonclustered index.
|
| AK_Employee_NationalIDNumber | nonclustered, unique located on PRIMARY | NationalIDNumber | Unique nonclustered index.
|
| AK_Employee_rowguid | nonclustered, unique located on PRIMARY | rowguid | Unique nonclustered index. Used to support replication samples.
|
| IX_Employee_ManagerID | nonclustered located on PRIMARY | ManagerID | Nonclustered index.
|
| PK_Employee_EmployeeID | clustered, unique, primary key located on PRIMARY | EmployeeID | Clustered index created by a primary key constraint.
|
References
Referenced by
Foreign key graph
Foreign keys
| name | columns | foreign columns | type | comment |
|---|
| FK_Employee_Contact_ContactID | ContactID | Contact.ContactID |
| Foreign key constraint referencing Contact.ContactID.
|
| FK_Employee_Employee_ManagerID | ManagerID | Employee.EmployeeID |
| Foreign key constraint referencing Employee.ManagerID.
|
Check constraints
| name | column | comment |
|---|
CK_Employee_BirthDate([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate())) | BirthDate
| Check constraint [BirthDate] >= '1930-01-01' AND [BirthDate] <= dateadd(year,(-18),GETDATE())
|
CK_Employee_MaritalStatus(upper([MaritalStatus])='S' OR upper([MaritalStatus])='M') | MaritalStatus
| Check constraint [MaritalStatus]='s' OR [MaritalStatus]='m' OR [MaritalStatus]='S' OR [MaritalStatus]='M'
|
CK_Employee_HireDate([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate())) | HireDate
| Check constraint [HireDate] >= '1996-07-01' AND [HireDate] <= dateadd(day,(1),GETDATE())
|
CK_Employee_Gender(upper([Gender])='F' OR upper([Gender])='M') | Gender
| Check constraint [Gender]='f' OR [Gender]='m' OR [Gender]='F' OR [Gender]='M'
|
CK_Employee_VacationHours([VacationHours]>=(-40) AND [VacationHours]<=(240)) | VacationHours
| Check constraint [VacationHours] >= (-40) AND [VacationHours] <= (240)
|
CK_Employee_SickLeaveHours([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120)) | SickLeaveHours
| Check constraint [SickLeaveHours] >= (0) AND [SickLeaveHours] <= (120)
|
Triggers
| name | comment |
|---|
| dEmployee | INSTEAD OF DELETE trigger which keeps Employees from being deleted.
|
Defaults
Dependency graph
Objects that [HumanResources].[Employee] depends on
| name | object type | level |
|---|
| Flag | type | 1 |
Objects that depend on [HumanResources].[Employee]
Sample rows
| EmployeeID | NationalIDNumber | ContactID | LoginID | ManagerID | Title | BirthDate | MaritalStatus | Gender | HireDate | SalariedFlag | VacationHours | SickLeaveHours | CurrentFlag | rowguid | ModifiedDate |
|---|
| 1
| 14417807
| 1209
| adventure-works\guy1
| 16
| Production Technician - WC60
| 5/15/1972 12:00:00 AM
| M
| M
| 7/31/1996 12:00:00 AM
| False
| 21
| 30
| True
| aae1d04a-c237-4974-b4d5-935247737718
| 7/31/2004 12:00:00 AM
|
| 2
| 253022876
| 1030
| adventure-works\kevin0
| 6
| Marketing Assistant
| 6/3/1977 12:00:00 AM
| S
| M
| 2/26/1997 12:00:00 AM
| False
| 42
| 41
| True
| 1b480240-95c0-410f-a717-eb29943c8886
| 7/31/2004 12:00:00 AM
|
| 3
| 509647174
| 1002
| adventure-works\roberto0
| 12
| Engineering Manager
| 12/13/1964 12:00:00 AM
| M
| M
| 12/12/1997 12:00:00 AM
| True
| 2
| 21
| True
| 9bbbfb2c-efbb-4217-9ab7-f97689328841
| 7/31/2004 12:00:00 AM
|
| 4
| 112457891
| 1290
| adventure-works\rob0
| 3
| Senior Tool Designer
| 1/23/1965 12:00:00 AM
| S
| M
| 1/5/1998 12:00:00 AM
| False
| 48
| 80
| True
| 59747955-87b8-443f-8ed4-f8ad3afdf3a9
| 7/31/2004 12:00:00 AM
|
| 5
| 480168528
| 1009
| adventure-works\thierry0
| 263
| Tool Designer
| 8/29/1949 12:00:00 AM
| M
| M
| 1/11/1998 12:00:00 AM
| False
| 9
| 24
| True
| 1d955171-e773-4fad-8382-40fd898d5d4d
| 7/31/2004 12:00:00 AM
|
| 6
| 24756624
| 1028
| adventure-works\david0
| 109
| Marketing Manager
| 4/19/1965 12:00:00 AM
| S
| M
| 1/20/1998 12:00:00 AM
| True
| 40
| 40
| True
| e87029aa-2cba-4c03-b948-d83af0313e28
| 7/31/2004 12:00:00 AM
|
| 7
| 309738752
| 1070
| adventure-works\jolynn0
| 21
| Production Supervisor - WC60
| 2/16/1946 12:00:00 AM
| S
| F
| 1/26/1998 12:00:00 AM
| False
| 82
| 61
| True
| 2cc71b96-f421-485e-9832-8723337749bb
| 7/31/2004 12:00:00 AM
|
| 8
| 690627818
| 1071
| adventure-works\ruth0
| 185
| Production Technician - WC10
| 7/6/1946 12:00:00 AM
| M
| F
| 2/6/1998 12:00:00 AM
| False
| 83
| 61
| True
| 3e3b6905-209e-442e-b8a8-9a0980241c6a
| 7/31/2004 12:00:00 AM
|
| 9
| 695256908
| 1005
| adventure-works\gail0
| 3
| Design Engineer
| 10/29/1942 12:00:00 AM
| M
| F
| 2/6/1998 12:00:00 AM
| True
| 5
| 22
| True
| ec84ae09-f9b8-4a15-b4a9-6ccbab919b08
| 7/31/2004 12:00:00 AM
|
| 10
| 912265825
| 1076
| adventure-works\barry0
| 185
| Production Technician - WC10
| 4/27/1946 12:00:00 AM
| S
| M
| 2/7/1998 12:00:00 AM
| False
| 88
| 64
| True
| 756a60ae-378f-43d8-9f93-1e821d1eaf52
| 7/31/2004 12:00:00 AM
|
Code
CREATE TABLE [HumanResources].[Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[NationalIDNumber] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ContactID] [int] NOT NULL,
[LoginID] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[NationalIDNumber] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ContactID] [int] NOT NULL,
[ManagerID] [int] NULL,
[Title] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BirthDate] [datetime] NOT NULL,
[MaritalStatus] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Gender] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HireDate] [datetime] NOT NULL,
[SalariedFlag] [dbo].[Flag] NOT NULL,
[VacationHours] [smallint] NOT NULL,
[SickLeaveHours] [smallint] NOT NULL,
[CurrentFlag] [dbo].[Flag] NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Contact_ContactID] FOREIGN KEY([ContactID])
REFERENCES [Person].[Contact] ([ContactID])
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [FK_Employee_Contact_ContactID]
ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Employee_ManagerID] FOREIGN KEY([ManagerID])
REFERENCES [HumanResources].[Employee] ([EmployeeID])
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [FK_Employee_Employee_ManagerID]
ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [CK_Employee_BirthDate] CHECK (([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate())))
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_BirthDate]
ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [CK_Employee_Gender] CHECK ((upper([Gender])='F' OR upper([Gender])='M'))
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_Gender]
ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [CK_Employee_HireDate] CHECK (([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate())))
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_HireDate]
ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [CK_Employee_MaritalStatus] CHECK ((upper([MaritalStatus])='S' OR upper([MaritalStatus])='M'))
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_MaritalStatus]
ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [CK_Employee_SickLeaveHours] CHECK (([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120)))
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_SickLeaveHours]
ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [CK_Employee_VacationHours] CHECK (([VacationHours]>=(-40) AND [VacationHours]<=(240)))
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_VacationHours]
ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [DF_Employee_SalariedFlag] DEFAULT ((1)) FOR [SalariedFlag]
ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [DF_Employee_VacationHours] DEFAULT ((0)) FOR [VacationHours]
ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [DF_Employee_SickLeaveHours] DEFAULT ((0)) FOR [SickLeaveHours]
ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [DF_Employee_CurrentFlag] DEFAULT ((1)) FOR [CurrentFlag]
ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [DF_Employee_rowguid] DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [DF_Employee_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]