Index question 2

[djj55]

Hello, SQL 2008R2
If I have a query that has a where clause like WHERE Column1 = 'hello' AND Column2 = '9/16/2012'.
I already have an index on Column1 but was thinking I needed an index on Column2. So, I have several ways to go, but I do not know which would be the best. Here are my ideas:

CREATE INDEX IX_Column2 ON Mytable(Column2);
CREATE INDEX IX_Column1_Column2 ON Mytable(Column1, Column2);
CREATE INDEX IX_Column1_Column2 ON Mytable(Column1) INCLUDE (Column2);

The last one I do not think would be the best, but might help.

Thus my question is which would help the most. Queries again only Column2 might be done.

Thanks

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[SQLScholar]

Middle one should help best.

Top one is missing one of the where elements. Bottom one returns more data from the index, but the second field is not actually part of the index.

Best thing to always do - try it! Do some before and after times.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[djj55]

I did not know how already having an index on Column1 would play into the equation.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[SQLScholar]

Sorry i didnt read the bit that said additonal.

The first one then would help, but if it decided to use the index (which is sometimes a little black magic from the execution plan) then I think then two indexes would be used and matched. However 2 would be a full would be a covering index.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[gmmastros]

It would only be a covering index based on the select clause.

Before determining the best index to create, we would need to know a little more about your data. Specifically, how many unique values are there in Column1 and how many unique values are there in column2. Whichever column has the most unique values should the the first column listed in the index.

The following 2 indexes are NOT the same:
[tt]
CREATE INDEX IX_Column1_Column2 ON Mytable([red]Column1[/red], [blue]Column2[/blue]);
CREATE INDEX IX_Column1_Column2 ON Mytable([blue]Column2[/blue], [red]Column1[red]);
[/tt]

Think about a phone book. The data in a phone book is sorted first by last name, then by first name. This is the best way to do it because there are many more variations of last name (there are more unique values). There are less variations in first name. If the phone book was sorted by first name and then last name, it would likely take longer to find the phone number you are looking for. For example, a phone book may only have a dozen entries for Gates, but my have thousands for Bill. Looking for Bill gates in the phone book would take longer.

Indexes in SQL are extremely similar to this situation. Bottom line, whichever column has the most unique values should be listed first in the index.

As far as covering indexes, I encourage you to read this blog I wrote:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-covering-indexes

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thank you to both. In my case Column1 defenently has more unique values as Column2 is the date of an action.

Does the order of criteria in the WHERE clause matter? (Based on index)

WHERE Column1 = 'Hello'
    AND Column2 = '9/16/2012'
-- versus
WHERE Column2 = '9/16/2012'
    AND Column1 = 'Hello'

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

No. The order of the where clause does not matter.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thanks again,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!