HumanResources.Employee Table

Description

Employee information such as salary, department, and title.

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	290
Data Space Used	56.00 KB
Index Space Used	136.00 KB

Columns

Column Name	Description	Datatype	Length	Default	Formula
BusinessEntityID	Primary key for Employee records. Foreign key to BusinessEntity.BusinessEntityID.	Integer	4
NationalIDNumber	Unique national identification number such as a social security number.	VarWChar	15
LoginID	Network login.	VarWChar	256
OrganizationNode	Where the employee is located in corporate hierarchy.	UserDefined	892
OrganizationLevel	The depth of the employee in the corporate hierarchy.	SmallInt	2		([OrganizationNode].[GetLevel]())
JobTitle	Work title such as Buyer or Sales Representative.	VarWChar	50
BirthDate	Date of birth.	DBDate	8
MaritalStatus	M = Married, S = Single	WChar	1
Gender	M = Male, F = Female	WChar	1
HireDate	Employee hired on this date.	DBDate	8
SalariedFlag	Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.	dbo.Flag	1	((1))
VacationHours	Number of available vacation hours.	SmallInt	2	((0))
SickLeaveHours	Number of available sick leave hours.	SmallInt	2	((0))
CurrentFlag	0 = Inactive, 1 = Active	dbo.Flag	1	((1))
rowguid	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	GUID	16	(newid())
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
AK_Employee_LoginID	Unique nonclustered index.
AK_Employee_NationalIDNumber	Unique nonclustered index.
AK_Employee_rowguid	Unique nonclustered index. Used to support replication samples.
IX_Employee_OrganizationLevel_OrganizationNode	Unique nonclustered index.
IX_Employee_OrganizationNode	Unique nonclustered index.
PK_Employee_BusinessEntityID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_Employee_BirthDate	Check constraint [BirthDate] >= '1930-01-01' AND [BirthDate] <= dateadd(year,(-18),GETDATE())	([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate()))
CK_Employee_Gender	Check constraint [Gender]='f' OR [Gender]='m' OR [Gender]='F' OR [Gender]='M'	(upper([Gender])='F' OR upper([Gender])='M')
CK_Employee_HireDate	Check constraint [HireDate] >= '1996-07-01' AND [HireDate] <= dateadd(day,(1),GETDATE())	([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate()))
CK_Employee_MaritalStatus	Check constraint [MaritalStatus]='s' OR [MaritalStatus]='m' OR [MaritalStatus]='S' OR [MaritalStatus]='M'	(upper([MaritalStatus])='S' OR upper([MaritalStatus])='M')
CK_Employee_SickLeaveHours	Check constraint [SickLeaveHours] >= (0) AND [SickLeaveHours] <= (120)	([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120))
CK_Employee_VacationHours	Check constraint [VacationHours] >= (-40) AND [VacationHours] <= (240)	([VacationHours]>=(-40) AND [VacationHours]<=(240))

Triggers

Trigger	Description
dEmployee	INSTEAD OF DELETE trigger which keeps Employees from being deleted.

Relationships

Relationship	Description
FK_Document_Employee_Owner	Foreign key constraint referencing Employee.BusinessEntityID.
FK_Employee_Person_BusinessEntityID	Foreign key constraint referencing Person.BusinessEntityID.
FK_EmployeeDepartmentHistory_Employee_BusinessEntityID	Foreign key constraint referencing Employee.EmployeeID.
FK_EmployeePayHistory_Employee_BusinessEntityID	Foreign key constraint referencing Employee.EmployeeID.
FK_JobCandidate_Employee_BusinessEntityID	Foreign key constraint referencing Employee.EmployeeID.
FK_PurchaseOrderHeader_Employee_EmployeeID	Foreign key constraint referencing Employee.EmployeeID.
FK_SalesPerson_Employee_BusinessEntityID	Foreign key constraint referencing Employee.EmployeeID.

Objects that depend on HumanResources.Employee

Database Object	Object Type	Description	Dep Level
Sales.Customer	Table	Current customer information. Also see the Person and Store tables.	3
dEmployee	Trigger	INSTEAD OF DELETE trigger which keeps Employees from being deleted.	1
Production.Document	Table	Product maintenance documents.	1
HumanResources.EmployeeDepartmentHistory	Table	Employee department transfers.	1
HumanResources.EmployeePayHistory	Table	Employee pay history.	1
iPurchaseOrderDetail	Trigger	AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.	3
HumanResources.JobCandidate	Table	Résumés submitted to Human Resources by job applicants.	1
Production.ProductDocument	Table	Cross-reference table mapping products to related product documents.	2
Purchasing.PurchaseOrderDetail	Table	Individual products associated with a specific purchase order. See PurchaseOrderHeader.	2
Purchasing.PurchaseOrderHeader	Table	General purchase order information. See PurchaseOrderDetail.	1
Sales.SalesOrderHeader	Table	General sales order information.	2
Sales.SalesOrderHeaderSalesReason	Table	Cross-reference table mapping sales orders to sales reason codes.	3
Sales.SalesPerson	Table	Sales representative current information.	1
Sales.SalesPersonQuotaHistory	Table	Sales performance tracking.	2
Sales.SalesTerritoryHistory	Table	Sales representative transfers to other sales territories.	2
Sales.Store	Table	Customers (resellers) of Adventure Works products.	2
dbo.ufnGetContactInformation	User Defined Function	Table value function returning the first name, last name, job title and contact type for a given contact.	1
uPurchaseOrderDetail	Trigger	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.	3
uPurchaseOrderHeader	Trigger	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.	2
uSalesOrderHeader	Trigger	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the SalesOrderHeader table.Updates the SalesYTD column in the SalesPerson and SalesTerritory tables.	3
dbo.uspGetEmployeeManagers	Stored Procedure	Stored procedure using a recursive query to return the direct and indirect managers of the specified employee.	1
dbo.uspGetManagerEmployees	Stored Procedure	Stored procedure using a recursive query to return the direct and indirect employees of the specified manager.	1
dbo.uspSearchCandidateResumes	Stored Procedure		2
HumanResources.uspUpdateEmployeeHireInfo	Stored Procedure	Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters.	1
HumanResources.uspUpdateEmployeeLogin	Stored Procedure	Updates the Employee table with the values specified in the input parameters for the given BusinessEntityID.	1
HumanResources.uspUpdateEmployeePersonalInfo	Stored Procedure	Updates the Employee table with the values specified in the input parameters for the given EmployeeID.	1
HumanResources.vEmployee	View	Employee names and addresses.	1
HumanResources.vEmployeeDepartment	View	Returns employee name, title, and current department.	1
HumanResources.vEmployeeDepartmentHistory	View	Returns employee name and current and previous departments.	1
Sales.vIndividualCustomer	View	Individual customers (names and addresses) that purchase Adventure Works Cycles products online.	4
HumanResources.vJobCandidate	View	Job candidate names and resumes.	2
HumanResources.vJobCandidateEducation	View	Displays the content from each education related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed.	2
HumanResources.vJobCandidateEmployment	View	Displays the content from each employement history related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed.	2
Sales.vSalesPerson	View	Sales representiatives (names and addresses) and their sales-related information.	1
Sales.vSalesPersonSalesByFiscalYears	View	Uses PIVOT to return aggregated sales information for each sales representative.	1
Sales.vStoreWithAddresses	View	Stores (including store addresses) that sell Adventure Works Cycles products to consumers.	3
Sales.vStoreWithContacts	View	Stores (including store contacts) that sell Adventure Works Cycles products to consumers.	3
Sales.vStoreWithDemographics	View	Stores (including demographics) that sell Adventure Works Cycles products to consumers.	3

Objects that HumanResources.Employee depends on

Database Object	Object Type	Description	Dep Level
Person.BusinessEntity	Table	Source of the ID that connects vendors, customers, and employees with address and contact information.	2
dbo.Flag	User Defined Data Type		1
dbo.Name	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		2
Person.Person	Table	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.	1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[Employee](
    [BusinessEntityID] [int] NOT NULL,
    [NationalIDNumber] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [LoginID] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [OrganizationNode] [hierarchyid] NULL,
    [OrganizationLevel]  AS ([OrganizationNode].[GetLevel]()),
    [JobTitle] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [BirthDate] [date] 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] [date] 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_BusinessEntityID] PRIMARY KEY CLUSTERED 
(
    [BusinessEntityID] 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] 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]
ALTER TABLE [HumanResources].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Person_BusinessEntityID] FOREIGN KEY([BusinessEntityID])
REFERENCES [Person].[Person] ([BusinessEntityID])
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [FK_Employee_Person_BusinessEntityID]
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]

Related Objects