Table: HumanResources.Employee

Properties

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. 

Columns

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

Identity column

Name Seed Increment Not for replication
  EmployeeID  1  1   

Indexes

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)

Triggers

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  1 
  Name  dbo  User Defined type  1 
  NameStyle  dbo  User Defined type  1 
  Phone  dbo  User Defined type  1 
  Contact  Person  Table  2 
Total: 5 object(s)

Objects that depend on [HumanResources].[Employee]

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

SQL

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()),
    CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY  CLUSTERED
    (
        [EmployeeID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_Employee_Contact_ContactID] FOREIGN KEY
    (
        [ContactID]
    ) REFERENCES [Contact] (
        [ContactID]
    ),
    CONSTRAINT [FK_Employee_Employee_ManagerID] FOREIGN KEY
    (
        [ManagerID]
    ) REFERENCES [Employee] (
        [EmployeeID]
    ),
    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))
) ON [PRIMARY]
GO


See Also

List of tables