Options (Query Execution: SQL Server: Advanced Page)
Updated: August 18, 2016
Several options are available using the SET command. Use this page to specify a set option for running Microsoft SQL Server queries in the SQL Server Query Editor. They have no effect on other code editors. Changes to these options are only applied to new SQL Server queries. To change the options for the current queries, click Query Options on the Query menu or the shortcut menu of the SQL Server Query window. Under Execution, click Advanced. For more information on each of these, see SQL Server Books Online.
Does not return the count of the number of rows, as a message with the result set. This check box is cleared by default.
Does not run the query. This check box is cleared by default.
Checks the syntax of each query but does not run the queries. This check box is cleared by default.
When this check box is selected, queries that concatenate an existing value with a NULL, always return a NULL as the result. When this check box is cleared, an existing value concatenated with a NULL, returns the existing value. This check box is selected by default.
SET XACT_ABORT ON
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF.
When this check box is selected, when an INSERT, DELETE, or UPDATE statement encounters an arithmetic error (overflow, divide-by-zero, or a domain error) during expression evaluation the query or batch is terminated. When this check box is cleared, a NULL is provided for that value if possible, the query continues, and a message is included with the result. For more information, see SET ARITHABORT (Transact-SQL). This check box is selected by default.
When this check box is selected, the query plan is returned in text format with each query. This check box cleared by default.
SET STATISTICS TIME
When this check box is selected, the time statistics are returned with each query. This check box is cleared by default.
SET STATISTICS IO
When this check box is selected, statistics regarding input and output are returned with each query. This check box is cleared by default.
SET TRANSACTION ISOLATION LEVEL
The READ COMMITTED transaction isolation level is set by default. For more information, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL). SNAPSHOT transaction isolation level is not available. To use SNAPSHOT isolation, add the following Transact-SQL statement:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; GO
SET DEADLOCK PRIORITY
The default value of Normal allows each query to have the same priority when a deadlock occurs. Select a priority of Low if you want this query to lose any deadlock conflict and be selected as the query to be terminated.
SET LOCK TIMEOUT
The default value of -1 indicates that locks are held until transactions are completed. A value of 0 means not to wait at all and return a message as soon as a lock is encountered. Provide a value of greater than 0 milliseconds to terminate a transaction if the locks for transaction must be held for greater than that time.
Use the QUERY_GOVERNOR_COST_LIMIT option to specify an upper limit for the time in which a query can run. Query cost refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration. The default setting of 0 indicates no limit to the length of time a query will run.
Suppress provider message headers
When this check box is selected, status messages from the provider (such as the SQLClient provider) are not displayed. This check box is selected by default. Clear this check box to see the provider messages when troubleshooting queries that may be failing at the provider level.
Disconnect after the query executes
When this check box is selected, the connection to SQL Server is terminated after the query completes. This check box is cleared by default.
Reset to Default
Resets all values on this page to the original default values.