How do I know if an index column is in descending order from SQL Server?

Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks
>
> "GregO" <grego@.community.nospam> wrote in message
> news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
>> Hi Peter
>>
>> EXECUTE sp_MShelpindex N'authors', N'aunmind'
>>
>> try this in PUBS
>>
>> http://www.sql-server-performance.com/ac_sql_server_7_undocumented_sp.asp
>>
>>
>> --
>> kind regards
>> Greg O
>> Need to document your databases. Use the firs and still the best AGS SQL
>> Scribe
>> http://www.ag-software.com
>>
>>
>> "Peter Reid" <noreply@.microsoft.com> wrote in message
>> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
>> How do I know if an index column is in descending order from SQL Server?
>>
>> I don't want to use "indexkey_property" as it doesn't work from another
>> DB.
>> I also know that I can do something like this:
>>
>> USE <db1>
>> select into a temp table
>>
>> USE <db2>
>> select into another temp table
>>
>>
>> But what I'm actually interested in knowing is where this information is
>> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
>> and furthermore how to query it.
>>
>> Thanks
>>
>>
>>
>>
>
>Hi Peter
Did you try:
EXECUTE pubs..sp_MShelpindex N'authors', N'aunmind'
John
"Peter Reid" wrote:
> Hey all that I want is to be able to run from another DB.
> This "sp_MShelpindex" like the "indexkey_property" only work running from
> the DB where the index sits.
> I need to join this information from multiple DBs in a single query, and I
> was wondering if (and how) this is possible.
> Thanks
> >
> > "GregO" <grego@.community.nospam> wrote in message
> > news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
> >> Hi Peter
> >>
> >> EXECUTE sp_MShelpindex N'authors', N'aunmind'
> >>
> >> try this in PUBS
> >>
> >> http://www.sql-server-performance.com/ac_sql_server_7_undocumented_sp.asp
> >>
> >>
> >> --
> >> kind regards
> >> Greg O
> >> Need to document your databases. Use the firs and still the best AGS SQL
> >> Scribe
> >> http://www.ag-software.com
> >>
> >>
> >> "Peter Reid" <noreply@.microsoft.com> wrote in message
> >> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> >> How do I know if an index column is in descending order from SQL Server?
> >>
> >> I don't want to use "indexkey_property" as it doesn't work from another
> >> DB.
> >> I also know that I can do something like this:
> >>
> >> USE <db1>
> >> select into a temp table
> >>
> >> USE <db2>
> >> select into another temp table
> >>
> >>
> >> But what I'm actually interested in knowing is where this information is
> >> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> >> and furthermore how to query it.
> >>
> >> Thanks
> >>
> >>
> >>
> >>
> >
> >
>
>sql

How do I know if an index column is in descending order from SQL Server?

How do I know if an index column is in descending order from SQL Server?
I don't want to use "indexkey_property" as it doesn't work from another DB.
I also know that I can do something like this:
USE <db1>
select into a temp table
USE <db2>
select into another temp table
But what I'm actually interested in knowing is where this information is
stored in SQL Server (as it doesn't seam to on the sysindexkeys table), and
furthermore how to query it.
ThanksHi Peter
EXECUTE sp_MShelpindex N'authors', N'aunmind'
try this in PUBS
http://www.sql-server-performance.c...cumented_sp.asp
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>|||Look up the INDEXKEY_PROPERTY function in BOL.
SELECT indexkey_property(OBJECT_ID('authors'),2
,2,'IsDescending')
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>|||Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks
"GregO" <grego@.community.nospam> wrote in message
news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
> Hi Peter
> EXECUTE sp_MShelpindex N'authors', N'aunmind'
> try this in PUBS
> http://www.sql-server-performance.c...cumented_sp.asp
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
>
> "Peter Reid" <noreply@.microsoft.com> wrote in message
> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
>|||Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks

> "GregO" <grego@.community.nospam> wrote in message
> news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
>

How do I know if an index column is in descending order from SQL Server?

How do I know if an index column is in descending order from SQL Server?
I don't want to use "indexkey_property" as it doesn't work from another DB.
I also know that I can do something like this:
USE <db1>
select into a temp table
USE <db2>
select into another temp table
But what I'm actually interested in knowing is where this information is
stored in SQL Server (as it doesn't seam to on the sysindexkeys table), and
furthermore how to query it.
Thanks
Hi Peter
EXECUTE sp_MShelpindex N'authors', N'aunmind'
try this in PUBS
http://www.sql-server-performance.co...umented_sp.asp
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>
|||Look up the INDEXKEY_PROPERTY function in BOL.
SELECT indexkey_property(OBJECT_ID('authors'),2,2,'IsDesc ending')
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>
|||Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks
"GregO" <grego@.community.nospam> wrote in message
news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
> Hi Peter
> EXECUTE sp_MShelpindex N'authors', N'aunmind'
> try this in PUBS
> http://www.sql-server-performance.co...umented_sp.asp
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
>
> "Peter Reid" <noreply@.microsoft.com> wrote in message
> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
>
|||Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks

> "GregO" <grego@.community.nospam> wrote in message
> news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
>

How do I know if an index column is in descending order from SQL Server?

How do I know if an index column is in descending order from SQL Server?
I don't want to use "indexkey_property" as it doesn't work from another DB.
I also know that I can do something like this:
USE <db1>
select into a temp table
USE <db2>
select into another temp table
But what I'm actually interested in knowing is where this information is
stored in SQL Server (as it doesn't seam to on the sysindexkeys table), and
furthermore how to query it.
ThanksHi Peter
EXECUTE sp_MShelpindex N'authors', N'aunmind'
try this in PUBS
http://www.sql-server-performance.com/ac_sql_server_7_undocumented_sp.asp
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>|||Look up the INDEXKEY_PROPERTY function in BOL.
SELECT indexkey_property(OBJECT_ID('authors'),2,2,'IsDescending')
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>|||Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks
"GregO" <grego@.community.nospam> wrote in message
news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
> Hi Peter
> EXECUTE sp_MShelpindex N'authors', N'aunmind'
> try this in PUBS
> http://www.sql-server-performance.com/ac_sql_server_7_undocumented_sp.asp
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
>
> "Peter Reid" <noreply@.microsoft.com> wrote in message
> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
>> How do I know if an index column is in descending order from SQL Server?
>> I don't want to use "indexkey_property" as it doesn't work from another
>> DB.
>> I also know that I can do something like this:
>> USE <db1>
>> select into a temp table
>> USE <db2>
>> select into another temp table
>>
>> But what I'm actually interested in knowing is where this information is
>> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
>> and furthermore how to query it.
>> Thanks
>>
>

How do I get DESC order?

Hey guys, I have a view with dates (TheDate) meant to be arranged in descending order. When I 'Execute SQL' while in the view, the DESC order works just fine and shows up with the latest date first going down. However, once I 'OPEN VIEW' the order keeps defaulting to ASCending order.

How do I keep it in DESC order for viewing? Here's the statement:

SELECT TOP (100) PERCENT TheDate
FROM dbo.MyDates
ORDER BY TheDate DESC

By the way, I'm using SQL Server Express, if it makes any difference.

|||

hi,

views, as tables, do not have an order at all.. in fact yoe are executing a SELECT ... FROM view .. ORDER BY ...;

this is the relevant (!) step in your requirement.. the order by clause is a cursor task, not associated with the underlying table/view.. data inside a table (on which the view is defined) is not ordered

please have a look at wikipedia and here as well, where you can read

"..A view is a relational table, and the
relational model states that a table is a set of rows. Since sets are not sorted - per definition - the rows in a view are not ordered either. .."

and even

".. A view is a logical relational table, and the relational model mandates that a table is a set of rows, implying no sort order whatsoever. .."

so, what you are getting, is correct..

if you like, you can "open" the view (in SSMSE) and apply the required "sort" clause adding the ORDER BY specs..

regards

|||

Andrea Montanari wrote:

hi,

views, as tables, do not have an order at all.. in fact yoe are executing a SELECT ... FROM view .. ORDER BY ...;

this is the relevant (!) step in your requirement.. the order by clause is a cursor task, not associated with the underlying table/view.. data inside a table (on which the view is defined) is not ordered

please have a look at wikipedia and here as well, where you can read

"..A view is a relational table, and the
relational model states that a table is a set of rows. Since sets are not sorted - per definition - the rows in a view are not ordered either. .."

and even

".. A view is a logical relational table, and the relational model mandates that a table is a set of rows, implying no sort order whatsoever. .."

so, what you are getting, is correct..

if you like, you can "open" the view (in SSMSE) and apply the required "sort" clause adding the ORDER BY specs..

regards

Thank you for the very informative response as well as the links, Andrea. I was starting to go insane wondering what I was missing! Another day another lesson, I guess!

Once again, thanks for the info.