SQL Server Paging - The Holy Grail

	DECLARE @startRow INT
	SET @startrow = 50
	;WITH cols
	AS
	(
	SELECT table_name, column_name,
	ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq,
	ROW_NUMBER() OVER(ORDER BY table_name DESC, column_name desc) AS totrows
	FROM [INFORMATION_SCHEMA].columns
	)
	SELECT table_name, column_name, totrows + seq -1 as TotRows
	FROM cols
	WHERE seq BETWEEN @startRow AND @startRow + 49
	ORDERBY seq

view raw Paging.sql hosted with ❤ by GitHub

More info on the above see SqlServerCentral

Another version:

	CREATE PROCEDURE LogRead
	@PageNumber INT = 1,
	@PageSize INT = 100
	AS
	BEGIN
	SET NOCOUNT ON;
	-- Sanity check
	IF (@PageNumber <= 0)
	SET @PageNumber = 1;
	WITH pg AS
	(
	SELECT Id
	FROM Logs
	ORDER BY Id DESC
	OFFSET @PageSize * (@PageNumber - 1) ROWS
	FETCH NEXT @PageSize ROWS ONLY
	)
	SELECT Added, LogLevel, LogText
	FROM Logs L
	INNER JOIN pg
	ON pg.Id = L.Id
	ORDER BY L.Id;
	END;

view raw LogRead.sql hosted with ❤ by GitHub