In an SQL c lass and took a 0 on the Cursors assignment because I just couldn't wrap my head around it, and my instructor was no help. I wish I'd have watched this video, thanks for the perfect explanation!
why not group the second table by account_number and calculate the sum of payments for each account number, first, then join this result of that with the balance table and subtract aggregated results?
You said that the payments data can be aggregated, but that is missing the point. But I think it is a great point, since after watching this video, I still don't know a case scenario where a cursor would be the best option. I would only use cursors as a last resort, since they perform poorly and complicates debugging.
If anyone at my work used a cursor instead of just using SUM to perform this update I would fail their code review immediately and have a teaching session with them Cursors can dynamically update if the source is updated mid update and can global depending on configuration unless you specify LOCAL FAST_FORWARD. People also tend to use them to call stored procedures per row in triggers and more frequently then not, it has lead to more time fixing issues than time saved by that sole developer in having to think about a more adequate solution. I've received multiple late night calls to fix performance issues where jobs stalled as a result of devs doing this kind of stuff The only time I think I've actually seen a decent argument for cursors was for operating on DMV objects such as forcing index and more importantly statistics rebuilds and I'd bet you can still just do that with a while loop at the loss of ignoring changes during the loop firing (forcing it to be processed on the next call to that job) I've literally wrote a temporally stable audit table code generator that generates 1. Audit table for a table 2. Audit Foreign Keys connecting Audit tables by AuditIDs in the same manner as the original table 3. Triggers for INSERT, UPDATE, and DELETE operations And I STILL never needed to use a CURSOR. If you're familiar with the idea of "bad parts" of a language such as "with" or "this" in JS it's the exact same concept. You can write better code by constraining yourself to not use the bad parts of a language and Cursors are a horrible part of the SQL language
I just love the WHERE-statement in the UPDATE; that will be applied n-times for each entry. I hope MS SQL offers some cashing natively, because that looks like a killer app.
nice video, but you didnt explain why you were writing each line, for instance why do you have to deallocate the cursor? or, why do you declare the cursor and then write "FOR" and then a query? is that FOR, I suppose referring to the whole query that you write ahead of it? Thanks
In an SQL c lass and took a 0 on the Cursors assignment because I just couldn't wrap my head around it, and my instructor was no help. I wish I'd have watched this video, thanks for the perfect explanation!
You're very welcome. :) Appreciate the kind words.
Learn english before SQL.
@@aholmes1397 Why should he?
So detailed and calmly-paced, absolute newbies at the end of this video should end up knowing 95% of all cursor practical uses. Period. Subscribed!
I watched multiple videos about Cursors , finally I found the one that explains cursors in a simple way
We can just use summary of group by right? I still dont get the value for the cursor
So fortunate to find exact scenario that fits my need. Thanx James.
why not group the second table by account_number and calculate the sum of payments for each account number, first, then join this result of that with the balance table and subtract aggregated results?
Why indeed
this is the first SQL tutorial i found on youtube which is not from an indian guy, thank you
This is the clear explanation of why we have to use cursor. Thanks alot !
Fantastic video, if you keep making videos like this you will help a lot of people and find serious success.
won yourself a suscriber just because of this video, huge props man
That’s awesome James . Definitely to use cursor . Thank you for sharing your knowledge. This is typical use case for cursor
You said that the payments data can be aggregated, but that is missing the point. But I think it is a great point, since after watching this video, I still don't know a case scenario where a cursor would be the best option. I would only use cursors as a last resort, since they perform poorly and complicates debugging.
You are the MAN!!!
Nice & Smooth explanation.
well done.
VERY nice......... clear and concise. Thank you!
Love it, thanks!
Well produced video, but it’s the equivalent to very professionally explaining how to set yourself on fire
You have tastefully placed fast-forward segments when you type your code. Take my sub!!!
If anyone at my work used a cursor instead of just using SUM to perform this update I would fail their code review immediately and have a teaching session with them
Cursors can dynamically update if the source is updated mid update and can global depending on configuration unless you specify LOCAL FAST_FORWARD.
People also tend to use them to call stored procedures per row in triggers and more frequently then not, it has lead to more time fixing issues than time saved by that sole developer in having to think about a more adequate solution.
I've received multiple late night calls to fix performance issues where jobs stalled as a result of devs doing this kind of stuff
The only time I think I've actually seen a decent argument for cursors was for operating on DMV objects such as forcing index and more importantly statistics rebuilds and I'd bet you can still just do that with a while loop at the loss of ignoring changes during the loop firing (forcing it to be processed on the next call to that job)
I've literally wrote a temporally stable audit table code generator that generates
1. Audit table for a table
2. Audit Foreign Keys connecting Audit tables by AuditIDs in the same manner as the original table
3. Triggers for INSERT, UPDATE, and DELETE operations
And I STILL never needed to use a CURSOR.
If you're familiar with the idea of "bad parts" of a language such as "with" or "this" in JS it's the exact same concept.
You can write better code by constraining yourself to not use the bad parts of a language and Cursors are a horrible part of the SQL language
***** 5Star to you. awesome comparison, finally found the real reason after so many years.
Thank you for explaining so clearly! Great walk through
hello mam have u noticed he is calling the table with different columnnames how could it happen
You've earned yourself a sub my friend
thanks for the video
I just love the WHERE-statement in the UPDATE; that will be applied n-times for each entry. I hope MS SQL offers some cashing natively, because that looks like a killer app.
You made my life easy .. thank you so much
excellent video and explanation
That was beautiful!
va thalaiva va thalaiva va thalaiva
Nicely done! Thanks
nice video, but you didnt explain why you were writing each line, for instance why do you have to deallocate the cursor? or, why do you declare the cursor and then write "FOR" and then a query? is that FOR, I suppose referring to the whole query that you write ahead of it? Thanks
Thank you so much, it is very helpful.
Amazing explanation
Very well explained.
Script to try yourself:
create table Account_Balance(
Account_Number int,
Balance int);
insert into Account_Balance values(1,100),
(2,150),
(3,200),
(4,250),
(5,1000),
(6,5000)
select * from Account_Balance
create table Account_Payments(
Date date,
Account_Number int,
Payment int);
insert into Account_Payments values('2021-02-06',1,50),
('2021-02-06',2,25),
('2021-02-06',2,125),
('2021-02-06',3,100),
('2021-02-06',4,50),
('2021-02-06',5,500),
('2021-02-06',6,250),
('2021-02-06',6,250)
select * from Account_Payments
Hey in check u used columname but it in resukt set we get only after_payment how could it happen
you can solve it trigger that problem because when it makes insert subtract each other using trigger
gr8 job
What keyboard are you using?
love ya
Gotta ask, how did you get it to run even with errors?
They are not actually errors. His IntelliSense appears to have not been updated.
is this similar for MySQL? I tried to look for MySQL but I can't understand anything from all those videos as their English is Horrible!
This is what happens when you don’t have foreign keys like TransactionID & TransactionType lol
Okay? Okay? Okay?
Great video. Lose that cheesy corporate intro, it's so gosh darn cringe. Corporatey stuff like that is soulless