SQL Server SET Commands

Views: 3.182
Reading Time: 6 minutes

Readers,
Good afternoon!

In this post I will talk more about the SQL Server SET statement, which besides being used for setting variables, can also be used to change current session settings. Are they:

Category Instruction Description
Date and time SET DATEFIRST Defines the first day of the week as a number from 1 to 7, where:
1 = Monday
2 = Tuesday
3 = Wednesday
4 = Thursday
5 = Friday
6 = Saturday
7 = Sunday (Default)
SET DATEFORMAT Sets the default date format (mdy, dmy, ymd, etc.)
LOCKS SET DEADLOCK_PRIORITY Specifies the importance of the current session if there is a deadlock.
If two sessions go into Deadlock and have the same priority, both are eligible to be deleted.
If they have different priorities, the lower priority session is deleted.

Parameters:
- LOW: Priority below NORMAL, HIGH, or an integer value greater than -5.
- NORMAL: Priority below HIGH or for an integer value greater than 0. It is the default priority.
- HIGH: Priority below a session with an integer value greater than 5.
- <numeric-priority>: Is an integer value range (-10 to 10) to provide 21 deadlock priority levels instead of just 3 if you use the defaults.

SET LOCK_TIMEOUT Specifies the number of milliseconds until the bank returns a lock error, where:
- -1 (default): Indicates no timeout (ie wait indefinitely).
- 0: means no waiting and returning a message as soon as a lock is encountered.
Various SET CONCAT_NULL_YIELDS_NULL When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string generates a NULL result.

For example, SELECT 'abc' + NULL generates NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string generates its own string (the null value is treated as an empty string). For example, SELECT 'abc' + NULL generates abc

SET CURSOR_CLOSE_ON_COMMIT When SET CURSOR_CLOSE_ON_COMMIT is ON, this setting closes any open cursor on commit or rollback in accordance with ISO.

When SET CURSOR_CLOSE_ON_COMMIT is OFF, the cursor will not be closed when a transaction is committed.

SET FIPS_FLAGGER Specifies the FIPS 127-2 compliance check. It is based on the ISO standard.

Parameters:
- ENTRY: Verification of standards for ISO input level compliance.
- FULL: Verification of standards for full compliance with ISO.
- INTERMEDIATE: Verification of standards for ISO intermediate level compliance.
- OFF: No defaults are checked.

SET IDENTITY_INSERT Allows you to enter explicit values ​​in an IDENTITY column (Only one table in a session can have the IDENTITY_INSERT property set to ON)

Example (Parameters = ON or OFF):
SET IDENTITY_INSERT dbo.Client ON;

SET LANGUAGE Specifies the language environment for the session. The session language determines the datetime formats and system messages.

Example: SET LANGUAGE 'Brazilian'
Full list: SELECT * FROM syslanguages

SET OFFSETS Returns the offset (position relative to the beginning of an instruction) of the specified keywords (Do not use as it will be removed in future versions)
SET QUOTED_IDENTIFIER When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks and strings must be delimited by single quotation marks.
When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and strings can be delimited by single or double quotation marks.
Query runs SET ARITHABORT Defines if overflow or zero division error occurs the query will be closed (ON)
SET ARITHIGNORE Controls whether overflow or zero division error messages are returned (OFF)
SET FMTONLY Causes, if enabled (ON), the statement to return only column information (Metadata). No data row returned (Do not use, will be discontinued)
SET NOCOUNT Controls whether to display the number of records affected by the query (OFF) or whether to hide (ON).

Note: The @@ ROWCOUNT function is updated even when SET NOCOUNT is ON.

SET NOEXEC When SET NOEXEC is ON, the database compiles the TSQL statements, validates (PARSE), but does not execute them.
SET NUMERIC_ROUNDABORT When SET NUMERIC_ROUNDABORT is ON, an error is generated after a loss of precision in an expression occurs. When it is OFF, precision losses do not generate error messages and the result is rounded to the precision of the column or variable that stores the result.

Precision loss occurs when an attempt is made to store a value with a fixed precision in a column or variable with less precision.

SET PARSEONLY When SET PARSEONLY is ON, examines the syntax of each statement and returns any error messages without compiling or executing the statement.
SET QUERY_GOVERNOR_COST_LIMIT Defines an integer value that specifies the highest cost a query in session can achieve
This option overrides the Query governor cost limit setting.
SET ROWCOUNT Causes SQL Server to stop query processing after the specified number of rows is returned.

Example for returning only 10 rows (Prefer to use TOP)
SET ROWCOUNT 10;
GO

SELECT * FROM dbo.Clients

SET TEXTSIZE Specifies the varchar (max), nvarchar (max), varbinary (max), text, ntexte image data size returned by a SELECT statement.
ISO settings SET ANSI_DEFAULTS Controls a group of SQL Server configurations. When ON, this option enables the following ISO settings:
- SET ANSI_NULLS
- SET CURSOR_CLOSE_ON_COMMIT
- SET ANSI_NULL_DFLT_ON
- SET IMPLICIT_TRANSACTIONS
- SET ANSI_PADDING
- SET QUOTED_IDENTIFIER
- SET ANSI_WARNINGS
SET ANSI_NULL_DFLT_OFF When SET ANSI_NULL_DFLT_OFF is ON, new columns created using the ALTER TABLE and CREATE TABLE statements will be NOT NULL if not explicitly specified.
SET ANSI_NULL_DFLT_ON When SET ANSI_NULL_DFLT_OFF is ON, new columns created using the ALTER TABLE and CREATE TABLE statements will accept NULL values ​​if not explicitly specified.
SET ANSI_NULLS Specifies the ISO-compliant behavior of Equal to (=) and Non-(<>) when used with null values.

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even though there are null values ​​in column_name.
When SET ANSI_NULLS is OFF, the comparison operators Equal to (=) and Not equal to (<>) do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns rows that have null values ​​in column_name

SET ANSI_PADDING When SET ANSI_PADDING = ON, trailing spaces will be cut into VARCHAR and VARBINARY columns.
SET ANSI_WARNINGS Specifies the ISO default behavior for various error conditions:
- When set to ON, if null values ​​are displayed in aggregate functions such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT, a warning message is generated. When set to OFF, no warning is issued.
- When set to ON, arithmetic overflow and division by zero errors cause the instruction to be returned and an error message is generated.
Estatísticas SET FORCEPLAN When FORCEPLAN is set to ON, SQL Server query optimizer processes JOINS in the same order as tables are displayed in the FROM clause of a query.
SET SHOWPLAN_ALL When SET SHOWPLAN_ALL is ON, it causes the bank not to execute queries, just returns detailed information and cost estimates on how the instructions are executed.
SET SHOWPLAN_TEXT When SET SHOWPLAN_TEXT is ON, it causes the bank not to execute queries, it only returns in TEXT format detailed information and cost estimates on how the instructions are executed.
SET SHOWPLAN_XML When SET SHOWPLAN_XML is ON, it causes the bank not to execute queries, it only returns in XML format detailed information and cost estimates on how the instructions are executed.
SET STATISTICS IO When SET STATISTICS IO is ON, causes SQL Server to display information regarding the amount of disk activity generated by statements.
SET STATISTICS XML When ON, causes SQL Server to execute statements and generate detailed information about how statements were executed in the form of an XML document.
SET STATISTICS PROFILE When STATISTICS PROFILE is ON, each query executed returns the number of rows generated by each operator and number of times the operator was executed.
SET STATISTICS TIME When set to ON, displays the number of milliseconds required to parse, compile, and execute each statement.
Transactions SET IMPLICIT_TRANSACTIONS When ON, SET IMPLICIT_TRANSACTIONS sets the connection in implicit transaction mode. In this mode, an implicit BEGIN TRANSACTION statement will initiate two nested transactions and executing any of the following statements initiates a transaction:
- ALTER TABLE
- FETCH
- REVOKE
- BEGIN TRANSACTION
- GRANT
- SELECT
- CREATE
- INSERT
- TRUNCATE TABLE
- DELETE
- OPEN
- UPDATE
- DROP
SET REMOTE_PROC_TRANSACTIONS When ON, Specifies that when a local transaction is active, executing a StoredProcedure remotely starts a distributed transaction.
SET TRANSACTION ISOLATION LEVEL Controls blocking and line versioning behavior:

- READ UNCOMMITTED: Specifies that statements can read rows that have been modified by other transactions but not yet committed (NOLOCK).

- READ COMMITTED (Default): Specifies that statements cannot read data that has been modified but not yet committed by other transactions. This prevents dirty readings.

- REPEATABLE READ: Specifies that statements cannot read data that has been modified but not yet committed by other transactions, and that no other transactions can modify data that was read by the current transaction until the current transaction completes.

- SNAPSHOT: Specifies that the data read by any statement in a transaction will be the transactionally consistent version that existed at the beginning of the transaction. The transaction can only recognize data changes that were committed before the transaction started. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect will be as if the statements in a transaction take a snapshot of the committed data as it existed at the beginning of the transaction.

- SERIALIZABLE: Statements cannot read data that has been modified and not yet confirmed by other transactions. No other transaction can modify data read by the current transaction until the current transaction is completed. Other transactions cannot insert new rows with key values ​​that are in the key range read by any statement in the current transaction until it completes.

SET XACT_ABORT When ON, specifies that SQL Server should automatically roll back (ROLLBACK) the current transaction when a Transact-SQL statement generates a runtime error.

And that's it folks! For more information, see the Microsoft official page about the SET command

To the next!