Where Statement affects Index?

Question

Where Statement affects Index?

Charmer

SSChampion

Points: 14903
More actions
May 11, 2012 at 6:37 am

#268845

Hi Friends,
I am here to discuss the general topics of index....
One of my colleague says if we add filtering(where statement) , it will affect index. Is that true.? How far this is true?
Give me your suggestion friends...
Thanks,
Charmer

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 1

Do you mean the WHERE clause is in the index creation statement? Or do you mean in the SELECT statement you are using to query?

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

Charmer SSChampion Points: 14903 More actions · Answer 2

Matthew Darwin (5/11/2012)
Do you mean the WHERE clause is in the index creation statement? Or do you mean in the SELECT statement you are using to query?

oh yeah Matthew...i mean in the select statement....while not in index creation

Thanks,
Charmer

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Charmer (5/11/2012)
Hi Friends,
I am here to discuss the general topics of index....
One of my colleague says if we add filtering(where statement) , it will affect index. Is that true.? How far this is true?
Give me your suggestion friends...

A little vague, so you might not get the answers you expect. Using a WHERE clause which utilises an index won't change that index - but the usage of the index will be logged.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Charmer SSChampion Points: 14903 More actions · Answer 4

ChrisM@Work (5/11/2012)
Charmer (5/11/2012)
Hi Friends,
I am here to discuss the general topics of index....
One of my colleague says if we add filtering(where statement) , it will affect index. Is that true.? How far this is true?
Give me your suggestion friends...
A little vague, so you might not get the answers you expect. Using a WHERE clause which utilises an index won't change that index - but the usage of the index will be logged.

i mean in the select statement if we use where condition (Column which is not in the index) , will affect index? in the performance wise?

Thanks,
Charmer

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Charmer (5/11/2012)
ChrisM@Work (5/11/2012)
Charmer (5/11/2012)
Hi Friends,
I am here to discuss the general topics of index....
One of my colleague says if we add filtering(where statement) , it will affect index. Is that true.? How far this is true?
Give me your suggestion friends...
A little vague, so you might not get the answers you expect. Using a WHERE clause which utilises an index won't change that index - but the usage of the index will be logged.
i mean in the select statement if we use where condition (Column which is not in the index) , will affect index? in the performance wise?

If you are using a column in your WHERE clause that is not in an index, you will end up with a table scan (or clustered index scan, which is the same thing).

Simply means it won't use an index to identify target records.

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 6

I think you have it backwards: if you use a WHERE clause in a SELECT statement, having an appropriate index on the column(s) that your WHERE clause is using will result in better performance.

If you have some examples of the code you are trying to use, we will be able to make it clearer through examples.

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

Charmer SSChampion Points: 14903 More actions · Answer 7

Charmer

SSChampion

Points: 14903

May 11, 2012 at 7:13 am

#1486291

Thank you Guys...i am clear now..

Thanks,
Charmer

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Charmer (5/11/2012)
ChrisM@Work (5/11/2012)
Charmer (5/11/2012)
Hi Friends,
I am here to discuss the general topics of index....
One of my colleague says if we add filtering(where statement) , it will affect index. Is that true.? How far this is true?
Give me your suggestion friends...
A little vague, so you might not get the answers you expect. Using a WHERE clause which utilises an index won't change that index - but the usage of the index will be logged.
i mean in the select statement if we use where condition (Column which is not in the index) , will affect index? in the performance wise?

The index itself will not be affected by running the query, whether or not the WHERE clause references a key or include column in the index.

If a column in the WHERE clause matches a key column of an index, it will likely be used. The optimiser may well choose to use an index even if a column in the WHERE clause isn't covered by the index, if the cost (including rid or key lookups) is calculated to be less than the alternative.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Charmer (5/11/2012)
Thank you Guys...i am clear now..

You will be if you can find the time to read this excellent article by David Durant[/url].

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Charmer SSChampion Points: 14903 More actions · Answer 10

ChrisM@Work (5/11/2012)
Charmer (5/11/2012)
Thank you Guys...i am clear now..
You will be if you can find the time to read this excellent article by David Durant[/url].

Thanks Chris..Definitely i would read it...

Thanks,
Charmer