【探讨】通用分页？SQL2005使用CLR函数获取行号
blog原文
http://blog.csdn.net/jinjazz/archive/2009/04/16/4082793.aspx
SQL2005使用Row_Number来获取，但这个需要配合Order来处理，数据量大的情况下可能会影响性能。如果你还不知道CLR函数如何使用，到网上去搜索一下，或者参考我以前的文章
http://blog.csdn.net/jinjazz/archive/2008/12/05/3455854.aspx
CLR的C#代码
using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;/// <summary>/// 引用请保留以下信息：/// /// 用户自定CLR函数，用来生成一个序列/// by:jinjazz（近身剪）/// http://blog.csdn.net/jinjazz/// /// </summary>public partial class UserDefinedFunctions{ /// <summary> /// 初始化查询标识 /// </summary> public static System.Collections.Generic.Dictionary<string, long> rnList = new System.Collections.Generic.Dictionary<string, long>(); /// <summary> /// 根据标识获取序列 /// </summary> /// <param name="key">查询标识</param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction] public static SqlInt64 GetRowNum(SqlString key) { try { if (rnList == null) rnList = new System.Collections.Generic.Dictionary<string, long>(); if (rnList.ContainsKey(key.Value) == false) rnList.Add(key.Value, 1); return rnList[key.Value]++; } catch { return -1; } } /// <summary> /// 销毁查询标识 /// </summary> /// <param name="key"></param> [Microsoft.SqlServer.Server.SqlProcedure] public static void GetRowNumEnd(SqlString key) { try { if (rnList == null || rnList.ContainsKey(key.Value) == false) return ; rnList.Remove(key.Value); return ; } catch { } }};exec sp_configure 'show advanced options', '1';goreconfigure;goexec sp_configure 'clr enabled', '1'goreconfigure;exec sp_configure 'show advanced options', '1';go --测试数据库create database testDBgouse testDBgoALTER DATABASE testDB SET TRUSTWORTHY On goCREATE ASSEMBLY testAss FROM 'E:\SqlServerProject3.dll' WITH PERMISSION_SET = UnSAFE;--goCREATE FUNCTION dbo.GetRowNum ( @key nvarchar(100)) RETURNS bigintAS EXTERNAL NAME testAss.[UserDefinedFunctions].GetRowNumgoCREATE proc dbo.GetRowNumEnd ( @key nvarchar(100)) AS EXTERNAL NAME testAss.[UserDefinedFunctions].GetRowNumEnd
--获取带行号的结果select *,rn=dbo.GetRowNum(1) from sysobjects--清理结果exec GetRowNumEnd 1
select count(*) from syscolumns a,syscolumns b,syscolumns c--75151448
declare @key varchar(100)set @key=newid()select top 10 * from(select a.* ,dbo.GetRowNum(@key) as rn from syscolumns a,syscolumns b,syscolumns c)t where rn >200000exec dbo.GetRowNumEnd @key
关注.......
[解决办法]
jj总能另辟蹊径，学习了！
自己做了一些row_number()的测试。
select count(*) from syscolumns a,syscolumns b,syscolumns c/*331373888*/set statistics time ongo--test1:select top 10 * from(select a.* ,row_number() over(order by a.id) as rn from syscolumns a,syscolumns b,syscolumns c)t where rn >=200000go/*SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。(10 行受影响)SQL Server 执行时间: CPU 时间 = 94 毫秒，占用时间 = 97 毫秒。*/--test2:select top 10 * from(select a.* ,row_number() over(order by newid()) as rn from syscolumns a,syscolumns b,syscolumns c)t where rn >=200000go/*执行3分钟无果，不想等了*/--test3:select top 10 * from(select a.* ,row_number() over(order by getdate()) as rn from syscolumns a,syscolumns b,syscolumns c)t where rn >=200000go/*SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。(10 行受影响)SQL Server 执行时间: CPU 时间 = 93 毫秒，占用时间 = 99 毫秒。*/set statistics time offgo
[解决办法]
不懂么那个
[解决办法]
支持一个，推荐！
[解决办法]
推荐啊。支持
[解决办法]
支持
[解决办法]
哇，好文，收藏，辛苦啦
[解决办法]
顶个
------解决方案--------------------
支持，学习~\(≧▽≦)/~啦啦啦！
[解决办法]
.
[解决办法]
帮顶。
[解决办法]
通用性很不错！
不过通过CLR调用应该比内置的函数要差吧，毕竟要多一层与CLR的通讯。
Dictionary内部应该是通过HASH探测比较的，似乎比直接row_number() over( order by) 多了一个步骤？
[解决办法]
mark@@@
[解决办法]
[解决办法]
说实话，一直没找到性能非常好的分页
[解决办法]
学习了。。。
不过我还是喜欢 row_number() over(order by getdate() )
近1000w的数据，发现是很快的。。。。
[解决办法]
路过,看看!
[解决办法]
学习下
[解决办法]
郁闷，怎么看呀！！！现在看了，天亮就忘记了！
[解决办法]
学习了
[解决办法]
ORDER BY 的话,建立在主键上试试.
大数据量分区后比较好处理.
个人还是比较倾向于ROW_NUMBER().
偶目前的一种方案是按照ROWVERSION,是个时间变量.
作为分区和主键.并且按照这个栏位来分页.
没有做过大数据量的测试,
有空可以试试这种方案的性能.