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

This article shares with you the content of a sample analysis of SQL Server page numbering. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Today, I read about T-SQL. When I saw UNBOUNDED PRECEDING, I wanted to compare the running speed of ROW_NUMBER ().

Sql and related results are as follows: the data in the database is 5W +.

ROW_NUMBER (): SET STATISTICS TIME ONSELECT ROW_NUMBER () OVER (ORDER BY Id DESC) rn,IdFROM dbo.T_MyCourse

Running result

UNBOUNDED PRECEDINGSET STATISTICS TIME ONSELECT SUM (1) OVER (ORDER BY Id DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rn,IdFROM dbo.T_MyCourse

Running result

After running, we can see the result and use the method provided by Microsoft to sort the number, and the speed is significantly improved.

However, when I use the above method to paginate the data, the results are a little different.

Get data by paging:

ROW_NUMBER () pagination to get data:

SET STATISTICS TIME ONSELECT * FROM (SELECT ROW_NUMBER () OVER (ORDER BY Id DESC) rn,Id FROM dbo.T_MyCourse) aWHERE a.rn BETWEEN 55 AND 444

Execute the sql command: DBCC DROPCLEANBUFFERS, the result after clearing the database cache

UNBOUNDED paging to get data:

SET STATISTICS TIME ONSELECT * FROM (SELECT SUM (1) OVER (ORDER BY Id DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rn,Id FROM dbo.T_MyCourse) aWHERE a.rn BETWEEN 22 AND

The sql that executes DBCC DROPCLEANBUFFERS to clear the cache in UNBOUNDED mode is also useless, and the execution time has not changed.

From the above results, we can see that ROW_NUMBER () is significantly faster to get paged data, and I guess Microsoft optimizes the method of ROW_NUMBER (), probably with caching, reading the data in the cache and then paging.

Thank you for reading! This is the end of this article on "sample analysis of SQL Server page numbers". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.