New way of implementing Pagination in SQL Server 2012

Pagination is common requirement for any application, where we pull data in chunks depending upon the page number and size of page. Here in this article we will see how we can achieve pagination through Server 2012, now it is very easy to use and implement paging in SQL Server 2012. We only need to know these keywords (OFFSET, FETCH NEXT) with Order By Clause and we this we can start our pagination request.

Lets start our demo, here we are declaring a table Employee with three fields.

create table Employees (

empid int NOT NULL,

empname varchar(25) NOT NULL,

salary decimal(15,2)

)

Command(s) completed successfully.

Lets insert few data in the table.

declare @i int = 0

while @i < 10000

begin

insert into Employees

select round(rand() * 10000,0),'abc' + cast(round(rand()*10,0) as varchar), rand()*1000

set @i = @i + 1

end

Now since our table is ready, its time to enter implement pagination,

declare @offset int = 0

declare @next int = 5

select * from Employees  order by empid asc

OFFSET @offset ROWS

FETCH NEXT @next ROWS ONLY

empid       empname                   salary

----------- ------------------------- ---------

1           abc9                      364.48

2           abc8                      14.74

3           abc10                     618.69

4           abc10                     852.50

5           abc6                      788.89

OFFSET: The query excludes the number of records we mentioned in OFFSET. It says how many records we need to exclude

FETCH NEXT: It says how many records you want to fetch from the query.

In our example we use OFFSET as 0, that means we are not interest in skipping any row and FETCH NEXT is 5 means first five records, we are interest in.

That’s all one need to do to implement Pagination in SQL Server 2012.

SQL Server with a Tea glass makes a perfect combination to deal with darkness :)

Post Reference: Vikram Aristocratic Elfin Share

Pagination prior to SQL Server 2012

Pagination is common requirement for any application, where we pull data in chunks depending upon the page number and size of page. Here in this article we will see how we can achieve pagination through SQL server prior to SQ: Server 2012,

Here we are declaring a table Employee with three fields.

create table Employees (

empid int NOT NULL,

empname varchar(25) NOT NULL,

salary decimal(15,2)

)

Command(s) completed successfully.

Lets insert few data in the table.

declare @i int = 0

while @i < 10000

begin

insert into Employees

select round(rand() * 10000,0),'abc' + cast(round(rand()*10,0) as varchar), rand()*1000

set @i = @i + 1

end

Now since our table is ready, its time to enter implement pagination, here in the below script, we have taken 2 variable, @PageNo, which indicate which page to see for data and second one is @Size which says the number of records in each page. Here we are using Row_Number ranking function, Approach is

• ·         We are giving a row number in sequence to all rows of table using ROW_NUMBER Rank function.
• ·         Then according to the value of page number and page size, we are extracting the records.
• ·         Eg. If @PageNo is 4 and row @PageSize is 5 then the row to return should  have 20-25 as row numbers.

declare @pageNo int

set @pageNo=1

declare @Size int

set @Size=5

select * from (select ROW_NUMBER ()  over ( order by empid asc ) as rowno , employees.* from Employees ) s

where s.rowno > (@pageNo*@Size) and s.rowno <=((@pageNo+1)*@Size)

rowno                empid       empname                   salary

-------------------- ----------- ------------------------- ---------

1                    10          abc5                      80.35

2                    11          abc9                      287.90

3                    12          abc5                      815.79

4                    13          abc5                      433.73

5                    14          abc4                      645.19

That’s all one need to do to implement Pagination in SQL Server Prior to SQL Server 2012.

Everything gets better when you have tea and SQL Server J

Post Reference: Vikram Aristocratic Elfin Share

Cannot create default constraint with sequence object on global temporary table

I was toying with SQL sequence object and all of sudden I was surprise to find that we cannot create default constraint with sequence object on Global temporary table. Let me demonstrate my finding with an example.

Here we are creating a global temporary table with two fields

create table ##temoAddSequence

(id int,

name varchar(20))

Command(s) completed successfully.

Lets insert few records in it.

insert into ##temoAddSequence values(2,'Amrapali')

insert into ##temoAddSequence values(5,'Mahin')

lets create a sequence object

create sequence seq_IdCounter

as int

       minvalue 1

       no maxvalue

       start with 4

       increment by 1

       ;

go

Command(s) completed successfully.

Lets try to associate this sequence object to Id column of ##temoAddSequence table

alter table ##temoAddSequence

add constraint id_default_sequence

default (next value for seq_Counter) for id;

Msg 208, Level 16, State 1, Line 19

Invalid object name 'seq_Counter'.

Conclusion: We cannot create default constraint with sequence object on Global temporary table.

When you are on my head SQL, I abscond from all tasks, to shower my love to you  :)

Post Reference: Vikram Aristocratic Elfin Share