Sql-server – SQL Server: Use Implicit or Explicit Cursor?

cursorsexecsql server

SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHILE (1=1)
BEGIN
 EXEC 'drop view ' + Table_Name
END

or

DECLARE my_cursor CURSOR FOR
SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES

OPEN my_cursor
WHILE 1 = 1
BEGIN
  FETCH my_cursor INTO @vname
  IF @@fetch_status != 0 BREAK
  EXEC('drop view ' + @vname)
END
CLOSE my_cursor;

The examples more or less describe what I'm doing, but that's not the question. The question is: When should I be trying to use an explicitly defined cursor, as in the second example, and what are the conditions where the first example would work?

Best Answer

Your first example doesn't work, so that isn't an option. The SELECT will return the result to the client and then you (would) have en endless loop, except that it wouldn't compile due to the Table_name doesn't exist (so to speak).

Anyhow, I see three main categories of cursor usage. Others are welcome to add in and/or criticize:

DDL and maintenance stuff. I.e., stuff where you cannot work set-based. Like dropping each view with the prefix VV. Or rebuilding each table. Etc. There is no set based command or way to do those things.

When you can whip up a cursor based solution quickly (comparatively) and it solves you needs and the perf and readability is OK. I.e., it isn't worth spending 3 days re-doing this cursor solution since it only runs once a month and saving 10 minutes once a month doesn't justify the work effort. This isn't clear-cut, and going for a set-based solution will most often be beneficial.

When a set-based solution performs worse than a cursor based solution. Running aggregates is a classic, although with the new windowing stuff added in 2012 I can imagine that this classic example might not be a good example anymore.