SQL Server Simple Data paging

Back-end projects developed at hand have been using the. NET MVC framework, access to the database using its own EF Codefirst model, and the ability to write stored procedures is rapidly deteriorating

Free to do nothing, I wrote a page of stored procedures, similar articles on the Internet, there is only one case, based on the analysis function to generate line numbers to achieve paging query

Environment: SQL Server 2014

The process of creating a database is no longer allocations, where it goes directly to focus:

1, first created a testadmin table, the primary key is the ID field int type and self-increment

1 Create Table testadmin (2intIdentity(1,1primaryKey , 3 varchar (Max), 4 int 5 )

2, then batch import 1000 simulation data

1 Declare @count int2 --this defines the number of analog data bars3 Set @count= +4 5 6  while(@count>0)7 begin8 Insert  intoTestadmin (Name,age)Values('Zhuyuan'+Convert(varchar,@count),@count)9 Set @count=@count-1Ten End

1 Select *  from Testadmin

Import complete, start paging:

The general idea is to first query out all the data by a certain sort rule, then automatically generate line numbers for each row, and then filter the where statement to the table after the row number is generated

3, we first insert a column of the same data generation table V1 for each row of the main table, the purpose is mainly for the following analysis function can be a single-line aggregation of the table

1 Select *,1 as from Testadmin

• (Table V1)

4, the table V1 to generate line number processing, using SQL Server's own analysis function row_number () to achieve this function

1 Select  Over  by Order  by  as Row,*from (select*,1 as from Testadmin) m

Generate Table V2

• (Table V2)

At this point we already have a table V2 with index row numbers, and the subsequent operations are clear.

5, suppose we need 10 data per page, and query the second page

1 Select *  from(SelectRow_number () Over(Partition bySamerowOrder  byID) asRow,*  from(Select *,1  asSamerow fromtestadmin) m) owhereO.rowbetween 1*Ten+1  and 2*Ten

6, do another package, create a stored procedure for it, so that we can call again later

1 Create procSelect_page2 (3 @pageIndex int,--Current page number4 @pagecount int--number of bars per page5 )6  as7 begin8 Select *  from(SelectRow_number () Over(Partition bySamerowOrder  byID) asRow,*  from(Select *,1  asSamerow fromtestadmin) m) owhereO.rowbetween @pageIndex*@pagecount+1  and(@pageIndex+1)*@pagecount9 End

The stored procedure was created successfully!

7, let us try, if you want to query the 5th page, 10 article per page

1 5,ten

It is much easier to make a paging query on the table later on ^o^

Leave a footprint--2016.12.16 Noon (the Sun is just right)

SQL Server Simple Data paging