Professional Web Applications Themes

clustered index does not appear to be sorted - Microsoft SQL / MS SQL Server

I must have misunderstood what a clustered index does or how it is displayed. I have created a clustered index on a table using ... 'CREATE CLUSTERED INDEX transID ON #tmpTransactions (learningActivityTranscriptID ASC)' However when I do a 'select * from #tmpTransactions', the field does not appear sorted. The values will be sorted for a while (probably just do the their natural order in the table) and then they will break the sort order, i.e., 57126, 57127, 56705 and then 56706. It was my understanding that a clustered index physically ordered the table. Did I misunderstand this or did I ...

  1. #1

    Default clustered index does not appear to be sorted

    I must have misunderstood what a clustered index does or how it is
    displayed. I have created a clustered index on a table using ...

    'CREATE CLUSTERED INDEX transID ON #tmpTransactions
    (learningActivityTranscriptID ASC)'

    However when I do a 'select * from #tmpTransactions', the field does not
    appear sorted. The values will be sorted for a while (probably just do
    the their natural order in the table) and then they will break the sort
    order, i.e.,
    57126, 57127, 56705 and then 56706.

    It was my understanding that a clustered index physically ordered the
    table. Did I misunderstand this or did I do something wrong?

    Thanks for the help.

    Robert

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Robert Guest

  2. #2

    Default Re: clustered index does not appear to be sorted

    Hi Robert

    A clustered index will sort the data so that it is virtually sorted at the
    data level. The pages may not be phyiscally in sorted order on the disk
    itself, because that would require too much maintenance work when a new page
    needed to be linked in. However, the linked list of data pages will be in
    order.

    However, just having a clustered index is NEVER a guarantee of any
    particular order when the table data is returned. You must include ORDER BY
    in your query if you want it in any particular order. If there is a
    clustered index on the table, and you ORDER BY the clustered index key, SQL
    Server will be able to just follow the pointers to get the data in sorted
    order, and will avoid all the overhead of sorting. But if you don't use
    ORDER BY, SQL Server may retrieve the data whatever order it deems is most
    efficient, which may end up being physical order on the disk, or it may be
    the order of the clustered index keys, or it may be something else.

    --
    HTH
    ----------------
    Kalen Delaney
    SQL Server MVP
    www.SolidQualityLearning.com


    "Robert Taylor" <com> wrote in message
    news:phx.gbl... 


    Kalen Guest

  3. #3

    Default Re: clustered index does not appear to be sorted

    Thank YOU, Robert. It's always nice to know that what I've written has been
    useful.

    --
    HTH
    ----------------
    Kalen Delaney
    SQL Server MVP
    www.SolidQualityLearning.com


    "Robert Taylor" <com> wrote in message
    news:phx.gbl... 


    Kalen Guest

Similar Threads

  1. Clustered index on GUID
    By Miroo_news in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 23rd, 06:41 AM
  2. Convert index to clustered
    By Anith Sen in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 11th, 04:57 PM
  3. Correct DataTable index from sorted DataView...
    By Nathan Baulch in forum ASP.NET General
    Replies: 0
    Last Post: June 26th, 12:16 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139