Post reply

what is faster NonClustered on Clustered Index or NON Clustered Index only?

  • March 4, 2004 at 2:09 am

    #107223

    I want to ask .. what if the table has NO clustered index and all the indexes are NON clustered ... would this be faster than using NonClustered on Clustered Index ??

    and take in your mind the Case of getting field that has no index on it .. so you have to get the data from the physical Pages.

    by the way, Access 2002 [XP] upsizing wizard uses only NonClustered Index when it trafser any Tables to SQL Server

    thanks for any one who reply or suggest an idea


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • March 4, 2004 at 7:53 am

    #497291

    I didn't see your post before I posted my question.  See "Efficiency of non-clustered index in Heap Tables."  We can both find out what the answer is.

    Yours,

    Scott Rankin
    CoolSavings, Inc
    Database Administrator (DBA)

    Chicago, IL 60601

    http://www.coolsavings.com

  • March 5, 2004 at 2:32 am

    #497490

    Depending on the structure and usage of your table, a table without a clustered index is a heap, which is fine if rows are added sequentially and no deferred updates or deletes happen. What you can't do without a clustered index is remove the leaf level fragmentation in the table which can cause you problems.  I'd personally advise against leaving out clustered indexes - I have a couple of applications that make minimal use of clustered indexes, part of my investigations have shown a decrease in i/o when a table has a clustered index, sometimes even if this index is not being used,  in multiple table joins. ( in some instances the number of table scans within the join dropped dramatically with the inclusion of clustered indexes )

    The question of speed is relative, you may well find a simple select on a non-clustered index to be quick, but it will always use more i/o than the same query on a clustered index - but in the overall scheme of things considering the whole database and it's operations not having clustered indexes is more likley to cause degraded performance.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • March 6, 2004 at 6:34 am

    #497700

    I agree with Colin, best way to test your performance is to use the command "set statistics IO on" and watch your logical IO

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

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

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