John Rowan (9/2/2009)

---

1. Yes. The query optimizer will analyze the query, the table/index structure, and the data distribution and determine the most optimal query plan for that query. So you can create indexes, but depending on how your table is laid out and how efficient your queries are, it may or may not choose to use your index.

2. Yes. You can use query hints to force the optimizer to use a specific index. I would caution you on this, however, especially if you have no experience in query/index tuning. The optimizer will pick the best plan for you the vast majority of the time. Index hints should only be used as a last resort and with the full understanding on the potential negative effects. If you have a query that is not performing as you want it to, an index hint is rarely needed. Instead, look at tuning how the SQL code is written and how the tables are laid out.

Put it this way, if you create an index and SQL Server chooses to ignore it, do you presume to be more knowledgable about query execution than the service? Indexing can be a good thing, but the way you write your queries will determine how the query should be executed. Forcing the index is one of those pay now or pay later things.