A cursor is an object that specifies a row within a set of data. Cursors let you move data within a set, update, and delete data.
SQL Server supports several types of cursors, but one read-only, non-scrollable cursor that is specially optimized for fast performance and worthy of note is the firehose cursor. You serve up the firehose cursor with a FAST_FORWARD command, for sequential access and forward traversal or your database records. The firehose cursor is the default cursor type for Recordset objects when that Recordset is defined with the Execute method for Command and Connection objects. The firehose mode refers to a connection that is maintained for as long as data can be returned. A server sends the data in a continuous stream, and does not wait for client requests using this kind of cursor. The reason that this cursor is fast is that it only requires data to be read, and there are no updates required by the cursor in its operation.
You can't always use a firehose cursor, even though you might want to. When you use a SELECT statement to define a cursor, if the statement contains a TOP clause and references to text, ntext, or image data, SQL server will substitute a keyset cursor in place of a firehose cursor. Also, when a SELECT statement defines a cursor using tables that contain triggers (Transact-SQL scripts) with ones that don't, SQL Server will substitute a static cursor in place of the firehose cursor. A static cursor is much slower than a firehose cursor; avoid it if possible.
With a firehose cursor you can add, delete, and update records for a FORWARD_ONLY cursor, provided you have a correct LockType. However this is not the case for a read-only cursor. You will not also be able to set a bookmark to the current row as well; nor can you use the Resync method should you wish to see the changes made to a recordset.
About the author
Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database- and Web-related), training and technical documentation.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free SQL Server tips and scripts.
- Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
- Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.
