CODEWITHSUNDEEP
sqlsql-server
user369182
user369182

Reputation: 2085

does result of ORDER BY is always same (SQL Server 2005,2008)?

Suppose I have a table Item (Id int Primary Key, Number INT) having records

Id    Number
100     3
200     3

Now I am running a query

SELECT I.Id,ROW_NUMBER()OVER(ORDER BY I.Number) RN
FROM Item I

in my machine result set is

Id   RN
100   1
200   2

Now my question is does this result set will be on all machine, OR in some machine it may change as

Id   RN
200   1
100   2

Any help is welcome

Upvotes: 1

Views: 322

Answers (3)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131403

You do not specify any ordering for the final result so you don't have any guarantees on how the results will appear.

To order by row number (RN) you should add an ORDER BY RN clause:

SELECT I.Id,ROW_NUMBER()OVER(ORDER BY I.Number) RN 
FROM Item I
ORDER BY RN

The server doesn't order the results in any specific way unless you request it. This means that the results will appear in the order they are processed by the server.

The server creates an execution plan for each query that may sort data to speed up processing and use different join techniques that may also affect ordering. The form of the execution plan depends on the existence of indexing and the statistics of the tables involved. Finally, the server may use a parallel execution plan in which case partial result sets from different cores are merged in the end.

You will probably never notice this with a simple query, but result order may change even for the same server as data and statistics change with time, resulting in different execution plans.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

The result set may be any of:

Id        RN
100        1
200        2

or,

Id        RN
200        1
100        2

or,

Id        RN
100        2
200        1

or,

Id        RN
200        2
100        1

The rules of SQL are very simple - if you want a specific ordering, you have to ask for it. If you incompletely specify an ordering, then the system is free to arrange rows in any order it finds most convenient, so long as it conforms to the specification you have given it.

If you want to force a specific ordering, specify it completely. E.g. if you want the first result always, specify ORDER BY I.Number,I.Id and have that both within the ROW_NUMBER specification and as the ORDER BY clause.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453298

As well as the undefined order of results mentioned by Panagiotis

The result of ROW_NUMBER is undeterministic in the event of ties. You would need to add a tie breaker of a unique column to the ORDER BY so that the two rows with Number=3 have a deterministic numbering applied.

Assuming Id is unique the following would be deterministic

SELECT I.Id,ROW_NUMBER()OVER(ORDER BY I.Number,I.Id) RN
FROM Item I
ORDER BY RN

Upvotes: 2

Related Questions