HumanResources.Employee Table

Information

Name	Employee
Schema	HumanResources
Row Count	0
Data Size
Index Size
Reserved Size
Unused Size
Created	29.3.2010. 21:58:18
Modified	29.3.2010. 21:58:56

Extended Properties

Name	Type	Property Name	Value
Employee	TABLE	MS_Description	Employee information such as salary, department, and title.

Columns

Name	Data Type	Collation	References	Default
EmployeeID	int
NationalIDNumber	nvarchar(15)	SQL_Latin1_General_CP1_CI_AS
ContactID	int		Person.Contact.ContactID
LoginID	nvarchar(256)	SQL_Latin1_General_CP1_CI_AS
ManagerID	int		HumanResources.Employee.EmployeeID
Title	nvarchar(50)	SQL_Latin1_General_CP1_CI_AS
BirthDate	datetime
MaritalStatus	nchar(1)	SQL_Latin1_General_CP1_CI_AS
Gender	nchar(1)	SQL_Latin1_General_CP1_CI_AS
HireDate	datetime
SalariedFlag	bit			((1))
VacationHours	smallint			((0))
SickLeaveHours	smallint			((0))
CurrentFlag	bit			((1))
rowguid	uniqueidentifier			(newid())
ModifiedDate	datetime			(getdate())

Indexes

Name	Type	Columns
PK_Employee_EmployeeID	Clustered	EmployeeID
AK_Employee_LoginID	NonClustered	LoginID
AK_Employee_NationalIDNumber	NonClustered	NationalIDNumber
AK_Employee_rowguid	NonClustered	rowguid
IX_Employee_ManagerID	NonClustered	ManagerID

Check Constraints

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

Foreign Key Constraints

Name	Type	Referenced Table
FK_Employee_Contact_ContactID	Foreign key	Person.Contact
FK_Employee_Employee_ManagerID	Foreign key	HumanResources.Employee

Triggers

Name	Activates on	Instead Of	For Replication	Enabled	Created	Modified
dEmployee	DELETE				29.3.2010. 21:58:56	29.3.2010. 21:58:56

SQL Script

CREATE TABLE [HumanResources].[Employee] (
    [EmployeeID] int NOT NULL IDENTITY,
    [NationalIDNumber] nvarchar(15) NOT NULL,
    [ContactID] int NOT NULL,
    [LoginID] nvarchar(256) NOT NULL,
    [ManagerID] int NULL,
    [Title] nvarchar(50) NOT NULL,
    [BirthDate] datetime NOT NULL,
    [MaritalStatus] nchar(1) NOT NULL,
    [Gender] nchar(1) NOT NULL,
    [HireDate] datetime NOT NULL,
    [SalariedFlag] bit NOT NULL DEFAULT ((1)),
    [VacationHours] smallint NOT NULL DEFAULT ((0)),
    [SickLeaveHours] smallint NOT NULL DEFAULT ((0)),
    [CurrentFlag] bit NOT NULL DEFAULT ((1)),
    [rowguid] uniqueidentifier NOT NULL DEFAULT (newid()),
    [ModifiedDate] datetime NOT NULL DEFAULT (getdate())
);
CREATE UNIQUE INDEX [AK_Employee_LoginID] on [HumanResources].[Employee]([LoginID]);
CREATE UNIQUE INDEX [AK_Employee_NationalIDNumber] on [HumanResources].[Employee]([NationalIDNumber]);
CREATE UNIQUE INDEX [AK_Employee_rowguid] on [HumanResources].[Employee]([rowguid]);
CREATE INDEX [IX_Employee_ManagerID] on [HumanResources].[Employee]([ManagerID]);