560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Using Cursors in SQL Server
Part one of a five-part series of blogs
Cursors allow you to step through a set of data one record at a time. They’re not the quickest tool in SQL Server’s box, but they have their uses and this blog explains how they work.
Most of the actions you perform in SQL are applied to an entire set of records at the same time; these are often referred to as set-based operations. Set-based operations are what make SQL so efficient when it comes to processing large sets of data and, in most cases, you can achieve the results you need using only this type of operation. Occasionally, however, you may find that you need to process just a single record at a time and, if you want to do this, you'll need to learn how to use cursors.
Cursors move through a set of records one row at a time.
Before we talk about how to use cursors it's worth mentioning something about their performance: cursors are s-l-o-w! Processing one record at a time will never be as fast as an equivalent set-based operation; if speed is important then cursors are not the tools you should be using.
If cursors are so slow compared to set-based operations why would you use them? The simple answer is that there are certain things that you simply can't do with a set-based operation, for instance executing a stored procedure against each individual record in a table.
Even when it is possible to achieve the result you want with a set-based operation you might even find that you want to use a cursor simply because it's easier to understand the code. Sometimes clarity and ease-of-use is more important than performance.
Bearing all of this in mind the next part of this blog shows you how to declare and use a simple cursor to process a list of films.
We have removed cursors from our advanced SQL course, for good reason - we never use them ourselves. Before becoming a cursors guru, you should ask yourself if you should be doing what you're trying to do in a different way.
|Parts of this blog|
25 Aytoun Street