Bookmarks

So what is a bookmark and why should you care? To put it simply, a bookmark is a row unique identifier on an open rowset that allows for very fast random seeks. In order to get a rowset with bookmarks, you must set the DBPROP_BOOKMARKS property to VARIANT_TRUE. The resulting rowset has the bookmark value at column ordinal zero and its size and type varies according to the database engine. On all versions of SQL Compact the bookmark type is a 32-bit integer (DBTYPE_I4 on SQL CE 2.0 and DBTYPE_UI4 on 3.0 and 3.5).

There are two important bookmark restrictions that you must be aware of:

1. The bookmark value is valid only for the particular rowset that generated it. You should not store this value for later use after the rowset closes because the bookmark values will be rendered invalid. Even if you open the same rowset again, there is no guarantee that the bookmark values will be the same. (Ok, now that I wrote this disclaimer I can tell you that apparently on base table cursors, SQL Compact does seem to reuse the same bookmark values. Nevertheless don't reuse them, please.)
2. There is no way to know a bookmark's value before loading the row data to memory. This means that if you are using bookmarks to navigate in your rowset, your code must visit the row before knowing its bookmark value, just like in a book.

So why should you care about bookmarks? I have found a very interesting use for them: editing table data without using unique indexes or primary keys. The bookmark gives you an exact and unique identifier to any row and the rowset pointer moves very quickly to that particular row.

Before you can seek to a known bookmark, your rowset must be created with the DBPROP_IRowsetBookmark property set to VARIANT_TRUE. Doing so exposes the optional IRowsetBookmark interface on the IRowset, and you can use it to very quickly position your rowset pointer to any valid bookmark by calling the PositionOnBookmark method.

Before looking at BLOBs, the next post will show how to edit data using a base table cursor and bookmarks.