Click on the banner to learn about and purchase my database training on Azure

SQL Server SET Commands

Views: 3.708
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.
- : 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.

SET LOCK_TIMEOUT Specifies the number of milliseconds until the bank returns a lock error, where:
- -1 (default): indicates no timeout (that is, wait indefinitely).
- 0: means not to wait and return a message as soon as a lock is found
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 the string itself (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 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.

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 the comparison operators Equal to (=) and Different from (<>) 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 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

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 will be 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 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.

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!