
Cursors in sql server Part 63
Text version of the video
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%’)
Nguồn: https://medinaportal.net/
Xem thêm bài viết: https://medinaportal.net/category/cong-nghe
great.thank you.clear,simple code
can we use select statement in while loop ? i was asked this question in sql developer interview
Please Can You Share The Video How to download and Install SSMS. I was download Many Times But It Was Not Work
Thank you sir.. sir, what is scroll keyword sometimes seen in declare statement???
Pointer to resultset, this is the most important thing, that explains cursors to a programmer at once. Thanks for the video.
Thank you for sharing! This video is very helpful for my work!
Thanks for your knowledge sharing.. thanks a lot sir
Thanks for the Videos, it's Awesome 😀
Well explained
good job. thank you.
sir plz can u make one demo tool for migrating data from ms sql to oracle
Question: Everywhere they say not to use Cursor as it causes high compilation time and parse of the fetch statements and also there are other quicker ways to obtain same results using joins. Then, why is Cursor an option for querying in SQL server? Is there any specific reason to only use Cursor for any particular query? Will appreciate anyone who clarify this for me. 🙂
Nice explanation thanks…
Thank you sir..for giving us good concept
Excellent tutorial! Thank you!
HELLO Sir
Your videos are very beneficial for freshers….
M alwys watching ur videos and recommended to all my frndzzz….
really great
⒞cursor in sql server ⓠ
brilliant and a clear guide, thank you for this
The red pulsing cursor is annoying. Simply use RED color cursor instead.
Hi Sir Please Upload video on collation.