How can you find out if the columns that are part of the index are sorted descending or ascending in that index? For example when you create the following index

CREATE CLUSTERED  INDEX ix_test_clust ON test (id ASC, col1 DESC)

How would you find out without scripting the index if the columns are in descending or ascending order? SQL Server has a function for that, the name of this function is INDEXKEY_PROPERTY

Before starting I need to warn you that this will only run on SQL Server 2005 and above, I have not tested this on SQL Server 2000 but if you need it to run on SQL Server 2000 remove sys. in front of the tables…so instead of sys.sysindexes use sysindexes that should do the trick.

Let’s see how that works. first create this table in the tempdb

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

Now create 2 indexes, one clustered with 2 columns and one non clustered with 4 columns

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

First we need to see what the arguments are for the INDEXKEY_PROPERTY function; here is what you can find in Books On Line

INDEXKEY_PROPERTY ( object_ID ,index_ID ,key_ID ,property )

Arguments

So to use it we need to know a couple of things, we need the table name, the index id and the index key column position.

Run the following query which will give you all that info for the table Test, change the table name if you are interested in other tables.

select OBJECT_NAME(k.id) as TableName,i.name as IndexName,c.name as ColumnName,k.keyno,k.indid as IndexID 
from sys.sysindexes i
join sys.sysindexkeys k on i.id = k.id
and k.indid = i.indid
join sys.syscolumns c on k.id = c.id
and k.colid = c.colid
where OBJECT_NAME(k.id) = 'Test'
order by OBJECT_NAME(k.id),i.name,k.keyno

Here is the output

Now with the output from above can easily call the INDEXKEY_PROPERTY function, we use the IsDescending property to find out the sort order, below are the function calls for the two indexes we created.

--index ix_test 
SELECT INDEXKEY_PROPERTY ( OBJECT_ID('Test') , 2 , 1 , 'isdescending' )
SELECT INDEXKEY_PROPERTY ( OBJECT_ID('Test') , 2 , 2 , 'isdescending' )
SELECT INDEXKEY_PROPERTY ( OBJECT_ID('Test') , 2 , 3 , 'isdescending' )
SELECT INDEXKEY_PROPERTY ( OBJECT_ID('Test') , 2 , 4 , 'isdescending' )

-- index ix_test_clust
SELECT INDEXKEY_PROPERTY ( OBJECT_ID('Test') , 1 , 1 , 'isdescending' )
SELECT INDEXKEY_PROPERTY ( OBJECT_ID('Test') , 1 , 2 , 'isdescending' )

Of course we are not amateurs and nobody wants to type all that stuff, we will just take the query from before and put the following in the select statement _INDEXKEYPROPERTY (k.id,k.indid ,c.colid,‘isdescending’) as IsDescending

Run the code below to see how that works.

select INDEXKEY_PROPERTY (k.id,k.indid ,k.keyno,'isdescending') as IsDescending,OBJECT_NAME(k.id) as TableName,i.name as IndexName,c.name as ColumnName,c.colid,k.indid as IndexID 
from sys.sysindexes i
join sys.sysindexkeys k on i.id = k.id
and k.indid = i.indid
join sys.syscolumns c on k.id = c.id
and k.colid = c.colid
where OBJECT_NAME(k.id) = 'Test'
order by OBJECT_NAME(k.id),i.name,k.keyno

Here is the output

Of course if you can do that you can also very simply write a query that returns all the columns that are sorted descending in any index by making the WHERE clause the following: where INDEXKEY_PROPERTY (k.id,k.indid ,k.keyno,‘isdescending’) = 1. Run the query below to see how that works

select INDEXKEY_PROPERTY (k.id,k.indid ,k.keyno,'isdescending') as IsDescending,OBJECT_NAME(k.id) as TableName,i.name as IndexName,c.name as ColumnName,k.keyno,k.indid as IndexID 
from sys.sysindexes i
join sys.sysindexkeys k on i.id = k.id
and k.indid = i.indid
join sys.syscolumns c on k.id = c.id
and k.colid = c.colid
where INDEXKEY_PROPERTY (k.id,k.indid ,k.keyno,'isdescending') = 1

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