What is the TSQL "FOR BROWSE" option used for?

Question

I am working on a query, and have found minimal documentation from Microsoft on the TSQL "FOR BROWSE" option on a select statement.

I have seen FOR BROWSE documented as an option to cursors, but I haven't been able to find any good examples of using this, or reasons to use FOR BROWSE on a SELECT statement.

What are good reasons to use FOR BROWSE in a TSQL SELECT statement?

I am using SQL Server 2008 and 2012.

Solution 1

As far as I can tell. It appears to be an interface for implementing optimistic concurrency control within an application where one or more users will be accessing and updating data from the same source at the same time. It also appears to work in conjunction with a compatible front end library (DB-Library). However, it would appear that this is a somewhat deprecated mechanism as you can achieve all of the above without using the "For Browse" statement. This can be further confirmed by the necessity to create two DBPROCESS structures which are the results of the DB-Library (a deprecated C library) call "dbopen".

In addition, browse mode requires two DBPROCESS structures: one for selecting the data and another for updating based on the selected data. src

Here is an example of using a "For Browse" query in conjunction with the DB-Library.

Ultimately, it would be reasonable to conclude that this mechanism still exists for the purpose of backwards-compatibility. So unless you're maintaining a C based client using the DB-Library, I wouldn't worry too much about this sql "for clause".

Some other sources

• SQL Server Reference Manual - Browse Mode
• Additional information about the FOR BROWSE option
• Using browse mode instead of cursors

One more observation

Cursors declared with FOR BROWSE must wait for uncommitted changes (made by anyone, including the cursor owner) only during the OPEN CURSOR operation. After the cursor is open, subsequent changes do not cause the cursor to wait. When a cursor is reopened, it can be blocked by uncommitted changes. src

Solution 2

Appending FOR BROWSE to an SQL SELECT statement or using SET NO_BROWSETABLE ON on a SQL Server connection may be required to retrieve more elaborate schema information.

This may be useful if an application interacts directly or indirectly via the ODBC layer with SQL Server because some of the details returned by the ODBC function SQLColAttribute will return an empty string unless the SQL SELECT has the FOR BROWSE appended.

For example, if your C++ app uses libodbc++ and you get a result set from the query SELECT * FROM A, B you will not be able to retrieve the table names of the columns from the ResultSetMetaData object. However, if you add FOR BROWSE, the table names will be populated corretly.