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

image

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

image

Please note that this feature is available in both – SQL 2008 and SQL 2005.

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

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