max() min() Query Plan

Question

Post reply

max() min() Query Plan

vlad-548036

Hall of Fame

Points: 3511
More actions
April 27, 2009 at 2:37 pm

#130294

I was hoping for an Index Seek, alas it's a Scan, but Why ??
create table Tbl1 (i int not null primary key);
insert Tbl1(i) values (1),(2),(3),(4),(5),(6);
set statistics io on;
-- 1st try:
select max(i) from Tbl1;
-- Table 'Tbl1'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0,
-- lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- 2nd try:
select top 1 i from Tbl1 order by i desc;
-- Table 'Tbl1'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0,
-- lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--drop table Tbl1;
Point me in the right direction ➡ please.
_{Thanks in advance.}

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

vlad (4/27/2009)
I was hoping for an Index Seek, alas it's a Scan, but Why ??

Because a seek requires a predicate, something to seek on, and neither of your queries has one.

That said, even though it's a scan operator, it's actually not reading the entire table. It's easy to see if you add a lot more rows

insert into Tbl1

SELECT top 1000000 ROW_NUMBER() over (order by a.column_id)

from sys.columns a cross join sys.columns b

With 1000000 rows, that table now has 447 pages in it, however....

select max(i) from Tbl1;

-- Table 'Tbl1'. Scan count 1, logical reads 2, physical reads 0

select top 1 * from Tbl1 order by i desc;

-- Table 'Tbl1'. Scan count 1, logical reads 2, physical reads 0

If you look at the exec plans, estimated rows 1, actual rows 1.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

You can' get a seek without any criteria (Where clause). It has nothing to seek on, it has to scan each row.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

vlad-548036 Hall of Fame Points: 3511 More actions · Answer 3

Thank you !!!

Yes it does run fast regardless of the table size --

I thought it was something simple just could not quite get it.

🙂