title: Custom sorting and pagination over large tables
body: <p>I'm used to get benefits from ROW_NUMBER function in MS SQL Server scripts since 2005 version. But I noticed there is big performance disadvantage querying big tables using this function.</p>
  
  <p>Imagine table with four columns (a real table from external database has more columns, but I used only those to avoid complexity of example):</p>
  
  <pre><code>DECLARE TABLE StockItems (
    Id int PRIMARY KEY IDENTITY(1,1),
    StockNumber nvarchar(max),
    Name nvarchar(max),
    [Description] nvarchar(max))
  </code></pre>
  
  <p>I've written procedure for querying this table filled up by 200 000+ rows with following parameters:</p>
  
  <ul>
  <li>@SortExpression - name of column by which I want to sort</li>
  <li>@SortDirection - bit information (0=ascending, 1=descending)</li>
  <li>@startRowIndex - zero based index at which I want retrieve rows</li>
  <li>@maximumRows - number of rows to be retrieved</li>
  </ul>
  
  <p>Query:</p>
  
  <pre><code>SELECT sortedItems.Id
      ,si.StockNumber
      ,si.Name
      ,si.Description
  FROM (SELECT s.Id
           ,CASE WHEN @SortDirection=1 THEN
              CASE
                 WHEN CHARINDEX('Name',@SortExpression)=1 THEN
                   ROW_NUMBER() OVER (ORDER by s.Name DESC)
                 WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN
                   ROW_NUMBER() OVER (ORDER by s.StockNumber DESC)
              ELSE ROW_NUMBER() OVER (ORDER by s.StockNumber DESC)
              END
            ELSE    
              CASE
                 WHEN CHARINDEX('Name',@SortExpression)=1 THEN
                    ROW_NUMBER() OVER (ORDER by s.Name ASC)
                 WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN
                    ROW_NUMBER() OVER (ORDER by s.StockNumber ASC)
              ELSE  ROW_NUMBER() OVER (ORDER by s.StockNumber ASC)
              END
            END AS RowNo
         FROM stockItems s
       ) as sortedItems
  INNER JOIN StockItems si ON sortedItems.Id=si.Id
  ORDER BY sortedItems.RowNo
  </code></pre>
  
  <p>In situation when number of rows is growing rapidly, ROW_NUMBER became ineffective, because must sort all rows.</p>
  
  <p>Please can you help me to avoid this performance disadvantage and speed up the query?</p>