Sql Server 数据库分页

创建 Student

   CREATE TABLE Student
   (
        Id INT IDENTITY(1,1) PRIMARY KEY,
        Name VARCHAR(20),
        Sex CHAR(2),
        Address VARCHAR(20)
   )

创建 RoundData

  CREATE PROC RoundData
	@count INT
  AS
	DECLARE @i INT SET @i=0
	DECLARE @fName TABLE(Id INT IDENTITY(1,1) PRIMARY KEY, NAME NVARCHAR(20))
	DECLARE @lName TABLE(Id INT IDENTITY(1,1) PRIMARY KEY, NAME NVARCHAR(20))
	DECLARE @sex TABLE(Id INT IDENTITY(1,1) PRIMARY KEY, NAME NVARCHAR(20))
	DECLARE @address TABLE(Id INT IDENTITY(1,1) PRIMARY KEY, NAME NVARCHAR(20))

	INSERT @fName VALUES
	('赵'),('钱'),('孙'),('李'),('周'),('吴'),('郑'),('王'),('冯'),('陈'),('楮'),('卫'),('蒋'),('沈'),('韩'),('杨'),
	('朱'),('秦'),('尤'),('许'),('何'),('吕'),('施'),('张'),('孔'),('曹'),('严'),('华'),('金'),('魏'),('陶'),('姜'),
	('戚'),('谢'),('邹'),('喻'),('柏'),('水'),('窦'),('章'),('云'),('苏'),('潘'),('葛'),('奚'),('范'),('彭'),('郎'),
	('鲁'),('韦'),('昌'),('马'),('苗'),('凤'),('花'),('方'),('俞'),('任'),('袁'),('柳'),('酆'),('鲍'),('史'),('唐'),
	('费'),('廉'),('岑'),('薛'),('雷'),('贺'),('倪'),('汤'),('滕'),('殷'),('罗'),('毕'),('郝'),('邬'),('安'),('常'),
	('乐'),('于'),('时'),('傅'),('皮'),('卞'),('齐'),('康'),('伍'),('余'),('元'),('卜'),('顾'),('孟'),('平'),('黄'),
	('和'),('穆'),('萧'),('尹')

	INSERT @lName VALUES ('爱'),('安'),('百'),('邦'),('宝'),('保'),('抱'),('贝'),('倍'),('蓓'),('本'),
	('必'),('碧'),('璧'),('斌'),('冰'),('兵'),('炳'),('步'),('彩'),('曹'),('昌'),('长'),('常'),('超'),
	('朝'),('陈'),('晨'),('成'),('呈'),('承'),('诚'),('崇'),('楚'),('传'),('春'),('纯'),('翠'),('村'),
	('殿'),('丁'),('定'),('东'),('冬'),('二'),('凡'),('方'),('芳'),('昉'),('飞'),('菲'),('纷'),('芬'),
	('奋'),('风'),('峰'),('锋'),('凤'),('芙'),('福'),('付'),('复'),('富'),('改'),('刚'),('高'),('阁'),
	('铬'),('根'),('庚'),('耕'),('公'),('功'),('冠'),('光'),('广'),('归'),('桂'),('国'),('海'),('寒'),
	('翰'),('昊'),('浩'),('荷'),('红'),('宏'),('洪'),('鸿'),('厚'),('华'),('存'),('大'),('丹'),('道'),
	('德'),('登'),('砥'),('典'),('佃')

	INSERT @sex VALUES ('男'),('女')

	INSERT @address VALUES ('北京'),('天津'),('河北'),('山西'),('内蒙古'),('辽宁'),('吉林'),('黑龙江'),
	('上海'),('江苏'),('浙江'),('安徽'),('福建'),('江西'),('山东'),('河南'),('湖北'),('湖南'),('广东'),
	('广西'),('海南'),('重庆'),('四川'),('贵州'),('云南'),('西藏'),('陕西'),('甘肃'),('青海'),('宁夏'),
	('新疆'),('台湾'),('香港'),('澳门')

	WHILE @i < @count
	BEGIN
		INSERT INTO Student
		SELECT RTRIM((SELECT NAME FROM @fName WHERE Id = Round(Rand()*(100-1)+1,0)))
		+RTRIM(LTRIM((SELECT NAME FROM @lName WHERE Id = Round(Rand()*(100-1)+1,0))))
		+RTRIM(LTRIM((SELECT NAME FROM @lName WHERE Id = Round(Rand()*(100-1)+1,0)))),
		RTRIM((SELECT NAME FROM @sex WHERE Id = Round(Rand()*(2-1)+1,0))),
		RTRIM((SELECT NAME FROM @address WHERE Id = Round(Rand()*(34-1)+1,0)))

		SET @i+=1
	END

插入 200000 假数据

  EXEC RoundData @count=200000

数据库分页

1. top not in

  DECLARE @TABLENAME VARCHAR(20);
  DECLARE @PAGEINDEX INT;
  DECLARE @PAGESIZE INT;
  DECLARE @SQL VARCHAR(MAX);

  SET @TABLENAME = 'Student';
  SET @PAGEINDEX = 400;
  SET @PAGESIZE = 20;

  SET @SQL
      = N'SELECT TOP ' + STR(@PAGESIZE) + N' * FROM ' + @TABLENAME + N' WHERE(ID NOT IN(SELECT TOP '
        + STR(@PAGESIZE * @PAGEINDEX) + N' ID FROM ' + @TABLENAME + N' ORDER BY ID ASC)) ORDER BY ID';
  EXECUTE (@SQL);

2. top max

  DECLARE @TABLENAME VARCHAR(100);
  DECLARE @PAGEINDEX INT;
  DECLARE @PAGESIZE INT;
  DECLARE @SQL VARCHAR(MAX);

  SET @TABLENAME = 'Student';
  SET @PAGEINDEX = 400;
  SET @PAGESIZE = 20;

  SET @SQL
      = 'SELECT TOP ' + STR(@PAGESIZE) + ' * FROM ' + @TABLENAME + ' WHERE(ID>(SELECT MAX(ID) FROM (SELECT TOP '
        + STR(@PAGESIZE * @PAGEINDEX) + ' ID FROM ' + @TABLENAME + ' ORDER BY ID) AS T)) ORDER BY ID';
  EXECUTE (@SQL);

3. row_number

  DECLARE @TABLENAME VARCHAR(100);
  DECLARE @PAGEINDEX INT;
  DECLARE @PAGESIZE INT;
  DECLARE @SQL VARCHAR(MAX);

  SET @TABLENAME = 'Student';
  SET @PAGEINDEX = 400;
  SET @PAGESIZE = 20;

  SET @SQL
      = 'SELECT * FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY ID ASC) AS _id FROM ' + @TABLENAME
        + ') AS TEMP
      WHERE _id>' + STR(@PAGESIZE * @PAGEINDEX) + ' AND _id<=' + STR(@PAGESIZE * (@PAGEINDEX + 1)) + '';
  EXECUTE (@SQL);

4. offset fetch

  DECLARE @TABLENAME VARCHAR(100);
  DECLARE @PAGEINDEX INT;
  DECLARE @PAGESIZE INT;
  DECLARE @SQL VARCHAR(MAX);

  SET @TABLENAME = 'Student';
  SET @PAGEINDEX = 400;
  SET @PAGESIZE = 20;

  SET @SQL = 'SELECT * from ' + @TABLENAME + '
	      ORDER BY ID
	      OFFSET　' + STR(@PAGESIZE * @PAGEINDEX) + ' ROW
	      FETCH NEXT ' + STR(@PAGESIZE) + ' ROW ONLY';
  EXECUTE (@SQL);

相关资料

1. https://www.cnblogs.com/elliot-lei/p/5340838.html
2. https://blog.csdn.net/weixin_37610397/article/details/80892426