title: Pagination over union with SQL Server
body: <p>This is my query:</p>
  
  <pre><code>select * from (
      select u.id, 'u' as [type], u.firstName, u.lastName, c.name as companyName,
          u.lastName + u.firstName + isNull(c.name, '') as sortName
      from users as u
          left outer join companies as c on c.id = u.company_id
  
      union all
  
      select id, 'c' as [type], null as firstName, null as lastName, name as 
          companyName, name as sortName
      from companies
  ) as result
  where sortName like '%a%'
  order by sortName
  OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
  </code></pre>
  
  <p>It returns 10 rows from the tables <code>users</code> and <code>companies</code> which matches the search string "a". With the offset/fetch I can easily do pagination in my application. But what I still need to know, is how many rows are in the database which matches the search string "a" without the fetch limit. </p>
  
  <p>My first approach was a <code>count(*)</code> over the result query, but this doesn't work (because of the union I guess).</p>