CODEWITHSUNDEEP
sql-servert-sql
kamil.dice
kamil.dice

Reputation: 11

Top 1 without ordering

I encountered a problem with query result. The result wasn't same on the live environment and the test environment.

Table A

id, name
1,ValueA1

Table B

Id, name
1,ValueB1
2,ValueB2
3,ValueB3
4,ValueB4

On the test environment the result was ValueB1, on the live environment was ValueB2

After the quick investigation I found that the problem is that the procedure was using top 1 without ordering in conjunction. Ok, this is clear for everybody that if we have conjunction we have to add order by clause if we use top 1.

But I tried to explain the problem to a not technical person.

So I wrote a simple while loop with a 1 000 000 iteration. Inside I clear execution plans:

DBCC FREEPROCCACHE

and

sp_recompile 'ProcedureName'

and next I was executing procedure.

Unfortunately the result was always the same. On the both environments. (on the test: ValueB1, on the live: ValueB2)

I tried changing calling procedure to just copy the content of the procedure. The results still exactly same as in previous case.

I tried to write a very simple conjunction query with top 1 without ordering. the result that time was same on both servers: ValueB1 in all 1M iterations.

The db procedure is pretty complicated it use CTE and it join to the table B multiple times.

I am very frustrated that I didn't manage to show the problem to the non technical person.

Can anyone explain what I did wrong or how it should have been done?

Upvotes: 1

Views: 155

Answers (1)

CodeMonkey
CodeMonkey

Reputation: 3668

You can't.

If you don't specify an order by clause the engine is free to deliver the result to you as it sees fit.

In practice that mostly means its ordered by the clustered index, but it don't have to be!

Upvotes: 1

Related Questions