Another way of pagination in sql server

Today, I wrote a paginating query using ranking function row_number(), with help of my team mate Neha. Initial idea was from her, and later I hijacked it to post it here. :). Poor Neha..

Ceiling function seems to be crux of it.  First 3 columns are data columns, rest are informational for paging data.

select

          *

from

(

          SELECT                                                                         

          ID                                                      --Data Columns

          ,GrandSlamLocation                          --Data Columns    

          ,GrandSlamLogin                               --Data Columns

          ,ROW_NUMBER() OVER(ORDER BY ID) [S.No]                                             

          , ceiling(ROW_NUMBER() OVER(ORDER BY ID)/ 10.00)  as Page

          ,count(*) over() as TotalRecords

          ,ceiling(count(*) over()/10.00) as TotalPages

          from

          (

                   --Base Query Start

                   select

                             ID,                                  --Data Columns

                             GrandSlamLocation,      --Data Columns    

                             GrandSlamLogin            --Data Columns

                   from  

                             Ticket

                   --Base Query End

          )A

           

)B where Page =  1

Tally table - equivalent of "select * from dual" in oracle

This sql query genrates unlimited sequential rows/virtual rows, using CTE

; WITH

Table1 AS (SELECT 1 N UNION ALL SELECT 1 N),    -- 2            ,              2 raise to power 1

Table2 AS (SELECT 1 N FROM Table1 x, Table1 y),            -- 8            ,   2 raise to power 3

Table3 AS (SELECT 1 N FROM Table2 x, Table2 y),            -- 128         ,   2 raise to power 7

Table4 AS (SELECT 1 N FROM Table3 x, Table3 y),            -- 32768        , 2 raise to power 15

Table5 AS (SELECT 1 N FROM Table4 x, Table4 y),           -- 2147483648 , 2 raise to power 31

tally as

(

                   SELECT

                             ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N                      

                   FROM

                             Table1 , Table2 , Table3, Table4 --, Table5

                             -- you can delete or add tables here to increase decrease the rows, or

)

select * from tally where n < 5000

-- you can control number of rows here also

Truncate your database log file

CREATE PROC [dbo].[PROC_TruncateLog]

AS BEGIN

      DECLARE @DBName sysname,

                  @logName sysname

      --

      SET @DBName = DB_NAME()

      --

      BACKUP LOG @DBName WITH NO_LOG

      --

      DECLARE logCursor CURSOR FOR

      SELECT name

      FROM sysfiles

      WHERE groupid = 0

      --

      OPEN logCursor

      FETCH NEXT FROM logCursor

      INTO @logName

      WHILE @@FETCH_STATUS = 0 BEGIN

            SELECT @logName = RTRIM(@logName)

            DBCC SHRINKFILE (@logName)

            FETCH NEXT FROM logCursor

            INTO @logName

      END

      CLOSE logCursor

      DEALLOCATE logCursor

END