SPL Server stored procedure paging scheme competition

xiaoxiao2021-03-06  49

[Sharing] Millions Level Substruments (Effect Demo): http://blog.9cbs.net/evafly920/archive/2006/03/03/614813.aspx effect Demo: http://www.cn5135.com/ _APP / Enterprise / QueryResult.aspx SQL Server stored procedure paging, this question has been discussed for a few years, many friends ask me, so I will publish my point to build a table: Create Table [TestTable] ([ID] [ int] IDENTITY (1, 1) NOT NULL, [FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL, [LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL, [Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL, [NVARCHAR] (2000) Collate Chinese_PRC_CI_AS NULL) ON [PRIMARY] GO Insert Data: (20,000 <= 20000begin insert into TestTable ([id], FirstName, LastName, Country, Note) values ​​(@i, 'FirstName_XXX', 'LastName_XXX', 'Country_XXX', 'Note_XXX') set @ i = @ i 1end SET IDENTITY_INSERT TestTable Off ------------------------------------ Paging Solution 1: (Use Not in and SELECT TOP Paged) statement: SELECT TOP 10 * from testTablewhere (Id Not in (SELECT TOP 20 ID from TestTable Order By ID)) ORDER BY ID SELECT TOP Page Size * from testtablewhere (ID Not in (SELECT TOP page size * page ID from table ORDER BY ID)) ORDER BY ID ----------------- -------------------- Page 2: (Use ID to be greater than how much and select top paging) statement form: SELECT TOP 10 * from testTablewhere (ID> (SELECT MAX ID) from (select top 20 id from testtable order by id) as t)) Order by ID SELECT TOP page size * from testtablewhere (id>

转载请注明原文地址:https://www.9cbs.com/read-116851.html

New Post(0)