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.
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.
Execution or Run time set statements
These statements are taking effect at execution time only
SET STATISTICS IO
SET STATISTICS XML
SET STATISTICS PROFILE
SET STATISTICS TIME
SET TRANSACTION ISOLATION LEVEL
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.