Post reply

Need help in creating useful index

  • August 3, 2010 at 3:56 am

    #224964

    Hi Friends,

    Need your help.

    I purchased one postal code database table which has following schema:

    Table has approx. 10,00,000 records and does not have any index (clustered/unclustered). I have one simple query and would like to optimize by creating index on the table.

    Select Country,ZIP,MAX(Lat) as Lat,MAX(Lng) as Lng

    From GeoPC

    Group By Country,ZIP

    It performs index scan on the whole table as shown in attached exec plan. Could you please help me creating useful index on this table so that above query will not do index scan ?

  • August 3, 2010 at 4:05 am

    #1201422

    An index scan is the best that query can do. You're asking for the entire table aggregated. Hence it has to scan something. Better a nonclustered index than the cluster.

    You can only get an index seek when you're filtering on something (join or where clause). This is an unfiltered select with an aggregation.

    The execution plan you have is just about the best you'll get for this query.

    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

  • August 3, 2010 at 4:11 am

    #1201425

    Thanks for your help Gail !!!

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

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