Additional information about the FOR BROWSE option and the NO_BROWSETABLE option in SQL Server 2005
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.
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: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.
SET NO_BROWSETABLE ON
- 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.
- 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
- 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
- Turn on the NO_BROWSETABLE option. To do this, run the following Transact-SQL statements in the query window:
SET NO_BROWSETABLE ON GO
- 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:Notice the following output in the Results pane:
SELECT tleft.c1 FROM tleft RIGHT JOIN tright ON tleft.c1 = tright.c1 WHERE tright.c1 <> 2
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.
Article ID: 885146 - Last Review: 03/20/2007 07:46:06 - Revision: 1.5
- kbtsql kbdocfix kbtable kbhowto kbinfo KB885146