SQL Server 2008中SQL应用系列--目录索引
前些天看到一篇文章《SQL Server 2012 - Server side paging demo using OFFSET/FETCH NEXT》,原文地址。作者在文中称,要SQL Server 2012使用OFFSET/FETCH NEXT分页,比SQL Server 2005/2008中的RowNumber()有显著改进。今天特地作了简单测试。现将过程分享如下:
附:我的测试环境为:
SQL Server 2012,命名实例
Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
Feb 10 2012 19:13:17
Copyright (c) Microsoft Corporation
Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)
沿用上文的测试数据库和表:
IF OBJECT_ID('DemoPager2012') IS NOT NULL
DROP DataBase DemoPager2012
GO
CREATE Database DemoPager2012
GO
USE DemoPager2012
GO/*
Setup script to create the sample table and fill it with
sample data.
*/
IF OBJECT_ID('Customers','U') IS NOT NULL
DROP TABLE Customers
CREATE TABLE Customers ( CustomerID INT primary key identity(1,1),
CustomerNumber CHAR(4),
CustomerName VARCHAR(50),
CustomerCity VARCHAR(20) )
GOTRUNCATE table Customers
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
/*****运用CTE递归插入,速度较快,邀月注***********************/
WITH Seq (num,CustomerNumber, CustomerName, CustomerCity) AS
(SELECT 1,cast('0000'as CHAR(4)),cast('Customer 0' AS NVARCHAR(50)),cast('X-City' as NVARCHAR(20))
UNION ALL
SELECT num + 1,Cast(REPLACE(STR(num, 4), ' ', '0') AS CHAR(4)),
cast('Customer ' + STR(num,6) AS NVARCHAR(50)),
cast(CHAR(65 + (num % 26)) + '-City' AS NVARCHAR(20))
FROM Seq
WHERE num <= 10000
)
INSERT INTO Customers (CustomerNumber, CustomerName, CustomerCity)
SELECT CustomerNumber, CustomerName, CustomerCity
FROM Seq
OPTION (MAXRECURSION 0)
插入1万条数据后,在SQL Server 2008 R2中执行Row_Number():
/*
Server side paging demo using ROW_NUMBER() - SQL Server
2005/2008 version.
*/
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
DECLARE @page INT, @size INT
SELECT @page = 3, @size = 10
;WITH cte AS (
SELECT TOP (@page * @size)
CustomerID,
CustomerName,
CustomerCity,
ROW_NUMBER() OVER(ORDER BY CustomerName ) AS Seq,
COUNT(*) OVER(PARTITION BY '') AS Total
FROM Customers
WHERE CustomerCity IN ('A-City','B-City')
ORDER BY CustomerName ASC
)
SELECT * FROM cte
WHERE seq BETWEEN (@page - 1 ) * @size + 1 AND @page * @size
ORDER BY seq;
GO
SET STATISTICS IO OFF ;
SET STATISTICS TIME OFF;
GO
SQL Server 2012中执行OFFSET/FETCH NEXT语句如下:
/*
Server side paging demo using the new enhancements added
in SQL Server 2012
*/
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
DECLARE @page INT, @size INT
SELECT @page = 3, @size = 10
SELECT
*,
COUNT(*) OVER(PARTITION BY '') AS Total
FROM Customers
WHERE CustomerCity IN ('A-City','B-City')
ORDER BY CustomerID
OFFSET (@page -1) * @size ROWS
FETCH NEXT @size ROWS ONLY;
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
在SQL Server 2012中执行如下语句:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
DECLARE @page INT, @size INT
SELECT @page = 3, @size = 10
;WITH cte AS (
SELECT TOP (@page * @size)
CustomerID,
CustomerName,
CustomerCity,
ROW_NUMBER() OVER(ORDER BY CustomerName ) AS Seq,
COUNT(*) OVER(PARTITION BY '') AS Total
FROM Customers
WHERE CustomerCity IN ('A-City','B-City')
ORDER BY CustomerName ASC
)
SELECT * FROM cte
WHERE seq BETWEEN (@page - 1 ) * @size + 1 AND @page * @size
ORDER BY seq;
SELECT
*,
COUNT(*) OVER(PARTITION BY '') AS Total
FROM Customers
WHERE CustomerCity IN ('A-City','B-City')
ORDER BY CustomerID
OFFSET (@page -1) * @size ROWS
FETCH NEXT @size ROWS ONLY;
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
结论:无论是从逻辑读取数还是响应时间、实际执行行数等关键参数看,SQL Server 2012提供的OFFSET/FETCH NEXT分页方式都比Row_Number()方式有了较大的提升。
邀月注:本文版权由邀月和CSDN共同所有,转载请注明出处。
助人等于自助! 3w@live.cn
分享到:
相关推荐
在Sql Server 2012之前,实现分页主要是使用ROW_NUMBER(),在SQL Server2012,可以使用Offset ...Rows Fetch Next ... Rows only的方式去实现分页数据查询,具体代码详解大家参考下本
PLSQL程序编程,主要介绍了SQL语言得基本操作,使初学者能很快得步入PLSQL的编程世界
最近在分析 Sql Server 2012 中 offset and fetch 的新特性,发现 offset and fetch 无论语法的简洁还是功能的强大,都是相当相当不错的
在mysql数据库连接时碰到Warning: mysql_fetch_array() expects …错误提示,根据我的经验这个是sql返回的query为空了,我们没有加己判断直接使用了. mysql_fetch_array()函数导致的,下面我们一起来看问题解决方案,我...
方法名:mssql_fetch_row() 测试: 复制代码 代码如下: require ‘dbconn.php’; $sql = ‘select * from _Test’; $query = mssql_query($sql); while($row=mssql_fetch_row($query)) { echo $row[‘UserId’].’::...
复制代码 代码如下:<... while($row = mysql_fetch_row($result)) { echo $row[‘cid’].’::’.$row[1].' ’; } $result = mysql_query($sql); while($row = mysql_fetch_array($result)) {
此程序采用的是labview软件下编写SQL的查找程序
如今,有越来越多的商业逻辑和应用逻辑转向了Oracle Server,因此,PL/SQL编程也成了整个开发过程的一个重要组成部分。PL/SQL Developer侧重于易用性、代码品质和生产力,充分发挥Oracle应用程序开发过程中的主要...
$row[1],这样以数组下标来读取数据,而mysql_fetch_array()返回的数组既包含第一种,也包含键值 对的形式,我们可以这样读取数据,(假如数据库的字段是 username,passwd): $row[‘username’], $row[‘passwd’]...
mysql_fetch_assoc只能用字段,就像mysql_fetch_array($result, MYSQL_ASSOC)结果一样。 mysql_fetch_row 只能是数字 加起来就是mysql_fetch_array 您可能感兴趣的文章:Lesson03_01 什么是CSS和CSS的设置方式...
1.Offset and Fetch 方式分页 [SqlServer2012+] OFFSET 起始值 ROWS FETCH NEXT 每页条数 ROWS ONLY 1.分页条件不能重复,建议使用 分页条件+主键 一起作为 分页参数 2.分页语句中 FIRST=NEXT ROWS=ROW 3.不使用排序...
fetch next from @exec_cur into @Id, @UserName, @Address; print 'Id: ' + convert(varchar, @Id) + ', name: ' + @UserName + ', Address: ' + @Address; end close @exec_cur; deallocate @exec_cur;--删除...
SQL Server 2008高级程序设计 4/6 SQL Server 2008 2010 高级程序设计 作者:(美)维埃拉 著,杨华,腾灵灵 译 出版社:清华大学 出版日期:2010-4-1 ISBN:9787302222729 字数:1250000 页码:730 ----------------...
FETCH NEXT FROM H_BM_Cursor into @bmid,@bmmc -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN declare @strtemp varchar(50) -- Concatenate and ...
《SQL Server 2008高级程序设计》由世界顶尖SQL Server权威专家Robert Vieira编写,旨在指导您熟练运用一系列日趋复杂的功能,助您更高效地管理数据。 本书首先介绍SQL Server 2008的新功能,然后在更详实的示例...
$row=$sql->fetch(); echo 'id:'.$row['id'].'<br />'; */ foreach($row as $row) { //循环保存所有结果到数组变量$row echo 'id:'.$row['id'].'<br />'; } } } unset($pdo); //注销pdo对象 } catch ...
测试3条数据 CREATE TABLE test_main ( id INT, value VARCHAR(10), PRIMARY KEY(id) ); INSERT INTO test_main(id, value) VALUES (1, 'ONE'); INSERT INTO test_main(id, value) VALUES (2, 'TWO'); ...
Fetch error error fetch fetch