Cursor in SQL

What is Cursor?

Cursor is a SQL Objects and it is having Row by Row Scrolling behavior while processing on the result-set (Set of records). It will act as pointer and referencing to the each row at a time. We can change the reference to other row whenever need.

Types of Cursor in SQL:

Cursor is classified into following types which are Static, Dynamic, Forward-only and static.

Syntax:

DECLARE <Cursor_Name> CURSOR

[LOCAL | GLOBAL]

[FORWARD ONLY | SCROLL]

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]

[TYPE_WARNING]

FOR <Selecting Statements>

[FOR UPDATE [OF Column_name [,....N]]]

Sample Cursor Script:

Use AdventureWorks

GO

Declare DBLogCursr cursor

For Select top 10 * from dbo.DatabaseLog

Open DBLogCursr

Fetch Next From DBLogCursr

While @@FETCH_STATUS <> -1

Begin

Fetch Next From DBLogCursr

End

Close DBLogCursr

Deallocate DBLogCursr

GO

Note: Click on the image to see the Maximized View

To know more about cursor, please refer this documentation from

http://msdn.microsoft.com/en-us/library/ms180169.aspx

Disadvantages of Cursor:

• Whenever fetch the record thru cursor, it will do the network roundtrip between server and client
• Cursor need more temporary memory storage (For IO Operations)
• Some restrictions are in select statement when we use with cursor
• Cursor is not recommendable, because it will leads to performance issue

So Please use the cursor on critical situation. Hope it helps you!