因为最近涉及到大数据量查询的SQL查询,在优化原本的查询的时候对比了新版本数据库和旧版的查询,旧版时不支持OFFSET 的函数的。
SQL Server 2019 分页查询
DECLARE @page int ,@limit int --@page当前页,@limit 分页大小
set @page =1; set @limit =10;
SELECT * FROM AquariusOther.dbo.Unit_UserBasicInfo
WHERE IsDeleted = 0
order by ReportDate desc
OFFSET (@page -1)*10 ROWS FETCH NEXT @limit ROWS ONLY
SQL Server 2008 分页查询
DECLARE @page int ,@limit int --@page当前页,@limit 分页大小
set @page =1; set @limit =10;
SELECT * FROM AquariusOther.dbo.Unit_UserBasicInfo
WHERE IsDeleted = 0
order by ReportDate desc
OFFSET (@page -1)*10 ROWS FETCH NEXT @limit ROWS ONLY
--此处查询了前100条来对比
SELECT Top(100) * FROM AquariusOther.dbo.Unit_UserBasicInfo
WHERE IsDeleted = 0
order by ReportDate desc