Generates totals that appear as additional summary columns at the end of the result set. When used with BY, the COMPUTE clause generates control-breaks and subtotals in the result set. You can specify COMPUTE BY and COMPUTE in the same query.
Syntax
[ COMPUTE { { AVG COUNT MAX MIN STDEV STDEVP VAR VARP SUM } (expression) } [,...n] [ BY expression [,...n] ]]
Arguments
AVG COUNT MAX MIN STDEV STDEVP VAR VARP SUM
Specifies the aggregation to be performed. These row aggregate functions are used with the COMPUTE clause.
Row aggregate function
Result
AVG
Average of the values in the numeric expression
COUNT
Number of selected rows
MAX
Highest value in the expression
MIN
Lowest value in the expression
STDEV
Statistical standard deviation for all values in the expression
STDEVP
Statistical standard deviation for the population for all values in the expression
SUM
Total of the values in the numeric expression
VAR
Statistical variance for all values in the expression
VARP
Statistical variance for the population for all values in the expression
There is no equivalent to COUNT(*). To find the summary information produced by GROUP BY and COUNT(*), use a COMPUTE clause without BY.
These functions ignore null values.
The DISTINCT keyword is not allowed with row aggregate functions when they are specified with the COMPUTE clause.
When you add or average integer data, SQL Server treats the result as an int value, even if the data type of the column is smallint or tinyint
FOR BROWSE Clause
Specifies that updates be allowed while viewing data in client applications using DB-Library.
A table can be browsed in an application under these conditions:
The table includes a time-stamped column (defined with the timestamp data type).
The table has a unique index.
The FOR BROWSE option is at the end of the SELECT statement(s) sent to SQL Server.
Note It is not possible to use the
The FOR BROWSE option cannot appear in SELECT statements joined by the UNION operator.
Syntax
[ FOR BROWSE ]
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. The OPTION clause must be specified with the outermost query of 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, SQL Server recompiles the query without the specified query hints, and issues a SQL Server Profiler event.
Caution Because the query optimizer usually selects the best execution plan for a query, it is recommended that
Syntax
[ OPTION (
Arguments
{HASH ORDER} GROUP
Specifies that aggregations described in the GROUP BY 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 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 for the allowed ones. If, in the same query, a join hint is also specified for a specific pair of tables, it takes precedence in the joining of the two tables.
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.
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.
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.
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.
