Answer Posted / bhaskar
row by row processing by Using Cursors
Row by row processing means for example we can update the
one column , on that column we have 500 records ,we are
going to update the all recodes row by row by using cursors.
SQL cursors have been a curse to database programming for
many years because of their poor performance. On the other
hand, they are extremely useful because of their
flexibility in allowing very detailed data manipulations at
the row level. Using cursors against SQL Server tables can
often be avoided by employing other methods, such as using
derived tables, set-based queries, and temp tables. A
discussion of all these methods is beyond the scope of this
article, and there are already many well-written articles
discussing these techniques.The focus of this article is
directed at using non-cursor-based techniques for
situations in which row-by-row operations are the only, or
the best method available, to solve a problem. Here, I will
demonstrate a few programming methods that provide a
majority of the cursor’s flexibility, but without the
dramatic performance hit.
Let’s begin by reviewing a simple cursor procedure that
loops through a table. Then we’ll examine a non-cursor
procedure that performs the same task.
if exists (select * from sysobjects where name =
N’prcCursorExample’)
drop procedure prcCursorExample
go
CREATE PROCEDURE prcCursorExample
AS
/*
** Cursor method to cycle through the Customer table and
get Customer Info for each iRowId.
**
** Revision History:
** —————————————————
** Date Name Description Project
** —————————————————
** 08/12/03 DVDS Create —-
**
*/
SET NOCOUNT ON
– declare all variables!
DECLARE @iRowId int,
@vchCustomerName nvarchar(255),
@vchCustomerNmbr nvarchar(10)
– declare the cursor
DECLARE Customer CURSOR FOR
SELECT iRowId,
vchCustomerNmbr,
vchCustomerName
FROM CustomerTable
OPEN Customer
FETCH Customer INTO @iRowId,
@vchCustomerNmbr,
@vchCustomerName
– start the main processing loop.
WHILE @@Fetch_Status = 0
BEGIN
– This is where you perform your detailed row-by-row
processing.
– Get the next row.
FETCH Customer INTO @iRowId,
@vchCustomerNmbr,
@vchCustomerName
END
CLOSE Customer
DEALLOCATE Customer
RETURN
--------------------------
2.row by row processing in general
As you can see, this is a very straight-forward cursor
procedure that loops through a table called CustomerTable
and retrieves iRowId, vchCustomerNmbr and vchCustomerName
for every row. Now we will examine a non-cursor version
that does the exact same thing:
if exists (select * from sysobjects where name =
N’prcLoopExample’)
drop procedure prcLoopExample
go
CREATE PROCEDURE prcLoopExample
AS
/*
** Non-cursor method to cycle through the Customer table
and get Customer Info for each iRowId.
**
** Revision History:
** ——————————————————
** Date Name Description Project
** ——————————————————
** 08/12/03 DVDS Create —–
**
*/
SET NOCOUNT ON
– declare all variables!
DECLARE @iReturnCode int,
@iNextRowId int,
@iCurrentRowId int,
@iLoopControl int,
@vchCustomerName nvarchar(255),
@vchCustomerNmbr nvarchar(10)
@chProductNumber nchar(30)
– Initialize variables!
SELECT @iLoopControl = 1
SELECT @iNextRowId = MIN(iRowId)
FROM CustomerTable
– Make sure the table has data.
IF ISNULL(@iNextRowId,0) = 0
BEGIN
SELECT ‘No data in found in table!’
RETURN
END
– Retrieve the first row
SELECT @iCurrentRowId = iRowId,
@vchCustomerNmbr = vchCustomerNmbr,
@vchCustomerName = vchCustomerName
FROM CustomerTable
WHERE iRowId = @iNextRowId
– start the main processing loop.
WHILE @iLoopControl = 1
BEGIN
– This is where you perform your detailed row-by-row
processing.
– Reset looping variables.
SELECT @iNextRowId = NULL
– get the next iRowId
SELECT @iNextRowId = MIN(iRowId)
FROM CustomerTable
WHERE iRowId > @iCurrentRowId
– did we get a valid next row id?
IF ISNULL(@iNextRowId,0) = 0
BEGIN
BREAK
END
– get the next row.
SELECT @iCurrentRowId = iRowId,
@vchCustomerNmbr = vchCustomerNmbr,
@vchCustomerName = vchCustomerName
FROM CustomerTable
WHERE iRowId = @iNextRowId
END
RETURN
There are several things to note about the above procedure.
For performance reasons, you will generally want to use a
column like “iRowId” as your basis for looping and row
retrieval. It should be an auto-incrementing integer data
type, along with being the primary key column with a
clustered index.
There may be times in which the column containing the
primary key and/or clustered index is not the appropriate
choice for looping and row retrieval. For example, the
primary key and/or clustered index may have already been
built on a column using uniqueindentifier as the data type.
In such a case, you can usually add an auto-increment
integer data column to the table and build a unique index
or constraint on it.
The MIN function is used in conjunction with greater
than “>” to retrieve the next available iRowId. You could
also use the MAX function in conjunction with less than “<”
to achieve the same result:
SELECT @iNextRowId = MAX(iRowId)
FROM CustomerTable
WHERE iRowId < @iCurrentRowId
Be sure to reset your looping variable(s) to NULL before
retrieving the next @iNextRowId value. This is critical
because the SELECT statement used to get the next iRowId
will not set the @iNextRowId variable to NULL when it
reaches the end of the table. Instead, it will fail to
return any new values and @iNextRowId will keep the last
valid, non-NULL, value it received, throwing your procedure
into an endless loop. This brings us to the next point,
exiting the loop.
When @iNextRowId is NULL, meaning the loop has reached the
end of the table, you can use the BREAK command to exit the
WHILE loop. There are other ways of exiting from a WHILE
loop, but the BREAK command is sufficient for this example.
You will notice that in both procedures I have included the
comments listed below in order to illustrate the area in
which you would perform your detailed, row-level processing.
| Is This Answer Correct ? | 7 Yes | 2 No |
Post New Answer View All Answers
Why de-normalization required?
Explain what you mean by 3 tier architecture.
What are the purposes and advantages stored procedure?
What are the common performance issues in sql server?
What are different types of views?
What does the automatic recovery do?
What is a constant or literal in ms sql server?
You have a table with close to 100 million records recently, a huge amount of this data was updated now, various queries against this table have slowed down considerably what is the quickest option to remedy the situation?
To automatically record the time on which the data was modified in a table, which data type should you choose for the column?
Explain indexed views and partitioned view with their syntax.
What is mapping schema?
Explain the phases a transaction has to undergo?
What do you understand by the denormalisation?
List out the differences between global and local temp tables in sql server?
sql database suspect We have a sql database that is showing as suspect. How can we recover?
