Joh wrote:
> Note:
> 1) Non Clustered Index on MedID
> 2) Clustered Index on LInfoID
>
> Working:- select top 1 MedID, LInfoID from Medicalhistory order by 1
>
> Working:- select top 1 LInfoID from Medicalhistory order by 1 LInfoID
> desc
>
> Not working:- select top 1 MedID, LInfoID from Medicalhistory order
> by 1 desc
>

Joh wrote:
> Here is the quick question, i have created MedicalHistory
> Partitionned view and it's working pretty good.. when I write this
> select top 1 * from MedicalHistory ... result comes in a second but
> when I write this query.. select top 1 * from MedicalHistory order by
> 1 desc... result will not come till half an hour but when I hit it's
> partitioned table like MedicalHistory_2005 then the result comes on
> the spot... I think there is some minor problem but can't findout ....
>
> Any idea?

"David Gugick" <david.gugick-nospam@quest.com> wrote in message
news:O0LZD7hYFHA.796@TK2MSFTNGP10.phx.gbl...
> Joh wrote:
> > Here is the quick question, i have created MedicalHistory
> > Partitionned view and it's working pretty good.. when I write this
> > select top 1 * from MedicalHistory ... result comes in a second but
> > when I write this query.. select top 1 * from MedicalHistory order by
> > 1 desc... result will not come till half an hour but when I hit it's
> > partitioned table like MedicalHistory_2005 then the result comes on
> > the spot... I think there is some minor problem but can't findout ....
> >
> > Any idea?
>
> By issuing the Order By (which is a requirement for TOP) you are forcing
> SQL Server to sort the table by the first column. If there is no index
> on that column, then SQL Server must sort the entire partitioned view
> and then pull the first row. Probably performing table scan / clustered
> index scan operations on all underlying tables. In any case, you should
> avoid SELECT *.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>