استفاده از ROW_NUMBER() over ()

وقت بخیر دوستان آیا استفاده از روش زیر برای ایجاد ستون ردیف، روی بهینه گی کوئری تاثیر میزاره؟

SELECT ROW_NUMBER() over (order by [MadrakId]) as Radif , [Id]

      ,[MadrakId]      

  FROM [MasterList_ToziNosakhTbl]

البته این یک کوئری ساده هست.توئ کوئری هایی که از پیوند ها استفاده شده باشه چی؟

خودم به یه نتایج مشترکی رسیدم که نمونه ای ازش رو میزارم بقیه دوستان هم استفاده کنن:

I've written about this a few times actually; ROW_NUMBER is by far the most flexible and easy-to-use, and performance is good, but for extremely large data sets it is not always the best. SQL Server still needs to sort the data and the sort can get pretty expensive.


There's a different approach here that uses a couple of variables and SET ROWCOUNT and is extremely fast, provided that you have the right indexes. It's old, but as far as I know, it's still the most efficient. Basically you can do a totally naïve SELECT with SET ROWCOUNT and SQL Server is able to optimize away most of the real work; the plan and cost ends up being similar to two MAX/MIN queries, which is usually a great deal faster than even a single windowing query. For very large data sets this runs in less than 1/10th the time.


Having said that, I still always recommend ROW_NUMBER when people ask about how to implement things like paging or groupwise maximums, because
of how easy it is to use. I would only start looking at alternatives like the above if you start to notice slowdowns with ROW_NUMBER.

لینک https://stackoverflow.com/questions/...-paging-in-sql



و یه مطلب خوب و کامل با مثال زیبا

پست دومش
https://stackoverflow.com/questions/...rmance-problem