0
SET Statements
Good morning everyone! Apologies for not positing on here for a while, I am quite busy at the moment so regretfully I won't be posting on here so much.
Today, I will be discussing SET statements and how they can be categorised and used within SQL Server. I have used several in the past but I haven't yet gone into detail about what each one specifically does, which is something I am going to rectify in this blog post. Before I begin, massive thanks to Microsoft and their technet website for providing me with information on the SET statements that I was unsure about:
http://technet.microsoft.com/en-us/library/ms190356.aspx
SET DATEFIRST
Uses an integer value to show the first day of the week. (Valid parameters: 1-7, Monday = 1, Tuesday - 2, Wednesday = 3, Thursday = 4, Friday = 5, Saturday = 6 and Sunday = 7.)
SET DATEFORMAT
Allows the user to determine the order of date parts. (Valid parameters: mdy, dmy, ymd, ydm, myd and dym.)
A collective group which, when switched on, will allow the user to have access to SET ANSI_DFLT_OFF, SET ANSI_DFLT_ON, SET ANSI_NULLS, SET ANSI_PADDING and SET ANSI_WARNINGS.
SET ANSI_DFLT_OFF
Overrides the default so when you try to add new columns after this Boolean has been set, new columns will not be nullable if nothing is declared.
SET ANSI_DFLT_ON
Allows you to restore the default setting whereby when new columns are created in the database, if not specified they will always be nullable.
SET ANSI_NULLS
Specifies the behaviour of = and <> operators when used with NULL values. Note that in future versions of SQL Server, this option will always be set to ON and any application that attempt to turn it OFF will produce an error message.
SET ANSI_PADDING
Controls the way shorter than defined column values are dealt with. Note that in future versions of SQL Server, this option will always be set to ON and any application that attempt to turn it OFF will produce an error message.
SET ANSI_WARNINGS
Specifies behaviour for certain error conditions using a Boolean variable.
Allows the user to specify a numeric value to give priority to processes during a deadlock situation. This influences which processes will continue.
SET LOCK_TIMEOUT
Allows the user to specify how long a statement should wait for a lock to be released, in milliseconds.
Determines whether concatenation results are treated as either NULL or empty string values.
SET CURSOR_CLOSE_ON_COMMIT
Determines whether to automatically close cursors once they have been declared. This is set to OFF by default, which means you will have to manually close the cursor by typing:
SET FIPS_FLAGGER
Determines whether to check for compliance with the FIPS 127-2 standard. For more information on this standard, please click on the following link:
http://www.itl.nist.gov/fipspubs/fip127-2.htm
SET IDENTITY_INSERT
When switched ON, this allows you to add values to an identity column in a database table.
SET LANGUAGE
Specifies the language for this session, which is reflected in the datetime formatting and the language of error messages relayed to the user.
SET OFFSETS
Only used in DB-Library applications, SET OFFSETS returns the relative position of keywords in T-SQL statements.
SET QUOTED_IDENTIFIER
Determines whether SQL Server should follow ISO rules for using double quotation marks or single quotation marks. With QUOTE_IDENTIFIER set to ON, identifiers must be quoted with double quotation marks and literals must be quoted with single quotation marks. When QUOTED_IDENTIFIER is set to OFF, literals can have either single or double quotation marks however identifiers can't be quoted (and must follow T-SQL rules).
Stops a query when either an overflow or a divide-by-zero error (when SQL Server tries to divide a number by 0) occurs whilst the query is executing. By default, this setting is set to ON and should really remain on as turning it off can have negative impact on performance, whereby the query runs fast in SQL Server but much more slowly in the application itself. Plus, it can make it harder to debug the problem as you can receive different query plans.
SET ARITHIGNORE
Determines whether an error message is displayed on screen to the user when either an overflow or divide-by-zero query occurs. If this setting is set to OFF, SQL Server will still return a NULL value as the result of the query.
SET NOCOUNT
Usually when you run a query in SQL Server, it will tell you the number of rows that have been affected by the query that has just run in the messages window. When you set NOCOUNT to ON, it prevents SQL Server from putting this count at the end of the result set.
SET NOEXEC
When SET NOEXEC is set to ON, it means that the query will compile but it will not be executed.
SET NUMERIC_ROUNDABORT
Sets the error level generated when rounding an expression may cause unwanted loss of accuracy.
SET PARSEONLY
If SET PARSEONLY is set to ON, it means that the query won't be compiled or executed, however if any errors occurs due to the syntax used, these errors will be raised on screen to the user.
SET QUERY_GOVERNOR_COST_LIMIT
Determines what the maximum query cost is of a query to run on the database. If the query cost in the estimated execution plan is more than the limit set, then the execution of that query statement will be prohibited. This SET statement overrides the current limit for the current active connection.
SET ROWCOUNT
Tells SQL Server when to stop processing the query. If more rows are attempted to be returned, they will simply be left out of the returning result set and a message stating that will be displayed on screen to the user.
SET TEXTSIZE
Determines the size of textual database fields returned by a SELECT statement. The fields covered by TEXTSIZE are:
Processes the joins in the same order that tables appear in the FROM cause of a SELECT statement.
SET SHOWPLAN_ALL
Shows an estimated execution plan of the query, along with information on how the statements in the query are going to be executed.
SET SHOWPLAN_TEXT
Shows an estimated execution plan in a textual format.
SET SHOWPLAN_XML
Shows an estimated execution plan in a well formatted XML document.
SET STATISTICS IO
Shows information on disk activity whilst a query is being executed.
SET STATISTICS XML
Shows the actual execution plan of the running query in a well formatted XML document.
SET STATISTICS PROFILE
Shows the profile information of a query.
SET STATISTICS TIME
Shows how long it takes to execute each query from start to finish, in milliseconds.
Determines whether the connection is in implicit transaction mode. If it is, then executing any of the following will begin a transaction:
SET REMOTE_PROC_TRANSACTIONS
States that when a transaction is active, running a stored procedure causes a T-SQL distributed Transaction to execute (which is then managed by Microsoft Distributed Transaction Coordinator).
SET TRANSACTION ISOLATION LEVEL
Controls the locking behaviour of your queries as they are run.
SET XACT_ABORT
Determines whether SQL Server automatically rolls back any transactions that cause a run-time error. Can be used as an alternative to a TRY... CATCH block.
Today, I will be discussing SET statements and how they can be categorised and used within SQL Server. I have used several in the past but I haven't yet gone into detail about what each one specifically does, which is something I am going to rectify in this blog post. Before I begin, massive thanks to Microsoft and their technet website for providing me with information on the SET statements that I was unsure about:
http://technet.microsoft.com/en-us/library/ms190356.aspx
What are SET Statements?
SET statements change the way that the current SQL Server session handles information. These are settings that are predefined in Note that these are different to using the SET keyword to give a default value to a local variable.
Date and Time Statements.
SET DATEFIRSTUses an integer value to show the first day of the week. (Valid parameters: 1-7, Monday = 1, Tuesday - 2, Wednesday = 3, Thursday = 4, Friday = 5, Saturday = 6 and Sunday = 7.)
SET DATEFORMAT
Allows the user to determine the order of date parts. (Valid parameters: mdy, dmy, ymd, ydm, myd and dym.)
ISO Settings Statements.
SET ANSI_DEFAULTSA collective group which, when switched on, will allow the user to have access to SET ANSI_DFLT_OFF, SET ANSI_DFLT_ON, SET ANSI_NULLS, SET ANSI_PADDING and SET ANSI_WARNINGS.
SET ANSI_DFLT_OFF
Overrides the default so when you try to add new columns after this Boolean has been set, new columns will not be nullable if nothing is declared.
SET ANSI_DFLT_ON
Allows you to restore the default setting whereby when new columns are created in the database, if not specified they will always be nullable.
SET ANSI_NULLS
Specifies the behaviour of = and <> operators when used with NULL values. Note that in future versions of SQL Server, this option will always be set to ON and any application that attempt to turn it OFF will produce an error message.
SET ANSI_PADDING
Controls the way shorter than defined column values are dealt with. Note that in future versions of SQL Server, this option will always be set to ON and any application that attempt to turn it OFF will produce an error message.
SET ANSI_WARNINGS
Specifies behaviour for certain error conditions using a Boolean variable.
Locking Statements.
SET DEADLOCK_PRORITYAllows the user to specify a numeric value to give priority to processes during a deadlock situation. This influences which processes will continue.
SET LOCK_TIMEOUT
Allows the user to specify how long a statement should wait for a lock to be released, in milliseconds.
Miscellaneous Statements.
SET CONCAT_NULL_YEILDS_NULLDetermines whether concatenation results are treated as either NULL or empty string values.
SET CURSOR_CLOSE_ON_COMMIT
Determines whether to automatically close cursors once they have been declared. This is set to OFF by default, which means you will have to manually close the cursor by typing:
SET FIPS_FLAGGER
Determines whether to check for compliance with the FIPS 127-2 standard. For more information on this standard, please click on the following link:
http://www.itl.nist.gov/fipspubs/fip127-2.htm
SET IDENTITY_INSERT
When switched ON, this allows you to add values to an identity column in a database table.
SET LANGUAGE
Specifies the language for this session, which is reflected in the datetime formatting and the language of error messages relayed to the user.
SET OFFSETS
Only used in DB-Library applications, SET OFFSETS returns the relative position of keywords in T-SQL statements.
SET QUOTED_IDENTIFIER
Determines whether SQL Server should follow ISO rules for using double quotation marks or single quotation marks. With QUOTE_IDENTIFIER set to ON, identifiers must be quoted with double quotation marks and literals must be quoted with single quotation marks. When QUOTED_IDENTIFIER is set to OFF, literals can have either single or double quotation marks however identifiers can't be quoted (and must follow T-SQL rules).
Query Execution Statements.
SET ARITHABORTStops a query when either an overflow or a divide-by-zero error (when SQL Server tries to divide a number by 0) occurs whilst the query is executing. By default, this setting is set to ON and should really remain on as turning it off can have negative impact on performance, whereby the query runs fast in SQL Server but much more slowly in the application itself. Plus, it can make it harder to debug the problem as you can receive different query plans.
SET ARITHIGNORE
Determines whether an error message is displayed on screen to the user when either an overflow or divide-by-zero query occurs. If this setting is set to OFF, SQL Server will still return a NULL value as the result of the query.
SET NOCOUNT
Usually when you run a query in SQL Server, it will tell you the number of rows that have been affected by the query that has just run in the messages window. When you set NOCOUNT to ON, it prevents SQL Server from putting this count at the end of the result set.
SET NOEXEC
When SET NOEXEC is set to ON, it means that the query will compile but it will not be executed.
SET NUMERIC_ROUNDABORT
Sets the error level generated when rounding an expression may cause unwanted loss of accuracy.
SET PARSEONLY
If SET PARSEONLY is set to ON, it means that the query won't be compiled or executed, however if any errors occurs due to the syntax used, these errors will be raised on screen to the user.
SET QUERY_GOVERNOR_COST_LIMIT
Determines what the maximum query cost is of a query to run on the database. If the query cost in the estimated execution plan is more than the limit set, then the execution of that query statement will be prohibited. This SET statement overrides the current limit for the current active connection.
SET ROWCOUNT
Tells SQL Server when to stop processing the query. If more rows are attempted to be returned, they will simply be left out of the returning result set and a message stating that will be displayed on screen to the user.
SET TEXTSIZE
Determines the size of textual database fields returned by a SELECT statement. The fields covered by TEXTSIZE are:
- image
- ntext
- nvarchar(max)
- text
- varbinary(max)
- varchar(max)
Statistics Statements.
SET FORCEPLANProcesses the joins in the same order that tables appear in the FROM cause of a SELECT statement.
SET SHOWPLAN_ALL
Shows an estimated execution plan of the query, along with information on how the statements in the query are going to be executed.
SET SHOWPLAN_TEXT
Shows an estimated execution plan in a textual format.
SET SHOWPLAN_XML
Shows an estimated execution plan in a well formatted XML document.
SET STATISTICS IO
Shows information on disk activity whilst a query is being executed.
SET STATISTICS XML
Shows the actual execution plan of the running query in a well formatted XML document.
SET STATISTICS PROFILE
Shows the profile information of a query.
SET STATISTICS TIME
Shows how long it takes to execute each query from start to finish, in milliseconds.
Transactions Statements.
SET IMPLICIT_TRANSACTIONSDetermines whether the connection is in implicit transaction mode. If it is, then executing any of the following will begin a transaction:
- ALTER TABLE
- BEGIN TRANSACTION
- CREATE
- DELETE
- FETCH
- GRANT
- INSERT
- OPEN
- REVOKE
- SELECT
- TRUNCATE TABLE
- UPDATE
SET REMOTE_PROC_TRANSACTIONS
States that when a transaction is active, running a stored procedure causes a T-SQL distributed Transaction to execute (which is then managed by Microsoft Distributed Transaction Coordinator).
SET TRANSACTION ISOLATION LEVEL
Controls the locking behaviour of your queries as they are run.
SET XACT_ABORT
Determines whether SQL Server automatically rolls back any transactions that cause a run-time error. Can be used as an alternative to a TRY... CATCH block.
As A Final Word...
Thank you for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there.
Post a Comment
Please post any feedback or comments here...