Difference between Static Cursors and Dynamic Cursors

Difference between Static Cursors and Dynamic Cursors

* A Static Cursor doesn't reflect data changes made to the DB once the ResultSet has been created whereas a Dynamic Cursor reflects the changes as and when they happen.
* A Static Cursor is much more performant than a Dynamic Cursor as it doesn't require further interaction with the DB server.
* A static cursor supports both Relative and Absolute Positioning whereas a Dynamic Cursor supports only Relative Positioning.
* A Static Cursor can be used for Bookmarking purposes as the data returned is static whereas a Dynamic Cursor can't be used for the same.

Static Cursor: A Database Cursor is called a Static Cursor if it captures the snapshot of data only at the time when the ResultSet (or Recordset in case of MS SQL Server) is created with no further DB interaction afterwards. And hence a Static Cursor will be unaware of any data changes made into the database after the ResultSet has been created. A Static Cursor facilitates scrolling through the static ResultSet and it supports both absolute and relative positioning. Reason being, the ResultSet is static and hence the cursor can always be sure of the position of all the records/rows. Relative positioning can be specified in terms of offsets from the current, top or bottom rows.

Dynamic Cursor: A Dynamic Cursor is the one which reflects all the data changes made into the database as and when they happen. This may require continuous re-ordering of data in the ResultSet due to the database changes and hence it's much more expensive than a Static Cursor. The possible re-ordering of records in the ResultSet doesn't allow a Dynamic Cursor to support absolute positioning as it can't be sure of the absolute position of a record/row in the ResultSet. Hence a Dynamic Cursor only supports relative positioning. Furthermore a Dynamic Cursor doesn't support Bookmarks for the obvious reasons.

參考來源：http://geekexplains.blogspot.com