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

Answer 1

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.