Query optimization is one art which is difficult to master. Just like any other art this requires creativity and imagination as well understanding of subject matter. Let us look at interesting observation which I came across.
In our example we have one query which is not using any index. On the same table there is already nonclustered index created, which is also not being used. Now when we created clustered index on the same table, our query suddenly started to use nonclustered index which was so far it is not using. The interesting part of this is that query is using nonclustered index when clustered index is created on the same.