(利用not in和select top分页卡塔尔(قطر‎ 语句情势,全角半角标点的改动

澳门新葡8455最新网站,几种高效mssql server sql分页语句

mssql
中文字符处理(字符集编码和排序规则)sqlserver中文处理涉及到字符集编码和排序规则,是个非常纠结的问题。

几种SQL
Server分页的存储过程写法以及性能比较存储过程的5种分页写法,下面的代码是从忘了什么时候从别人那Ctrl+C来的,所以仅仅作为收藏,希望作者看到不要喷我.——创建数据库教程data_Test
—–create database data_Test GO use data_Test GO create table
tb_TestTable –创建表 ( id int identity(1,1) primary key, userName
nvarchar(20) not null, userPWD nvarchar(20) not null, userEmail
nvarchar(40) null ) GO

分页方案三:(利用id大于多少和select top分页)效率最高,需要拼接sql语句
分页方案二:(利用not in和select top分页)
效率次之,需要拼接sql语句分页方案一:存储过程相对于大数据量会有明显的优势的

sql code

——插入数据—— set identity_insert tb_TestTable on declare
@count int set @count=1 while @count=2000000 begin insert into
tb_TestTable(id,userName,userPWD,userEmail)
values(@count,’admin’,’admin888′,’lli0077@yahoo.com.cn’) set
@count=@count+1 end set identity_insert tb_TestTable off

看一个简单存储过程实例的

–ascii字符select n,x=cast(n as binary(2)),u=nchar(n) from nums where
n between 32 and 126–unicode中文字符select n,x=cast(n as
binary(2)),u=nchar(n) from nums where n between 19968 and 4086919968
0x4e00 一40869 0x9fa5–以下两个条件用来判断字符串是否包含汉字like
n’%[-]%’ collate chinese_prc_ci_aslike n’%[一-]%’ collate
chinese_prc_bin–这是因为在以上两种不同的排序规则下,汉字的排列顺序是不同的。–中文全角标点符号select
n,x=cast(n as binary(2)),uq=nchar(n),ub=nchar(n-65248) from nums where
n between 65281 and 65374select nchar(12288),nchar(32)65281 0xff01 !
!65374 0xff5e ~ ~–以下条件用来判断字符串是否包含全角标点like
n’%[!-~]%’ collate chinese_prc_bin

—1、利用select top 和select not in进行分页,具体代码如下create
procedure proc_paged_with_notin –利用select top and select not in (
@pageIndex int, –页索引 @pageSize int –每页记录数 ) as begin set
nocount on; declare @timediff datetime –耗时 declare @sql nvarchar(500)
select @timediff=Getdate() set @sql=’select top ‘+str(@pageSize)+’ *
from tb_TestTable where(ID not in(select top
‘+str(@pageSize*@pageIndex)+’ id from tb_TestTable order by ID ASC))
order by ID’ execute(@sql) –因select
top后不支技直接接参数,所以写成了字符串@sql select
datediff(ms,@timediff,GetDate()) as 耗时 set nocount off; end

create procedure pr_getarticles -这里为存储过程名称@page int
,@pagenum int asdeclare @tablename nvarchar(20)set
@tablename=’article’ 表名declare @idname nvarchar(20)set
@idname=’article_id’ 表id名declare @strsql nvarchar(4000)declare
@topnum intset @topnum=(@page-1)*@pagenumset @strsql=n’select top’+
str(@pagenum)+’ *from ‘+@tablename+’where ‘+@idname+'(select
isnull(max(‘+@idname+’),0) from (select top ‘+str( @topnum)+’
‘+@idname+’ from ‘+@tablename+’ order by ‘+@idname+’) a)order by
‘+@idname+print (@strsql)exec(@strsql)go

全角半角标点的转换:

—2、利用select top 和 select max(列键)—

相关文章

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图