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.

  1. Cursors in SQL (this blog)
  2. Declaring and Using Cursors in SQL
  3. Fetching Records with an SQL Cursor
  4. SQL Cursor Options
  5. Updating Records Using a Cursor

This blog is part of our complete SQL tutorial; Wise Owl also run introductory and advanced SQL training courses.

Posted by Andrew Gould on 26 March 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Cursors in SQL

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.

Cursor animation

Cursors move through a set of records one row at a time.

 

A Note on Cursor Performance

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.

So Why Use Cursors?

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.

This blog has 0 threads Add post