title: What happens to data when running a query against a federated SQL Server?
body: <p>SQL Azure give you an option to setup federated SQL servers - e.g. essentially partition your data across multiple servers by some key.  However, when you run <code>select * from order</code>, it puts all the data back together for you and the entire process is transparent to your app.</p>
  
  <p>Now, let's say I have a stored procedure that makes liberal use of temp tables.  So in a situation like this:</p>
  
  <pre><code>-- query uses data from each partitioned server
  select tbl1.Column1, tbl2.Column2
  into #tmpTable
  from tbl1
  join tbl2.id = tbl1.id
  where tbl2.Column3 = '234'
  
  -- also uses data from each partitioned server
  select #tmpTable.*
  from tbl3
  join tbl3.fkey = #tmpTable.Column1
  </code></pre>
  
  <p>In this scenario, does the data flow each time from one server to the main one?  Where is the temp table actually stored?  All on the main box, or split up between federated ones?</p>