Jan 11, 2008

Index /Clustered Index SCAN When Top 1 Is Used

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.

2 comments:

Tharindu Dhaneenja said...

Hi preethi,
Recently I read your article and it's very helpful. After read this I have small doubt, I have executed following SQL Queries,

--TOP
SELECT TOP 1 ([col name])
FROM [Table Name]
WHERE [col name] < 2147483647
ORDER BY [col name] DESC

--MAX
SELECT MAX([col name])
FROM [Table Name]
WHERE [col name] < 2147483647

After comparing above SQL Queries which one is your recommendation?

Thanks
Tharindu Dhaneenja

Preethiviraj Kulasingham said...

I have written another article on Scan and Seek later: "Understanding Scans and Seeks" at sql-server-performance.com. Please read it too.
Actually speaking, with SQL Server 2005/2008, it does not matter. It is only a short operation if you have index on [col name]. Otherwise, you need a scan on the table anyway.

Post a Comment