Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Unhappy Unanswered: Query regarding index selection

    Can someone tell me if the optimiser will use index density or use magic percentage for the below query

    select * from abc where col_1>@tempvar (i know it uses index density in case of = operator but will it use index desity in case of > operator as well?)

    P.S: I do not have access to run DBCC


  2. #2
    Join Date
    Nov 2002
    It willuse the index density whether it has values. Be sure that your @tempval is not out of range of your histogram

  3. #3
    Join Date
    Jan 2004
    Thanks for your reply
    Actually i was talking about the case where optimiser will not be able to use histogram steps(which i guess should be the case if i am using a variable since the value can't be computed at compile time).

    Here i assume it will not be able to use index density and will have to use inbuilt percentages if the operator is anything else other than =(in case of = it will just have to do a mulipication with total rowcount,while in case of any other operator i do not see a way of optimiser computing the total affected rows).

    Also if i use = operator in the above query how does the optimser know that it is within the range of valid values(assuming it is not able to use histogram steps).It can get total rowcount from OAM page but where does it gets the min and max value(or it does not?)

Posting Permissions

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