Different sort order - same set up

I have copied and restore a database from one SQL 2000
server to another with the same set up. I then ran stored
procedures to insert data from one table to the resultant
table, and expect the data to be the same as that of the
original machine. I have discovered, however, that the
same data has been inserted, but the data sort order is
different of that of the original server. On the SQL
statement to insert data there is a group by and order by
clause so the inserted data should have the same order.
The resultant table does not have any indexes (on both
servers). Could anyone tell me what could have cause this?
This could be important to us, as the data in the
resultant table will be BCP out to a report server, and
the sort order could be crucial. I know I could possibly
solve the problem by adding clustered indexes, but I would
like to know the cause.
The only difference in specification is that the new
server is on Service Pack version 8:00:818 (SP3), and the
original server is on 8:00:760 (SP3).
Both servers have the same collation , and both on Windows
2000 SP3Only way to guarantee a certain order is to have ORDER BY in the queries. Not even having a
clustered index will guarantee getting the data in a certain order.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alex" <alex.au@.ace-ina.com> wrote in message news:2b3b001c392fb$3e127730$a601280a@.phx.gbl...
> I have copied and restore a database from one SQL 2000
> server to another with the same set up. I then ran stored
> procedures to insert data from one table to the resultant
> table, and expect the data to be the same as that of the
> original machine. I have discovered, however, that the
> same data has been inserted, but the data sort order is
> different of that of the original server. On the SQL
> statement to insert data there is a group by and order by
> clause so the inserted data should have the same order.
> The resultant table does not have any indexes (on both
> servers). Could anyone tell me what could have cause this?
> This could be important to us, as the data in the
> resultant table will be BCP out to a report server, and
> the sort order could be crucial. I know I could possibly
> solve the problem by adding clustered indexes, but I would
> like to know the cause.
> The only difference in specification is that the new
> server is on Service Pack version 8:00:818 (SP3), and the
> original server is on 8:00:760 (SP3).
> Both servers have the same collation , and both on Windows
> 2000 SP3|||Thanks for the reply Tibor. The problem is , as I stated
earlier, the sql statement has got group by and order by
included, and insert into a table with no indexes. I am
trying to say that for some reason - same sql used to do
insert on two machines with same set up somehow result
with data stored in different order.
>--Original Message--
>Only way to guarantee a certain order is to have ORDER BY
in the queries. Not even having a
>clustered index will guarantee getting the data in a
certain order.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"Alex" <alex.au@.ace-ina.com> wrote in message
news:2b3b001c392fb$3e127730$a601280a@.phx.gbl...
>> I have copied and restore a database from one SQL 2000
>> server to another with the same set up. I then ran
stored
>> procedures to insert data from one table to the
resultant
>> table, and expect the data to be the same as that of the
>> original machine. I have discovered, however, that the
>> same data has been inserted, but the data sort order is
>> different of that of the original server. On the SQL
>> statement to insert data there is a group by and order
by
>> clause so the inserted data should have the same order.
>> The resultant table does not have any indexes (on both
>> servers). Could anyone tell me what could have cause
this?
>> This could be important to us, as the data in the
>> resultant table will be BCP out to a report server, and
>> the sort order could be crucial. I know I could possibly
>> solve the problem by adding clustered indexes, but I
would
>> like to know the cause.
>> The only difference in specification is that the new
>> server is on Service Pack version 8:00:818 (SP3), and
the
>> original server is on 8:00:760 (SP3).
>> Both servers have the same collation , and both on
Windows
>> 2000 SP3
>
>.
>|||Sorry, I missed the part that the statements has ORDER BY. Does the columns you ORDER BY over have
the same collation? Try with sp_help <tblname>.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alex" <anonymous@.discussions.microsoft.com> wrote in message
news:2b58301c3930c$3af2b0e0$a601280a@.phx.gbl...
> Thanks for the reply Tibor. The problem is , as I stated
> earlier, the sql statement has got group by and order by
> included, and insert into a table with no indexes. I am
> trying to say that for some reason - same sql used to do
> insert on two machines with same set up somehow result
> with data stored in different order.
> >--Original Message--
> >Only way to guarantee a certain order is to have ORDER BY
> in the queries. Not even having a
> >clustered index will guarantee getting the data in a
> certain order.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"Alex" <alex.au@.ace-ina.com> wrote in message
> news:2b3b001c392fb$3e127730$a601280a@.phx.gbl...
> >> I have copied and restore a database from one SQL 2000
> >> server to another with the same set up. I then ran
> stored
> >> procedures to insert data from one table to the
> resultant
> >> table, and expect the data to be the same as that of the
> >> original machine. I have discovered, however, that the
> >> same data has been inserted, but the data sort order is
> >> different of that of the original server. On the SQL
> >> statement to insert data there is a group by and order
> by
> >> clause so the inserted data should have the same order.
> >> The resultant table does not have any indexes (on both
> >> servers). Could anyone tell me what could have cause
> this?
> >> This could be important to us, as the data in the
> >> resultant table will be BCP out to a report server, and
> >> the sort order could be crucial. I know I could possibly
> >> solve the problem by adding clustered indexes, but I
> would
> >> like to know the cause.
> >>
> >> The only difference in specification is that the new
> >> server is on Service Pack version 8:00:818 (SP3), and
> the
> >> original server is on 8:00:760 (SP3).
> >>
> >> Both servers have the same collation , and both on
> Windows
> >> 2000 SP3
> >
> >
> >.
> >|||And what Tibor is trying to say is that unless you specify ORDER BY then you
cannot guarantee the order you get the data back.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Alex" <anonymous@.discussions.microsoft.com> wrote in message
news:2b58301c3930c$3af2b0e0$a601280a@.phx.gbl...
> Thanks for the reply Tibor. The problem is , as I stated
> earlier, the sql statement has got group by and order by
> included, and insert into a table with no indexes. I am
> trying to say that for some reason - same sql used to do
> insert on two machines with same set up somehow result
> with data stored in different order.
> >--Original Message--
> >Only way to guarantee a certain order is to have ORDER BY
> in the queries. Not even having a
> >clustered index will guarantee getting the data in a
> certain order.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"Alex" <alex.au@.ace-ina.com> wrote in message
> news:2b3b001c392fb$3e127730$a601280a@.phx.gbl...
> >> I have copied and restore a database from one SQL 2000
> >> server to another with the same set up. I then ran
> stored
> >> procedures to insert data from one table to the
> resultant
> >> table, and expect the data to be the same as that of the
> >> original machine. I have discovered, however, that the
> >> same data has been inserted, but the data sort order is
> >> different of that of the original server. On the SQL
> >> statement to insert data there is a group by and order
> by
> >> clause so the inserted data should have the same order.
> >> The resultant table does not have any indexes (on both
> >> servers). Could anyone tell me what could have cause
> this?
> >> This could be important to us, as the data in the
> >> resultant table will be BCP out to a report server, and
> >> the sort order could be crucial. I know I could possibly
> >> solve the problem by adding clustered indexes, but I
> would
> >> like to know the cause.
> >>
> >> The only difference in specification is that the new
> >> server is on Service Pack version 8:00:818 (SP3), and
> the
> >> original server is on 8:00:760 (SP3).
> >>
> >> Both servers have the same collation , and both on
> Windows
> >> 2000 SP3
> >
> >
> >.
> >|||Tibor
The two server are of the same collation
(SQL_Latin1_General_CP1_CI_AS) and nothing was specified
on the columns during insert.
What happened was that we are migrating a database from
one server to another server. Using the same codes but the
result from the second server was of different sort order.
Software wise are the same I can only think of something
in the set up. The original database is in US, and we are
migrating it to UK. Also the UK Server SP3 version
8:00:818 is the only difference to US ( 8:00:760)
>--Original Message--
>Sorry, I missed the part that the statements has ORDER
BY. Does the columns you ORDER BY over have
>the same collation? Try with sp_help <tblname>.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"Alex" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2b58301c3930c$3af2b0e0$a601280a@.phx.gbl...
>> Thanks for the reply Tibor. The problem is , as I stated
>> earlier, the sql statement has got group by and order by
>> included, and insert into a table with no indexes. I am
>> trying to say that for some reason - same sql used to do
>> insert on two machines with same set up somehow result
>> with data stored in different order.
>> >--Original Message--
>> >Only way to guarantee a certain order is to have ORDER
BY
>> in the queries. Not even having a
>> >clustered index will guarantee getting the data in a
>> certain order.
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at: http://groups.google.com/groups?oi=djq&as
>> ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Alex" <alex.au@.ace-ina.com> wrote in message
>> news:2b3b001c392fb$3e127730$a601280a@.phx.gbl...
>> >> I have copied and restore a database from one SQL
2000
>> >> server to another with the same set up. I then ran
>> stored
>> >> procedures to insert data from one table to the
>> resultant
>> >> table, and expect the data to be the same as that of
the
>> >> original machine. I have discovered, however, that
the
>> >> same data has been inserted, but the data sort order
is
>> >> different of that of the original server. On the SQL
>> >> statement to insert data there is a group by and
order
>> by
>> >> clause so the inserted data should have the same
order.
>> >> The resultant table does not have any indexes (on
both
>> >> servers). Could anyone tell me what could have cause
>> this?
>> >> This could be important to us, as the data in the
>> >> resultant table will be BCP out to a report server,
and
>> >> the sort order could be crucial. I know I could
possibly
>> >> solve the problem by adding clustered indexes, but I
>> would
>> >> like to know the cause.
>> >>
>> >> The only difference in specification is that the new
>> >> server is on Service Pack version 8:00:818 (SP3), and
>> the
>> >> original server is on 8:00:760 (SP3).
>> >>
>> >> Both servers have the same collation , and both on
>> Windows
>> >> 2000 SP3
>> >
>> >
>> >.
>> >
>
>.
>|||The two burning questions are:
1. The collation on the *column*, not the server. Check using sp_help <tblname>.
2. The query. That you indeed have an ORDER BY in the query.
If above both hold (same collation in all column(s) in all tables on both servers; and you do have
ORDER BY on the query and run exactly the same query on both servers), then it is strange.
Unless you have a collation where SQL Server "doesn't care". Some old SQL collation didn't care if
the upper or lower case letter was returned first. Say you have 'alex' and 'Alex' - which one should
come first? Does it matter?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alex" <anonymous@.discussions.microsoft.com> wrote in message
news:066601c39312$04bf5bd0$a001280a@.phx.gbl...
> Tibor
> The two server are of the same collation
> (SQL_Latin1_General_CP1_CI_AS) and nothing was specified
> on the columns during insert.
> What happened was that we are migrating a database from
> one server to another server. Using the same codes but the
> result from the second server was of different sort order.
> Software wise are the same I can only think of something
> in the set up. The original database is in US, and we are
> migrating it to UK. Also the UK Server SP3 version
> 8:00:818 is the only difference to US ( 8:00:760)
> >--Original Message--
> >Sorry, I missed the part that the statements has ORDER
> BY. Does the columns you ORDER BY over have
> >the same collation? Try with sp_help <tblname>.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"Alex" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2b58301c3930c$3af2b0e0$a601280a@.phx.gbl...
> >> Thanks for the reply Tibor. The problem is , as I stated
> >> earlier, the sql statement has got group by and order by
> >> included, and insert into a table with no indexes. I am
> >> trying to say that for some reason - same sql used to do
> >> insert on two machines with same set up somehow result
> >> with data stored in different order.
> >>
> >> >--Original Message--
> >> >Only way to guarantee a certain order is to have ORDER
> BY
> >> in the queries. Not even having a
> >> >clustered index will guarantee getting the data in a
> >> certain order.
> >> >
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at: http://groups.google.com/groups?oi=djq&as
> >> ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"Alex" <alex.au@.ace-ina.com> wrote in message
> >> news:2b3b001c392fb$3e127730$a601280a@.phx.gbl...
> >> >> I have copied and restore a database from one SQL
> 2000
> >> >> server to another with the same set up. I then ran
> >> stored
> >> >> procedures to insert data from one table to the
> >> resultant
> >> >> table, and expect the data to be the same as that of
> the
> >> >> original machine. I have discovered, however, that
> the
> >> >> same data has been inserted, but the data sort order
> is
> >> >> different of that of the original server. On the SQL
> >> >> statement to insert data there is a group by and
> order
> >> by
> >> >> clause so the inserted data should have the same
> order.
> >> >> The resultant table does not have any indexes (on
> both
> >> >> servers). Could anyone tell me what could have cause
> >> this?
> >> >> This could be important to us, as the data in the
> >> >> resultant table will be BCP out to a report server,
> and
> >> >> the sort order could be crucial. I know I could
> possibly
> >> >> solve the problem by adding clustered indexes, but I
> >> would
> >> >> like to know the cause.
> >> >>
> >> >> The only difference in specification is that the new
> >> >> server is on Service Pack version 8:00:818 (SP3), and
> >> the
> >> >> original server is on 8:00:760 (SP3).
> >> >>
> >> >> Both servers have the same collation , and both on
> >> Windows
> >> >> 2000 SP3
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||> On the SQL
> statement to insert data there is a group by and order by
> clause so the inserted data should have the same order.
I understand you basically have something like:
INSERT INTO MyTable
SELECT MyColumn
FROM MyOtherTable
GROUP BY MyColumn
ORDER BY MyColumn
And rows are not returned in sequence by the ordered column when you
run:
SELECT MyColumn
FROM MyTable
This is because the insert query is writing data to a table, not a
sequential file. Because a table is an unordered set of rows, a
relational database may return data in no particular sequence unless
constrained by an ORDER BY clause. As stated by the other responses,
you *must* specify ORDER BY on this SELECT query in order to guarantee
ordering:
SELECT MyColumn
FROM MyTable
ORDER BY MyTable
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Alex" <alex.au@.ace-ina.com> wrote in message
news:2b3b001c392fb$3e127730$a601280a@.phx.gbl...
> I have copied and restore a database from one SQL 2000
> server to another with the same set up. I then ran stored
> procedures to insert data from one table to the resultant
> table, and expect the data to be the same as that of the
> original machine. I have discovered, however, that the
> same data has been inserted, but the data sort order is
> different of that of the original server. On the SQL
> statement to insert data there is a group by and order by
> clause so the inserted data should have the same order.
> The resultant table does not have any indexes (on both
> servers). Could anyone tell me what could have cause this?
> This could be important to us, as the data in the
> resultant table will be BCP out to a report server, and
> the sort order could be crucial. I know I could possibly
> solve the problem by adding clustered indexes, but I would
> like to know the cause.
> The only difference in specification is that the new
> server is on Service Pack version 8:00:818 (SP3), and the
> original server is on 8:00:760 (SP3).
> Both servers have the same collation , and both on Windows
> 2000 SP3