which is more speed cluster index or noncluster index and why ?

  • which is more speed cluster index or noncluster index and why ?

    I make cluster index on temp table and noncluster index on same temp table

    I notice that cluster index is more speed from noncluster index

    are this correct and why ?

  • No.  It is not "correct".  The correct answer is "It Depends".

    It depends both on the data in the table, they key column(s) you've selected for the Clustered and Non-Clustered indexes, how wide the leaf level of the Clustered Index is (which is actually all of the data in the table), whether or not the non-Clustered index "covers" the whatever query you wrote, and what the query itself is, just to mention a couple of the things that can make huge differences.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • If all the columns you require are not contained in the non-clustered index then the database would have to do a seek and a key-lookup or a full table scan (a clustered index scan) to get the data it needs on the row from the table. This would be slower than a seek on the clustered index.

    If you have a non-clustered index that includes all the columns your query needs in either the indexed columns or the included columns then this should be the same or faster than using a clustered index. This will be even more noticeable if the table is very wide, i.e. a row uses a lot of data on the page.

  • And then you toss in aggregations and columnstore gets faster. Or, an XML index can speed up Xquery. Spatial indexes can speed up spatial queries.

    There isn't a single "This index type is the fastest" because each of the different types of indexes serves a different need. Look at your execution plans, reads, writes, and overall performance. Don't rely on a single measure and then decide that single measure applies in all cases.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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