how the top 1 works in sql server 2012

Question

I have a table with the following data

  IF OBJECT_ID('TEMPDB.DBO.#t1', 'U') IS NOT NULL
            DROP TABLE #t1;
CREATE TABLE #t1
    ([c1] varchar(100), [c2] varchar(10), [c3] varchar(100), [c4] varchar(100))
;

INSERT INTO #t1
    ([c1], [c2], [c3], [c4])
VALUES
    (93, '60-1.1.1.', 60, 3),
    (104, '60-1.2.1.', 60, 3),
    (102, '60-1.1.2.', 60, 3),
    (101, '60-1.2.2.', 60, 3),
    (92, '60-1.1.3.', 60, 3),
    (96, '60-1.2.3.', 60, 3),
    (103, '60-1.1.4.', 60, 3),
    (94, '60-1.2.4.', 60, 3),
    (105, '60-1.2.5.', 60, 3),
    (97, '60-1.2.6.', 60, 3),
    (99, '60-1.2.7.', 60, 3),
    (100, '60-1.2.8.', 60, 3),
    (98, '60-1.2.9.', 60, 3),
    (95, '60-1.2.10.', 60, 3),
    (91, '60-1.2.11.', 60, 3)
;
select * from #t1

the result of the table is as follows

select * from #t1 order by c3,c4

Now i ran the following query i got the result as expected

select  Cast(c4 AS VARCHAR(2)) + '~'+ Cast(c1 AS VARCHAR(100)) AS c5,* from #t1

the result of the above query was as follows

Now i have used top 1 to fetch a record i have written the code as follows

select top 1 Cast(c4 AS VARCHAR(2)) + '~'+ Cast(c1 AS VARCHAR(100)) AS c5,* from #t1

the result of the above query was as follows

Now i have used top with order by clause then i got the following result

select top 1 Cast(c4 AS VARCHAR(2)) + '~'+ Cast(c1 AS VARCHAR(100)) AS c5,*
 from #t1 order by c3,c4

Question: why there was a change in the results of last 2 queries , as i would like to expect the same result?

let me ask you in this way:

When i execute the top 1 query without order by i got 93 record values, so when i execute top 1 with order by columns i expect the same result. in the query as per my assumption there was no impact of order by caluse

Thanks in advance

EDIT 1

even when i executed 100 time results were same

EDIT 2

Server 1

Actually i have created a main table and inserted the above records . run the last two queries in two different sessions, the result was same

Server 2

Done the above (same steps in server 1) in second server. run the last two top 1 queries, results were same in two sessions.

the results seems to be same in both servers and sessions.

Results

Execution Plan

the first query doesn't use `order by` with `top`..and the second one does..however as there are ties in c3,c4 values, unless you specify a different column in `order by`, the results will be arbitrary. — Vamsi Prabhala, Jun 22 '17 at 17:19
@vkp thanks for the quick reply, but the results were same after executing 100 times — Smart003, Jun 22 '17 at 17:23
the result of 1000th run *may be* different..you can't rely on that if you need *consistent* results. — Vamsi Prabhala, Jun 22 '17 at 17:25
No boss i checked in two different sessions, actually i created a main tables then i see the results nut always the same — Smart003, Jun 22 '17 at 17:28
fwiw, posting images of your sample data and results is considered very rude here. It helps us (and therefore you) if you provide these as text we can copy/paste into our answers. — Joel Coehoorn, Jun 22 '17 at 18:01
From the execution plans, the internals of the Top N Sort algorithm cause the discrepancy. It's quite likely that at the 2nd row it realized there would be no other rows earlier in the sort and returned that row. Analyze the Table and you might get a different result again, when it realizes both columns have only 1 value. — Cyrus, Jun 22 '17 at 19:03

Joel Coehoorn · Answer 1 · 2017-06-22T20:33:40.007

If you don't have an ORDER BY clause, Sql Server is allowed to present the results in any order it wants to. That means you can get different results than you expect. Even if you have an ORDER BY clause, if some records in the result set tie for the same position, Sql Server may use whichever order it wants for the records that tie.

Typically, when an order is not specified, Sql Server will provide results in whatever order is fastest. That means over time, results will tend to be somewhat consistent, relying on things like a primary key order or index order. In your simple example, it would be surprising if that "base" order ever changed.

It's important to remember, though, that this ordering is not at all guaranteed. In a real production environment, the results can change between executions if you don't specify the order. There are a number of reasons this can happen, but one basic example is an optimization where two queries might piggyback on the same index or table seek, with the 2nd query picking up on the seek in the middle of the first. Another reason is statistics or rowcount on a table change such that Sql Server decides to use (or not use) an index differently than before.

So if you actually care about getting a specific record when using selectors like TOP, you really should use ORDER BY, too, and make sure you are specific enough to be unambiguous.

For this specific data and query sample, you have one sample without an ORDER BY clause and one sample with an ORDER BY clause, but the clause for the 2nd sample orders only by columns c3 and c4. Those columns have the same values for every record. This means Sql Server is still free to use whichever order is most convenient, because everything ties.

However, that doesn't mean Sql Server will use the same order in the second query as in the first. Adding the ORDER BY clause forced Sql Server to at least look at and evaluate the result set before it could know which record belonged in which place, and that very process can alter the arrangement of the result in memory, making a whole new order appear to be most convenient.

So we see if you care about the results, you need not only an ORDER BY clause, but that this clause must be sufficiently selective to guarantee the order you want. If you want a specific c1 value to show, you should include c1 in your ORDER BY clause.

Thank you boss, great explanation, I have used the top with order by clause only in the main environment, but our issue was to display 93 but 104 was displaying i don't know the reason behind it. can you plz explain that one, in the above table i order by columns were same still values differ — Smart003, Jun 22 '17 at 17:39
There columns are string/varchar type, not a number/int type. When ordering, the columns don't know there are number values, and does a text comparison. In that context, `1` comes before `9`, and so `104` comes before `93`. You get 93 in the unordered example because sql server look at the heap order (the order in which the record were inserted) and finds the 93 first, but 104 in the ordered example because you forced it to the top. — Joel Coehoorn, Jun 22 '17 at 17:40
but the ordering was done to other columns (c3,c4), as per you comment are you saying the selection columns will have an impact on the result in query having top 1 — Smart003, Jun 22 '17 at 17:43
I see I misread the columns in the order by, and the c3 and c4 values are the same. Nevertheless, including an ORDER BY clause forces Sql Server to go through the all the records in the result set and arrange them. It's going to use the same process for this every time, which will _tend to_ give the same order every time (you can still have variance in the case of a tie, as we see here) within that query, but not necessarily the same order as you'd see without the `ORDER BY`. You probably want to include c1 in your ORDER BY clause. — Joel Coehoorn, Jun 22 '17 at 17:47
So as per my assumption the result should be 93 instead of 104.. can you pleas have a look on the updated question — Smart003, Jun 22 '17 at 17:51
@Smart003 That is not true. c3 and c4 both have the same values in both records: (60, and 3). Sql Server is still allowed to use any order it wants _for both queries_, because the ORDER BY clause is not specific enough. Since the records tie, Sql Server can put whichever record is more convenient at the top. — Joel Coehoorn, Jun 22 '17 at 17:54
I've read it, doesn't change my answer. Without a sufficiently specific ORDER BY, SQL Server is allowed to use whatever order it wants, and offers no guarantees. — Joel Coehoorn, Jun 22 '17 at 18:23

sschmitz · Answer 2 · 2017-06-22T17:54:32.983

0

Without a proper order by clause you are going to continue getting unexpected results due to tables not necessarily being read in any order. The order by statement you used in the last query emphasizes this problem. Since all of the data has the same c3, c4 values they all have an equal priority in being returned. This means the order of the values returned is unspecified. I recommend checking out this post to learn more.

edited Jun 22 '17 at 17:54

answered Jun 22 '17 at 17:24

sschmitz

432
5
16

then why i got the same results even executing in different sessions – Smart003 Jun 22 '17 at 17:29
A relational database has no natural order and although it may appear that there is based on many tests, as @vkp said above it is not necessarily consistent. Check out this answer to a very similar question: https://stackoverflow.com/a/20050403/2461717 – sschmitz Jun 22 '17 at 17:33
The word "random" is this answer is incorrect and unnecessarily confusing. The order of the values is unspecified, not random. The query optimiser won't shuffle around the rows just for the hell of it, so as long as the same query compiles to the same query execution plan, the order of the results will not change. But seemingly trivial changes can lead to a different query execution plan with a different order. – Jun 22 '17 at 17:37
Thank you, I edited my answer to use unspecified instead of random. – sschmitz Jun 22 '17 at 17:54

how the top 1 works in sql server 2012

2 Answers2