SET Statements (Part 1)

Have you ever used query options while executing query? Or have you ever tried to find out use of SET statements in query? If you are willing to understand this, then this blog is for you.

SET Statements change the current session handling of specific information.

Below table explains useful SET statements which often used in T-SQL.

Category	Statements	Descirption	Syntax	Ex	Permission	IMP To Remember
Date and time	SET DATEFIRST	Sets the first day of the week to a number from 1 through 7.	SET DATEFIRST { number | @number_var }	SET DATEFIRST 7 ;	Public
	SET DATEFORMAT	Sets the order of the month, day, and year date parts for interpreting date character strings. These strings are of type date, smalldatetime, datetime, datetime2, or datetimeoffset.	SET DATEFORMAT { format | @format_var }	SET DATEFORMAT dmy;	Public
Locking statements	SET DEADLOCK_PRIORITY	Specifies the relative importance that the current session continue processing if it is deadlocked with another session.	SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }	SET DEADLOCK_PRIORITY NORMAL;	Public
	SET LOCK_TIMEOUT	Specifies the number of milliseconds a statement waits for a lock to be released.	SET LOCK_TIMEOUT timeout_period (In Milliseconds)	SET LOCK_TIMEOUT 1800;	Public
Query Execution Statements	SET ARITHABORT	Ends a query when an overflow or divide-by-zero error occurs during query execution.	SET ARITHABORT { ON | OFF }	SET ARITHABORT ON	Public	Always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to OFF can negatively impact query optimization, leading to performance issues.
	SET ARITHIGNORE	Controls whether error messages are returned from overflow or divide-by-zero errors during a query.	SET ARITHIGNORE { ON | OFF }	SET ARITHIGNORE OFF;	Public
	SET NOCOUNT	Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.	SET NOCOUNT { ON | OFF }	SET NOCOUNT OFF;	Public	When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.
	SET NOEXEC	Compiles each query but does not execute it.	SET NOEXEC { ON | OFF }	SET NOEXEC ON;	Public	When SET NOEXEC is ON, SQL Server parses and compiles each batch of Transact-SQL statements but does not execute them. When SET NOEXEC is OFF, all batches are executed after compilation.
	SET ROWCOUNT	Causes SQL Server to stop processing the query after the specified number of rows are returned.	SET ROWCOUNT { number | @number_var }	SET ROWCOUNT 4;	Public	Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax.
	SET TEXTSIZE	Specifies the size, in bytes, of varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data returned to the client by a SELECT statement.	SET TEXTSIZE { number }		Public	ntext, text, and image data types will be removed in a future version of MicrosoftSQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

To Be Continued... 

Ref : SET Statements (Transact-SQL) - SQL Server | Microsoft Docs