Need help regarding index keys for MS7 and MS-2K

[udayt]

Hi,

My application needs to calculate the sort order of an index key
(whether the index key is descending or ascending). The user may
connect to MS7 or MS2K servers.

As far as I know, the descending indices are supported in version 8
i.e SQL 2000. In MS2K, I can get the index key information by SELECT
INDEXKEY_PROPERTY(table_ID , index_ID , key_ID , IsDescending) which
returns 1 if key is descending. But as this is only for MS2K, it will
fail and report error for above query in case connected to SQL server
7.

Now, in the application we avoided using server version dependent
code. So, can you please suggest me other way to do the same so that
the query should not produce an error if connected to MS7 server but
should work for MS-2000 server.

Regards,
Uday

[Erland Sommarskog]

udayt (ud**@magicsoftware.com) writes:

My application needs to calculate the sort order of an index key
(whether the index key is descending or ascending). The user may
connect to MS7 or MS2K servers.

As far as I know, the descending indices are supported in version 8
i.e SQL 2000. In MS2K, I can get the index key information by SELECT
INDEXKEY_PROPERTY(table_ID , index_ID , key_ID , IsDescending) which
returns 1 if key is descending. But as this is only for MS2K, it will
fail and report error for above query in case connected to SQL server
7.

Now, in the application we avoided using server version dependent
code. So, can you please suggest me other way to do the same so that
the query should not produce an error if connected to MS7 server but
should work for MS-2000 server.

You might be able to find this in status column in some system table,
which if you are lucky are both on SQL7 and SQL2000. However, I would
discourage you from using this, as this column is guaranteed to be
undocumented. The INDEXKEY_PROPERTY function will work in Yukon too,
but there is no guarantee that that status column will.

Having server-version dependent code for this type of thing is perfectly
normal. I have a load tool in which I have no less than three different
versions of some queries against sysindexes - one each for SQL 6.5, SQL 7
and SQL 2000. Have yet to add one for Yukon.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp