Owner: HumanResources 
Creation Date: 04/26/2006 
Located On: PRIMARY 
Data Size KB: 56 
Index Size KB: 120 
Rows: 290 
Description: Employee information such as salary, department, and title. 


Name Data Type Length NULL Default IsIdentity IsGUID Description
    EmployeeID  int          Primary key for Employee records. 
    NationalIDNumber  nvarchar  15          Unique national identification number such as a social security number. 
    ContactID  int          Identifies the employee in the Contact table. Foreign key to Contact.ContactID. 
    LoginID  nvarchar  256          Network login. 
    ManagerID  int          Manager to whom the employee is assigned. Foreign Key to Employee.M 
    Title  nvarchar  50          Work title such as Buyer or Sales Representative. 
    BirthDate  datetime          Date of birth. 
    MaritalStatus  nchar          M = Married, S = Single 
    Gender  nchar          M = Male, F = Female 
    HireDate  datetime          Employee hired on this date. 
    SalariedFlag  Flag    ((1))      Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining. 
    VacationHours  smallint    ((0))      Number of available vacation hours. 
    SickLeaveHours  smallint    ((0))      Number of available sick leave hours. 
    CurrentFlag  Flag    ((1))      0 = Inactive, 1 = Active 
    rowguid  uniqueidentifier  16    (newid())      ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. 
    ModifiedDate  datetime    (getdate())      Date and time the record was last updated. 
Total: 16 column(s)

Identity column

Name Seed Increment Not for replication


Index Primary Unique Description
  PK_Employee_EmployeeID      Primary key (clustered) constraint 
  IX_Employee_ManagerID      Nonclustered index. 
  AK_Employee_LoginID      Unique nonclustered index. 
  AK_Employee_NationalIDNumber      Unique nonclustered index. 
  AK_Employee_rowguid      Unique nonclustered index. Used to support replication samples. 
Total: 5 index(es)


Name Owner Instead Of Disabled Table/View Description
  dEmployee  HumanResources      HumanResources.Employee  INSTEAD OF DELETE trigger which keeps Employees from being deleted. 
Total: 1 trigger(s)

Check Constraints

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

Referencing Tables

Table Foreign Key Primary Key or Unique Constraint
  HumanResources.JobCandidate  FK_JobCandidate_Employee_EmployeeID  PK_Employee_EmployeeID 
  Purchasing.PurchaseOrderHeader  FK_PurchaseOrderHeader_Employee_EmployeeID  PK_Employee_EmployeeID 
  Sales.SalesPerson  FK_SalesPerson_Employee_SalesPersonID  PK_Employee_EmployeeID 
  HumanResources.Employee  FK_Employee_Employee_ManagerID  PK_Employee_EmployeeID 
  HumanResources.EmployeeAddress  FK_EmployeeAddress_Employee_EmployeeID  PK_Employee_EmployeeID 
  HumanResources.EmployeeDepartmentHistory  FK_EmployeeDepartmentHistory_Employee_EmployeeID  PK_Employee_EmployeeID 
  HumanResources.EmployeePayHistory  FK_EmployeePayHistory_Employee_EmployeeID  PK_Employee_EmployeeID 
Total: 7 table(s)

Referenced Tables

Table Foreign Key Primary Key or Unique Constraint
  Person.Contact  FK_Employee_Contact_ContactID  PK_Contact_ContactID 
  HumanResources.Employee  FK_Employee_Employee_ManagerID  PK_Employee_EmployeeID 
Total: 2 table(s)

Objects that [HumanResources].[Employee] depends on

Object Name Owner Object Type Dep Level
  Flag  dbo  User Defined type 
  Name  dbo  User Defined type 
  NameStyle  dbo  User Defined type 
  Phone  dbo  User Defined type 
  Contact  Person  Table 
Total: 5 object(s)

Objects that depend on [HumanResources].[Employee]

Object Name Owner Object Type Dep Level
  EmployeeAddress  HumanResources  Table 
  EmployeeDepartmentHistory  HumanResources  Table 
  EmployeePayHistory  HumanResources  Table 
  JobCandidate  HumanResources  Table 
  PurchaseOrderHeader  Purchasing  Table 
  SalesPerson  Sales  Table 
  uspGetEmployeeManagers  dbo  Procedure 
  uspGetManagerEmployees  dbo  Procedure 
  uspUpdateEmployeeLogin  HumanResources  Procedure 
  uspUpdateEmployeePersonalInfo  HumanResources  Procedure 
  dEmployee  HumanResources  Trigger 
  vEmployee  HumanResources  View 
  vEmployeeDepartment  HumanResources  View 
  vEmployeeDepartmentHistory  HumanResources  View 
  vJobCandidate  HumanResources  View 
  vJobCandidateEducation  HumanResources  View 
  vJobCandidateEmployment  HumanResources  View 
  vSalesPerson  Sales  View 
  PurchaseOrderDetail  Purchasing  Table 
  SalesOrderHeader  Sales  Table 
  SalesPersonQuotaHistory  Sales  Table 
  SalesTerritoryHistory  Sales  Table 
  Store  Sales  Table 
  uspUpdateEmployeeHireInfo  HumanResources  Procedure 
  uPurchaseOrderHeader  Purchasing  Trigger 
  vSalesPersonSalesByFiscalYears  Sales  View 
  SalesOrderDetail  Sales  Table 
  SalesOrderHeaderSalesReason  Sales  Table 
  StoreContact  Sales  Table 
  iPurchaseOrderDetail  Purchasing  Trigger 
  iStore  Sales  Trigger 
  iuIndividual  Sales  Trigger 
  uPurchaseOrderDetail  Purchasing  Trigger 
  uSalesOrderHeader  Sales  Trigger 
  ufnGetContactInformation  dbo  Function 
  vStoreWithDemographics  Sales  View 
  iduSalesOrderDetail  Sales  Trigger 
Total: 37 object(s)


CREATE TABLE [Employee] (
    [EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
    [NationalIDNumber] [nvarchar] (15) COLLATE Latin1_General_CS_AS NOT NULL ,
    [ContactID] [int] NOT NULL ,
    [LoginID] [nvarchar] (256) COLLATE Latin1_General_CS_AS NOT NULL ,
    [ManagerID] [int] NULL ,
    [Title] [nvarchar] (50) COLLATE Latin1_General_CS_AS NOT NULL ,
    [BirthDate] [datetime] NOT NULL ,
    [MaritalStatus] [nchar] (1) COLLATE Latin1_General_CS_AS NOT NULL ,
    [Gender] [nchar] (1) COLLATE Latin1_General_CS_AS NOT NULL ,
    [HireDate] [datetime] NOT NULL ,
    [SalariedFlag] [Flag] NOT NULL CONSTRAINT [DF_Employee_SalariedFlag] DEFAULT ((1)),
    [VacationHours] [smallint] NOT NULL CONSTRAINT [DF_Employee_VacationHours] DEFAULT ((0)),
    [SickLeaveHours] [smallint] NOT NULL CONSTRAINT [DF_Employee_SickLeaveHours] DEFAULT ((0)),
    [CurrentFlag] [Flag] NOT NULL CONSTRAINT [DF_Employee_CurrentFlag] DEFAULT ((1)),
    [rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Employee_rowguid] DEFAULT (newid()),
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Employee_ModifiedDate] DEFAULT (getdate()),
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_Employee_Contact_ContactID] FOREIGN KEY
    ) REFERENCES [Contact] (
    CONSTRAINT [FK_Employee_Employee_ManagerID] FOREIGN KEY
    ) REFERENCES [Employee] (
    CONSTRAINT [CK_Employee_BirthDate] CHECK ([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate())),
    CONSTRAINT [CK_Employee_Gender] CHECK (upper([Gender])='F' OR upper([Gender])='M'),
    CONSTRAINT [CK_Employee_HireDate] CHECK ([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate())),
    CONSTRAINT [CK_Employee_MaritalStatus] CHECK (upper([MaritalStatus])='S' OR upper([MaritalStatus])='M'),
    CONSTRAINT [CK_Employee_SickLeaveHours] CHECK ([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120)),
    CONSTRAINT [CK_Employee_VacationHours] CHECK ([VacationHours]>=(-40) AND [VacationHours]<=(240))

See Also

List of tables