Shulou(Shulou.com)06/01 Report--

1. SET ANSI_DEFAULTS {ON | OFF}

Example:

SET ANSI_DEFAULTS ON; DBCC USEROPTIONS; SET ANSI_DEFAULTS OFF

Set up SET ANSI_DEFAULTS ON and use the DBCC USEROPTIONS statement to display the affected settings.

2. SET ANSI_NULLS {ON | OFF}

T-SQL supports allowing the comparison operator to return TRUE or FALSE when comparing to a null value.

The ANSI_NULLS setting does not affect join columns that contain NULL. The row that contains NULL in the join column is not part of the result set.

When SET ANSI_NULLS is ON, the SELECT statement using WHERE column_name = NULL returns zero rows even if the column_name contains null values. Even if the column_name contains a non-null value, the SELECT statement that uses WHERE column_name NULL returns zero rows.

When SET ANSI_NULLS is OFF, the equal (=) and not equal () comparison operators do not comply with the ISO standard. Use the SELECT statement of WHERE column_name = NULL to return rows with null values in column_name. Use the SELECT statement of WHERE column_name NULL to return rows with non-null values in the column. In addition, use the SELECT statement of WHERE column_name XYZ_value to return all rows that are neither XYZ_value nor NULL.

In other words, when set to on, you can only write: where xx is null or where xx is not null, while when set to off, it can be written like this: where xx = null or where xx null

If SET ANSI_NULLS ON, then in the SQL statement, to determine the non-empty writing, change it to: WHERE field IS NULL. After SET ANSI_NULLS ON, NULL = NULL will return false.

3. SET ANSI_PADDING {ON | OFF}

When set to ON, trailing zeros inserted into varchar columns in trailing spaces and binary values in character values are not clipped. Values are not populated by the length of the column. When set to OFF, trim the trailing spaces of the varchar column and the trailing zeros of the varbinary column. This setting affects only the definition of the new column.

When ANSI_PADDING is ON, Char (n) and binary (n) columns that allow null values are populated to the column length, while when ANSI_PADDING is OFF, trailing spaces and zeros are clipped, and Char (n) and binary (n) columns that do not allow null values are always populated to the column length.

4. SET ANSI_WARNINGS {ON | OFF}

ANSI_WARNINGS can affect the following situations:

When set to ON, a warning message is generated if null values appear in aggregate functions, such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT. When set to OFF, no warning is issued.

When set to ON, division by zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. When set to OFF, division by zero errors and arithmetic overflow errors will cause null values to be returned. If you attempt to perform an INSERT or UPDATE operation on a character, Unicode, or binary column, and the length of the new value in those columns exceeds the maximum column size, a division by zero error and an arithmetic overflow error will cause null values to be returned.

If SET ANSI_WARNINGS is ON, the INSERT or UPDATE operation is canceled according to the ISO standard. Trailing spaces for character columns and trailing zeros for binary columns are ignored. When set to OFF, the data is clipped to the size of the column and the statement executes successfully.

5. SET ARITHABORT {ON | OFF}

Terminates the query when an overflow or division by zero error occurs during query execution.

If SET ARITHABORT is ON, these error conditions will cause the query or batch to terminate. If an error occurs within the transaction, the transaction is rolled back. If SET ARITHABORT is OFF and one of these errors occurs, a warning message is displayed and a null value is assigned to the result of the arithmetic operation.

Indicates that if neither SET ARITHABORT nor SET ARITHIGNORE is set, Microsoft ®SQL Server ™will return NULL and a warning message after executing the query.

If SET ARITHABORT is OFF and the INSERT, DELETE, or UPDATE statement encounters an arithmetic error (overflow, division by zero, or domain error) during the evaluation of the expression, SQL Server inserts or updates the NULL value. If the target column cannot be empty, the insert or update operation fails and the user receives an error message.

If SET ARITHABORT or SET ARITHIGNORE is OFF and SET ANSI_WARNINGS is ON, SQL Server still returns an error message when a division by zero or overflow error is encountered.

The settings for SET ARITHABORT are set at execution or run time, not at parse time.

SET ARITHABORT must be ON when creating or manipulating an index on a computed column or indexed view. If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on indexed tables on computed columns or indexed views will fail. For more information about the SET option settings necessary to calculate indexed views and indexes on columns, see "considerations when using SET statements" in SET.

Permissions:

SET ARITHABORT permissions are granted to all users by default.

6. SET ARITHIGNORE {ON | OFF}

Controls whether an error message is returned from an overflow or division by zero error during query execution.

The SET ARITHIGNORE setting only controls whether an error message is returned. Regardless of this setting, Microsoft ®SQL Server ™returns NULL in a calculation that contains an overflow or division by zero error. You can use the SET ARITHABORT setting to determine whether to terminate the query. This setting does not affect errors that occur during the execution of INSERT, UPDATE, and DELETE statements.

If SET ARITHABORT or SET ARITHIGNORE is OFF and SET ANSI_WARNINGS is ON, SQL Server still returns an error message when a division by zero or overflow error is encountered.

The settings for SET ARITHIGNORE are set at execution or run time, not at parse time.

Authority

SET ARITHIGNORE permissions are granted to all users by default.

7. SET CONCAT_NULL_YIELDS_NULL {ON | OFF}

Treat the concatenation result as a Null or an empty string value.

Controls whether string values are connected when it is a NULL or an empty string.

For example:

When the SET option CONCAT_NULL_YIELDS_NULL is set to ON, the expression 'abc' + NULL returns the value NULL.

When CONCAT_NULL_YIEDS_NULL is set to OFF, the result of the expression is' abc'.

8. SET CURSOR_CLOSE_ON_COMMIT {ON | OFF}

Indicates that the server will not close the cursor when the transaction is committed.

When SET CURSOR_CLOSE_ON_COMMIT is ON, this setting closes any open cursors on commit or rollback in compliance with ISO. If SET CURSOR_CLOSE_ON_COMMIT is ON, this setting complies with the ISO standard and closes all open cursors on commit or rollback. If SET CURSOR_CLOSE_ON_COMMIT is OFF, the cursor will not be closed when the transaction is committed.

9. SET DATEFIRST {number | @ number_var}

Number | @ number_var is an integer indicating the first day of the week, which can be one of the following values:

The first day of the week is 1 Monday, 2 Tuesdays, 3 Wednesdays, 4 Thurs, 5 Fridays, 6 Saturdays, 7 (default, American English) Sunday

Note:

Use the @ @ DATEFIRST function to check the current settings of SET DATEFIRST.

The settings for SET DATEFIRST are set at execution or run time, not at parse time.

Permissions: SET DATEFIRST permissions are granted to all users by default.

10. SET DEADLOCK_PRIORITY {LOW | NORMAL | HIGH | | @ deadlock_var | @ deadlock_intvar}

:: = {- 10 |-9 |-8 |... | | 0 |... | | 8 | 9 | 10} |

Controls how the session reacts in the event of a deadlock situation. If both processes lock the data, and each process cannot release its own lock until the other process releases its own lock, a deadlock condition occurs.

Parameters:

LOW

Specifies that the current session is the preferred deadlock victim. Microsoft ®SQL Server ™automatically rolls back the transaction of the deadlock victim and returns deadlock error message 1205 to the client application. NORMAL

Specifies that the session returns to the default deadlock handling method.

@ deadlock_varbr/ > NORMAL

Specifies that the session returns to the default deadlock handling method.

@ deadlock_var

Note: the settings for SET DEADLOCK_PRIORITY are set at execution or run time, not at analysis time.

Permissions: SET DEADLOCK_PRIORITY permissions are granted to all users by default.

11 、 SET LOCK_TIMEOUT timeout_period

Specifies the number of milliseconds that the statement waits for the lock to be released.

Parameters:

Timeout_period

Is the number of milliseconds that have elapsed before Microsoft ®SQL Server ™returns a locking error. A value of-1 (the default) indicates that there is no timeout period (that is, wait indefinitely).

An error is returned when the lock wait exceeds the timeout value. A value of 0 means that there is no waiting at all and information is returned as soon as a lock is encountered.

Note:

At the beginning of the connection, the value of this setting is-1. After the settings are changed, the new settings remain in effect for the rest of the connection.

The settings for SET LOCK_TIMEOUT are set at execution or run time, not at parse time.

The READPAST lock prompt provides another way for this SET option.

Permissions: SET LOCK_TIMEOUT permissions are granted to all users by default.

Example:

The following example sets the lock timeout period to 1800 milliseconds.

SET LOCK_TIMEOUT 1800

12. SET NOCOUNT {ON | OFF}

Prevents messages showing the count of rows affected by Transact-SQL statements or stored procedures from being returned in the result set.

Note:

When SET NOCOUNT is ON, no count is returned. Returns a count when SET NOCOUNT is OFF.

The The @ @ ROWCOUNT function is updated even when SET NOCOUNT is ON.@@ROWCOUNT function will be updated, even in SET NOCOUNT ON.

When SET NOCOUNT is ON, DONE_IN_PROC messages for each statement in the stored procedure are not sent to the client. If the stored procedure contains statements that do not return much actual data, or if the procedure contains Transact-SQL loops, network traffic is greatly reduced, so setting SET NOCOUNT to ON can significantly improve performance.

The settings specified by SET NOCOUNT take effect at execution or run time, not at analysis time.

13. SET NOEXEC {ON | OFF}

Compile each query but not execute it.

Note:

When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them. When SET NOEXEC is set to OFF, all batches are executed after compilation.

Statement execution in SQL Server consists of two phases: compilation and execution. This setting can be used to have SQL Server validate the syntax and object name in the Transact-SQL code when it executes. It can also be used to debug some statements that are usually in larger batches.

The SET NOEXEC setting is set at execution or run time, not at parse time.

Permissions: membership in the public role is required.

14. SET PARSEONLY {ON | OFF}

Checks the syntax of each Transact-SQL statement and returns any error messages, but does not compile and execute the statement.

Note:

When SET PARSEONLY is ON, SQL Server parses only statements. When SET PARSEONLY is OFF, SQL Server compiles and executes the statement.

The settings for SET PARSEONLY are set at parse time, not at execution or run time.

Do not use PARSEONLY in stored procedures or triggers. If the OFFSETS option is ON and there are no errors, SET PARSEONLY returns the offset.

Permission: Requires membership in the public role. Public roles are required to have membership.

15. SET SHOWPLAN_ALL {ON | OFF}

Causes Microsoft SQL ServerSQL Server not to execute Transact-SQLTransact-SQL statements. Causes Microsoft SQL ServerSQL Server to not execute the Transact-SQL statement. SQL Server returns details about the execution of the statement and estimates the resource requirements of the statement.

Note:

The settings for SET SHOWPLAN_ALL are set at execution or run time, not at parse time.

If SET SHOWPLAN_ALL is ON, SQL Server returns execution information for each statement but does not execute the statement. The Transact-SQL statement is not executed. When this option is set to ON, information about all subsequent Transact-SQL statements is always returned until the option is set to OFF. For example, if the CREATE TABLE statement is executed when SET SHOWPLAN_ALL is ON, SQL Server returns an error message from subsequent SELECT statements involving the same table, informing the user that the specified table does not exist. Therefore, subsequent references to this table will fail. If SET SHOWPLAN_ALL is OFF, SQL Server executes the statement but does not generate a report.

SET SHOWPLAN_ALL is used by applications written to process its output. Use SET SHOWPLAN_TEXT to return readable output for the application at the Microsoft Win32 command prompt, such as the osql utility.

SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL cannot be specified within a stored procedure, they must be the only statements in the batch.

16. SET SHOWPLAN_TEXT {ON | OFF}

Causes Microsoft SQL ServerSQL Server not to execute Transact-SQLTransact-SQL statements. Causes Microsoft SQL Server to not execute the Transact-SQL statement. Instead, SQL Server returns details about how to execute the statement.

Note:

The settings for SET SHOWPLAN_TEXT are set at execution or run time, not at parse time.

When SET SHOWPLAN_TEXT is ON, SQL ServerSQL Server returns execution information for each Transact-SQLTransact-SQL statement without executing it. When SET SHOWPLAN_TEXT is ON, SQL ServerSQL Server returns execution information for each Transact-SQL statement, but does not execute the statement. When this option is set to ON, execution plan information about all subsequent SQL Server statements is returned until the option is set to OFF. For example, if you execute a CREATE TABLE statement when SET SHOWPLAN_TEXT is ON, SQL Server returns an error message from subsequent SELECT statements involving the same table to inform the user that the specified table does not exist. Therefore, subsequent references to this table will fail. If SET SHOWPLAN_TEXT is OFF, SQL Server executes the statement, but does not generate a report that contains execution plan information.

Setting set SHOWPLAN_TEXT is designed to return readable output from the application at the Microsoft Win32 command prompt, such as the osql utility. SET SHOWPLAN_ALL returns more detailed output to be processed by programs that specialize in processing its output.

17. SET STATISTICS IO {ON | OFF}

Note:

If STATISTICS IO is ON, statistics are displayed. If OFF, no statistics are displayed.

After this option is set ON, all subsequent Transact-SQLTransact-SQL statements return the statistical information until the option is set to OFF. If this option is set to ON, all subsequent Transact-SQL statements will return statistics until the option is set to OFF.

18. SET STATISTICS TIME {ON | OFF}

Displays the number of milliseconds required to parse, compile, and execute each statement.

Note:

When SET STATISTICS TIME is ON, the time statistics of the statement are displayed. When OFF, time statistics are not displayed.

The settings for SET STATISTICS TIME are set at execution or run time, not at parse time.

Microsoft SQL Server cannot provide accurate statistics in fiber mode when you enable the lightweight pool configuration option.

The table is updated only when the column sysprocesses query execution in Cpu uses SET STATISTICS TIME ON. When OFF,SET STATISTICS TIME, 0 returns.

The ON and OFF settings also affect the CPU column in the currently active process Information View within SQL Server Management Studio.

19. SET XACT_ABORT {ON | OFF}

Note:

When SET XACT_ABORT is ON, if the execution of the Transact-SQL statement produces a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, sometimes only the wrong Transact-SQL statement is rolled back, and the transaction continues processing. If the error is serious, the entire transaction may be rolled back even if SET XACT_ABORT is OFF. OFF is the default setting.

Compilation errors, such as syntax errors, are not affected by SET XACT_ABORT.

For most OLE DB providers, including SQL Server, XACT_ABORT in data modification statements in implicit or display transactions must be set to ON. The only situation where this option is not required is when the provider supports nested transactions.

When ANSI_WARNINGS=OFF, the violation of permissions causes the transaction to abort.

The settings for SET XACT_ABORT are set at execution or run time, not at parse time.

To view the current settings for this setting, run the following query.

DECLARE @ XACT_ABORT VARCHAR (3) = 'OFF'; IF ((16384 & @ @ OPTIONS) = 16384) SET @ XACT_ABORT =' ON'; SELECT @ XACT_ABORT AS XACT_ABORT

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.