title: Use of SET ROWCOUNT in SQL Server - Limiting result set
body: <p>I have a sql statement that consists of multiple SELECT statements. I want to limit the total number of rows coming back to let's say 1000 rows. I thought that using the SET ROWCOUNT 1000 directive would do this...but it does not. For example:</p>
  
  <pre><code>SET ROWCOUNT 1000
  
  select orderId from TableA
  
  select name from TableB
  </code></pre>
  
  <p>My initial thought was that SET ROWCOUNT would apply to the <strong>entire</strong> batch, not the individual statements within it. The behavior I'm seeing is it will limit the first select to 1000 and then the second one to 1000 for a total of 2000 rows returned. Is there any way to have the 1000  limit applied to the batch as a whole?</p>