Search content within the blog

Wednesday, April 22, 2009

Cursors in Sqlserver -Part I

Cursor is a database object 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. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable.

The server side cursors were first added in SQL Server 6.0 release and now supported in all editions of SQL Server 7.0 and SQL Server 2000.

Before using cursor, you first must declare the cursor. Once a cursor has been declared, you can open it and fetch from it. You can fetch row by row and make multiple operations on the currently active row in the cursor. When you have finished working with a cursor, you should close cursor and deallocate it to release SQL Server resources.


Advantages of Using Cursors with the Query Processor :

Some advantages of using cursors in conjunction with the query processor are:

* 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.




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.

consumes lot of resources.

No comments:

Post a Comment