TempTable SQL

The first one I would comment on is the TempTable method. This is actually a widely proposed solution and I encountered it several times. Here is another article that describes it along with the explanation and a sample how to use custom paging with the DataGrid: 

CREATE TABLE #Temp (
    ID int IDENTITY PRIMARY KEY,
    PK  /* here goes PK type */
)

INSERT INTO #Temp SELECT PK FROM Table ORDER BY SortColumn

SELECT ... FROM Table JOIN #Temp temp ON Table.PK = temp.PK ORDER BY temp.ID 
WHERE ID > @StartRow AND ID < @EndRow

RowCount

The base logic of this method relies on the SQL SET ROWCOUNT expression to both skip the unwanted rows and fetch the desired ones:

DECLARE @Sort /* the type of the sorting column */
SET ROWCOUNT @StartRow
SELECT @Sort = SortColumn FROM Table ORDER BY SortColumn
SET ROWCOUNT @PageSize
SELECT ... FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn