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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
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
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’)

T-SQL
1
2
3
SELECT 'ix_test',INDEXPROPERTY(OBJECT_ID('Test'), 'ix_test','IsClustered')
union all
SELECT 'ix_test_clust',INDEXPROPERTY(OBJECT_ID('Test'), 'ix_test_clust', '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

T-SQL
1
2
3
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')
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

T-SQL
1
2
3
SELECT name,INDEXPROPERTY(id, name,'IsClustered')
from sysindexes 
where name in( 'ix_test', 'ix_test_clust')
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