Understanding Cursor Options in the SQL Server Driver for PHP
After v1.0 of the SQL Server Driver for PHP was released (back in July of 2008), the development team received lots of requests to implement “scrollable result sets” (i.e. random access to the rows in a result set) in the next release. They met this request by adding a new option to the sqlsrv_query and sqlsrv_prepare functions in the v1.1 release. Basically, in the $options parameter, you can now specify that one of 4 types of cursors be opened when a query is executed: forward-only (the default), static, dynamic, and keyset. Great! But what exactly is each of those and what does each one do? That’s what I’ll address in this post.
First, a disclaimer: The behavior of cursors is affected by the transaction isolation level set on the connection. To keep things simple here, I’ll assume that you are using the driver’s default value of SQLSRV_TXN_READ_COMMITTED. For a list of other possible values, see Connection Options. To understand more about transaction isolation levels, see Isolation Levels in the Database Engine.
Here is a high-level view of the 4 cursor types:
Forward-only cursors: This one is simple…a forward-only cursor does not provide random access to the rows of a result set. It provides forward-only, sequential access to the rows of a result set (starting with the first row). Forward-only cursors are very fast, but you sacrifice the flexibility of moving freely about the result set to get the speed.
Static cursors: A static cursor provides random access to the rows of a result set (optionally starting with any row in the result set). However, the result set is static in that if any other process makes a change (INSERT, UPDATE, or DELETE) that would affect the rows of the result set, the change won’t be visible until you close the cursor and open a new one. Another way to think about this is that with a static cursor you are working with a copy of a result set that no other process has access to. Static cursors are fast and flexible, but you have to weigh these benefits against the importance of having the most up-to-date information from your database.
Dynamic Cursors: A dynamic cursor is similar to a static cursor in that it provides random access to the rows of a result set, but a dynamic cursor also allows you to see changes to the result set made by other processes. In other words, with a dynamic cursor, if someone INSERTs, UPDATEs, or DELETEs a row that would be (or is) in your result set, you will be able to see those changes. Dynamic cursors aren’t as fast as static cursors, but you have access to the most up-to-date information in your database. Again, whether you want to use this type of cursor depends on the needs of your application.
Keyset Cursors: A keyset cursor (also called a keyset-driven cursor) is a mix between a static cursor and a dynamic cursor. A keyset cursor allows random access to the rows of a result set and it allows you to see UPDATEs made by other processes (but not INSERTs and DELETEs). This is possible because keys that uniquely identify the rows of a result set are stored in a temporary table when your query is executed. When a fetch is executed, the key for the fetched row is used to fetch the data from the actual table. So, if an update to the row has been made, it will be visible. However, one consequence of this is that you could have “empty” rows in a result set if a row has been deleted by another process after your query has been executed.
For information about using cursor options (including code examples) in the SQL Server Driver for PHP, see Specifying a Cursor Type and Selecting Rows. However, that topic only covers information about using the native sqlsrv driver. If you want to use a scrollable cursor with the PDO driver, see PDO::prepare. Also note that with the PDO driver you have only two options: PDO::CURSOR_FWDONLY and PDO::CURSOR_SCROLL. With the PDO::CURSOR_SCROLL option, you get a static cursor (see above). If you are interested in additional reading about cursors, I’d suggest topics in this section of the MSDN documentation: Scrollable Cursors.
Hope this is helpful.
Thanks.
-Brian
Comments
- Anonymous
May 13, 2010
Very nice!We are currently about to implement the SQL server driver in our company and this article comes very useful to me.Thanks a lotBenjamin - Anonymous
May 14, 2010
Thanks! Glad this was useful. If you have any questions, post as comments here or post questions in the forum: http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/threads. I'd be interested to know how things go.-Brian - Anonymous
May 31, 2011
Thank you very much for this article.One remark :"Keyset Cursors: .......to see UPDATEs made by other processes (but not UPDATEs and DELETEs). ...."I think you mean "(but not INSERTs and DELETEs)" - Anonymous
June 01, 2011
@Zjan-Yes...that's what I meant. :-)I've updated the post.Thanks.-Brian - Anonymous
September 21, 2012
it wil be nice if ur explanation wil be accompaned by images of cursor