Check the indexes

Hello, everyone:
How to check the index is clustered or non-clustered? Thanks.
ZYTOne 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|||sp_MSindex @.tablename = 'table_name'

--Like Clive said, it will be number one on the list.|||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