Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    19

    Unanswered: Non clustered index performance

    Is retrieving rows using a non clustered index in a table with another index that is clustered, slower than retrieveing rows from a table with no clustered indexes what so ever?

    If I understand things correctly, when you look up a non clustered index in a table with clustered indexes, SQL Server finds the cluster key, so that it can use the clustered index to find the data. So, it seems that retrieval by non clustered indexes would be faster when there are no clustered indexes at all.

    (To clarify: Of course using clustered indexes to retrieve data is faster, but that's not the question here.)

    [edit in italics]
    Last edited by Anna-J; 09-23-10 at 11:02.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't think your understanding is correct; some of what you say above implies you have some things wrong and much of it I am afraid does not make much sense to me.

    However, to answer the question: Yes, SQL Server typically needs to read less pages to retrieve the data from a heap (a table without a clustered index) than from a clustered index when using a non-clustered index.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2010
    Posts
    19
    I've tried to edit the question, still not sure it's clear or correct though.

    I thought it was something like
    Look for "foo" in non clustered index -> found a pointer to the page where the data is -> load the page and get the data
    for heaps, compared to
    Look for "foo" in non clustered index -> found the cluster key "bar" -> look for "bar" in clustered index -> found the data
    for tables w clustered indexes.

    Anyway, you're answer is what I was looking for, thx!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your explanation in your last post is pretty good.
    Note that there are a lot more considerations than just this when it comes to comparing clustered indexes and heaps - it would not be right to say that heaps are superior to clustered indexes. It's only in particular circumstances that you would prefer a heap.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •