And Answer on SQL Server Cursor :
is cursor in SQL Server?
Cursor is a database object that represents a result set and is used to
manipulate data row by row.
a cursor is opened, it is positioned on a row and that row is available for
Server supports three types of cursor namely Transact-SQL server cursor, API
server cursor, and client cursor.
Server cursors use Transact-SQL statements and are declared using DECLARE
Server cursors can be used in Transact-SQL scripts, stored procedures, and
cursors are implemented on the server.
can fetch only one row at a time in Transact-SQL Server cursors.
can use FETCH statements with Transact-SQL cursors to retrieve rows from a
cursor?s result set.
server cursors support the API cursor functions.
server cursors are implemented on the server.
server cursors support fetching blocks of rows with each fetch.
cursor fetches multiple rows at a time is called a block cursor.
steps to use Transact-SQL Cursor.
Open the cursor,
Fetch record row by row,
of a cursor
Declare curEmp CURSOR READ_ONLY FOR SELECT EmpId FROM Employee
Fetch next from curEmp into @EmpId
While @@FETCH_STATUS = 0
Fetch next from curEmp
the cursor types.
reflects changes happened on the table while scrolling through the row.
STATIC: It works on snapshot of record set and disconnects from the server.
This kind doesn?t reflects changes happened on the table while scrolling
through the row.
KEYSET: In this kind, new record is not reflected, but data modification can be
cursor lock types.
types of locks
READ ONLY: This prevents any updates on the table.
SCROLL LOCK: This allows you to make changes to the table.
OPTIMISTIC: This checks if any change in the table record since the row
fetched before updating.
If there is no change, the cursor can update.
in brief the cursor optimization tips.
cursor when it is not required.
You shouldn?t forget to deallocate cursor after closing it.
You should fetch least number of records.
You should use FORWARD ONLY option when there is no need to update rows.
disadvantages/limitation of the cursor.
requires a network roundtrip each time it fetches a record, thus consume network
While data processing, it issues locks on part of the table, or on the whole
can use keyword SCROLL to make cursor Scrollable.
It can scroll to any row and can access the same row in the result set
A non-scrollable cursor is also known as forward-only and each row can be
fetched at most once.
table VS Table variable: Cursor alternative
This can improve processing speed but consume disk space.
Table variable that can be used in stored procedures, functions and batches.
Table variable get destroyed at the end of the stored procedure, function or
batch in which it is defined.
Since it can be used in stored procedure, it is compiled once and can be used
You can't create index on the Table variable.
Since you can create index on the temporary table, it is good where data is