One of my recent articles is published at SQL Server Worldwide User Group.
Even a simple statement which SELECTs the last (or first) row of a table may cause clustered index scan or index scan. But there are ways to make it a seek.
Interested?? Have a look at http://www.sswug.org/see/37061
The site may request you to be a member to read the article. But eventhough you may have to give your credit card details, You can have a free trial membership.
If you dont like the site, you can cancel the membership without paying any money.