Indexing Columns

If you have a table with 3 columns,

ID (Primary Key)

Col1

Col2

And you have to perform the following query frequently

Code Snippet

Select ID where Col1='SomeValue' and Col2='SomeOtherValue'

Is it a bad idea to define a non clustered index on "Col1, Col2, ID" or am I better off just having the default indexing on the the primary key "ID"

I have never had to define an index that included all the columns in a table before so I am not sure if this is a bad idea

If you are using SQL 2005, and this is a frequent or common query, you may wish to explore using the new 'INCLUDE' option.

You could create an INDEX on Col1, Col2, and include [ID].

Something like this:

CREATE NONCLUSTERED INDEX ix_MyTable_Col1Col2
ON MySchema.MyTable( Col1, Col2 )
INCLUDE ( [ID] );

This is a 'covered' index. The entire query is satisfied by the index.

|||Thanks Arnie.... I have to support both SQL 2005 and SQL 2000 for this application at the moment.

|||

For SQL 2000, if you use this query frequently, index all three columns.

|||

you can not define ID as non clustered since it is a PK.

ID can be clustered index and check the unique checkbox.

you could define col1 and col2 as non clustered and ID as included column but beware of space used by index.

|||

You should create clustered primary indexes based off the 80/20 rule. If you are accessing this table 80% of the time by col1 and col2, then create a clustered primary index over col1, col2, ID. Creating an index over just the ID column will almost always cause bookup lookups. Create primary key indexes based off of usage, not how fast can I load data.

|||Chances are that your ID column is part of the automatically created clustered index since it's the primary key.

If that's the case, remember that all columns in the clustered index are appended to all non-clustered indexes for that table.

So there is no reason to add ID to your non-clustered index since it will be there already.

I think that SQL Server is smart enough to just ignore the ID column in the index definition since it knows that it's part of the clustered index, but I'm not sure on that one.

Having all three columns part of an index (ID in the clustered, and Col1 and Col2 in the non-clustered) creates, as somebody else mentioned, a "covering" index.

Basically, a covering index is an index which includes all references columns in your query (from the SELECT, JOIN, and WHERE clauses) so that no bookmark loops are necessary to return all the data. This data can come entirely from indexes... which is much faster than having to go read additional data pages to snag the original row from the table.

|||<P align=left><FONT face=Arial size=2>Hi,</FONT></P>
<P align=left>&nbsp;</P>
<P align=left>&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;We could arrive at a decision of using the index on&nbsp;columns based on the recommendation of the SQL Profiler utility. The input for the profiler would be a database&nbsp;trace file. This trace file will capture the usage of the table by the users and using this profiler will decide whether to use index. Also in this scenario, the table has only 3 columns and all the three columns are accessed by the user frequently. So the choice would be going for the covering index where all the three columns will be covered under index.</P>
<P align=left>&nbsp;</P>
<P align=left>Thanks.</P>