Reordering SQL Tables

Hi,

I have inherited an inhouse employee self service application used to display various employee data.

I have one table that needs to have the data permanently reordered, as the front end web page reads the data from the table from the top to the bottom, but somehow this data has got mixed up and is not in the sequential order that it should be in.

I am new to SQL and haven't a clue how to change the web code, so was hoping I could run a script on that table to reorder the data in an ascending manner on one of the columns.

Is this possible? How can I do this?

Thanks

Chris

Hi Chris,

When you say "not in the sequential order" do you mean there is a column that has incrementing numeric values such as 1,2,3...100000 or that you expect results to be ordered by something like customer name etc?

You can only "order" a table via a clustered index, but this has no impact upon client query result formats (in fact it did pre 2000 if no order by was specified, but that's another story). You will need to specify an ORDER BY in the client, or in a view (via the TOP clause) or stored procedure. You cannot rely upon sql server to return ordered results unless you have explicity stated what and how you wish the ordering to ocurr.

Cheers,

Rob

reordering in paralellism-multiprocessor machine

Excuse me if this is a double post!
Consider these queries:
1) select C,D,E,F from T where (A - B) >= 0
2) select C,D,E,F from T where B >= 0 and B <10
3) select * from T where (A - B) >= 0
4) select * from T where B >= 0 and B <10
Description of the table 'T':
256 columns
No identity column defined on T
columns Z,Y,X have nonclustered on PRIMARY
column C, being the primary key column , has the index: clustered, unique,
primary key located on PRIMARY
A is of type int and B is of type decimal.
No. of rows in the table: 100406
Process followed for replication: run these queries in a MULTI PROCESSOR
machine. See the execution plan.
Issue:
Queries 1, 3, 4 execute by the serial plan, whereas query 2 executes by
parallel plan. Still worse, the order in which the records are returned are
not the same each time query 2 is run. When tried with the hint option
(MAXDOP 1), the query 2 executes in serial mode, without re-arrangement. Need
an answer for the rearrangement of the rows resulting in case of query 2.This has been discussed ad-nauseum. The order of returned rows is never
guaranteed unless the query includes an order by clause.
"Saravana" <Saravana@.discussions.microsoft.com> wrote in message
news:6B76B67A-AD98-415D-8296-9619039A7377@.microsoft.com...
> Excuse me if this is a double post!
> Consider these queries:
> 1) select C,D,E,F from T where (A - B) >= 0
> 2) select C,D,E,F from T where B >= 0 and B <10
> 3) select * from T where (A - B) >= 0
> 4) select * from T where B >= 0 and B <10
> Description of the table 'T':
> 256 columns
> No identity column defined on T
> columns Z,Y,X have nonclustered on PRIMARY
> column C, being the primary key column , has the index: clustered, unique,
> primary key located on PRIMARY
> A is of type int and B is of type decimal.
> No. of rows in the table: 100406
> Process followed for replication: run these queries in a MULTI PROCESSOR
> machine. See the execution plan.
> Issue:
> Queries 1, 3, 4 execute by the serial plan, whereas query 2 executes
by
> parallel plan. Still worse, the order in which the records are returned
are
> not the same each time query 2 is run. When tried with the hint option
> (MAXDOP 1), the query 2 executes in serial mode, without re-arrangement.
Need
> an answer for the rearrangement of the rows resulting in case of query 2.
>|||Scott,
Isn't there any other way of stopping this reordering: tweaking of the
tables, or the columns..
In other words: what is the exact quantitative statistic that is considered
by the server for executing a query paralelly or serially.. i came to know
about execution costs, but i am not able to get any useful information from
the execution plans from the query anaylzer. What i am more concerned is that
query 2 is re-ordered whereas query 1 is ordered...
Thanks.
"Scott Morris" wrote:
> This has been discussed ad-nauseum. The order of returned rows is never
> guaranteed unless the query includes an order by clause.
>|||As I said - no order by clause, no guarantee. There is a thread in
.progamming with the subject "order by and identity" that was active
yesterday. You might find the discussion interesting as it deals with the
same basic issue. You can search the newsgroups for discussions / rants on
this FAQ.
"Saravana" <Saravana@.discussions.microsoft.com> wrote in message
news:0FD75B28-2543-4128-8F6F-E888F5110751@.microsoft.com...
> Scott,
> Isn't there any other way of stopping this reordering: tweaking of the
> tables, or the columns..
> In other words: what is the exact quantitative statistic that is
considered
> by the server for executing a query paralelly or serially.. i came to know
> about execution costs, but i am not able to get any useful information
from
> the execution plans from the query anaylzer. What i am more concerned is
that
> query 2 is re-ordered whereas query 1 is ordered...
> Thanks.
> "Scott Morris" wrote:
> > This has been discussed ad-nauseum. The order of returned rows is never
> > guaranteed unless the query includes an order by clause.
> >
>|||Scott is 100% correct. If you want a specific order to be guaranteed for
the returned data you MUST specify an order by clause, period, end of story.
--
Andrew J. Kelly SQL MVP
"Saravana" <Saravana@.discussions.microsoft.com> wrote in message
news:0FD75B28-2543-4128-8F6F-E888F5110751@.microsoft.com...
> Scott,
> Isn't there any other way of stopping this reordering: tweaking of the
> tables, or the columns..
> In other words: what is the exact quantitative statistic that is
> considered
> by the server for executing a query paralelly or serially.. i came to know
> about execution costs, but i am not able to get any useful information
> from
> the execution plans from the query anaylzer. What i am more concerned is
> that
> query 2 is re-ordered whereas query 1 is ordered...
> Thanks.
> "Scott Morris" wrote:
>> This has been discussed ad-nauseum. The order of returned rows is never
>> guaranteed unless the query includes an order by clause.
>

reordering in paralellism-multiprocessor machine

Excuse me if this is a double post!
Consider these queries:
1) select C,D,E,F from T where (A - B) >= 0
2) select C,D,E,F from T where B >= 0 and B <10
3) select * from T where (A - B) >= 0
4) select * from T where B >= 0 and B <10
Description of the table 'T':
256 columns
No identity column defined on T
columns Z,Y,X have nonclustered on PRIMARY
column C, being the primary key column , has the index: clustered, unique,
primary key located on PRIMARY
A is of type int and B is of type decimal.
No. of rows in the table: 100406
Process followed for replication: run these queries in a MULTI PROCESSOR
machine. See the execution plan.
Issue:
Queries 1, 3, 4 execute by the serial plan, whereas query 2 executes by
parallel plan. Still worse, the order in which the records are returned are
not the same each time query 2 is run. When tried with the hint option
(MAXDOP 1), the query 2 executes in serial mode, without re-arrangement. Need
an answer for the rearrangement of the rows resulting in case of query 2.
This has been discussed ad-nauseum. The order of returned rows is never
guaranteed unless the query includes an order by clause.
"Saravana" <Saravana@.discussions.microsoft.com> wrote in message
news:6B76B67A-AD98-415D-8296-9619039A7377@.microsoft.com...
> Excuse me if this is a double post!
> Consider these queries:
> 1) select C,D,E,F from T where (A - B) >= 0
> 2) select C,D,E,F from T where B >= 0 and B <10
> 3) select * from T where (A - B) >= 0
> 4) select * from T where B >= 0 and B <10
> Description of the table 'T':
> 256 columns
> No identity column defined on T
> columns Z,Y,X have nonclustered on PRIMARY
> column C, being the primary key column , has the index: clustered, unique,
> primary key located on PRIMARY
> A is of type int and B is of type decimal.
> No. of rows in the table: 100406
> Process followed for replication: run these queries in a MULTI PROCESSOR
> machine. See the execution plan.
> Issue:
> Queries 1, 3, 4 execute by the serial plan, whereas query 2 executes
by
> parallel plan. Still worse, the order in which the records are returned
are
> not the same each time query 2 is run. When tried with the hint option
> (MAXDOP 1), the query 2 executes in serial mode, without re-arrangement.
Need
> an answer for the rearrangement of the rows resulting in case of query 2.
>
|||Scott,
Isn't there any other way of stopping this reordering: tweaking of the
tables, or the columns..
In other words: what is the exact quantitative statistic that is considered
by the server for executing a query paralelly or serially.. i came to know
about execution costs, but i am not able to get any useful information from
the execution plans from the query anaylzer. What i am more concerned is that
query 2 is re-ordered whereas query 1 is ordered...
Thanks.
"Scott Morris" wrote:

> This has been discussed ad-nauseum. The order of returned rows is never
> guaranteed unless the query includes an order by clause.
>
|||As I said - no order by clause, no guarantee. There is a thread in
..progamming with the subject "order by and identity" that was active
yesterday. You might find the discussion interesting as it deals with the
same basic issue. You can search the newsgroups for discussions / rants on
this FAQ.
"Saravana" <Saravana@.discussions.microsoft.com> wrote in message
news:0FD75B28-2543-4128-8F6F-E888F5110751@.microsoft.com...
> Scott,
> Isn't there any other way of stopping this reordering: tweaking of the
> tables, or the columns..
> In other words: what is the exact quantitative statistic that is
considered
> by the server for executing a query paralelly or serially.. i came to know
> about execution costs, but i am not able to get any useful information
from
> the execution plans from the query anaylzer. What i am more concerned is
that
> query 2 is re-ordered whereas query 1 is ordered...
> Thanks.
> "Scott Morris" wrote:
>
|||Scott is 100% correct. If you want a specific order to be guaranteed for
the returned data you MUST specify an order by clause, period, end of story.
Andrew J. Kelly SQL MVP
"Saravana" <Saravana@.discussions.microsoft.com> wrote in message
news:0FD75B28-2543-4128-8F6F-E888F5110751@.microsoft.com...
> Scott,
> Isn't there any other way of stopping this reordering: tweaking of the
> tables, or the columns..
> In other words: what is the exact quantitative statistic that is
> considered
> by the server for executing a query paralelly or serially.. i came to know
> about execution costs, but i am not able to get any useful information
> from
> the execution plans from the query anaylzer. What i am more concerned is
> that
> query 2 is re-ordered whereas query 1 is ordered...
> Thanks.
> "Scott Morris" wrote:
>

Reordering entries in form

I have a form with a few entries running in it, 1-10. Now, what I would like
if possible, is for the entries to be reorganised so that they are listed
alphabetically. So, when I open the form, instead of getting entry number
one, I get the entry beginning with A.

I have resorted the columns in my table, however this ain't helped."Neil Greenough" <neilgreenough@.btopenworld.com> wrote in message
news:ckj98c$f20$1@.hercules.btinternet.com...
>I have a form with a few entries running in it, 1-10. Now, what I would
>like
> if possible, is for the entries to be reorganised so that they are listed
> alphabetically. So, when I open the form, instead of getting entry number
> one, I get the entry beginning with A.
> I have resorted the columns in my table, however this ain't helped.

Is this another Access question? :-)

In MSSQL, you could order the data with an ORDER BY when you retrieve it
from the database:

select col1, col2, col3
from table1
order by col2

I have no idea how you would do it in an Access form, though. You might want
to try in microsoft.public.access.forms.

Simon