Normalmente nelle applicazioni web i dati vengono presentati paginati. Su grosse tabelle la paginazione deve assolutamente essere fatta lato server per evitare di trasferire grosse quantità di dati per poi visualizzarne un piccolo sottoinsieme.

SQL Server 2012 mette a disposizione le nuove keyword OFFSET e FETCH per gestire la paginazione.

Questo è un esempio dove nella variabile @startIndex indico il record da cui partire e in @pageSize il numero di record da ritornare.
DECLARE @startIndex int = 0;  -- numero di riga da cui INIZARE (0=primo record)
DECLARE @pageSize int = 10;  -- numero di RIGHE da ritornare

SELECT * 
FROM [AdventureWorks2012].[Person].[Person] 
ORDER BY [FirstName]
OFFSET @startIndex  ROWS 
FETCH NEXT @pageSize ROWS ONLY;
L'ORDER BY è obbligatorio

Se invece devo ragionare in termini di numero pagina e non numero record, questo è l'esempio:
DECLARE @pageNumber int = 1;  -- numero pagina da 1 a N
DECLARE @pageSize int = 10;

IF @pageNumber <=0
  SET @pageNumber =1

-- calcolo lo startIndex in funzione del numero pagina e degli elementi presenti nella pagina
DECLARE @startIndex int = (@pageNumber -1)*@pageSize ;

SELECT *
FROM [AdventureWorks2012].[Person].[Person] 
ORDER BY [FirstName]
OFFSET @startIndex  ROWS 
FETCH NEXT @pageSize ROWS ONLY;

il tutto può essere incluso in una store procedure aggiungendo anche un parametro per il sort:
CREATE PROCEDURE SpuTodosSearch
(
  @pageNumber int = 1,
  @pageSize int = 10,
  @sort nvarchar(50) = 'ID DESC'

)
AS
BEGIN
  SET NOCOUNT ON
  IF @pageNumber <=0
    SET @pageNumber =1

  -- calcolo lo startIndex in funzione del numero pagina e degli elementi presenti nella pagina
  DECLARE @startIndex int = (@pageNumber -1)*@pageSize ;

  SELECT *
  FROM [AdventureWorks2012].[Person].[Person] 
  ORDER BY 
	CASE WHEN @sort='FirstName' THEN [FirstName] END,
	CASE WHEN @sort='FirstName desc' THEN [FirstName] END DESC,
    CASE WHEN @sort='ModifiedDate' THEN [ModifiedDate] END,
    CASE WHEN @sort='ModifiedDate desc' THEN [ModifiedDate] END DESC
  OFFSET @startIndex  ROWS 
  FETCH NEXT @pageSize ROWS ONLY;
END
che può essere richiamata con
EXEC @return_value = [dbo].[uspTodosSearch]
     @pageNumber = 1,
     @pageSize = 10,
     @sort = N'modifieddate desc'