how to loop through table and perform some calculation and update data .
Normally, Cursors are good used when performing row-by-row operations, if you'd like to verify each row data, and perform calculations, you can use Cursors.
Note:
1) As we know, using Cursors can be really slow when performing operations on large number of records.
2) If possible, please clarify more about your scenario and requirement.
With Regards,
Fei Han
MSDN Community Support
Please remember to "Mark as Answer" the responses that resolved your issue.
There are various properties of
SQL cursors, if you are looking for performance you can use read only and forward only cursors
But SQL engine is optimized for set based operations instead of iterations
Especially with window functions, analytical functions database developers can calculate most required values in a single SELECT statement for all rows. This removes the requirement for looping for each row to make those calculations.
So, if you could share your requirement, we can suggest more confidently that a cursor can be replaced by set based SQL commands
Member
504 Points
1776 Posts
Stored Procedure or Function or Cursors
Oct 26, 2018 11:46 AM|JagjitSingh|LINK
Hi
how to loop through table and perform some calculation and update data . What is better Using Stored Procedure or Function or Cursors.
Thanks
All-Star
46260 Points
16904 Posts
Re: Stored Procedure or Function or Cursors
Oct 26, 2018 04:06 PM|PatriceSc|LINK
Hi,
Are you sure you have to explicity loop? Always try to use SQL ie are you sure an UPDATE statement is not enough ?
All-Star
40485 Points
6233 Posts
Microsoft
Re: Stored Procedure or Function or Cursors
Oct 29, 2018 02:39 AM|Fei Han - MSFT|LINK
Hi JagjitSingh,
Normally, Cursors are good used when performing row-by-row operations, if you'd like to verify each row data, and perform calculations, you can use Cursors.
Note:
1) As we know, using Cursors can be really slow when performing operations on large number of records.
2) If possible, please clarify more about your scenario and requirement.
With Regards,
Fei Han
MSDN Community Support
Please remember to "Mark as Answer" the responses that resolved your issue.
Contributor
6081 Points
1443 Posts
Re: Stored Procedure or Function or Cursors
Oct 30, 2018 06:03 AM|eralper|LINK
There are various properties of SQL cursors, if you are looking for performance you can use read only and forward only cursors
But SQL engine is optimized for set based operations instead of iterations
Especially with window functions, analytical functions database developers can calculate most required values in a single SELECT statement for all rows. This removes the requirement for looping for each row to make those calculations.
So, if you could share your requirement, we can suggest more confidently that a cursor can be replaced by set based SQL commands
SQL Server 2017