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
| object_ID |
| index_ID |
| key_ID |
| property |
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
| TableName |
| Test |
| Test |
| Test |
| Test |
| Test |
| Test |
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 INDEXKEY_PROPERTY (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
| IsDescending |
| 1 |
| 1 |
| 1 |
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
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.
