Share via

Transact-SQL Editor Options (Query Execution/Advanced)

  • Article

Use this page to specify the options for running queries and scripts in the Transact-SQL editor. If you make changes with the Options dialog box, they will apply only to new instances of the editor. Existing instances will maintain the global settings that were in effect when that instance of the editor was created. If you make changes with the Query Options dialog box, they will apply only to the active Transact-SQL editor window. For more information, see How to: Set Options for Query Execution and Results.

  • SET NOCOUNT
    If you select this check box, the count of the number of rows does not appear as a message with the result set. This check box is cleared by default. For more information, see SET NOCOUNT (Transact-SQL).

  • SET NOEXEC
    If you select this check box, all Transact-SQL statements in your query are compiled but not executed. This check box is cleared by default. For more information, see SET NOEXEC (Transact-SQL).

  • SET PARSEONLY
    If you select this check box, the syntax of each Transact-SQL statement in your query is evaluated, but the statements are not compiled or executed. This check box is cleared by default. For more information, see SET PARSEONLY (Transact-SQL).

  • SET CONCAT_NULL_YIELDS_NULL
    If you select this check box, queries that concatenate an existing value with a NULL always return a NULL as the result. If you clear this check box, queries that concatenate an existing value will a NULL will return the existing value. This check box is selected by default. For more information, see SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).

  • SET ARITHABORT
    If you select this check box, the query or batch is terminated if an INSERT, DELETE, or UPDATE statement encounters an arithmetic error (overflow, divide-by-zero, or a domain error) during expression evaluation. If you clear this check box, a NULL is provided for that value if possible, the query continues, and a message appears with the result. This check box is selected by default. For more information, SET ARITHABORT (Transact-SQL).

  • SET SHOWPLAN_TEXT
    If you select this check box, the instanced of SQL Server returns detailed information about how Transact-SQL statements are executed. If you clear this check box, SQL Server executes the statements and does not generate a report. This check box is cleared by default. For more information, see SET SHOWPLAN_TEXT (Transact-SQL).

  • SET STATISTICS TIME
    If you select this check box, time statistics are returned with each query. This value represents the number of milliseconds that are required to parse, compile, and execute each statement. If you clear this check box, no time statistics are returned. This check box is cleared by default. For more information, see SET STATISTICS TIME (Transact-SQL).

  • SET STATISTICS IO
    If you select this check box, statistics regarding input and output (I/O) are returned with each query. This value represents the amount of disk activity generated by Transact-SQL statements. If this check box is cleared, no I/O statistics are returned. This check box is cleared by default. For more information, see SET STATISTICS IO (Transact-SQL).

  • SET TRANSACTION ISOLATION LEVEL
    The value that you specify controls the locking and row versioning behavior of Transact-SQL statements that a connection to SQL Server issues.

    • READ UNCOMMITTED specifies that statements can read rows that have been modified by other transactions but not yet committed.

    • READ COMMITTED specifies that statements cannot read data that has been modified but not committed by other transactions. This setting prevents dirty reads. Other transactions can change between individual statements within the current transaction, which results in nonrepeatable reads or phantom data. This option is the SQL Server default.

    • REPEATABLE READ specifies that statements cannot read data that has been modified but not yet committed by other transactions. In addition, no other transactions can modify data that has been read by the current transaction until the current transaction completes.

    • SNAPSHOT specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can recognize only those data modifications that were committed before the start of the transaction. Data modifications that other transactions made after the start of the current transaction are not visible to statements that are executing in the current transaction. As a result, the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

    The default value is READ COMMITTED. For more information, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

  • SET DEADLOCK_PRIORITY
    The value that you specify controls how the query reacts if a deadlock occurs. Specify Normal if you want each query to have the same priority when a deadlock occurs. Specify Low if you want this query to lose any deadlock conflict and be selected as the query to be terminated. The default value is Normal. For more information, see SET DEADLOCK_PRIORITY (Transact-SQL).

  • SET LOCK_TIMEOUT
    The value that you specify controls lock time-out behavior. A value of -1 indicates that locks are held until transactions are completed. A value of 0 indicates that a message should be returned as soon as a lock is encountered. A value of greater than 0 milliseconds indicates that a transaction should be terminated if the locks for that transaction must be held for greater than that amount of time. The default value is -1. For more information, SET LOCK_TIMEOUT (Transact-SQL).

  • SET QUERY_GOVERNOR_COST_LIMIT
    The value that you specify indicates an upper limit for the time in which a query can run. Query cost refers to the estimated amount of time, in seconds, that is required to complete a query on a specific hardware configuration. The default setting of 0 indicates no limit to how long a query will run. For more information, see SET QUERY_GOVERNOR_COST_LIMIT (Transact-SQL).

  • Suppress provider message headers
    If you select this check box, no provider-specific messages (such as the SQLClient provider) appear. If you clear this check box, you can see the provider messages when you troubleshoot queries that might be failing at the provider level. This check box is selected by default.

  • Disconnect after the query executes
    If you select this check box, the connection to SQL Server is terminated after the query completes. This setting is useful if you want to disconnect from the server after a long-running operation, such as re-indexing, completes. This check box is cleared by default.

  • Reset to Default
    You can click this button to reset all settings on this page to their default values.

See Also

Tasks

How to: Set Options for Query Execution and Results

How to: Control the Gathering and Display of Client Statistics

Reference

Transact-SQL Editor Options (Editor Tab and Status Bar)

Transact-SQL Editor Options (Query Execution/General)

Transact-SQL Editor Options (Query Execution/ANSI)

Transact-SQL Editor Options (Query Results/General)

Transact-SQL Editor Options (Query Results/Grid)

Transact-SQL Editor Options (Query Results/Text)

Concepts

Editing Database Scripts and Objects with the Transact-SQL Editor