Home > SQL Server > SET Statements And Its Active zone – Pharse Time and Run-time

SET Statements And Its Active zone – Pharse Time and Run-time


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.

pic1These 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.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: