SQL Server paging memory execution efficiency for various query SQL statements

CREATE Database Data_test on primary (name='Data_test_data', filename='C:\data_test_data.mdf', size=5mb,maxsize=100mb,filegrowth= the%) Log on (name='Data_test_log', filename='C:\data_test_log.ldf', size=2mb,filegrowth=1mb) gouse data_testgocreate table tb_testtable (IDintIdentity (1,1) primary key,username nvarchar ( -) notNULL, userpwd nvarchar ( -) notNULL, UserEmail nvarchar ( +)NULL)Setidentity_insert tb_testtable ondeclare @countintSet@count =1 while@count <=200000Begininsert into tb_testtable (id,username,userpwd,useremail) VALUES (@count,'Admin','admin888','[email protected]')Set@[email protected]+1EndSetIdentity_insert tb_testtable off--Using SELECT top andSelectNotinchTime consuming 1533s--SelectTopTen* fromTb_testtablewhere(ID notinch(SelectTop theId fromtb_testtable ORDER by ID ASC)) ORDER BY Idcreate procedure Proc_page_withnotin (@pageIndexint,--Page Index @pagesizeint--per page display number) asbeginSetnocount on;declare @timediff datetime--consumption time declare @sql nvarchar ( -)Select@timediff =Getdate ()Set@sql ='Select Top'+str (@pageSize) +'* from tb_testtable where (id not in (select top'+ STR (@pageIndex-1) * @pageSize) +'ID from tb_testtable ORDER by ID)) ORDER BY ID'Execute (@sql)--because the select top is not technically directly connected to the parameters, it is written as a string @sqlSelectDateDiff (MS, @timediff, Getdate ()) asWastetimeSetnocount Off;end--Using SELECT top andSelectmax time is 33s--SelectTopTen* fromTb_testtablewhere--(Id > (SelectMax (ID) from(SelectTopTenId fromTb_testtable ORDER by ID) astemp))--ORDER by Idcreate procedure Proc_page_withtopmax (@pageIndexint, @pageSizeint) asbeginSetnocount on;declare @timediff datetimedeclare @sql nvarchar ( -)Select@timediff =getDate ()Set@sql ='Select top * from Tb_testtable where (the id> (select Max (ID) from (select Top'+str (@pageIndex-1) * @pageSize) +'ID from tb_testtable ORDER by ID) (as temp)) Order by ID'Execute (@sql)SelectDateDiff (MS, @timediff, GETDATE ()) asWastetimeSetnocount Off;end--Take advantage of row_number () time consuming 1633s--Select* from(Select*,row_number () over (order by ID) RN fromtb_testtable) asTempwhereRN between Oneand -CREATE PROCEDURE Proc_pagewithrownumber (@pageIndexint, @pageSizeint) asbeginSetnocount on;declare @timediff datetimedeclare @sql nvarchar ( -)Select@timediff =getdate ()Set@sql ='SELECT * FROM (select *,row_number () over (order by ID) RN from tb_testtable) as temp where RN between'+str (@pageIndex-1) * @pageSize +1)+' and'+STR (@pageIndex *@pageSize) Execute (@sql)SelectDateDiff (MS, @timediff, GETDATE ()) asWastetimeSetnocount off;endexec Proc_page_withnotin2,100000--Wastetime (1533) EXEC Proc_page_withtopmax2,100000--wastetime ( -) EXEC Proc_pagewithrownumber2,100000--wastetime (1633)

SQL Server paging memory execution efficiency for various query SQL statements