Actually, the behavior you've seen in SQL Server may be a pure artifact of the table structures underneath your
queries.
Most database architects will (appropriately) put a primary key on every table and the default in SQL Server is to make
primarykeys clustered...and clustering arranges the physical storage of the rows in the increasing order of that key.
Ifthat is what exists in the SQL Server db, then KH_.r_object_id would be a clustered PK and so of course would return
rowsin that order, automatically. As Kellerer said, otherwise it is random ordering, without an Order By clause.
Postgres PKs are not clustered by default, so you'll experience the random row ordering you mentioned. Cluster that
columnand you'll get the same behavior...but read up on postgres clustering since it works very differently than SQL
Server.
Mike
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar
Sent: Monday, February 08, 2016 10:15 PM
To: Adrian Klaver <adrian.klaver@aklaver.com>; David G. Johnston <david.g.johnston@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql@postgresql.org
Subject: Re: [SQL] Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
My concern here is that I want to maintain consistency ( in our application to retain sort order) between different
databases.
I don't see the issue in SQL Server and Oracle databases.
"SELECT KH_.r_object_id, KH_.object_name FROM dbo.dm_location_s AS ZS_ INNER JOIN
dbo.dm_sysobject_sAS KH_ ON ZS_.r_object_id = KH_.r_object_id "
The above query is sorted based on the first column in the select list. Same is not happening in PostgreSQL.
Is this something to do with collation setting in database?
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Tuesday, February 09, 2016 11:32 AM
To: Venkatesan, Sekhar; David G. Johnston
Cc: Tom Lane; pgsql-sql@postgresql.org
Subject: Re: [SQL] Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
On 02/08/2016 09:53 PM, Venkatesan, Sekhar wrote:
> Yes. is there an option/configuration to tell the postgres
> optimizer/planner to generate plans to include the sort order instead
> of speed?
What columns in a table would that be and then what order?
>
> *From:*David G. Johnston [mailto:david.g.johnston@gmail.com]
> *Sent:* Tuesday, February 09, 2016 11:20 AM
> *To:* Venkatesan, Sekhar
> *Cc:* Tom Lane; pgsql-sql@postgresql.org
> *Subject:* Re: [SQL] Question on PostgreSQL DB behavior w.r.t JOIN and
> sort order.
>
> On Monday, February 8, 2016, Venkatesan, Sekhar
> <sekhar.venkatesan@emc.com <mailto:sekhar.venkatesan@emc.com>> wrote:
>
> Is there a way to tell the optimizer to retain the sort order if that
> is possible please?
>
> You mean, besides the ORDER BY clause?
>
> David J.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
