SET Statements are used to control the session of the SQL Server. There are some settings that are set at instance level or database level. So, whenever we initiate a connection to the SQL instance, it applies all the setting to the new connection and each user will get separate session with all settings applied by default. We can also change the server level setting and it is done by administrator only and the changes will affect to all the connections made to that instance. There are some times that we need to apply certain settings to the current connection or session and it should not affect other connections at the same time. In this scenario, we use explicit set statements. There are various categories of set statement available in SQL Server those are given in the below image.
These set statements will change or override the existing server level or database level settings when it is used in the SQL connection. These set statements are applied in two zones, they are Execution or Run time or Parse time.
Parse time set statements
The following statements are used in the parse time of the SQL statements.
SET PARSEONLY
SET FIPS_FLAGGER
SET OFFSETS
SET QUOTED_IDENTIFIER
Execution or Run time set statements
These statements are taking effect at execution time only
SET DATEFIRST
SET DATEFORMAT
SET DEADLOCK_PRIORITY
SET LOCK_TIMEOUT
SET CONCAT_NULL_YIELDS_NULL
SET CURSOR_CLOSE_ON_COMMIT
SET IDENTITY_INSERT
SET LANGUAGE
SET ARITHABORT
SET ARITHIGNORE
SET NOCOUNT
SET NOEXEC
SET NUMERIC_ROUNDABORT
SET QUERY_GOVERNOR_COST_LIMIT
SET ROWCOUNT
SET TEXTSIZE
SET ANSI_DEFAULTS
SET ANSI_NULL_DFLT_OFF
SET ANSI_NULL_DFLT_ON
SET ANSI_NULLS
SET ANSI_PADDING
SET ANSI_WARNINGS
SET FORCEPLAN
SET SHOWPLAN_ALL
SET SHOWPLAN_TEXT
SET SHOWPLAN_XML
SET STATISTICS IO
SET STATISTICS XML
SET STATISTICS PROFILE
SET STATISTICS TIME
SET IMPLICIT_TRANSACTIONS
SET REMOTE_PROC_TRANSACTIONS
SET TRANSACTION ISOLATION LEVEL
SET XACT_ABORT
These statements are very helpful while executing a batch statement or creating stored procedure. These set statement are used in different ways some with just ON or OFF switch and some of them need integer or time value. Learn about all set statements in this website. I have written about each set statement with detailed example and some of them are available in videos in YouTube channel. So, feel free to browse all the content and share your feedback.
SQL Server Rider 