cyberdata paging server

server| Paging Now generally used in the following 2 ways: 1. Select Top @pagesize * "from table1 where id" (SELECT top @pagesize * (@page-1) ID to table1 order by ID by ID 2. Select * FROM (select top @pagesize * to (select top @pagesize * @page * FROM table1 ORDER BY id DESC) b Order by ID Which method is better? Try it. Do two table, each has 10,000 records, a table ID has index, one does not F

PublicIlistintPageSize,intPageIndex, out intTotalitemcount) { varTotalsql ="SELECT COUNT (1) from UserInfo"; Object result=getscalar (Totalsql); Totalitemcount= result = =NULL?0: Converthandler.toint32 (Result); varsql =string. Format (@"Select U2.N, u.* from UserInfo U, (select TOP {0} row_number () Over (ORDER by Name DESC) N, CODE from UserInfo) u2 WHERE U.code = U2. CODE and U2.N >= {1} ORDER by U2.N ASC", ((@pageIndex-1) * @pageSize) + @pageSize, ((@pageIndex-1) * @pageSize) +1); returnG

Tags: sort a table without bit BER 9.png CAS-Pre A little bit of the online circulation of the general change, even the results of the table query can be returned Create PROC [dbo]. [PageView] ( @sql nvarchar (max),---The original query statement @PageIndex int,--page number @PageSize int,--Number of records per page @Sort VARCHAR (255),-- Sort fields and rules without adding an order by @GetCount bit--whether the total number of records is 1 for the total number of records, 0 for the total nu

CREATE procedure P_splitpage@sql nvarchar (4000),--The SQL statement to execute @page int=1, --the page number to display @pagesize int, -- Size per page @pagecount int=0 out,--total number of pages @recordcount int=0 out--Total records asset nocount Ondeclare @p1 intexec sp_cursoropen @p1 output, @sql, @scrollopt =1, @ccopt =1,@[email protected] outputset @recordCount = @pageCountselect @pagecount =ceiling (1.0*@ pagecount/@pagesize), @page = (@page-1) * @pagesize +1exec sp_cursorfetch @p1,

1 Use [Database name]2 GO3 4 SETAnsi_nulls on5 GO6 7 SETQuoted_identifier on8 GO9 CREATE PROCEDURE [dbo].[Stored Procedure name]Ten @pageIndex INT, One @pageSize INT, A @totalCount INTOUTPUT - as - SETNOCOUNT on the - DECLARE @PageStart INT - DECLARE @PageEnd INT - + SET @pageStart=(@pageIndex-1)*@pageSize - SET @pageEnd=@pageIndex*@pageSize + BEGIN A SELECT @totalCount=COUNT(1) from [dbo].[Table name] at SELECT - [Id] - , Field 1 - , Field 2 - ,

create PROCEDURE [dbo].[GetPageDataOutRowNumber](@tn nvarchar(30),--表名称@idn nvarchar(20),--表主键名称@pi int = 1,--当前页数 @ps int = 7,--每页大小 @wh nvarchar(255) = ‘‘,--wehre查询条件@oby nvarchar(255) = ‘‘,--orderby 排序@rc int output,--总行数(传出参数)@pc int output--总页数(传出参数))ASDECLARE @sql NVARCHAR(225)=‘‘,@sqlCount NVARCHAR(225)=‘‘--1.计算总行数和总页数SET @sqlCount = ‘SELECT @rc=COUNT([‘[emailprotected]+‘]),@pc=CEILING((COUNT(‘[emailprotected]+‘)+0.0)/‘+ CAST(@ps AS VARCHAR)+‘) FROM ‘ + @tnIF LEN(@wh)>1set @[emailprotecte

Method 1:Applies to SQL Server 2000/2005SELECT TOP Page Size *From table1WHERE ID not in ( SELECT TOP Page Size * (pages-1) ID from table1 ORDER by ID )ORDER by IDMethod 2:Applies to SQL Server 2000/2005SELECT TOP Page Size *From table1WHERE ID > ( ( SELECT TOP Page Size * (pages-1) ID from table1 ORDER by ID

Label:Writing:Pretend to have a table shop, which has a list of shopname, taking 100000 to 100,050 data. The writing of Row_number SELECT * from (SELECT Over (ORDER by Shopname) as R from Shop ) T WHERE R>100000 and R100050 The writing of Offset,fetch SELECT from Shop ORDER by Shopname OFFSET 100000 ROW FETCHNEXT Comparison:The difference in execution time can be clearly seen when 100,000 data are taken. Although the previous people have given a lot of arguments, I will stick to th

Tags: procedure between server char declare default serve ORDER by SQCreate PROCEDURE [dbo]. [Proc_splitpage]@tblName varchar (255),--table name@strFields varchar (1000) = ' * ',--the column that needs to be returned, default *@strOrder varchar (255) = ',--sort field name, required@strOrderType varchar = ' ASC ',--sort the way, default ASC@PageSize int = 10,--page size, default 10@PageIndex int = 1,---page number, default 1@strWhere varchar (1500) = "

Use [Reportservertempdb]gocreate PROCEDURE [dbo]. [Seachtablepage] (@TableName varchar,--table name @fileds varchar,--query field @orderfiled varchar,--sort field @isdesc BIT,-- Whether to sort in descending order @wherestring VARCHAR (2000),--query field @pageindex int,--current page @pagesize int,--number of bars per page @totalrecord int output--return total number of bars) ASBE Gindeclare @OrderString VARCHAR (@PageIndex is NULL OR @PageIndex SQL Server