For example let's say I have to create a table called
Employee with columns
So I create the table as follows with a primary key and a nonclustered index:
ID [int] (Auto increment column) - This is the primary key of the table. EmployeeID [char(10)] This is the non-clustered index. DOB [Date] This column is added as a INCLUDED column to the non clustered index. Name [varchar(100)] Address [Varchar(max)] DepartmentID [Char(10)]
I have created a clustered index with
EmployeeID column and
DOB has been included to the index considering possible query patterns like...
- Show Name and DOB of all employees who has born after 1986
- Show all details of employee holding the EmployeeID ='M123456789'
- Show all details of all employees whose EmployeeID starts with 'M' and have born after year '1986'
Assuming this table may have large no of rows, could you let me know whether the above design would be optimum or should I do any changes?
Is it better to make the column
EmployeeID the primary key without adding an
ID column? (Performance wise and from application perspective)
NOTE: Please note that this is a hypothetical scenario only. I'm not going to create a database in this way. I just expecting your expert advices for my questions.