Knowing The Options

Have you ever executed a query in SQL Server Management Studio, looked at the execution plan, and noticed that it was a different plan than what was generated on the server?

A potential reason for this could be a different option setting. The options represent the SET values of the current session. SET options can affect how the query is executed thus having a different execution plan. You can find these options in two places within SSMS under -

Tools -> Options -> Query Execution -> SQL Server -> Advanced 
 
as well as,
 
Tools -> Options -> Query Execution -> SQL Server -> ANSI

@@Options

Using the interface to check what is set can get tiresome. Instead, you can use the system function @@OPTIONS. Each option shown above has a BIT value for all 15 options indicating whether or not it is enabled.

It would look like this: 001010101111000. Each bit corresponds to an option in order from the table below.

Options

Value Configuration
1 DISABLE_DEF_CNST_CHK
2 IMPLICIT_TRANSACTIONS
4 CURSOR_CLOSE_ON_COMMIT
8 ANSI_WARNINGS
16 ANSI_PADDING
32 ANSI_NULLS
64 ARITHABORT
128 ARITHIGNORE
256 QUOTED_IDENTIFIER
512 NOCOUNT
1024 ANSI_NULL_DFLT_ON
2048 ANSI_NULL_DFLT_OFF
4096 CONCAT_NULL_YIELDS_NULL
8192 NUMERIC_ROUNDABORT
16384 XACT_ABORT

For example, the rightmost three bits are 0. These correspond to,

  1. DISABLE_DEF_CNST_CHK  
  2. IMPLICIT_TRANSACTIONS  
  3. CURSOR_CLOSE_ON_COMMIT  

So what does @@options really return?

Running @@OPTIONS on my machine, it returns a value of 5496. Useful, right? OK, not really! It would be nice to know exactly which options are set and which aren’t without having to go through the UI.

@@OPTIONS takes the binary representation and does a BITWISE operation on it to produce an integer value based on the sum of which BITS are enabled.

Let’s assume for a moment that the only two options that are enabled on my machine are ANSI_PADDING and ANSI_WARNINGS. The values for these two options are 8 and 16, respectively speaking. The sum of the two is 24.

You have options, and thankfully, there is an easier way. The script below will do the work for you and display what options are enabled.

  1. /***************************************************************  
  2. Author: John Morehouse  
  3. Summary: This script display what SET options are enabled for the current session.  
  4. You may alter this code for your own purposes. You may republish altered code as long as you give due credit.  
  5. THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.  
  6. ***************************************************************/  
  7. SELECT 'Disable_Def_Cnst_Chk' AS 'Option'CASE @@options & 1 WHEN 0 THEN 0 ELSE 1 END AS'Enabled/Disabled' UNION  
  8. SELECT 'IMPLICIT_TRANSACTIONS' AS 'Option'CASE @@options & 2 WHEN 0 THEN 0 ELSE 1 END AS'Enabled/Disabled' UNION  
  9. SELECT 'CURSOR_CLOSE_ON_COMMIT' AS 'Option'CASE @@options & 4 WHEN 0 THEN 0 ELSE 1 END AS'Enabled/Disabled' UNION  
  10. SELECT 'ANSI_WARNINGS' AS 'Option'CASE @@options & 8 WHEN 0 THEN 0 ELSE 1 END AS'Enabled/Disabled' UNION  
  11. SELECT 'ANSI_PADDING' AS 'Option'CASE @@options & 16 WHEN 0 THEN 0 ELSE 1 END AS'Enabled/Disabled' UNION  
  12. SELECT 'ANSI_NULLS' AS 'Option'CASE @@options & 32 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled'UNION  
  13. SELECT 'ARITHABORT' AS 'Option'CASE @@options & 64 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled'UNION  
  14. SELECT 'ARITHIGNORE' AS 'Option'CASE @@options &128 WHEN 0 THEN 0 ELSE 1 END AS'Enabled/Disabled' UNION  
  15. SELECT 'QUOTED_IDENTIFIER' AS 'Option'CASE @@options & 256 WHEN 0 THEN 0 ELSE 1 END AS'Enabled/Disabled' UNION  
  16. SELECT 'NOCOUNT' AS 'Option'CASE @@options & 512 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled'UNION  
  17. SELECT 'ANSI_NULL_DFLT_ON' AS 'Option'CASE @@options & 1024 WHEN 0 THEN 0 ELSE 1 END AS'Enabled/Disabled' UNION  
  18. SELECT 'ANSI_NULL_DFLT_OFF' AS 'Option'CASE @@options & 2048 WHEN 0 THEN 0 ELSE 1 END AS'Enabled/Disabled' UNION  
  19. SELECT 'CONCAT_NULL_YIELDS_NULL' AS 'Option'CASE @@options & 4096 WHEN 0 THEN 0 ELSE 1 END AS'Enabled/Disabled' UNION  
  20. SELECT 'NUMERIC_ROUNDABORT' AS 'Option'CASE @@options & 8192 WHEN 0 THEN 0 ELSE 1 END AS'Enabled/Disabled' UNION  
  21. SELECT 'XACT_ABORT' AS 'Option'CASE @@options & 16384 WHEN 0 THEN 0 ELSE 1 END AS'Enabled/Disabled'  

The output of the script will give you this.

Knowing The Options

Next time you are investigating an execution plan, remember to check to see what your options are.

Enjoy!


Similar Articles
Denny Cherry & Associates Consulting
Expert Consultants From HA to DR to up-time to SQL virtualization to scalability.