一文总结sqlserver数据库分页实现方案,值得收藏

sqlserver数据库日常巡检规范之上篇,值得收藏

概述 数据库巡检是数据库管理员保证数据库健康的必要维护项,全面的巡检可以及早的发现问题、解决问题、预防问题。今天主要分享一下sqlserver的一些巡检内容,仅供参考。 一、数据库空间及状态检查 1、所有数据库的大小  use dbname go exec sp_spaceused

概述

前段时间已经介绍了Oracle和MYSQL的分页实现方案,今天主要介绍一下sqlserver如何实现分页。

SQL server的分页与MySQL的分页的不同,mysql的分页直接是用limit (pageIndex-1),pageSize就可以完成,但是SQL server 并没有limit关键字,只有类似limit的top关键字。所以分页起来比较麻烦。


1、三重循环

先取前20页,然后倒序,取倒序后前10条记录,这样就能得到分页所需要的数据,不过顺序反了,之后可以将再倒序回来,也可以不再排序了,直接交给前端排序。还有一种方法是先查询出前10条记录,然后用not in排除了这10条,再查询。

一般实现过程如下:

-- 设置执行时间开始,用来查看性能的set statistics time on ;-- 分页查询select *  from (select top pageSize *          from (select top(pageIndex * pageSize) *                  from t                 order by tablenumber asc) -- 其中里面这层,必须指定按照升序排序,省略的话,查询出的结果是错误的。               as temp         order by tablenumber desc) temp_2 order by tablenumber asc


2、利用max(主键)

先top前11条行记录,然后利用max(id)得到最大的id,之后再重新再这个表查询前10条,不过要加上条件,where id>max(id)。

一般实现过程如下:

set statistics time on;-- 分页查询select top pageSize *  from table where tablenumber >=       (select max(tablenumber)          from (select top((pageIndex - 1) * pageSize + 1) tablenumber                  from table                 order by tablenumber asc) temp_max_ids) order by tablenumber;


3、利用row_number关键字

直接利用row_number() over(order by id)函数计算出行数,选定相应行数返回即可,不过该关键字只有在SQL server 2005版本以上才有。

set statistics time on;-- 分页查询select top pageSize *  from (select row_number() over(order by tablenumber asc) as rownumber,*          from table) temp_row where rownumber > ((pageIndex - 1) * pageSize);


4、offset /fetch next(2012版本及以上才有)

offset A rows ,将前A条记录舍去,fetch next B rows only ,向后在读取B条数据。

set statistics time on;-- 分页查询select *  from table order by tablenumber  offset((@pageIndex - 1) * @pageSize) rows  fetch next @pageSize rows only;


5、存储过程实现

写分页的时候,直接调用这个分页存储过程

create procedure paging_procedure(	@pageIndex int, -- 第几页	@pageSize int  -- 每页包含的记录数)asbegin 	select top (select @pageSize) *     -- 这里注意一下,不能直接把变量放在这里,要用select	from (select row_number() over(order by tablenumber) as rownumber,* 			from table) temp_row 	where rownumber>(@pageIndex-1)*@pageSize;end-- 到时候直接调用就可以了,执行如下的语句进行调用分页的存储过程exec paging_procedure @pageIndex=2,@pageSize=10;


6、实例演示

6.1、环境准备

CREATE TABLE [dbo].[t] (  [EMPNO] decimal(12)   NULL,  [ENAME] nvarchar(10) COLLATE Chinese_PRC_CI_AS  NOT NULL,  [JOB]   nvarchar(10) COLLATE Chinese_PRC_CI_AS  NOT NULL,  [MGR]   decimal(12)  NULL,  [HIREDATE] datetime,  [SAL] decimal(12)  NULL,  [COMM] decimal(12)   NULL,  [DEPTNO] decimal(12)  NULL)GOALTER TABLE [dbo].[t] SET (LOCK_ESCALATION = TABLE)GOINSERT INTO [dbo].[t] VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17 00:00:00', '800', NULL, '20');INSERT INTO [dbo].[t] VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20 00:00:00', '1600', '300', '30');INSERT INTO [dbo].[t] VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22 00:00:00', '1250', '500', '30');INSERT INTO [dbo].[t] VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02 00:00:00', '2975', NULL, '20');INSERT INTO [dbo].[t] VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28 00:00:00', '1250', '1400', '30');INSERT INTO [dbo].[t] VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01 00:00:00', '2850', NULL, '30');INSERT INTO [dbo].[t] VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09 00:00:00', '2450', NULL, '10');INSERT INTO [dbo].[t] VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19 00:00:00', '3000', NULL, '20');INSERT INTO [dbo].[t] VALUES ('7839', 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00', '5000', NULL, '10');INSERT INTO [dbo].[t] VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08 00:00:00', '1500', '0', '30');INSERT INTO [dbo].[t] VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23 00:00:00', '1100', NULL, '20');INSERT INTO [dbo].[t] VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03 00:00:00', '950', NULL, '30');INSERT INTO [dbo].[t] VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03 00:00:00', '3000', NULL, '20');INSERT INTO [dbo].[t] VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23 00:00:00', '1300', NULL, '10');GO

6.2、利用max实现

set statistics time on;-- 分页查询select top 5 *  from t where empno >=       (select max(empno)          from (select top((1 - 1) * 5 + 2) empno                  from t                 order by empno asc) temp_max_ids) order by empno desc;

6.3、利用row_number实现(推荐)

set statistics time on;-- 分页查询select top 6 *  from (select row_number() over(order by empno desc) as rownumber,*          from t) temp_row where rownumber > ((1 - 1) * 6);


觉得有用的朋友多帮忙转发哦!后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~

【云技术在线】为您提供以下专业技术服务:

上云服务:上云架构设计,大流量高并发架构设计,混合云架构设计,上云迁移,云上容灾,跨国云访问加速等;

安全服务:等保咨询及实施,渗透测试,漏洞扫描,木马查杀,安全加固,应急响应,DDOS/CC防护,勒索病毒防护,安全托管等;

运维服务:Linux系统运维,数据恢复及备份,负载均衡集群搭建及维护,数据库集群搭建及维护,网站运行环境搭建及维护,系统性能调优,中间件性能调优,数据库性能调优,数据库读写分离,分库分表,性能(压力)测试,7*24小时监控及故障应急响应,故障排查,大流量高并发活动护航等。