I have a unique clustered index on a table in SQL Server 2000.  Its columns
are Year (descending), Grove, Ticket.  The table's primary key is different
than the clustered index.

It was my understanding some time ago that if I created a clustered index on
a table, and then selected from the table, that the records would appear in
the order of the clustered index.  That worked for me at that time, but it's
not working anymore.  The table records don't appear to be in any order. 
Help!

Mary Fetsch wrote:
As others have already replied, the ONLY way to guarantee the order of
the returned rows is by using ORDER BY.  Put an ORDER BY on your query
and you're all set.

That said, it could be that the reason this "stopped working" is that
the query isn't using the clustered index anymore.  Review the execution
plan, you may discover that a different index is being used, possibly
impacting the performance of your query.

Thanks for your quick response.  I'm using my table in a form in Access 2000,
where I only want to show records changed or modified on the current date. 
The the form's record source is:
        "Select * from TRIP " & _
        "Where SPECIAL_CODE = 'NONE'"
        "And (convert(varchar(10),CREATE_DATE,101) = " & _
                     "convert(varchar(10), getdate(), 101) " & _
        "Or convert(varchar(10),MODIFY_DATE,101) = " & _
        "convert(varchar(10), getdate(), 101)) "

I had problems when I added an "Order by " clause to that.  Since it's been
a long time ago that I did that, I  can't remember exactly what the problem
was, but I know I had to remove the "Order by ".  The clustered index seemed
to be the answer to my problem.  It worked nicely at first, and I don't know
what happened to change that.  If the table's not using the clustered index,
how can I see what index it's using?  If I remove the clustered index, will
the table sort according to the primary key? 

Sounds like I'm stuck with unsorted records in my form unless you or someone
else can think of another way to sort my table.  Thanks!

Show quote

Hide quote

Four other posters have already presented you with the only relevant fact:
you cannot expect an ordered set of rows unless you use an ORDER BY clause.
That's the way SQL works - every table is an unordered set of rows.

Have you tried adding the ORDER BY clause? What is the error message that
you're getting?


ML

---
http://milambda.blogspot.com/

Thanks for your response.  As I mentioned earlier, I tried the Order By
clause but had to remove it.  I didn't get an error message, but the data
didn't work properly.  Once I removed the Order By, the problem was resolved.
I did it so long ago that I don't remember exactly what the problem was. 
Somehow the data didn't act the way it needed to.

This form (named Trip) has a child form that's a datasheet.  I'm using SQL
statements (run in VB) as the record sources for both the parent and child
forms, both when opening and querying (filtering) the forms.  (I wrote my own
query modules because I needed more than Access' filtering could give me.)

I actually have several forms, using several tables, set up like the Trip
form and table.  All were working well with the clustered indexes but are not
working now.  I understand now that the Order By is the only way to ensure
that my tables are sorted correctly, but I can't use it. 

Perhaps the Order By isn't working because of the way my VB is processing
the data, but the data needs to be processed as it is.  I tried the Order By
and all kinds of other ways to sort my data, and the clustered indexes were
the only thing that worked.  (Since I can't use the Order By, I need to have
each table sorted BEFORE it gets to its form so that my SQL can just bring
the table data into the form.  The clustered indexes accomplished that.)

I don't see any way to sort my form data, and I the users are just going to
have to live with that, since their data needs to be processed as it is. This
project is soon coming to an end, and it would take a major overhaul and lots
of time, which we don't have, to do things any differently.  They can query
data they need, which is what I think they'll be doing a lot of anyway. 

Will the data appear in the table in the order it's entered in the form, or
is that not guaranteed either?

Show quote

Hide quote

On Tue, 19 Dec 2006 06:59:00 -0800, Mary Fetsch wrote:

Hi Mary,

Sorry for barging in so late in the discussion, but I just couldn't hold
myself back.

Let me try to summarize what I think has happpend.

1. A long time ago, you tried getting the correct order using ORDER BY.
That somehow failed to yield the expected order. Obviously, you either
have struck a bug in the product, or made an error in the query. You no
longer know exactly what you did, so you can't reproduce the problem for
us to dive into.

2. After concluding that the ORDER BY didn't work as yoou expected, you
found a workaround. Granted, the effect has never been guaranteed but it
obviously worked for you for a long time.

3. Now the workaround no longer works. You're trying to find a solution.
Everybody here says that ORDER BY is the only soolution, but you have
tried it and apparently don't want to try it again. However, there is no
other way to ensure results in a guaranteed order, so that leaves yu
empty handed.


Now, what I don't understand is that you come here asking for help on
one issue, yet you are unwilling to accept the same help on the real
issue, the root cause of your problem: the flaky ORDER BY.

The way I see it, you have just two possibilities now. Either you accept
what your boss orders: you stop spending time on this and your boss will
explain to your users that he accepts responsibility for his decision to
not fix the incorrect ordering. Or you explain to your boss that you
have found a place on Usenet where some of the finest SQL Server brains
are gathered, all willing to help you solve this problem. If that
succeeds, then the next step is to reinstate the ORDER BY and test it
again. If it works this time, you're good - either it was indeed a bug
and Microsoft has fixed it, or you had an error in the query at that
time and don't have that error now. And if the ORDER BY produces
incorrect results again, than you post a script to reproduce the problem
(see www.aspfaq.com/5006 for an overview of information you need to
post). That will allow the regulars here to reproduce the problem, find
the cause and either confirm it's a bug in the product (and possibly
find a better workaround for you), or supply you with a correction to
your error.

Trust me, you've got nothing to lose and lots to win - provided you can
convince your boss to allow you a little bit more time for working on
this problem.

Good luck!

Your summary is correct, and I understand the frustration that all of you,
"some of the finest SQL Server brains", are experiencing.  I'm not being
facetious here - I greatly respect all of you and appreciate you wanting to
find a solution to this.  I want that, too, but this project has already gone
too long, and my boss and the users don't want it to go any longer because of
this.

It's just not that important to the users to have me spend more time on this
to have the data ordered on the form.  They don't necessarily go through it
record by record.  They're either adding new records (which appear on the
form in the order they're added), or they're querying an existing record or
small group of records to check or change them.  They've got all kinds of
reports that show their data in certain orders.  There are other things that
still need to be done in this project that are more important.   It's their
money, so I have to respect their wishes.

It would be more than a little bit more time.  This data is processed in a
pretty complicated way, and it's been a while  since I've delved into some of
the code.  Because of that, I'm nervous that if I "fix" this I might hurt
something else.  It would require a lot of time to test to make sure that
didn't happen.

This project has already cost more time and money than intended, and my boss
is anxious that it come to an end.  I want to keep my job and my good
relationship with my boss.  So I'm going to respect his and the users wishes
to let this go.

If I do a project like this again, I will definitely investigate this and
get back to the newsgroup if I have problems.  Newsgroups have been a great
resource for me.  I very much appreciate the time and expertise all of you
give to the newsgroup.

But you've spent more time debating this issue here than it could possibly
have taken to provide us the repro we've asked for at least a dozen times.
For all you know, it could be a two-minute fix.  Yet we continue to banter
back and forth and never really get a sense of what "doesn't work" means...

A

Stored procedure to change not nulls to nulls
To write SPs, or not to write SPs, that is the question....
Default Values Question
Query help
Default Table Owner using CREATE TABLE, INSERT, SELECT & DROP TABLE