CODEWITHSUNDEEP
sqlsql-server
itaigitt
itaigitt

Reputation: 27

Result order of a select

I have an interesting question: I have a function that get a comma separated string of integers.

Here are 2 executions:

select ID from dbo.udf_NumList2table ('124,126,132,180,176')
select RP.ID, My.ID 
from dbo.udf_NumList2table ('124,126,132,180,176') RP join MyTable My
        on RP.ID=My.ID

In the first execution the order of the results is like the order of the numbers in the string. In the second execution the order of the results is ascending (176 before 180).

Why? I guess that the PK on column ID in MyTable is the reason - the SQL prefer to run on the key.

Do I right?

Upvotes: 1

Views: 59

Answers (1)

mmmmmm
mmmmmm

Reputation: 32681

Select guarantees no ordering of the results, so your results are not even guaranteed to be the same each time you run, although in your case as the data is so small they will be the same. The order will be the most convienient to the optimizer so your guesses might well be correct here.

On higher volumes of data the query can be split across different threads of execution and so give a more confused order and depending on workload on the server could differ between runs.

To get a consistent order you have to use an ORDER BY clause.

Upvotes: 2

Related Questions