Cursors in sql server Part 63

Text version of the video

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

Slides

All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

Relational Database Management Systems, including sql server are very good at handling data in SETS. For example, the following "UPDATE" query, updates a set of rows that matches the condition in the "WHERE" clause at the same time.
Update tblProductSales Set UnitPrice = 50 where ProductId = 101

However, if there is ever a need to process the rows, on a row-by-row basis, then cursors are your choice. Cursors are very bad for performance, and should be avoided always. Most of the time, cursors can be very easily replaced using joins.

There are different types of cursors in sql server as listed below. We will talk about the differences between these cursor types in a later video session.
1. Forward-Only
2. Static
3. Keyset
4. Dynamic

Let us now look at a simple example of using sql server cursor to process one row at time. We will be using tblProducts and tblProductSales tables, for this example. On my machine, there are 400,000 records in tblProducts and 600,000 records in tblProductSales tables. If you want to learn about generating huge amounts of random test data, please watch Part - 61 in sql server video tutorial. The link is below.

Cursor Example: Let us say, I want to update the UNITPRICE column in tblProductSales table, based on the following criteria
1. If the ProductName = 'Product - 55', Set Unit Price to 55
2. If the ProductName = 'Product - 65', Set Unit Price to 65
3. If the ProductName is like 'Product - 100%', Set Unit Price to 1000

For the SQL code samples used in the demo please visit my blog at the following link

The cursor will loop thru each row in tblProductSales table. As there are 600,000 rows, to be processed on a row-by-row basis, it takes around 40 to 45 seconds on my machine. We can achieve this very easily using a join, and this will significantly increase the performance. We will discuss about this in our next video session.

To check if the rows have been correctly updated, please use the following query.
Select Name, UnitPrice
from tblProducts join
tblProductSales on tblProducts.Id = tblProductSales.ProductId
where (Name='Product - 55' or Name='Product - 65' or Name like 'Product - 100%')

[huslerbling]

You're an excellent teacher. I only watch your videos now and I subscribed! keep it up!

[ranweerkumar]

Sir what is difference between cursor and while loop, why we use while inspite of using cursor.

[krzysztofs]

Great difficult code, but it works. I'm going fast to the next video session. U are great teacher. I've improved new skills thankx to U. Greetings from Poland.

[sridharaphyd]

Hi. Nice videos. Could u upload video about "execution plan" in sql server. If already it is uploaded plz send me the link. I watched almost all videos but I did not found. Thank u

[satheeshkumarsuthari]

Dear Venkat..awesome work really...but can you upload oracle sql and plsql...!!!you are uploading just microsoft server concepts..but we need oracle concepts as well please...thanks

[davelb]

I know it's been a long time since your SQL Server videos 63 and 64 were published, but I was wondering how I would go about coding the original cursor example in video 63 (printing the product id and product name for the rows in the database) using a set-based/joins approach instead of a cursor.

[Csharp-video-tutorialsBlogspot]

Sure, Anurag, will do it as soon as I can.

[waelalghazouli]

Thank you very much, amazing explanation as always!

[Csharp-video-tutorialsBlogspot]

Thank you very much for taking time to give feedback. I upload training videos on a daily basis. If you want to receive email alerts when new videos are uploaded, please feel free to subscribe to my youtube channel.

[barbaraulitsky]

This video was very helpful for me and it's very neat and organized. Thank you so much!!!

[anup]

It is not displaying anything it is just writing commands completed sucessfully. It is not displaying the result in your first query.

[pramodmaurya]

Thanks for your knowledge sharing.. thanks a lot sir

[pushkarraj]

Thank you sir.. sir, what is scroll keyword sometimes seen in declare statement???

[bhavyar]

great video... why two times fetch statement?

[barbaraulitsky]

Thank you for sharing! This video is very helpful for my work!

[samajad]

thanx man .i learned a lot of things . a lot 

[Csharp-video-tutorialsBlogspot]

Hi Jason, I almost forgot about it. Yet to record. Thanks for reminding. Will do it as soon as I can.

[babzo]

Thanks Venkat .You are the man. Looking forward to more insightful tutorials from you .And please talk about the execution plans .Cheers

[lavanyaarumugam]

Hi Sir, Your videos and way of expressing it is really awesome..Please kindly give videos on dynamic SQL ..

[oddnumber]

where is the data again??some times you have it