(?) is showing weather it is Department_Name or Department_ID
Now I have unique data into Department table, means, there is no duplicate department name and it will remain unique, now suggest me, Should I use department name instead Department ID, from my point of view, it will decrease the cost of query while extracting from Employee table.
which is better query cost wise
Select e.Name, e.Designation, d.Department_Name from Employee e, Department d (this is if we have department ID instead of department name)
Select e.Name, e.Designation, (Select Department_Name from Department where ID = e.DepartmentId) from Employee
Select Name, Department, Designation from Employee Table (this is if we have Department Name, the department name will remain unique forever)
But studies showings that I should use department ID instead of Department Name, but question arises the cost of the query
Use a department_id as an int to link the two tables. Your indexes will be much smaller and faster to traverse.
Aside from query cost, maintenance will be easier with an integer link. If a dept name ever changed, you only need to change it in one table instead of two or possibly more.
If you are using Mssql 2008 r2 or higher on a properly configured server, the cost of this straight forward query is really immaterial. Set the index up properly and queries returning thousands of records should be less than 1 second.