Readers,
Good afternoon!

In this post I will talk more about the SQL Server SET statement, which in addition to being used to define variables, can also be used to change settings for the current session. They are:

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

Parameters:
– LOW: Priority below NORMAL, HIGH or an integer value greater than -5.
– NORMAL: Priority below HIGH or to an integer value greater than 0. It is the default priority.
– HIGH: Priority below a session with an integer value greater than 5.
– : It is an integer value range (-10 to 10) to provide 21 deadlock priority levels instead of just 3 if using the predefined ones.

SET LOCK_TIMEOUT Specifies the number of milliseconds until the database returns a blocking error, where:
– -1 (default): indicates no timeout (i.e., wait indefinitely).
– 0: means do not wait and return a message as soon as a block is found
Miscellaneous SET CONCAT_NULL_YIELDS_NULL When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string produces 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 the string itself (the null value is treated as an empty string). For example, SELECT ‘abc’ + NULL yields abc
SET CURSOR_CLOSE_ON_COMMIT When SET CURSOR_CLOSE_ON_COMMIT is ON, this setting closes any open cursor on ISO-compliant commit or rollback.

When SET CURSOR_CLOSE_ON_COMMIT is OFF, the cursor will not be closed when a transaction is committed
SET FIPS_FLAGGER Specifies checking for compliance with the FIPS 127-2 standard. It is based on the ISO standard.

Parameters:
– ENTRY: Verification of standards against ISO entry level compliance.
– FULL: Verification of standards in relation to full compliance with ISO.
– INTERMEDIATE: Verification of standards in relation to ISO intermediate level compliance.
– OFF: No patterns are checked.

SET IDENTITY_INSERT Allows you to insert explicit values ​​into 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.Cliente 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 enclosed in double quotes and strings must be enclosed in single quotes.
When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be enclosed in quotation marks and strings can be enclosed in single or double quotation marks.
Query Executions SET ARITHABORT Defines whether, when overflow or division by zero error occurs, the query will be closed (ON)
SET ARITHIGNORE Controls whether overflow or division by zero (OFF) error messages are returned
SET FMTONLY If enabled (ON), the instruction returns only column information (Metadata). No rows of data are returned (Do not use, will be deprecated)
SET NOCOUNT Controls whether the number of records affected by the query will be displayed (OFF) or whether it will be hidden (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 instructions, validates them (PARSE), but does not execute them
SET NUMERIC_ROUNDABORT When SET NUMERIC_ROUNDABORT is ON, an error is generated after a loss of precision occurs in an expression. When it is OFF, losses in precision 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 that a query in the 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 are returned

Example to return only 10 lines (Prefer to use TOP)
SET ROWCOUNT 10;
GO

SELECT * FROM dbo.Customers

SET TEXTSIZE Specifies the size of varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data returned by a SELECT statement
ISO Settings SET ANSI_DEFAULTS Controls a group of SQL Server settings. When activated (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 the Equals (=) and Not Equal to (<>) comparison operators when used with null values.

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

SET ANSI_PADDING When SET ANSI_PADDING = ON, trailing spaces will be trimmed in VARCHAR and VARBINARY columns
SET ANSI_WARNINGS Specifies ISO standard 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 will be generated. When set to OFF, no warning is issued.
– When set to ON, arithmetic overflow and divide by zero errors cause the instruction to return and an error message is generated
Statistics SET FORCEPLAN When FORCEPLAN is set to ON, the SQL Server query optimizer processes JOINS in the same order as the tables are displayed in the FROM clause of a query
SET SHOWPLAN_ALL When SET SHOWPLAN_ALL is ON, the bank does not execute the queries, but only returns detailed information and cost estimates on how the instructions are executed.
SET SHOWPLAN_TEXT When SET SHOWPLAN_TEXT is ON, the database does not execute the queries, but only returns detailed information and cost estimates on how the instructions are executed in TEXT format.
SET SHOWPLAN_XML When SET SHOWPLAN_XML is ON, the database does not execute the queries, but only returns detailed information and cost estimates on how the instructions are executed in XML format.
SET STATISTICS IO When SET STATISTICS IO is ON, it causes SQL Server to display information regarding the amount of disk activity generated by the statements
SET STATISTICS XML When ON, Causes SQL Server to execute statements and generate detailed information about how the statements were executed in the form of an XML document
SET STATISTICS PROFILE When STATISTICS PROFILE is ON, each executed query returns the number of rows generated by each operator and the number of times the operator was executed
SET STATISTICS TIME When ON, displays the number of milliseconds required to parse, compile, and execute each instruction
Transactions SET IMPLICIT_TRANSACTIONS When ON, SET IMPLICIT_TRANSACTIONS sets the connection to implicit transaction mode. In this mode, an implicit BEGIN TRANSACTION statement will start two nested transactions, and executing any of the following statements starts 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 (Do not use, will be deprecated)
SET TRANSACTION ISOLATION LEVEL Controls row locking and versioning behavior:

– READ UNCOMMITTED: Specifies that instructions can read lines that have been modified by other transactions, but have not yet been committed (NOLOCK).

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

– REPEATABLE READ: Specifies that instructions cannot read data that has been modified but not yet committed by other transactions and that no other transaction can modify data that has been 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 modifications that were committed before the transaction started. Data modifications made by other transactions after the start of the current transaction are not visible to instructions executing in the current transaction. The effect will be as if the instructions in a transaction take a snapshot of the committed data as it existed at the beginning of the transaction.

– SERIALIZABLE: Instructions cannot read data that has been modified, and that has not yet been committed by other transactions. No other transactions can modify data read by the current transaction until the current transaction completes. Other transactions cannot insert new rows with key values ​​that are in the key range read by any statement in the current transaction until the current transaction 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 run-time error.

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

Until next time!