Additional information about the FOR BROWSE option and the NO_BROWSETABLE option in SQL Server 2005 (885146)

MORE INFORMATION

The "Arguments" section of the "FOR Clause" topic in SQL Server Books Online should include the following information in the "BROWSE" 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:

1. Start SQL Server Management Studio, and then connect to an instance of SQL Server 2005.
2. Create a database, and then name the database SampleDB.
3. 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
   

4. 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
   

5. Turn on the NO_BROWSETABLE option. To do this, run the following Transact-SQL statements in the query window:

   	SET NO_BROWSETABLE ON
   	GO
   

6. 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:

   c1
   ----
   NULL
   NULL

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.