I saw this question in in Google Analytics from a Google search that hit our site.

So, how can you determice if an index is clustered or not? There are two ways, you can use either the INDEXPROPERTY function or the sysindexes/sys.sysindexes system table/view

To see how it works we will create a table with one clustered and one non clustered index on it

Here is the code for that

USE tempdb
go
 
CREATE TABLE Test (id INT, col1 VARCHAR(40), col2 INT, col3 INT)
go


CREATE NONCLUSTERED  INDEX ix_test ON test (id ASC, col1 DESC,col2 DESC, col3 ASC)
go
 
 
CREATE CLUSTERED  INDEX ix_test_clust ON test (id ASC, col1 DESC)
go

INDEXPROPERTY

To use INDEXPROPERTY you need to know the table ID, the name of the index and use IsClustered for the property. To get the table id, you use the OBJECT_ID function with the table name passed in

So for the index ix_test on table Test we will use INDEXPROPERTY(OBJECT_ID(‘Test’), ‘ix_test’,‘IsClustered’)

SELECT 'ix_test',INDEXPROPERTY(OBJECT_ID('Test'), 'ix_test','IsClustered')
union all
SELECT 'ix_test_clust',INDEXPROPERTY(OBJECT_ID('Test'), 'ix_test_clust', 'IsClustered')

Output
-------------------
ix_test     0
ix_test_clust   1

SYSINDEXES

indid holds the information needed to determine if the index is clustered or not

This info is for SQL Server 2000

_1 = Clustered index

1 = Nonclustered

255 = Entry for tables that have text or image data_

This info is for SQL Server 2005 and up

_0 = Heap

1 = Clustered index

1 = Nonclustered index_

select name,case indid when 1 then 'Clustered' else 'Non Clustered' end as TypeOfIndex
from sysindexes --or sys.sysindexes on sql 2005 and up
where name in( 'ix_test', 'ix_test_clust')

Output
-----------------------
name    TypeOfIndex
ix_test_clust   Clustered
ix_test Non Clustered

And of course you can combine the two methods

SELECT name,INDEXPROPERTY(id, name,'IsClustered')
from sysindexes 
where name in( 'ix_test', 'ix_test_clust')

Output
-------------
ix_test_clust   1
ix_test     0

As you can see it is pretty easy to determine if an index is a clustered index or a non clustered index. I prefer to use INDEXPROPERTY instead of SYSINDEXES, what about you?

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum