Viacheslav Eremin | Transact-SQL Reference for SQL 2000

Transact-SQL Reference

OPTION Clause

Specifies that the indicated query hint should be used throughout the entire query. Each query hint can be specified only once, although multiple query hints are permitted. Only one OPTION clause may be specified with the statement. The query hint affects all operators in the statement. If a UNION is involved in the main query, only the last query involving a UNION operator can have the OPTION clause. If one or more query hints causes the query optimizer to not generate a valid plan, error 8622 is produced.

Caution Because the query optimizer usually selects the best execution plan for a query, it is recommended that <join_hint>, <query_hint>, and <table_hint> be used only as a last resort by experienced database administrators.

Syntax

[ OPTION ( < query_hint > [ ,...n ) ]

< query_hint > ::=
    {    { HASH | ORDER } GROUP
    | { CONCAT | HASH | MERGE } UNION
    | { LOOP | MERGE | HASH } JOIN
    | FAST number_rows
    | FORCE ORDER
    | MAXDOP number
    | ROBUST PLAN
    | KEEP PLAN
    | KEEPFIXED PLAN
    | EXPAND VIEWS
    }

Arguments

{ HASH | ORDER } GROUP

Specifies that aggregations described in the GROUP BY, DISTINCT, or COMPUTE clause of the query should use hashing or ordering.

{ MERGE | HASH | CONCAT } UNION

Specifies that all UNION operations are performed by merging, hashing, or concatenating UNION sets. If more than one UNION hint is specified, the query optimizer selects the least expensive strategy from those hints specified.

{ LOOP | MERGE | HASH } JOIN

Specifies that all join operations are performed by loop join, merge join, or hash join in the whole query. If more than one join hint is specified, the optimizer selects the least expensive join strategy from the allowed ones.

If, in the same query, a join hint is also specified for a specific pair of tables, this join hint takes precedence in the joining of the two tables although the query hints still must be honored. Thus, the join hint for the pair of tables may only restrict the selection of allowed join methods in the query hint. See Hints for details.

FAST number_rows

Specifies that the query is optimized for fast retrieval of the first number_rows (a nonnegative integer). After the first number_rows are returned, the query continues execution and produces its full result set.

FORCE ORDER

Specifies that the join order indicated by the query syntax is preserved during query optimization.

MAXDOP number

Overrides the max degree of parallelism configuration option (of sp_configure) only for the query specifying this option. All semantic rules used with max degree of parallelism configuration option are applicable when using the MAXDOP query hint. For more information, see max degree of parallelism Option.

ROBUST PLAN

Forces the query optimizer to attempt a plan that works for the maximum potential row size, possibly at the expense of performance. When the query is processed, intermediate tables and operators may need to store and process rows that are wider than any of the input rows. The rows may be so wide that, in some cases, the particular operator cannot process the row. If this happens, SQL Server produces an error during query execution. By using ROBUST PLAN, you instruct the query optimizer not to consider any query plans that may encounter this problem.

KEEP PLAN

Forces the query optimizer to relax the estimated recompile threshold for a query. The estimated recompile threshold is the point at which a query is automatically recompiled when the estimated number of indexed column changes (update, delete, or insert) have been made to a table. Specifying KEEP PLAN ensures that a query will not be recompiled as frequently when there are multiple updates to a table.

KEEPFIXED PLAN

Forces the query optimizer not to recompile a query due to changes in statistics or to the indexed column (update, delete, or insert). Specifying KEEPFIXED PLAN ensures that a query will be recompiled only if the schema of the underlying tables is changed or sp_recompile is executed against those tables.

EXPAND VIEWS

Specifies that the indexed views are expanded and the query optimizer will not consider any indexed view as a substitute for any part of the query. (A view is expanded when the view name is replaced by the view definition in the query text.) This query hint virtually disallows direct use of indexed views and indexes on indexed views in the query plan.

The indexed view is not expanded only if the view is directly referenced in the SELECT part of the query and WITH (NOEXPAND) or WITH (NOEXPAND, INDEX( index_val [ ,...n ] ) ) is specified. For more information about the query hint WITH (NOEXPAND), see FROM.

Only the views in the SELECT portion of statements, including those in INSERT, UPDATE, and DELETE statements are affected by the hint.

Remarks

The order of the clauses in the SELECT statement is significant. Any of the optional clauses can be omitted, but when used, they must appear in the appropriate order.

SELECT statements are allowed in user-defined functions only if the select lists of these statements contain expressions that assign values to variables that are local to the functions.

A table variable, in its scope, may be accessed like a regular table and thus may be used as a table source in a SELECT statement.

A four-part name constructed with the OPENDATASOURCE function as the server-name part may be used as a table source in all places a table name can appear in SELECT statements.

Some syntax restrictions apply to SELECT statements involving remote tables. For information, see External Data and Transact-SQL.

The length returned for text or ntext columns included in the select list defaults to the smallest of the actual size of the text, the default TEXTSIZE session setting, or the hard-coded application limit. To change the length of returned text for the session, use the SET statement. By default, the limit on the length of text data returned with a SELECT statement is 4,000 bytes.

SQL Server raises exception 511 and rolls back the current executing statement if either of these occur:

The SELECT statement produces a result row or an intermediate work table row exceeding 8,060 bytes.
The DELETE, INSERT, or UPDATE statement attempts action on a row exceeding 8,060 bytes.

In SQL Server, an error occurs if no column name is given to a column created by a SELECT INTO or CREATE VIEW statement.

Selecting Identity Columns

When selecting an existing identity column into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

The SELECT statement contains a join, GROUP BY clause, or aggregate function.
Multiple SELECT statements are joined with UNION.
The identity column is listed more than once in the select list.
The identity column is part of an expression.

If any of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property. All rules and restrictions for the identity columns apply to the new table.

Old-Style Outer Joins

Earlier versions of SQL Server supported the definition of outer joins that used the *= and =* operators in the WHERE clause. SQL Server version 7.0 supports the SQL-92 standard, which provides join operators in the FROM clause. It is recommended that queries be rewritten to use the SQL-92 syntax.

Processing Order of WHERE, GROUP BY, and HAVING Clauses

This list shows the processing order for a SELECT statement with a WHERE clause, a GROUP BY clause, and a HAVING clause:

The WHERE clause excludes rows not meeting its search condition.
The GROUP BY clause collects the selected rows into one group for each unique value in the GROUP BY clause.
Aggregate functions specified in the select list calculate summary values for each group.
The HAVING clause further excludes rows not meeting its search condition.

Permissions

SELECT permissions default to members of the sysadmin fixed server role, the db_owner and db_datareader fixed database roles, and the table owner. Members of the sysadmin, db_owner, and db_securityadmin roles, and the table owner can transfer permissions to other users.

If the INTO clause is used to create a permanent table, the user must have CREATE TABLE permission in the destination database.