title: Ordering the results of a query from a temp table
body: <p>I have a SQL query where I am going to be transferring a fair amount of response data down the wire, but I want to get the total rowcount as quickly as possible to facilitate binding in the UI.  Basically I need to get a snapshot of all of the rows that meet a certain criteria, and then be able to page through all of the resulting rows.</p>
  
  <p>Here's what I currently have:</p>
  
  <pre>
  SELECT --primary key column
  INTO #tempTable 
  FROM --some table 
  --some filter clause
  ORDER BY --primary key column
  
  SELECT @@ROWCOUNT
  
  SELECT --the primary key column and some others
  FROM #tempTable
  JOIN -- some table
  
  DROP TABLE #tempTable
  </pre>
  
  <p>Every once in a while, the query results end up out of order (presumably because I am doing an unordered select from the temp table).</p>
  
  <p>As I see it, I have a couple of options:</p>
  
  <ol>
  <li>Add a second order by clause to the select from the temp table.</li>
  <li>Move the order by clause to the second select and let the first select be unordered.</li>
  <li>Create the temporary table with a primary key column to force the ordering of the temp table.</li>
  </ol>
  
  <p>What is the best way to do this?</p>