For example let's say I have to create a table called Employee with columns EmployeeID, Name, DOB, Address, DepartmentID[FK].

So I create the table as follows with a primary key and a nonclustered index:

Table Name: Employee


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.

  • 3
    Ugh. Why are you embedding Male/Female into the employee ID? What do you gain from that that you don't get from having a column to indicate gender? Why don't you embed their birthday and SSN into their employee ID too? – Aaron Bertrand Nov 16 '14 at 14:24
  • If an employee has gender reassignment surgery will you update their ID? – Martin Smith Nov 16 '14 at 17:10
  • @Aaron Bertrand, I should have mentioned that this is just a hypothetical scenario. I just wanted to show something to get your advices solely with regard to table design issues which may leads to performance issues and NOT about best practices sort of things. Hope you would understand. ;) – CAD Nov 16 '14 at 18:05
  • 3
    You should never mix different types of information into a single column, not even "hypothetically". Especially your third query will benefit from properly putting the gender into its own column (you can do an equality on the gender column rather than needing a LIKE query on the EmployeeID column) – a_horse_with_no_name Nov 16 '14 at 18:35
  • 2
    Why have a separate employee ID and primary key? Is it possible for 1 person to have more than 1 id? If this is a new system personally I'd just use an int, and just make it a number without any other meaning. Performance is relative. How fast is fast enough? How many records do you anticipate? What kind of transactions would this be used for and what sort of volumes do you expect? – Sir Swears-a-lot Nov 17 '14 at 7:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.