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:
|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.
|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.
Specifies the FIPS 127-2 compliance check. It is based on the ISO standard.
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):
Specifies the language environment for the session. The session language determines the datetime formats and system messages.
Example: SET LANGUAGE 'Brazilian'
|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
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)
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.|
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.
|SET ANSI_PADDING||When SET ANSI_PADDING = ON, trailing spaces will be cut into VARCHAR and VARBINARY columns.|
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
|STATISTICS||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.|
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
- BEGIN TRANSACTION
- TRUNCATE TABLE
|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!
[…] Before executing the query, execute the command “SET STATISTICS IO ON”. When we do this, SQL Server will display information regarding the number of page readings that the query did. I advise you to read this post on Dirceu's blog. https://www.dirceuresende.com/blog/os-comandos-set-do-sql-server. [...]