title: Query fast, but when in a VIEW, it's slow - due to ROW_NUMBER
body: <p>I have a query that when run, it's result is instant.</p>
  
  <p>However, I paste the exact same query into VIEW, and the results take 6 seconds to reply. </p>
  
  <p>For example,</p>
  
  <pre><code>SELECT ... FROM MyTables WHERE PersonID = x
  </code></pre>
  
  <p>runs fast.</p>
  
  <p>But create a view with:</p>
  
  <pre><code>SELECT ... FROM MyTables 
  </code></pre>
  
  <p>And then call the view:</p>
  
  <pre><code>SELECT * FROM MyView WHERE PersonID = x
  </code></pre>
  
  <p>And it runs slow.</p>
  
  <p>Actual Query:</p>
  
  <pre><code>select ROW_NUMBER() over(partition by h.Id order by h.[SysStartTime]) as VersionNUmber,
        h.Id,
        fac.HIC,
        ... plus 18 other columns from the joined tables.
  
  from   [hist].[A_View] as h
  inner join [dbo].[Facilities] as fac
        on fac.Id = h.FacilityId
  inner join ref.FormStatus as r_fs
        on r_fs.Id = h.FormStatusId
  inner join TableA as data
        on data.Id = h.dataId
  inner join Consultants as c
        on c.Id = h.ConsultantId
  inner join dbo.Specialties spec
        on spec.Id = h.SpecialtyId
  inner join dbo.Users modifieduser
        on modifieduser.Id = h.ModifiedByUserId
  left join ref.ARefTable as r_uc
        on r_uc.Id = h.refId
  cross apply [dbo].[getPersonUrn](h.PersonId, h.AnotherIdId) as PersonURN
  </code></pre>
  
  <p>(Note, I am changing some table names and columns as we're in quite a confidential area)</p>
  
  <p>I notice that 97% of the time, it's in a Sort (Top N Sort), when executing the view. In the query, that 34%, but the plans are completely different.</p>
  
  <p>I suspected parameter sniffing, but don't think that's an issue with Views.</p>
  
  <p>I've actually just 'fixed' it, but no idea why.</p>
  
  <p>My first column in my select is a ROW_NUMBER.</p>
  
  <pre><code>SELECT ROW_NUMBER() over(partition by h.Id order by h.[SysStartTime]) as` VersionNumber,
  </code></pre>
  
  <p>Removing that, and I get instant results.
  Not sure why, as both the columns I order by and partition by, are already in the result set.</p>