SQL Server miracle ROW_NUMBER

Hi Guys, today I want to speak about partition by feature. I used this function for many different scenarios. Performance testing is not our topics in the text, but If I should say something, it's not a performance killer too much :)

1. Scenario is pagination modul

Let's assume you have 1000 registered users in your user table. This number is not enough to cause big problems :) let’s make 1 billion users.

One request for the get all users and putting a list for show or what you want to do. It will be really hard If each row is 1 kb, it makes 1 TB data.

The method is specifying to returning row numbers, segmented request structure.

SELECT *,ROW_NUMBER()OVER(PARTITION BY UserId order by RegisterDate)rn FROM Users

you can prepare a response between 0–299 and one more feature for the page index. If it's 0 you will return 0–299. If it's 1 you will return 300–599 :)

2. Scenario is finding duplicate data

You have an order table, and your table order included with order general details, one more table for ordered products. You can connect this with ID

You want to see product numbers of order

;with q as (select o.CustomerName,op.Name,ROW_NUMBER()OVER(PARTITION BY o.Id order by InsertDate)repeatCount from orders o
left join orderProduct op on op.OrderId = o.Id)
select * from q

This rn part will count the same id order products.