This is another most powerful object of SQL
Server which is mostly used by database programmers to process individual rows
returned by database system queries such as retrieval, addition and removal of
database records.
In computer science, Cursor is a database object which is used by applications to
manipulate data in a set on a row-by-row basis, instead of the typical SQL
commands that operate on all the rows in the set at one time.
In simple words, a cursor can be viewed
as a pointer to one row in a set of rows. The cursor can only reference one row
at a time, but can move to other rows of the result set as needed and they act
like a looping statement(i.e. while or for loop).
DBA programmers believe that they are
typically a thing to be avoided within SQL Server stored procedures if at all
possible due to some performance issues. Outside of the performance issues, programmers
think the biggest failing of cursors is they are painful to debug. They
generally use a lot of SQL Server resources and reduce the performance and
scalability of your applications.
Scope
of Cursors:
In SQL Server, there are two options to
define the scope of a cursor.
- A GLOBAL cursor can be referenced in any stored procedure or batch executed by a connection. GLOBAL cursors must be explicitly deallocated or they will be available until the connection is closed.
- LOCAL cursors are more secure as they cannot be referenced outside the procedure or trigger unless they are passed back to the calling procedure or trigger, or by using an output parameter. LOCAL cursors are implicitly deallocated when the stored procedure, the trigger, or the batch in which they were created terminates.
How
to Use Cursors: To use cursors in SQL procedures, you
need to do the following step by steps:
- Declare a cursor that defines a result set - Declare Cursor SQL Command is used to define the cursor with many options that impact the scalability and loading behavior of the cursor.
- Open the cursor to establish the result set - A Cursor can be opened locally or globally. By default it is opened locally.
- Fetch the data into local variables as needed from the cursor, one row at a time - Fetch statement provides the many options to retrieve the rows from the cursor. NEXT is the default option.
- Do the needful operations on the targeted table with the help of local variables.
- Close the cursor when done - Close statement closed the cursor explicitly.
- Deallocate the cursor when done - Deallocate statement delete the cursor definition and free all the system resources associated with the cursor.
Example: To understand the functionality of SQL Cursor,
we will create an Employee table in our database such as given below:
--Create Employee table
CREATE TABLE
Employee
(
EmpID
int PRIMARY KEY,
FirstName
varchar(50) NULL,
LastName
varchar(50) NULL,
Salary
int NULL,
Address
varchar(100) NULL,
)
--Insert Values into the employee table
Insert into
Employee(EmpID, FirstName, LastName, Salary, Address)
Values(1,'Ryan','Arjun',22000,'Delhi, India'),
(2,'Jullie','Philips',15000,'New York, USA'),
(3,'Black','Smith',19000,'Sydney, Australia'),
(4,'Rosey','White',19000,'London, England');
--Fetch records from the employee table
Select *
from dbo.Employee
|
Based
on the above table structure, we can create a very simple cursor to print the each
record of the table as given below:
SET NOCOUNT ON
-- declare local variable
DECLARE @EmpId
int
DECLARE @EmpFullName
varchar(50)
DECLARE @EmpSalary
int
-- declare cursor with static type
-- Set the Result set for the cursor also
DECLARE Cur_Employee
CURSOR
STATIC FOR
SELECT EmpID, EmpName=FirstName+' '+LastName, Salary from dbo.Employee
-- Open cursor for the operation
OPEN Cur_Employee
-- check result set rows in cursor
IF @@CURSOR_ROWS
> 0
BEGIN
--
set the first row values in the locat variables from the cursor
FETCH
NEXT FROM Cur_Employee INTO @EmpId, @EmpFullName, @EmpSalary
--
check the status of the result set
WHILE
@@Fetch_status =
0
BEGIN
--
print the result from the local variables
PRINT
'ID : '+ convert(varchar(20),@EmpId)+', Name : '+@EmpFullName+ ', Salary : '+convert(varchar(20),@EmpSalary)
--
set next row values in the locat variables from the cursor
FETCH
NEXT FROM Cur_Employee INTO @EmpId, @EmpFullName, @EmpSalary
END
END
-- close cursor after use
CLOSE Cur_Employee
-- deallocate cursor after close to free the session
DEALLOCATE Cur_Employee
SET NOCOUNT OFF
Result after execution of the cursor as given
below:
|
Benefits
of Cursor:
Cursor carries a record
sets in a session. If you want process some set of records from one or more table,
you write a cursor on that table (Select queries) then you can traverse
all those record set one by one and you can perform your operations such as
retrieval, addition and removal which is very similar to FOR loops that we use
in other programming languages and provides an easy facility to traverse the
entire resultant record set.
Alternatives
to using a cursor:
- Use WHILE LOOPS
- Use temp tables
- Use derived tables
- Use correlated sub-queries
- Use the CASE statement
- Perform multiple queries
If you have no choice but to use a
server-side cursor in your application, try to use a FORWARD-ONLY or
FAST-FORWARD, READ-ONLY cursor. Using cursors can reduce concurrency and lead
to unnecessary locking and blocking. To help avoid this, use the READ_ONLY
cursor option if applicable, or if you need to perform updates, try to use the
OPTIMISTIC cursor option to reduce locking.
Disadvantages
of cursors: The
following information may vary depending on the specific database system.
- Fetching a row from the cursor may result in a network round trip each time.
- This uses much more network bandwidth than would ordinarily be needed for the execution of a single SQL statement like DELETE.
- Repeated network round trips can severely impact the speed of the operation using the cursor. Some DBMSs try to reduce this impact by using block fetch.
- Block fetch implies that multiple rows are sent together from the server to the client.
- The client stores a whole block of rows in a local buffer and retrieves the rows from there until that buffer is exhausted.
- Cursors allocate resources on the server, such as locks, packages, processes, and temporary storage.
- If a cursor is not properly closed (deallocated), the resources will not be freed until the SQL session (connection) itself is closed.
- This wasting of resources on the server can lead to performance degradations and failures.
