Results 1 to 2 of 2

Thread: Query cost

  1. #1
    Join Date
    Mar 2013
    Posts
    1

    Lightbulb Unanswered: Query cost

    I have to two tables

    Employee
    Name
    Department(?)
    Designation

    Department
    ID
    Department_Name

    (?) 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.
    like
    which is better query cost wise

    Option 1
    Select e.Name, e.Designation, d.Department_Name from Employee e, Department d (this is if we have department ID instead of department name)
    or
    Select e.Name, e.Designation, (Select Department_Name from Department where ID = e.DepartmentId) from Employee

    Option 2
    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

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    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.
    Last edited by LinksUp; 03-19-13 at 07:02.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •