is it possible to change database order on purpose?

Question

is it possible to change database order on purpose?

Stephen Hulbert 41

SQL is famously non-deterministic.

That is, unless explicitly specified, you cannot guarantee the returned sort order.

We have changes to T-SQL specifications provided by the client.

Very often these require a join to a fresh table.

About as often the join columns do not uniquely identify the new record and so there is duplication.

A solution to this is to give up and just number the rows using ROW_NUMBER, taking the first one.

Unfortunately unless the PARTITION and ORDER BY uniquely identify the rows, it's not guaranteed which rows will be numbered 1.

Since SQL is mostly deterministic, though, and seems to be in the same environment, it's difficult to illustrate that this is a problem.

The differences only really manifest when run, say, from SSIS against a different server as opposed to interactively in SSMS.

Which is somewhat trying.

So, my question is, what determines database order, and is there anything i can do to force it to change.

 use master

Guesses at boilerplate answers not reading the question: how do i change the order of columns/anything about "order by"/discussion of ROW_NUMBER/discussion of PARTITION

Accepted answer

1 additional answer

Your answer

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

So, my question is, what determines database order, and is there anything i can do to force it to change.

Your ORDER BY clauses, and if there isn't any, or the ORDER BY is not unique, there is nothing that determines the order. You get what you get. Yes, if you are doing something like a Clustered Index Scan, you are likely to get rows in the order they are physically sorted. Then again, if you join in on a merry-go-round scan, you could get something else. If there is parallelism, the result could be from any thread.

Throw in sorting on something non-unique, and all bets are off.

If you want a deterministic result, you need to write deterministic queries which only have one possible result.

Stephen Hulbert 41 Reputation points

2023-09-12T08:16:20.6733333+00:00

thanks for reading the question.

i asked because a colleague insists that the code is adequate, even when i point out that although he can run it a hundred times in DEV with the same result, when pushed to UAT it produces (very slightly) different results. i want a process that when run forces the database order to change. i.e. in one SSMS session: run script; run process; run script with different results. i mean, for now i can follow him around adding columns to his ORDER BY clauses but that's hardly a solution.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-09-12T20:57:58.58+00:00

I don't really follow what your aim is. Do you want to find a way to show your colleague that the result can be different due to the code being non-deterministic? Or are you looking for some magic bullet that always give you the same order, even if the query is non-deterministic?

The latter case is a non-starter, there is no such magic bullet.

For the first situation, there are various things to try, exactly what depends on the query. There are hints to control available join operators, you can force indexes, you can manipulate parallelism.
Stephen Hulbert 41 Reputation points

2023-09-13T15:45:36.03+00:00

yes, it's the first one. adding to the order by in the ROW_NUMBER clause fixes the problem. i guess i'll have to try harder at managing across.

many thanks.

Answer 2

Anonymous

Hi @Stephen Hulbert

The order by statement can determine the ordering.

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver16

ROW_NUMBER is to create a new column and sort it in a new column.

I think this requires a case-by-case analysis of a specific issue.

Best regards,

Percy Tang

Share via

is it possible to change database order on purpose?

1 additional answer

Your answer