Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: Check the indexes

    Hello, everyone:

    How to check the index is clustered or non-clustered? Thanks.

    ZYT

  2. #2
    Join Date
    Nov 2002
    Posts
    71
    One way is to check the index id. If it is '1', it's a clustered index. You'll get a list of index id's returned as part of the results from dbcc showcontig. Alternatively, you can see the indexid in sysindexes.

    Clive

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    sp_MSindex @tablename = 'table_name'

    --Like Clive said, it will be number one on the list.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Nov 2002
    Posts
    71
    I've been looking into this stuff recently in relation to designing an efficient index degrag and/or re-index maintenance proc. Of interest is that if a table doesn't have a clustered index, examining SYSINDEXES, you'll see a table name with an index id of 0 and a blank field for 'index name'. However, if the table has a clustered index (only one allowed of course), you'll see the table name, index = 1, followed by the clustered index name...

    table with clustered index

    Table Name indexid index name
    tbl1 1 cluster
    tbl1 2 secondary1
    tbl1 3 secondary2

    table without a clustered index

    Table name indexid index name
    tbl1 0
    tbl1 2 secondary1
    tbl1 3 secondary2

    I wondered if the above rule with regard to index id 1/0 and non-null/null index name was always true? Following on from this, I'm not quite sure what's happening under the covers with regard to a clustered index as it's being presented almost as if it's part of the table rather than a separate index - as is the case with secondary indexes. What's the architecture?

    Clive

Posting Permissions

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