Advantages and Disadvantages of Cursor?

Answers were Sorted based on User's Feedback



Advantages and Disadvantages of Cursor?..

Answer / krishna sandeep

Advantage:

you can do row vise validation from a table

Disadvantage:

of example you are retrieving 100 row...
every single row you retrieve will hit the server as a
single select query so performance will be low.

Is This Answer Correct ?    122 Yes 17 No

Advantages and Disadvantages of Cursor?..

Answer / karthikumar.r

advantage:we can do row vise validation.
disadvantage:performance is less

Is This Answer Correct ?    103 Yes 18 No

Advantages and Disadvantages of Cursor?..

Answer / fusion

You can perform operation on each row.

Disadv. - Consumes more resources

Is This Answer Correct ?    55 Yes 9 No

Advantages and Disadvantages of Cursor?..

Answer / bed singh

1. We can do row vise validation but performance is
slow.
2. Each time we fetch a row from the cursor, it result
a network round trip, where as a normal select statement
query make only one round trip.
3. If the ResultSet is less than 50 or 100 records it
is better to go for implicit cursors. If the result set is
large then you should use explicit cursors. Otherwise it
will put Burdon on CPU.

Is This Answer Correct ?    43 Yes 12 No

Advantages and Disadvantages of Cursor?..

Answer / nikunj padaliya

Advantage of Cursor:

Row-by-row operations: Cursors are best used when performing
row-by-row operations that can't be accomplished with
set-based operations (i.e, when you need to fire a stored
procedure once per row in a table).

Efficiency: The query optimizer automatically selects the
appropriate query plan, so the developer does not need to
design a complex algorithm to access the required data.
Adaptability: As data changes or indexes are added or
dropped, the query optimizer automatically adapts its
behavior by using alternative plans.

Fewer errors: Instead of the developer handling data and
algorithms in the application, the SQL Server Compact 3.5
Database Engine natively supports the required operations.
--------------------------------------------------
Disadvantage of Cursor:

Resources consumed by cursors: it occupies memory from your
system that may be available for other processes. Poorly
written cursors can completely deplete available memory.

Speed and performance issues: Cursors can be faster than a
while loop but they do have more overhead. If your cursor
will not be updating the base tables, use a FAST_FORWARD
cursor for optimal performance. The problem with cursor
speed is that, in many cases, the operation can be more
efficiently written as a set operation or perhaps in a while
loop. It's these cursor rewrites that lead to the impression
that cursors are evil – or cursed.
Another factor affecting cursor speed is the number of rows
and columns brought into the cursor. Time how long it takes
to open your cursor and fetch statements. If it's lengthy,
look carefully at your cursor logic; see if you can remove
columns from the declare statement, and change your where
clause in the declare statement to only return rows the
cursor needs. If the fetch statements themselves are lengthy
or consuming too much IO or CPU, look at the cursor declare
statement and ensure you have optimal indexes in place on
your base tables or temporary tables.

Wrong tool for the wrong task: Cursors are frequently the
wrong tool for the wrong task. They're used for
quick-and-dirty programming when a developer does not have a
good understanding of set operations or they're used for the
wrong task entirely.
Subtle errors:
Cursors sometimes introduce subtle errors. We already
looked at a few:
1.Failing to check the value of @@Fetch_Status
2.Improper indexes on the base tables in your results set or
FETCH statement
3.Too many columns being dragged around in memory, which are
never referenced in the subsequent cursor operations
(probably the result of legacy code)
4.WHERE clause that brings too many rows into the cursor,
which are subsequently filtered out by cursor logic.

Is This Answer Correct ?    44 Yes 13 No

Advantages and Disadvantages of Cursor?..

Answer / hiral bhatt

Dear All,

Cursor, its an object in SQL Server and the main advantages
of cursor is that whenevr we need to fetch records in row
by row Manner..

But using cursor, it itself is not a good concept, we have
another alternate for cursor like Temp Table.

Regards
Hiral Bhatt
Sr. Software Engg.

Is This Answer Correct ?    43 Yes 21 No

Advantages and Disadvantages of Cursor?..

Answer / sharjeel

Hi Friends,

Cursor is more useful programming concept. It is a Data
Type which is used to define multivalue variable. Because
with cursor we fetch more records from tables or rows and
store in a static variable.

After Fetching values in a varible we can use that for
compairing from other values or other cursor.

Regards
Sharjeel Khan
Software Engineer.

Is This Answer Correct ?    29 Yes 12 No

Advantages and Disadvantages of Cursor?..

Answer / navneet chandra

Hi All,

In pl/sql if you want perform some actions more than one
records you should user these cursors only. By using these
cursors you process the query records. you can easily move
the records and you can exit from procedure when you
required by using cursor attributes.


disadvantage:

using implicit/explicit cursors are depended by Situation.
If the result set is less than 50 or 100 records it is
better to go for implicit cursors. if the result set is
large then you should use explicit cursors. other wise it
will put burden on CPU.

Is This Answer Correct ?    19 Yes 7 No

Advantages and Disadvantages of Cursor?..

Answer / ruchita

Adv:cursor is a private sql area in which
row by row operatioms and validations can be done
disadv:resource reqired are more

Is This Answer Correct ?    9 Yes 1 No

Advantages and Disadvantages of Cursor?..

Answer / sarat

Cursors allow row-by-row processing of the resultsets.

Disadvantages of cursors: Each time you fetch a row from the
cursor, it results in a network roundtrip, where as a normal
SELECT query makes only one rowundtrip, however large the
resultset is. Cursors are also costly because they require
more resources and temporary storage (results in more IO
operations). Further, there are restrictions on the SELECT
statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead
of cursors.

Is This Answer Correct ?    9 Yes 2 No

Post New Answer

More SQL Server Interview Questions

What is data modeling and Reterminal integrity?

0 Answers  


Explain different types of collation sensitivity?

0 Answers  


What is the default value of CHAR type?

10 Answers   Bosch,


What is apply operator in sql?

0 Answers  


What do you mean by sql server 2005 express management tools?

0 Answers  






What is use of attribute hierarchy optimized state? : sql server analysis services, ssas

0 Answers  


Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?

2 Answers  


What is an expensive query?

0 Answers  


Write a sql query to sort on different column name according to the parameters passed in the function?

0 Answers  


From where can you change the default port?

3 Answers  


Assume,there are three tables in a database, Can i have both primary key and foreign key for each table in the same database?

4 Answers  


How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

0 Answers  


Categories