Use of cursor in SQL

Question

I want to know what is the use of cursor? i search on google and i read that cursor is used for manipulate data like in this example cursor is use .. in this example the select statement Select firstName, lastName FROM myTable returns rows

and when i execute whole query with cursor then this returns same as select statement return so what is the difference? we may use only select instead of cursor? here cursor what is the use ? can anyone explain please in simple words

   DECLARE @fName varchar(50), @lName varchar(50)

DECLARE cursorName CURSOR -- Declare cursor

LOCAL SCROLL STATIC

FOR

Select firstName, lastName FROM myTable

OPEN cursorName -- open the cursor

FETCH NEXT FROM cursorName

   INTO @fName, @lName

   PRINT @fName + ' ' + @lName -- print the name

WHILE @@FETCH_STATUS = 0

BEGIN

   FETCH NEXT FROM cursorName

   INTO @fName, @lName

   PRINT @fName + ' ' + @lName -- print the name

END

CLOSE cursorName -- close the cursor

DEALLOCATE cursorName -- Deallocate the cursor

Answer 1

Many DBA's and developers have love/hate relationship with Cursors. Some will tell you not to use it, others that there is no danger and you can use it. As with many other tools, a cursor is just another tool to be used on some specific scenarios, correctly used can be an awesome tool. But incorrectly used can cause big performance issues.

Cursors work on a row basis and are a perfect sample of the RBAR "Row By Agonizing Row" instead of sets-based operations where tsql shines. The sample you provided is a really bad sample of correct cursor usage, yes, it can show you how a cursor works, but as you commented, that same action can be done with a simple SELECT.

If you do a quick search on your prefered search engine will find lot of good references about cursors, here are some to read:

• https://www.brentozar.com/sql-syntax-examples/cursor-example/
• RBAR vs. Set based programming for SQL
• https://www.red-gate.com/simple-talk/sql/t-sql-programming/rbar-row-by-agonizing-row/