Quote:
quote:Originally posted by rstelma
So, use your correctly constructed queries to build your indexes.
|
Therein lies the rub. It isn't always that simple to correctly construct a query.
Just to contrive an example, let's say you have a table of Person rows with a DateOfBirth column with an index on that column and you want to select the rows where the person is older than 21. (I know the following isn't really a correct age calculation - I said this was contrived :))
A WHERE clause such as:
WHERE DATEDIFF(yy, DateOfBirth, GETDATE()) > 21
will not use the index, whereas:
WHERE DateOfBirth < DATEADD(yy, -21, GETDATE())
will.
So, even though both forms will be "correct", in that they return the same resultsets, only the second is able to make use of an index. If you think you have a performance problem with a query, you really need to examine the query plan to see what's going on, and define indexes so as to influence the plan favorably. If you create an index, and the plan doesn't change, then you need to understand why. You don't want to create indexes just "because" - there should be a definitive reason why you do, i.e. the query processor actually makes use of the index...
Jeff Mason
[email protected]
