Transact-SQL Editor Options
This topic contains information about some of the options of the Transact-SQL Editor. To set these options, navigate to the Option dialog through the Tools\Options menu.
Query Execution
| Property | Description |
|---|---|
| SET ROWCOUNT | The default value of 0 indicates that SQL Server will wait for results until all results are received. Provide a value greater than 0 if you want SQL Server to halt the query after obtaining the specified number of rows. To turn this option off (so that all rows are returned), specify SET ROWCOUNT 0. |
| SET TEXTSIZE | The default value of 2,147,483,647 bytes indicates that SQL Server will provide a complete data field up to the limit of text, ntext, nvarchar(max), and varchar(max) data fields. It does not affect the XML data type. Provide a smaller number to limit results in the case of large values. Columns greater than the number provided will be truncated. |
| Execution time-out | Indicates the number of seconds to wait before canceling the query. A value of 0 indicates an infinite wait, or no time-out. |
| By default, open new queries in SQLCMD Mode | Select this check box to open new queries in SQLCMD mode. This check box is visible only when the dialog box is opened through the Tools menu. When you select this option, be aware of the following limitations: - IntelliSense in the Database Engine Query Editor is turned off. - Because Query Editor does not run from the command line, you cannot pass in command-line parameters such as variables. - Because Query Editor cannot respond to operating-system prompts, you must be careful not to run interactive statements. |
| SET NOCOUNT | Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results. For more information, see SET NOCOUNT. |
| SET NOEXEC | When ON, tells SQL Server to compile each batch of Transact-SQL statements but not to execute them. When OFF, tells SQL Server to execute all batches after compilation.For more information, see SET NOEXEC. |
| SET PARSEONLY | Checks the syntax of each Transact-SQL statement and returns any error messages without compiling or executing the statement. For more information, see SET PARSEONLY. |
| SET CONCAT_NULL_YIELDS_NULL | Controls whether or not concatenation results are treated as null or empty string values.For more information, see SET CONCAT_NULL_YIELDS_NULL. |
| SET ARITHABORT | Terminates a query when an overflow or divide-by-zero error occurs during query execution. For more information, see SET ARITHABORT. |
| SET SHOWPLAN_TEXT | Causes SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed. For more information, see SET SHOWPLAN_TEXT. |
| SET STATISTICS TIME | Displays the number of milliseconds required to parse, compile, and execute each statement. |
| SET STATISTICS IO | Causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements. |
| SET TRANSACTION ISOLATION LEVEL | Controls the default transaction locking behavior for all SQL Server SELECT statements issued by a connection. For more information, see SET TRANSACTION ISOLATION LEVEL. |
| SET LOCK_TIMEOUT | Specifies the number of milliseconds that a statement waits for a lock to be released. For more information, see SET LOCK_TIMEOUT |
| SET QUERY_GOVERNOR_COST_LIMIT | Overrides the currently configured value for the current connection. For more information, see SET QUERY_GOVERNOR_COST_LIMIT. |
| SET ANSI_DEFAULTS | Controls a group of SQL Server settings that collectively specify some SQL-92 standard behavior. For more information, see SET ANSI_DEFAULTS. |
| SET QUOTED_IDENTIFIER | Causes SQL Server to follow the SQL-92 rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks either can be Transact-SQL reserved keywords or can contain characters not usually allowed by the Transact-SQL syntax rules for identifiers.For more information, see SET QUOTED_IDENTIFIER. |
| SET ANSI_NULL_DFLT_ON | Alters the session's behavior to override default nullability of new columns when the ANSI null default option for the database is false. For more information, see SET ANSI_NULL_DFLT_ON. |
| SET IMPLICIT_TRANSACTIONS | When ON, sets the connection into implicit transaction mode. When OFF, returns the connection to autocommit transaction mode. For more information, see SET IMPLICIT_TRANSACTIONS. |
| SET CURSOR_CLOSE_ON_COMMIT | Controls whether or not a cursor is closed when a transaction is committed. For more information, see SET CURSOR_CLOSE_ON_COMMIT. |
| SET ANSI_PADDING | Controls the way the column stores values shorter than the defined size of the column and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data. For more information, see SET ANSI_PADDING. |
| SET ANSI_WARNINGS | Specifies SQL-92 standard behavior for several error conditions.For more information, see SET ANSI_WARNINGS. |
| SET ANSI_NULLS | Specifies SQL-92 compliant behavior for the Equals (=) and Not Equal to (<>) comparison operators when they are used with null values.For more information, see SET ANSI_NULLS. |
Query Results
| Property | Description |
|---|---|
| Include the query in the result set | Returns the text of the query as part of the result set. |
| Include column headers when copying or saving the results | Include column headers (titles) when results are copied to the clipboard, or saved in a file. Clear this check box if you do not want saved or copied result data to have only the data, and not the column headings. |
| Discard results after execution | Free memory by discarding the query results after the screen display has received them. |
| Display results in a separate tab | Display the result set in a new document window, instead of at the bottom of the query document window. |
| Switch to results tab after the query executes | Automatically set the screen focus to the result set. |
| Maximum Characters Retrieved | Non XML data: Enter a number from 1 through 65535 to specify the maximum number of characters that will be displayed in each cell. Note: Specifying a large number of characters may cause data in the result set to appear truncated. The maximum number of characters displayed in each cell is dependent on the font size. When large result sets are returned, a high value in this box can cause SQL Server Management Studio to run low on memory and hinder system performance. XML data: Select 1 MB, 2 MB, or 5 MB. Select Unlimited to retrieve all characters. |
| Output format | By default the output is displayed in columns created by padding the results with spaces. Other options are using commas, tabs, or spaces to separate columns. Select the Custom delimiter check box to specify a different delimiting character in the Custom delimiter box. |
| Custom delimiter | Specify the character of your choice to separate columns. This option is available only if the Custom delimiter check box is selected in the Output format box. |
| Include column headers in the result set | Clear this check box if you do not want each column labeled with a column title:. |
| Scroll as results are received | Select this check box to keep the display focus on the most recently returned records at the bottom. Clear this check box to keep the display focus on the first rows received. |
| Right align numeric values | Select this check box to align numeric values to the right of the column. This option can make it easier to review numbers with a fixed number of decimal places. |
| Discard result after query executes | Frees memory by discarding the query results after the screen display has received them. |
| Display results in a separate tab | Select this check box to display the result set in a new document window instead of at the bottom of the query document window. |
| Switch to results tab after the query executes | Click to automatically set the screen focus to the result set. |
| Maximum number of characters displayed in each column | This value defaults to 256. Increase this value to display larger result sets without truncation. |
| Reset to Default | Resets all values on this page to the original default values. |