SSMS – Properties window – Know your query options – ANSI_NULLS, QUOTED_IDENTIFIER, ARITHABORT and other SET options

SSMS continues to fascinate me with it’s immense wealth of features geared towards developer & administrator usability. Some time back, I wrote about how to use the Properties window during performance tuning and getting missing index information in SQL Server 2005.

Today, I will write about another small usability feature of the Properties window. Often, we multi-task and forget the exact set of SET options that a particular connection uses. The most frequently used SET options that one is worried about are:

ANSI_NULLS
QUOTED_IDENTIFIER
ARTIHABORT
NUMERIC_ROUNDABORT
ANSI_WARNINGS

The properties window provides us a way to check the various SET options used for a particular connection. Here’s a step-by-step guide:

Let’s run the following query in SQL Server Management Studio. Include the “Actual Execution Plan” by pressing Ctrl+M before running the query.

USE AdventureWorks2008R2
GO

SELECT e.LoginID, e.JobTitle, e.HireDate, e.CurrentFlag
FROM HumanResources.Employee e

Switch to the Actual Execution plan in the Results pane
Hit F4 to invoke the Properties Window (you may also use View->Properties Window menu option)
Select the top-most SELECT operator
Observe the Properties Window

As a cross-check, change the SET options on the query, or use the features of SSMS to change the SET options for just this connection.

USE AdventureWorks2008R2
GO

SET ANSI_NULLS OFF
GO

SELECT e.LoginID, e.JobTitle, e.HireDate, e.CurrentFlag
FROM HumanResources.Employee e

SET ANSI_NULLS ON
GO