What Are the Benefits of Using Database Cursor

Pros and cons of using a cursor (in SQL server)

There are several scenarios where cursors actually perform better than set-based equivalents. Running totals is the one that always comes to mind - look for Itzik's words on that (and ignore any that involve SQL Server 2012, which adds new windowing functions that give cursors a run for their money in this situation).

One of the big problems people have with cursors is that they perform slowly, they use temporary storage, etc. This is partially because the default syntax is a global cursor with all kinds of inefficient default options. The next time you're doing something with a cursor that doesn't need to do things like UPDATE...WHERE CURRENT OF (which I've been able to avoid my entire career), give it a fair shake by comparing these two syntax options:

DECLARE c CURSOR 
    FOR <SELECT QUERY>;

DECLARE c CURSOR 
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR <SELECT QUERY>;

In fact the first version represents a bug in the undocumented stored procedure sp_MSforeachdb which makes it skip databases if the status of any database changes during execution. I subsequently wrote my own version of the stored procedure (see here) which both fixed the bug (simply by using the latter version of the syntax above) and added several parameters to control which databases would be chosen.

A lot of people think that a methodology is not a cursor because it doesn't say DECLARE CURSOR. I've seen people argue that a while loop is faster than a cursor (which I hope I've dispelled here) or that using FOR XML PATH to perform group concatenation is not performing a hidden cursor operation. Looking at the plan in a lot of cases will show the truth.

In a lot of cases cursors are used where set-based is more appropriate. But there are plenty of valid use cases where a set-based equivalent is much more complicated to write, for the optimizer to generate a plan for, both, or not possible (e.g. maintenance tasks where you're looping through tables to update statistics, calling a stored procedure for each value in a result, etc.). The same is true for a lot of big multi-table queries where the plan gets too monstrous for the optimizer to handle. In these cases it can be better to dump some of the intermediate results into a temporary structure first. The same goes for some set-based equivalents to cursors (like running totals). I've also written about the other way, where people almost always think instinctively to use a while loop / cursor and there are clever set-based alternatives that are much better.

UPDATE 2013-07-25

Just wanted to add some additional blog posts I've written about cursors, which options you should be using if you do have to use them, and using set-based queries instead of loops to generate sets:

Best Approaches for Running Totals - Updated for SQL Server 2012

What impact can different cursor options have?

Generate a Set or Sequence Without Loops: [Part 1] [Part 2] [Part 3]

What is the use of a cursor in SQL Server?

Cursors are a mechanism to explicitly enumerate through the rows of a result set, rather than retrieving it as such.

However, while they may be more comfortable to use for programmers accustomed to writing While Not RS.EOF Do ..., they are typically a thing to be avoided within SQL Server stored procedures if at all possible -- if you can write a query without the use of cursors, you give the optimizer a much better chance to find a fast way to implement it.

In all honesty, I've never found a realistic use case for a cursor that couldn't be avoided, with the exception of a few administrative tasks such as looping over all indexes in the catalog and rebuilding them. I suppose they might have some uses in report generation or mail merges, but it's probably more efficient to do the cursor-like work in an application that talks to the database, letting the database engine do what it does best -- set manipulation.

Why is it considered bad practice to use cursors in SQL Server?

Because cursors take up memory and create locks.

What you are really doing is attempting to force set-based technology into non-set based functionality. And, in all fairness, I should point out that cursors do have a use, but they are frowned upon because many folks who are not used to using set-based solutions use cursors instead of figuring out the set-based solution.

But, when you open a cursor, you are basically loading those rows into memory and locking them, creating potential blocks. Then, as you cycle through the cursor, you are making changes to other tables and still keeping all of the memory and locks of the cursor open.

All of which has the potential to cause performance issues for other users.

So, as a general rule, cursors are frowned upon. Especially if that's the first solution arrived at in solving a problem.

Cursor verus while loop - what are the advantages/disadvantages of cursors?

Some of these depends on the DBMS, but generally:

Pros:

Outperform loops when it comes to row-by-row processing
Works reasonably well with large datasets

Cons:

Don't scale as well
Use more server resources
Increases load on tempdb
Can cause leaks if used incorrectly (eg. Open without corresponding Close)

What are the benefits of CursorLoaders?

There are two key benefits to using a CursorLoader in your app over Activity.managedQuery():

The query is handled on a background thread for you (courtesy of being build on AsyncTaskLoader) so large data queries do not block the UI. This is something the docs recommended you do for yourself when using a plain Cursor, but now it's done under the hood.
CursorLoader is auto-updating. In addition to performing the initial query, the CursorLoader registers a ContentObserver with the dataset you requested and calls forceLoad() on itself when the data set changes. This results in you getting async callbacks anytime the data changes in order to update the view.

Each Loader instance is also handled through the singular LoaderManager, so you still don't have to manage the cursor directly, and now the connection can persist even beyond a single Activity. LoaderManager.initLoader() and LoaderManager.restartLoader() allow you to reconnect with an existing Loader already set up for your query and, in some cases, instantly get the latest data if it is available.

Your Activity or Fragment will likely now implement the LoaderManager.Callback interface. Calling initLoader() will result in the onCreateLoader() method where you will construct the query and a new CursorLoader instance, if necessary. The onLoadFinished() method will be fired each time new data is available, and will include the latest Cursor for you to attach to the view or otherwise iterate through.

In addition, there is a pretty good example of all this fitting together on the LoaderManager class documentation page:
http://developer.android.com/reference/android/app/LoaderManager.html

Advantages on using cursor variable in SQL Server (declare @cn cursor)

From what I read the purpose of the cursor variable is to be able to use it as an output variable in stored proc, thus enabling you to send the data in the cursor to another controlling proc. I have not tried this so I don't know exactly how it would work, but that is what I get from reading Books Online. I would be surprised if there is any measurable performance difference and certainly not the the improvement you could get by not using a cursor in the first place. If you aren't planning to use it as an output variable, I'd suggest that staying with the more common cursor definiton might make the code easier to maintain.

That said, there are very, very few cases where a cursor is actually needed.

How does Cursor work in android

A Cursor object is returned from a query on a SQLite database.
It will return all rows that the query returns.

Say you have a table called names in your database database configured as such:

_id     _name
1       Space Ghost
2       Zorak
3       Moltar
4       Brak

If you want to get all data from this table and use it, you would do
something like this:

public HashMap<Integer, String> getNames(){

 HashMap<Integer, String> data = new HashMap<Integer, String>();

 try{
  SQLiteOpenHelper helper = new MyOpenDbHelper(context);
  SQLiteDatabase db = helper.getReadableDatabase();
  String selectQuery = "SELECT  * FROM names";
  Cursor cursor = db.rawQuery(selectQuery, null);
  if (cursor != null && cursor.moveToFirst()){ //make sure you got results, and move to first row
    do{
        int mID = cursor.getInt(0); //column 0 for the current row
        String mName = cursor.getString(1); //column 1 for the current row
        data.put(mID, mName);

      } while (cursor.moveToNext()); //move to next row in the query result

  }

 } catch (Exception ex) {
    Log.e("MyApp", ex.getMessage());
 } finally
 {
    if (cursor != null) {
       cursor.close();

    }
    if (db != null) {
        db.close();
    }

 }

return data;
}

Usually you will create your own class to extend SQLiteOpenHelper, as such:

public class MyOpenDbHelper extends SQLiteOpenHelper {
     //........
}