Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Tools

Authors

Main Categories

Search

XML Feeds

RSS 2.0: Posts, Comments
Atom: Posts, Comments

What is RSS?

Google Ads

« To SA or not to SA

Calculating The Percentage Of Null Values In A Column With Transact SQL »

comments

T-SQL To find Out If An Index Is Clustered Or Non Clustered

by SQLDenis on Oct 06, 2009 in categories Data Modelling & Design, Database Programming, Microsoft SQL Server Admin, Microsoft SQL Server

Instapaper

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

tsql

Line number Off | Hide | Select all

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')

tsql

Line number Off | Hide | Select all

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

tsql

Line number Off | Hide | Select all

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

tsql

Line number Off | Hide | Select all

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

About the Author

Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.

Social Sitings