Ordering the results of a query from a temp table.

Question

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.

Here's what I currently have:

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

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).

As I see it, I have a couple of options:

1. Add a second order by clause to the select from the temp table.
2. Move the order by clause to the second select and let the first select be unordered.
3. Create the temporary table with a primary key column to force the ordering of the temp table.

What is the best way to do this?

Answer 1

Personally I would select out the data in the order you want to eventually have it. So in your first select, have your order by. That way it can take advantage of any existing indexes and access paths.

Answer 2

Move the order by from the first select to the second select.

A database isn't a spreadsheet. You don't put the data into a table in a particular order.

Just make sure you order it properly when you get it back out.

Answer 3

Use number 2. Just because you have a primary key on the table does not mean that the result set from select statement will be ordered (even if what you see actually is).

There's no need to order the data when putting it in the temp table, so take that one out. You'll get the same @@ROWCOUNT value either way.

So do this:

SELECT --primary key column
INTO #tempTable 
FROM --some table 
--some filter clause


SELECT @@ROWCOUNT

SELECT --the primary key column and some others
FROM #tempTable
JOIN -- some table
ORDER BY --primary key column

DROP TABLE #tempTable