1

I have a complicated situation. There is a parent database P and then there is another database C which I am told is same as P (both SQL Server 2012). They both are on different server. I am doing a select * from tableA on both P and C. The data retrieved is exactly the same, but in different order. Both the tables have the same metadata. What could be the possible cause?

EDIT 1

The thing is, I have a stored procedure which I have got from my client and it runs on P, which is then used in a SSRS report. I dont have the permission to access P or the report. I am running the same stored procedure on C. That is when I get the same rows but in different order. No other order by clause has been added in the store procedure running C, apart from the ones already present. If the databases are same with same data and the table structure is also the same, why would the rows appear in different order?

EDIT 2

tableA

COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
100        C          1          2015-01-01
101        A          2          2015-01-05
102        A          2          2014-01-01
103        B          4          2011-09-01
104        C          1          2015-01-01

If in the above tableA (COL1 is the PRIMARY KEY), I do a select * from tableA order by COL2, COL3, COL4, then :

The result in P

COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
102        A          2          2014-01-01
101        A          2          2015-01-05
103        B          4          2011-09-01
100        C          1          2015-01-01
104        C          1          2015-01-01

The result in C

COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
102        A          2          2014-01-01
101        A          2          2015-01-05
103        B          4          2011-09-01
104        C          1          2015-01-01
100        C          1          2015-01-01

This is the issue.

PS - There is a change in collation of the DBs

KrazzyNefarious
  • 3,202
  • 3
  • 20
  • 32
  • Yea there is an order by on COL2 both of them are ordered on COL2 hence the order AABCC, if you want to have them the same then you need to add another Column into the order clause. In this case it would be COL1 since thats the only difference in your sample data. Like many answers here have pointed out, even if everything is the same there is no guarantee to the order of the rows in SQL Server unless you specify it. – ZeRaTuL_jF Jun 01 '15 at 13:48

3 Answers3

2

Even though both table has same metadata; per your post it can be seen that you are not using ORDER BY clause with your query and without ORDER BY; order of the returned result can never be guaranteed.

Even if you run the query in same server multiple times, you may observe different order of data coming in resultant output.

To make sure, getting specific order always use order by clause along with your query

select * from tableA order by some_column
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • @BhupeshC, that's what exactly said in answer; that the order can never be guranteed without `order by`. Would have the order can be guranteed then there is no need of an `order by` implementation. If you have access to the report/procedure running on `P`; run the proc multiple times and see that the order of resultant data is not same at all. – Rahul Jun 01 '15 at 13:17
  • If you wish, you may post the procedure code body in your question. – Rahul Jun 01 '15 at 13:18
  • There are `order by` clauses already specified in the sp. The report has been run several times on `P`. The order does not change.When running it on `C` it doesn't change either to what has been already appearing on `C`. Please see my edit for more. – KrazzyNefarious Jun 01 '15 at 13:38
  • The `order by COL2, COL3, COL4` is doing exactly what it should do. Only the PK column `COL1` is different in order. What if you do a `order by col1` instead in both machine? Can you post your table schema once if feasible. – Rahul Jun 01 '15 at 13:48
  • I can't do it in `P` as of now, but I am trying hard to get my hands on it. The point is, when I `order by` any other column but the PK, despite the likeness, the COL1 is not in the same order as it appears if I run the query on `P`. – KrazzyNefarious Jun 01 '15 at 13:59
  • @BhupeshC, I doubt your table structure is not same in both machine but as of now I can't offer any more help than what I had already. – Rahul Jun 01 '15 at 14:02
  • Its the copy of the MS Dynamics CRM db. Thanks Rahul. I didn't know the SQL Server resultset order is not always guaranteed – KrazzyNefarious Jun 01 '15 at 14:05
0

Unless you specify a PRIMARY KEY for your table the order in which your data is listed will strictly speaking not be defined. In MSSQL there usually is an "internal" order which seems reproducible for each table, but, as you have observed, this might well be different for the "same" table on two different servers. If you want to establish an identical order for the table on both servers you should:

  • introduce a primary key
  • or always use select with order by
Carsten Massmann
  • 26,510
  • 2
  • 22
  • 43
  • 3
    Primary keys -- not even clustered primary keys -- guarantee the order of a `select` set without an `order by`. Result sets are *unordered* unless an `ORDER BY` clause specifies the ordering. You might find this reading interesting: http://dba.stackexchange.com/questions/19203/storage-order-vs-result-order. – Gordon Linoff May 30 '15 at 22:22
0

As said in the other answers, unless you specify an order by, SQL Server can and is likely to return the results in an arbitrary order. The actual reasons are an implementation detail and depend on the query optimization decisions that SQL Server used when compiling the query.

Try this. At the end of the query that returns results, add the query hint option (maxdop 1). Then try your query against both databases. Does the result come back in the same order?

This query hint forces the SQL Server optimizer to avoid parallelism. If SQL Server calculates a huge cost for a query, it may choose to run the query in parallel (depending on the MAXDOP server option and query hints). When queries run in parallel, different threads may provide results before other threads, and the order of the results can be different on each run.

Without an order by clause, the query order can also depend on the query plan. Anything that affects the query plan can affect the query order. An example of this is parameter sniffing. For example, take the query select * from [Addresses] where [Country] = 'Luxembourg'. This query might use an index seek followed by a bookmark lookup to return the results, because the population of Luxembourg is fairly small compared to the number of addresses. This could result in rows ordered by a [Country] index.

Now clear the procedure cache and try 'select * from [Addresses] where [Country] = 'China'. This query might use a table scan, because the query is returning all columns, and it might be less expensive to scan the table than to do an index seek against [Country] = 'China' and then do a bookmark lookup. This query might be ordered by the physical order of the data on disk. There is no guarantee unless you specify an order by clause.

UPDATE

You provided a sample query and schema. The order by COL2, COL3, COL4 clause will guarantee that the results are ordered by those 3 columns. However, if there are duplicate values in those 3 columns, then those rows are not guaranteed to be returned in a consistent order. The order by clause would have to be amended to include the primary key if this matters to you.

Community
  • 1
  • 1
Paul Williams
  • 16,585
  • 5
  • 47
  • 82