Shulou(Shulou.com)05/31 Report--

This article mainly introduces the SQL Server paging method of what the relevant knowledge, the content is detailed and easy to understand, the operation is simple and fast, has a certain reference value, I believe that you read this SQL Server paging method which articles will have a harvest, let's take a look at it.

Method 1: (the most commonly used paging code, top / not in) SELECT TOP 3 [SalesOrderID], [RevisionNumber], [OrderDate] FROM [AdventureWorks2012]. [Sales]. [SalesOrderHeader] WHERE [SalesOrderID] NOT IN (SELECT TOP 15 [SalesOrderID] FROM [AdventureWorks2012]. [Sales]. [SalesOrderHeader] ORDER BY [SalesOrderID]) ORDER BY SalesOrderID

Note: note that the order by before and after is consistent.

Method 2: rank windowing function (row_number () over (order by)) SELECT [SalesOrderID], [RevisionNumber], [OrderDate] FROM (SELECT [SalesOrderID], [RevisionNumber], [OrderDate], ROW_NUMBER () OVER (ORDER BY SalesOrderID) AS rowid FROM [AdventureWorks2012]. [Sales]. [SalesOrderHeader]) cteWHERE rowidBETWEEN 16 AND 18; method 3: (offset / fetch next, SQL Server 2012 support, best performance!) SELECT [SalesOrderID], [RevisionNumber], [OrderDate] FROM [AdventureWorks2012]. [Sales]. [SalesOrderHeader] ORDER BY SalesOrderID OFFSET 15 ROW FETCH NEXT 3 ROWS ONLY; Summary: paging DECLARE @ page INT, @ size INT, @ Total INT;SELECT @ page = 3, @ size = 10 using variables -the current page is 3 Page size is 10-method-SELECT TOP (@ size) [SalesOrderID], [RevisionNumber] [OrderDate] FROM [AdventureWorks2012]. [Sales]. [SalesOrderHeader] WHERE [SalesOrderID] NOT IN (SELECT TOP ((@ page-1) * @ size) [SalesOrderID] FROM [AdventureWorks2012]. [Sales]. [SalesOrderHeader] ORDER BY [SalesOrderID]) ORDER BY SalesOrderID -methods: select [SalesOrderID], [RevisionNumber], [OrderDate] FROM (SELECT [SalesOrderID], [RevisionNumber], [OrderDate], ROW_NUMBER () OVER (ORDER BY SalesOrderID) AS rowid FROM [AdventureWorks2012]. [Sales]. [SalesOrderHeader]) cteWHERE rowidBETWEEN (@ page-1) * @ size + 1 AND @ page * @ size -method three: SELECT [SalesOrderID], [RevisionNumber], [OrderDate] FROM [AdventureWorks2012]. [Sales]. [SalesOrderHeader] ORDER BY SalesOrderID OFFSET (@ page-1) * @ size ROWS FETCH NEXT @ size ROWS ONLY;-the current page of offset is opened from scratch. This is the end of the article on "what are the ways to page SQL Server?" Thank you for reading! I believe you all have a certain understanding of the knowledge of "what are the methods of SQL Server paging". If you want to learn more knowledge, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.