SQL Server SET Commands

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.
- : It is an integer value range (-10 to 10) to provide 21 deadlock priority levels instead of just 3 if you use the predefined ones.

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 generates abc

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

Parameters:
- ENTRY: Verification of standards with respect to ISO entry level compliance.
- FULL: Verification of standards with respect to full ISO compliance.
- INTERMEDIATE: Verification of standards with respect to the compliance of the intermediate ISO level.
- OFF: No pattern is checked.

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

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

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

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

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

SELECT * FROM dbo.Clients

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 Different from (<>) do not follow the ISO standard. A SELECT statement using WHERE column_name = NULL returns rows that have null values ​​in column_name

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

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

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

- SNAPSHOT: Specifies that the data read by any instruction 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 confirmed before the transaction started. Data modifications made by other transactions after the start of the current transaction are not visible to the instructions running in the current transaction. The effect will be as if the instructions in a transaction take a snapshot of the confirmed 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 confirmed by other transactions. No other transactions can modify data read by the current transaction until the current transaction is completed. Other transactions cannot insert new lines with key values ​​that are in the range of keys read by any statement in the current transaction until it is completed.