Re: max(keyfield)

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: jxstern (jxstern_at_nowhere.com)
Date: 11/15/04 

Date: Mon, 15 Nov 2004 14:38:25 -0800

On Mon, 15 Nov 2004 15:18:06 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@dnartreb.noraa> wrote:
>> So a seek is better than a scan, anyway, and it can do this even for a
>> clustered index because the top index page has a last row. I just
>> wondered if it might do even better and cache the high value,
>
>No, I don't think individual column values can be cached the way you are
>describing.

Just the high and low.

>However, for your education, wouldn't it be relatively simple to set up a
>simple test?

Well, yeah, I guess, but sometimes it feels more productive to ask
than to do the reverse engineering, and some happy lurker might
benefit thereby.

>CREATE TABLE Kerplunk
>(
> foo INT,
> bar INT,
> raboof INT
>)
>GO
>
>CREATE CLUSTERED INDEX f ON Kerplunk(foo)
>CREATE INDEX f ON Kerplunk(foo)

on (bar), I suppose you meant.

>GO
>
>SET NOCOUNT ON
>-- <<< populate data here! >>>
>-- hit Ctrl+K to see execution plan
>-- it will show where scans/seeks are used...
>
>SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
>
>SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
>
>SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM Kerplunk)
>GO
>
>DROP TABLE Kerplunk
>GO

Relevant Pages

  • Re: Range query optimization help?
    ... (src, amp, asof) ... >unique clustered index asof_src on MeterEvent ... >table StdAmp, minamp int, maxamp int, stdval int) ...
    (microsoft.public.sqlserver.programming)
  • Inefficient query looping over cursor
    ... ID int (Primary key, clustered index) ... AMOUNT_PAID smallint ...
    (microsoft.public.sqlserver.programming)
  • (design question) non-unique clustered index confuses optimizer
    ... I store a large amount of hierarchal BOM data using a nested set table similar ... increase performance, I added JID as a non-unique clustered index, since it ... except the optimizer treats queries differently depending ...
    (microsoft.public.sqlserver.programming)
  • Re: CLUSTERD INDEXES
    ... -- This query includes a sort operator, ... -- with a clustered index scan. ... or the complete reverse (ASC to DESC). ... >the index are int he same order? ...
    (microsoft.public.sqlserver.programming)
  • Re: max(keyfield)
    ... CREATE TABLE Kerplunk ... foo INT, ... bar INT, ... CREATE CLUSTERED INDEX f ON Kerplunk ...
    (microsoft.public.sqlserver.server)