Microsoft KB Archive/885146

= Additional information about the FOR BROWSE option and the NO_BROWSETABLE option in SQL Server 2005 =

Article ID: 885146

Article Last Modified on 3/20/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Beta 2

-







Beta Information
This article discusses a Beta release of a Microsoft product. The information in this article is provided as-is and is subject to change without notice.

No formal product support is available from Microsoft for this Beta product. For information about how to obtain support for a Beta release, see the documentation that is included with the Beta product files, or check the Web location where you downloaded the release.



INTRODUCTION
In SQL Server Books Online, the &quot;FOR Clause&quot; topic does not contain complete information. This article contains additional information to be included in the &quot;BROWSE&quot; argument section in the &quot;FOR Clause&quot; topic. This article also discusses a scenario that explains the additional information.



MORE INFORMATION
The &quot;Arguments&quot; section of the &quot;FOR Clause&quot; topic in SQL Server Books Online should include the following information in the &quot;BROWSE&quot; argument section:

Note When the unique index key columns of a table can accept null values, and the table is on the inner side of an outer join operation, the browse mode does not support the index.

The browse mode lets you scan the rows in your SQL Server table and update the data in your table one row at a time. To access a SQL Server table in your application in the browse mode, you must use one of the following two options:  The SELECT statement that you use to access the data from your SQL Server table must end with the keywords FOR BROWSE. When you turn on the FOR BROWSE option to use browse mode, temporary tables are created.  You must run the following Transact-SQL statement to turn on the browse mode by using the NO_BROWSETABLE option: SET NO_BROWSETABLE ON When you turn on the NO_BROWSETABLE option, all the SELECT statements behave as if the FOR BROWSE option is appended to the statements. However, the NO_BROWSETABLE option does not create the temporary tables that the FOR BROWSE option generally uses to send the results to your application. 

When you try to access the data from SQL Server tables in browse mode by using a SELECT query that involves an outer join statement, and when a unique index is defined on the table that is present on the inner side of an outer join statement, the browse mode does not support the unique index. The browse mode supports the unique index only when all the unique index key columns can accept null values. The browse mode does not support the unique index if the following conditions are true:
 * You try to access the data from SQL Server tables in browse mode by using a SELECT query that involves an outer join statement.
 * A unique index is defined on the table that is present on the inner side of an outer join statement.

To reproduce this behavior in the browse mode, follow these steps:  Start SQL Server Management Studio, and then connect to an instance of SQL Server 2005. Create a database, and then name the database SampleDB.  In the SampleDB database, create a tleft table and a tright table that both contain a single column that is named c1. Define a unique index on the c1 column in the tleft table, and set the column to accept null values. To do this, run the following Transact-SQL statements in an appropriate query window: CREATE TABLE tleft(c1 INT NULL UNIQUE) GO   CREATE TABLE tright(c1 INT NULL) GO </li>  Insert several values in the tleft table and the tright table. Make sure that you insert a null value in the tleft table. To do this, run the following Transact-SQL statements in the query window: INSERT INTO tleft VALUES(2) INSERT INTO tleft VALUES(NULL) INSERT INTO tright VALUES(1) INSERT INTO tright VALUES(3) INSERT INTO tright VALUES(NULL) GO </li>  Turn on the NO_BROWSETABLE option. To do this, run the following Transact-SQL statements in the query window: SET NO_BROWSETABLE ON   GO </li>  Access the data in the tleft table and the tright table by using an outer join statement in the SELECT query. Make sure that the tleft table is on the inner side of the outer join statement. To do this, run the following Transact-SQL statements in the query window: SELECT tleft.c1   FROM tleft RIGHT JOIN tright ON        tleft.c1 = tright.c1     WHERE tright.c1 <> 2 Notice the following output in the Results pane: <pre class="fixed_text">c1

NULL NULL </li></ol>

After you run the SELECT query to access the tables in the browse mode, the result set of the SELECT query contains two null values for the c1 column in the tleft table because of the definition of the right outer join statement. Therefore, in the result set, you cannot distinguish between the null values that came from the table and the null values that the right outer join statement introduced. You may receive incorrect results if you must ignore the null values from the result set.

Note If the columns that are included in the unique index do not accept null values, all the null values in the result set were introduced by the right outer join statement.

<div class="references_section">