Post reply

MAX() Behavior on Indexed Columns

  • January 11, 2011 at 5:27 am

    #229931

    I want to investigate the following query

    SELECT MAX(CONTRACT_ID) FROM CONTRACT

    with execution plan as

    SELECT MAX(CONTRACT_ID) FROM CONTRACT

    |--Stream Aggregate(DEFINE: ([Expr1003]=MAX([DB].[dbo].[CONTRACT].[contract_id])))

    |--Top(TOP EXPRESSION: ((1)))

    |--Clustered Index Scan(OBJECT: ([DB].[dbo].[CONTRACT].[XPKCONTRACT]), ORDERED BACKWARD)

    depicting a TOP clause usage!

    my question is...

    That the said query did not table scan but... may just picked up the max with by TOP row?

    Please guide!

  • January 11, 2011 at 5:41 am

    #1271821

    Thats exactly whats happened , if you look at the graphical execution plan you should also see "Actual number of rows = 1" on the "Scan"


    Clear Sky SQL
    My Blog[/url]

  • January 11, 2011 at 5:56 am

    #1271828

    Was discussed here: http://www.sqlservercentral.com/Forums/FindPost1044266.aspx and in next few replies

    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

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

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