Friday, September 4, 2015

SQL – Cursors

http://www.sql-datatools.comThis 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.
  1. 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.
  2. 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

EmpID
FirstName
LastName
Salary
Address
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
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:
ID : 1, Name : Ryan Arjun, Salary : 22000
ID : 2, Name : Jullie Philips, Salary : 15000
ID : 3, Name : Black Smith, Salary : 19000
ID : 4, Name : Rosey White, Salary : 19000
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:
  1. Use WHILE LOOPS
  2. Use temp tables
  3. Use derived tables
  4. Use correlated sub-queries
  5. Use the CASE statement
  6. 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.
  1. Fetching a row from the cursor may result in a network round trip each time.
  2. This uses much more network bandwidth than would ordinarily be needed for the execution of a single SQL statement like DELETE.
  3. 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.
  4. Block fetch implies that multiple rows are sent together from the server to the client.
  5. The client stores a whole block of rows in a local buffer and retrieves the rows from there until that buffer is exhausted.
  6. Cursors allocate resources on the server, such as locks, packages, processes, and temporary storage.
  7. If a cursor is not properly closed (deallocated), the resources will not be freed until the SQL session (connection) itself is closed.
  8. This wasting of resources on the server can lead to performance degradations and failures.

2 comments:

  1. DROP a Stored Procedure in SQL Server https://lnkd.in/bG5byVk

    ReplyDelete
  2. Are you recommending database professional process records on a row-by-row basis?

    ReplyDelete