SQL
Cursor is a database object used by applications to manipulate data in a set on
a row-by-row basis; it’s like recordset in the ASP.
SQL
Server Cursor is a row base operator but cursor is not recommended because of
performance issue as it create different execution plan for each rows. So one
should look out for other i.e. try to implement logic with the help of while
loop or CASE statement or JOIN, SELECT, GROUP etc. statements.
But few
times the cursor is recommended and useful, for example if you just need to
update your DB only once through Query Analyzer and don’t really bothered about
performance, then cursor can be handful;
SQL
Server Cursor Basics: Before
jumping to example, let’s see basics of SQL Cursor statements.
Statement
|
Description
|
DECLARE
|
Declare
variables used in the code block
|
SET\SELECT
|
Initialize
the variables to a specific value
|
DECLARE
CURSOR
|
Populate
the cursor with values that will be evaluated. There are an equal number of
variables in the DECLARE <Name_Of_Cursor>
|
OPEN
|
Open
the cursor to begin data processing
|
FETCH
NEXT
|
Assign
the specific values from the cursor to the variables; This logic is used for
the initial population before the WHILE statement and then again during each
loop in the process as a portion of the WHILE statement
|
WHILE
|
Condition
to begin and continue data processing
|
BEGIN...END
|
Start
and end of the code block. There can be multiple BEGIN...END statements.
|
Processing
of Data
|
Any DML
statement or query logic you want to process.
|
CLOSE
|
Releases
the current data and associated locks, but permits the cursor to be re-opened
|
DEALLOCATE
|
Destroys
the cursor
|
Cursor
Example: For of all
let’s create one table; we’ll use this table in our example cursor.
/***** Create Table: CountryTable *****/
CREATE TABLE CountryTable
(
Id INT PRIMARY KEY,
Name NVARCHAR(50) NOT NULL
)
GO
/***** INSERT Into: CountryTable *****/
INSERT INTO CountryTable VALUES(1,'India')
INSERT INTO CountryTable VALUES(2,'USA')
INSERT INTO CountryTable VALUES(3,'Australia')
INSERT INTO CountryTable VALUES(4,'Germany')
INSERT INTO CountryTable VALUES(5,'UK')
|
Now we’ll create a simple cursor that’ll iterate over
this table and show all Id and Name of each country.
DECLARE @CountryId INT, @CountryName NVARCHAR(50)
/***** Declare Cursor *****/
DECLARE @GetCountryDetail CURSOR
/***** Set Cursor *****/
SET @GetCountryDetail = CURSOR FOR
SELECT Id, Name FROM CountryTable
/***** Open Cursor *****/
OPEN @GetCountryDetail
FETCH NEXT
FROM
@GetCountryDetail INTO @CountryId, @CountryName
WHILE @@FETCH_STATUS =
0
BEGIN
/*****
Print Result *****/
PRINT
'Country Id: ' +
CAST (@CountryId AS NVARCHAR(10))
+
' And Country Name: ' +
@CountryName
FETCH
NEXT
FROM
@GetCountryDetail INTO @CountryId, @CountryName
END
/***** Close Cursor *****/
CLOSE @GetCountryDetail
/***** Deallocate Cursor *****/
DEALLOCATE @GetCountryDetail
|
Output will be:
Country
Id: 1 And Country Name: India
Country
Id: 2 And Country Name: USA
Country
Id: 3 And Country Name: Australia
Country
Id: 4 And Country Name: Germany
Country
Id: 5 And Country Name: UK
|
Few points one should keep in mind regarding cursor:
·
Cursor is an actual object inside the database engine, there
is a little overhead involved in creating the cursor and destroying it.
·
Majority of cursor operations occur in tempdb, so a heavily
used tempdb will be even more overloaded with the use of cursors.
·
Once cursor is opened we have to close the cursor
·
After the usage cursor should be deallocated from the memory.
Comments
Post a Comment