April 11, 2015 at 11:46 pm
hi,
"So, what does that mean for index columns order? Quite simply, if queries are always going to filter with one or more equality predicates and one or more inequality predicates, the columns used for the inequalities must appear further to the right in the index than the equalities."
does above case also appliyes to following situation.
select * from emp where id >0 and status=1
like i have one status col which is 99 % 1
and i have one id col which sometimes stores 0 sometimes it stores numbers greater than 0
please suggest ,I think in this case index should have cols in following order "Id,Status"
yours sincerley
April 12, 2015 at 12:49 am
rajemessage 14195 (4/11/2015)
hi,"So, what does that mean for index columns order? Quite simply, if queries are always going to filter with one or more equality predicates and one or more inequality predicates, the columns used for the inequalities must appear further to the right in the index than the equalities."
does above case also appliyes to following situation.
select * from emp where id >0 and status=1
like i have one status col which is 99 % 1
and i have one id col which sometimes stores 0 sometimes it stores numbers greater than 0
please suggest ,I think in this case index should have cols in following order "Id,Status"
yours sincerley
Normally I would suggest placing the equality column first and the inequality column second but it depends on the cardinality and the distribution. Preferably include other columns to make it a covering index and further add a filter to the index on those predicates.
😎
April 13, 2015 at 2:34 am
No, you want the equality first and inequality second and it does not depend on cardinality and distribution of the data.
If you put the equality first and the inequality second, then SQL can do a single seek on *both* columns to the start (or end) of the inequality range where Status = 1 AND id > 0 and read just the rows that it needs. Do it the other way around and sQL can only seek to the beginning (or end) of the inequality range (ID>0) and has to read and discard rows that don't match the equality as it goes. The second option will result in SQL reading more of the table than it needs to.
But since you quoted part of my blog post, you know my reasons and you've seen my examples.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2015 at 11:31 am
GilaMonster (4/13/2015)
No, you want the equality first and inequality second and it does not depend on cardinality and distribution of the data.
Of course Gail is right, I should re-phrase my comment
Normally I would suggest placing the equality column first and the inequality column second but the application of the data, i.e. if equality has minimal selectivity then skip it.
😎
PS. Thanks Gail.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply