title: how does SELECT TOP works when no order by is specified?
body: <p>The <a href="http://msdn.microsoft.com/en-us/library/ms189463.aspx" rel="noreferrer">msdn documentation</a> says that when we write </p>
  
  <pre><code>SELECT TOP(N) ..... ORDER BY [COLUMN]
  </code></pre>
  
  <p>We get top(n) rows that are sorted by <code>column</code> (<code>asc</code> or <code>desc</code> depending on what we choose)</p>
  
  <p>But if we don't specify any order by, msdn says <code>random</code> as <code>Gail Erickson</code> pointed out <a href="http://msdn.microsoft.com/en-us/library/ms189463.aspx" rel="noreferrer">here</a>. As he points out it should be <code>unspecified</code> rather then <code>random</code>. But as <code>
  Thomas Lee</code> points out there that </p>
  
  <blockquote>
    <p>When TOP is used in conjunction with the ORDER BY clause, the result
    set is limited to the first N number of ordered rows; otherwise, it
    returns the first N number of rows ramdom </p>
  </blockquote>
  
  <p>So, I ran this query on a table that doesn't have any indexes, first I ran this..</p>
  
  <pre><code>SELECT *
  FROM
      sys.objects so
  WHERE
      so.object_id NOT IN (SELECT si.object_id
                           FROM
                               sys.index_columns si)
      AND so.type_desc = N'USER_TABLE'
  </code></pre>
  
  <p>And then in one of those tables, (in fact I tried the query below in all of those tables returned by above query) and <strong>I always got the same rows.</strong></p>
  
  <pre><code>SELECT TOP (2) *
  FROM
      MstConfigSettings
  </code></pre>
  
  <p>This always returned the same 2 rows, and same is true for all other tables returned by query 1. Now the execution plans shows 3 steps..</p>
  
  <p><img src="https://i.stack.imgur.com/BZC36.png" alt="enter image description here"></p>
  
  <p>As you can see there is no index look up, it's just a pure table scan, and</p>
  
  <p><img src="https://i.stack.imgur.com/2eH36.png" alt="enter image description here"></p>
  
  <p>The <code>Top</code> shows actual no of rows to be 2, and so does the <code>Table Scan</code>; Which is not the case (there I many rows).</p>
  
  <p>But when I run something like</p>
  
  <pre><code>SELECT TOP (2) *
  FROM
      MstConfigSettings
  ORDER BY
      DefaultItemId
  </code></pre>
  
  <p>The execution plan shows </p>
  
  <p><img src="https://i.stack.imgur.com/1rHrj.png" alt="enter image description here"></p>
  
  <p>and</p>
  
  <p><img src="https://i.stack.imgur.com/44TJB.png" alt="enter image description here"></p>
  
  <p>So, when I don't apply <code>ORDER BY</code> the steps are different (there is no sort). But the question is how does this <code>TOP</code> works when there is no <code>Sort</code> and why and how does it <strong>always</strong> gives the same result?</p>