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:
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
Please note that this feature is available in both – SQL 2008 and SQL 2005.
Until we meet next time,
Be courteous. Drive responsibly.