Transact-SQL Editor Options (Query Execution/ANSI)

Article
02/20/2011

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 ANSI_DEFAULTS
This check box controls a group of SQL Server settings that collectively specify some SQL-92 standard behavior. If you select this check box, all of the subsequent check boxes are also selected. If you clear this check box, all of the subsequent check boxes are also cleared. If you select some but not all of the subsequent check boxes, the SET ANSI_DEFAULTS check box appears as a green square. By default, this check box appears as a green square.
SET QUOTED_IDENTIFIER
If you select the SET QUOTED_IDENTIFIER check box, you can use double quotation marks to delimit identifiers, and you must use single quotation marks to delimit literals. If you clear the check box, you cannot use quotation marks around identifiers, and you must follow all Transact-SQL rules for identifiers. You can use either single or double quotation marks to delimit literals. By default, this check box is selected.
SET ANSI_NULL_DFLT_ON
This setting affects the nullability of new columns only if their nullability is not specified in the CREATE TABLE and ALTER TABLE statements. If you select this check box, new columns that you create by using the ALTER TABLE and CREATE TABLE statements allow null values if you do not explicitly specify the columns' nullability status. This check box does not affect columns that you create by using an explicit NULL or NOT NULL. By default, this check box is selected.
SET IMPLICIT_TRANSACTIONS
If you select this check box, the connection is set to implicit transaction mode. When a connection is in this mode but not currently in a transaction, a transaction is started if any one of the following statements is executed: ALTER TABLE, CREATE, DELETE, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRUNCATE TABLE, or UPDATE. If you clear this check box, the connection is returned to autocommit transaction mode. In this mode, all individual statements are committed if they complete successfully. By default, this check box is cleared.
SET CURSOR_CLOSE_ON_COMMIT
If you select this check box, any open cursors are closed on commit or roll back in compliance with SQL-92. If you clear this check box, the cursor is not closed when a transaction is committed. By default, this check box is cleared.
SET ANSI_PADDING
This setting controls how the column stores values that are shorter than the defined size of the column or that have trailing blanks in char, varchar, binary, and varbinary data. For more information, see SET ANSI_PADDING.

Important Note:

It is recommended that you always leave this check box selected.
SET ANSI_WARNINGS
This setting specifies SQL-92 standard behavior for several error conditions. If you select this check box, a warning appears if null values appear in aggregate functions (such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT) or if divide-by-zero and arithmetic overflow errors cause the statement to be rolled back. If you clear this check box, no warnings appear, and divide-by-zero and arithmetic overflow errors cause null values to be returned. By default, this check box is selected.
SET ANSI_NULLS
This setting specifies SQL-92 compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when you use them with null values. If you select this check box, a SELECT statement that uses WHEREColumnName = NULL returns zero rows even if the column contains null values. A SELECT statement that uses WHEREColumnName <> NULL returns zero rows even if the column contains nonnull values. If you clear this check box, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement that uses WHEREColumnName = NULL returns the rows that have null values in that column. A SELECT statement that uses WHEREColumnName <> NULL returns the rows that have nonnull values in that column. In addition, a SELECT statement that uses WHEREColumnName <> XYZ_value returns all rows that are not XYZ value and that are not NULL. By default, this check box is selected.
Reset to Default
You can click this button to reset these options to their default states.

Share via

Transact-SQL Editor Options (Query Execution/ANSI)

See Also

Tasks

Reference

Concepts

Additional resources