Paging at the database in MSSQL

Lacking limit and offset, modern MSSQL can still perform acceptable paging calls, it's just a bit more syntax to make it work. Here's a basic syntax run down you can start with:

WITH paging AS (
    SELECT
        --return record count so we know how many pages are possible in resultset (redundant, I know)
        COUNT(*) OVER (PARTITION BY '') AS totalRecords

        --get row number, this allows us to do paging (The ORDER BY in this statement replaces the SELECT's ORDER BY)
        ,ROW_NUMBER() OVER (
                ORDER BY A, B
        ) AS ROW_NUM

        ,TABLE.*
    FROM
        dbo.Table_Name TABLE
)
SELECT
    *
FROM
    paging
WHERE
    (
    @PageSize IS NULL
    OR
    ROW_NUM
        BETWEEN (@PageIndex - 1) * @PageSize + 1
        AND @PageIndex * @PageSize
    )
ORDER BY
    ROW_NUM
WITH paging AS (
    SELECT
        --return record count so we know how many pages are possible in resultset (redundant, I know)
        COUNT(*) OVER (PARTITION BY '') AS totalRecords

        --get row number, this allows us to do paging (The ORDER BY in this statement replaces the SELECT's ORDER BY)
        ,ROW_NUMBER() OVER (
                ORDER BY A, B
        ) AS ROW_NUM

        ,TABLE.*
    FROM
        dbo.Table_Name TABLE
)
SELECT
    *
FROM
    paging
WHERE
    (
    @PageSize IS NULL
    OR
    ROW_NUM
        BETWEEN (@PageIndex - 1) * @PageSize + 1
        AND @PageIndex * @PageSize
    )
ORDER BY
    ROW_NUM