Pagination in two tables in SQL Server
<p>I have two tables, </p>

<ul>
<li><code>Orders</code> - this is small, typically up to 50 thousands records</li>
<li><code>OrdersArchive</code> - this one is normal, about 80 millions records</li>
</ul>

<p>This situation might happens:</p>

<p>the order might have on of those values for <code>status</code>:</p>

<ol>
<li>'created'</li>
<li>'processing'</li>
<li>'finished'</li>
</ol>

<p>The finished orders from <code>Orders</code> are periodically moved to <code>OrdersArchive</code>. </p>

<p>In other words, <code>Orders</code> might contain orders with status <code>created</code>, <code>processing</code> or <code>finished</code>. <code>OrdersArchive</code> contains only orders with a status of <code>finished</code>.</p>

<p>The result has to be sorted in this order 'created', 'processing', 'finished'</p>

<p>I need a query in this two tables which supports pagination.</p>

<p>What is the best way to do it? (so fast as possible)</p>

<p>A pagination might be any type</p>

<p>I mean like:</p>

<ul>
<li>the classical pagination with PageNumber and CountOfRowsPerPage.</li>
<li>'lazy' pagination with count of orders after the specific Order.</li>
</ul>