How to preserve order of temp table rows when inner joined with another table?

Posted by Triynko on Stack Overflow See other posts from Stack Overflow or by Triynko
Published on 2010-05-20T01:38:58Z Indexed on 2010/05/20 1:50 UTC
Read the original article Hit count: 290

Filed under:
|
|

Does an SQL Server "join" preserve any kind of row order consistently (i.e. that of the left table or that of the right table)?

Psuedocode:

create table #p (personid bigint);
foreach (id in personid_list)
    insert into #p (personid) values (id)
select id from users inner join #p on users.personid = #p.id

Suppose I have a list of IDs that correspond to person entries. Each of those IDs may correspond to zero or more user accounts (since each person can have multiple accounts).

To quickly select columns from the users table, I populate a temp table with person ids, then inner join it with the users table.

I'm looking for an efficient way to ensure that the order of the results in the join matches the order of the ids as they were inserted into the temp table, so that the user list that's returned is in the same order as the person list as it was entered.

I've considered the following alternatives:

  1. using "#p inner join users", in case the left table's order is preserved
  2. using "#p left join users where id is not null", in case a left join preserves order and the inner join doesn't
  3. using "create table (rownum int, personid bigint)", inserting an incrementing row number as the temp table is populated, so the results can be ordered by rownum in the join
  4. using an SQL Server equivalent of the "order by order of [tablename]" clause available in DB2

I'm currently using option 3, and it works... but I hate the idea of using an order by clause for something that's already ordered. I just don't know if the temp table preserves the order in which the rows were inserted or how the join operates and what order the results come out in.

© Stack Overflow or respective owner

Related posts about sql-server-2008

Related posts about joins