sqlserver查询用时的sql

背景：

在日常工作中，要进行查询sql 为了提高效率和时间

create database DBTest
use DBTest

--创建测试表
create table pagetest
(
id int identity(1,1) not null,
col01 int null,
col02 nvarchar(50) null,
col03 datetime null
)

--1万记录集
declare @i int
set @i=0
while(@i<10000)
begin
    insert into pagetest select cast(floor(rand()*10000) as int),left(newid(),10),getdate()
    set @i=@i+1
end 

--写法1，not in/top
select top 50 * from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan]
where id not in (
	select top 78600 id from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan] order by id
	)
order by id
--耗时110毫秒




--写法2，not exists
select top 50 * from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan] pagetest
where not exists 
(
	select 1 from 
		(select top 786000 id from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan] order by id)a  
	where a.id=pagetest.id
)
order by id
--耗时：453毫秒




--写法3，max/top
select top 50 * from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan]
where id>(
	select max(id) from (select top 786000 id from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan] order by id)a
		)
order by id
--耗时：313毫秒





--写法4，row_number()
select top 50 * from 
(
	select row_number()over(order by id)rownumber,* from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan]
)a
where rownumber>786000
--耗时：546毫秒



select * from 
(select row_number()over(order by id)rownumber,* from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan])a
where rownumber>786000 and rownumber<786051
--耗时：530毫秒




select * from 
(select row_number()over(order by id)rownumber,* from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan])a
where rownumber between 786000 and 786051
--耗时：500毫秒




--写法5，在csdn上一帖子看到的，row_number() 变体，不基于已有字段产生记录序号，先按条件筛选以及排好序，再在结果集上给一常量列用于产生记录序号
select *
from (
    select row_number()over(order by tempColumn)rownumber,*
    from (select top 786051 tempColumn=0,* from [CRB_TPM].[dbo].[BO_DW_AttVisitPlan] where 1=1 order by id)a
)b
where rownumber>786000

耗时：300毫秒