Professional Microsoft® SQL Server® 2008 Programming

Chapter 13. SQL Cursors

Throughout this book thus far, we've been dealing with data in sets. This tends to go against the way that the more procedure-driven languages go about things. Indeed, when the data gets to the client end, they almost always have to take our set and then deal with it row by row. What they are dealing with is a cursor. Indeed, even in traditional SQL Server tools, we can wind up in something of a cursor mode if we utilize a non-SQL-oriented language in our scripts using the new CLR-based language support.

In this chapter, we will be looking at:

• What a cursor is

• The life span of a cursor

• Cursor types (sensitivity and scrollability)

• Uses for cursors

We'll discover that there's a lot to think about when creating cursors.

Note

Perhaps the biggest thing to think about when creating cursors is, "Is there a way I can get out of doing this?" If you ask yourself that question every time you're about to create a cursor, then you will be on the road to a better performing system. That being said, we shall see that there are times when nothing else will do.

What Is a Cursor?

Cursors are a way of taking a set of data and being able to interact with a single record at a time. It doesn't happen nearly as often as one tends to think, but there are indeed times where you just can't obtain the results you want to by modifying or even selecting the data in an entire set. The set is generated by something all of the rows have in common (as defined by a SELECT statement), but then you need ...