As you know, we are exploring the world of graphical tools & utilities that are available to us out of the box in a Microsoft SQL Server installation. In the previous part of the tutorial, I provided an introduction to the SQL Server Management Studio (SSMS) and the Query Editor window. I hope that you have started exploring these most important tools.

In this part, I will help you explore the two most important windows after the Query Editor. These are the Object Explorer & the Object Explorer Details. Both are related, and therefore, easier to understand.

The base functionality offered by these windows was around at least since the days of SQL Server 7.0. We lost most of this functionality when SQL Server underwent massive re-engineering in SQL Server 2005, but they were reintroduced starting SQL Server 2008.

Object Explorer window

The Object Explorer window is perhaps the 2nd most used part of the SQL Server Management studio. The Object explorer has the ability to connect to following services of SQL Server:

1. Database Engine instances
2. Analysis Services
3. Integration Services
4. Reporting Services
5. SQL Server Compact editions

The primary functions of the Object Explorer are:

• Provides a view of the objects available in the SQL Server
• Provides a user interface, or invokes wizards to manage these objects

The Object Explorer UI

The object explorer is so powerful, that it comes with it’s own UI elements – a toolbar and a working area. Here’s the screenshot.

To connect to the object explorer, click on the “connect” button in the Object Explorer toolbar, which pulls up the “Connect to Server…” dialog box. Once logged in, the user immediately notices that all database objects are neatly arranged in the logical groups. When connected to a database engine, the groups are:

1. SQL Server instance – allows you to:
   1. Manage SQL Server configuration
   2. Manage part of the SQL Server surface configuration
2. Databases – allows you to:
   • View a list of accessible databases, their states
   • View information about database objects
   • Create & modify database objects
   • Manage databases
   • Manage database security
3. Security – allows you to:
   • Manage SQL Server instance security, including logins
4. Server Objects – allows you to:
   • Manage endpoints
   • Manage devices
   • Manage linked servers
5. Replication – allows you to:
   • Manage & configure replication
6. Management
   • View activity log information
   • Run basic
7. Integration Services
8. SQL Server Agent
   • Setup, configure and manage agent jobs
   • Manage SQL Server automation

Not only does the object explorer allow an administrator to manage the SQL Server instance, it also allows a developer to:

1. Filter & identify database objects from all objects of a database
2. Once filtered, the developer can script these objects out

Filtering objects within the object explorer

We can summarize filtering within the object explorer in 3 simple steps!

Step 01: A. In the Object explorer, right click on the “Tables” B. Go to Filter –> Filter Settings
Step 02: Enter the object information as necessary. For our case, enter: Name = Person (If you only want to work within a schema, enter only the schema name)
Step 03: Simply click “OK” to apply the filters in the Object Explorer
Step 04: To remove filtering, all one needs to do is: A. In the Object explorer, right click on the “Tables” B. Go to Filter –> Remove Filter

Filtering is applicable to all object types within the Object Explorer. If one is to only work on a particular schema or a set of objects, it is very productive to have only those required objects filtered out. For SQL Server 2005 users, there are no bad news because this feature is available in Microsoft SQL Server 2005 as well.

Scripting a database object

Once you have filtered for the required object, you can script out the object for saving a copy or deploying the object on another database/SQL Server instance. Again, we have a very concise sequence of steps:

Step 01: Right click on any object.
Step 02: Go to Script Table As –> Create To –> New Query Editor window
Step 03: A script that can be used to recreate a database object is now generated.

Adjusting scripting options

You can adjust the scripting options to suit your standards and needs. For example, many prefer that the script check for pre-existing versions of the objects before going ahead with the deployment. To adjust the scripting options, you need to follow the following steps:

Step 01: Go to Tools –> Options
Step 02: Under SQL Server Object Explorer, go to “Scripting”
Step 03: Configure scripting options of your choice depending upon your project/organization best practices

Once these options are set, the SSMS will follow these settings for all scripting capabilities available.

Object Explorer Details window

In addition to providing object listing functionality same as the Object explorer, the Object Explorer details also provides certain additional information:

1. Database information
   • Information about database owner, space available, recovery model and other database properties
2. Table & index usage
   • Information about # of rows in a table/index, size of table/rows, etc
3. Space utilization
   • Log and data file space utilization

The object explorer details is not launched by default. To launch the Object explorer details window, simply hit F7 after launching SSMS or go to View->Object explorer details.

IMPORTANT: On 32-bit operating systems, Object Explorer can only display 64,000 objects. An icon must be selected to access additional objects.

The following screenshots demonstrate how you can move about the object explorer details window via a drill-down mechanism.

Object explorer base state
Drill down to the “Databases” node, and select a database to view database properties (Also, you can use the column chooser)
Double click on the database, browse out to the “Tables” node and double-click on any table to get the table Details
Index Details

Object Search

One of the best features I like is the object search. It’s power-packed, fast and is absolutely geared towards reducing developer and administrator efforts.

Here are the brief steps:

1. Navigate to the Object Explorer Details window by using the View menu or pressing the (F7) key when in the SQL Server Management Studio
2. Drill down to the database against which the search needs to be carried out
3. In the Search bar, enter the object name that you would like to search for (you can use wild-card characters)
4. Simply hit “Enter” to execute the search!

If no specific database is chosen, the search is executed across all databases in the entire SQL Server instance. This is very useful feature, however, can be very slow. If you happen to use this accidentally, there’s always the panic button labeled – “Stop”!

Once an object is found, one might want to look for it in the Object Explorer. To do so, simply right-click and choose “Synchronize”.

Step 01: Right-click and choose “Synchronize”
Step 02: Notice that both the object explorer and object explorer details now points to the selected object

In addition, the object explorer details window allows the administrator or the developer to script out the object.

Practice Exercises/How To…?

It’s time to practice! The practice exercises for Object Explorer and the Object Explorer details are fast, simple and easy.

Object Explorer

1. Explore how to start and stop the SQL Server and the SQL Server Agent services via the Object Explorer window
2. From the AdventureWorks2008R2 database, attempt to script at least 2 similar objects (stored procedures, triggers, etc) to a single script together

Object Explorer Details

1. Create the following stored procedure within the AdventureWorks2008R2 database

   CREATE PROCEDURE proc_tmpOESearch
   AS
   BEGIN
       PRINT 'BeyondRelational.com'
   END
   GO
     

   • Search for the text – “BeyondRelational” within the AdventureWorks2008R2 database. Are any results returned?
   • Now, search on the object name – “OESearch” within the AdventureWorks2008R2 database. Are any results returned?
2. Search for the object names using wild cards  – “dm_os%counters” within the AdventureWorks2008R2 database
3. Can you search for objects using the .NET regular expressions?
4. From the AdventureWorks2008R2 database, attempt to script at least 2 similar objects (stored procedures, triggers, etc) to a single script together. Are the results different from the Object Explorer window?

Further Reading

• Using Object Explorer Details and SQL Server Object Search
• How to: Search with Wildcards

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

Per Books-On-Line, The Microsoft SQL Server is a database management and analysis system for e-commerce, line-of-business, and data warehousing solutions. The SQL Server Management Studio is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server.

The SQL Server Management Studio

For those who have worked with Microsoft SQL Server in the past (i.e. circa SQL Server 7 or SQL Server 2000 era), the following would be familiar terms to you:

• Enterprise Manager
  • Provided a Microsoft Management Console (MMC) compliant UI to administer, configure and manage a given set of SQL Server instances
• Query Analyzer
  • Provided a development interface to develop and debug T-SQL code
• Analysis Manager
  • Provided an IDE for data warehousing solution development and setup

Starting Microsoft SQL Server 2005, all the 3 interfaces mentioned above have been combined into one – the SQL Server Management Studio, popularly known as the SSMS. In addition to the above, the SSMS also features a broad variety of graphical tools and rich script editors to provide access to SQL Server developers and administrators of various skill levels.

Accessing the SQL Server Management Studio

In a default installation of Microsoft SQL Server 2008 R2, the SSMS is installed in the directory – C:Program FilesMicrosoft SQL Server100ToolsBinnVSShellCommon7IDE.

Program shortcuts in the Start Menu are created for the SSMS. The user can either use these shortcuts or directly execute “ssms” from the “Run…” window.

Connecting to a SQL Server instance

To work with any instance of SQL Server, the first thing that we need to do is to connect to the particular instance. Almost each window of the SSMS establishes its’ own connection to the SQL Server.

Whenever SSMS is launched, one of the first screens that one encounters is the login screen. Based upon the user inputs, the login screen builds a connection string that will be used by the various components of the SSMS. The very basic information that needs to be provided to this screen is the name of the SQL Server instance, the authentication type, user name and password. However, clicking on the “Options” button provides you the option to customize the following parameters of a connection:

• Default Database (from a list of all databases on the chosen instance)
• Network protocol (auto-determines the protocol to use – shared memory for local connections, TCP/IP for remote instances)
• Network Packet Size (default is 4096 bytes)
• Connection & Execution timeouts (default is 15 seconds)
• Encryption details (not enforced by default)
• Any other connection parameters that one might want to use

Login dialog	Login dialog – setting connection properties	Login dialog – add any additional connection parameters here.

The windows of SQL Server Management Studio

Upon successful login, you will see a number of windows and tools in the SQL Server Management Studio, namely:

1. Query Editor
2. Object Explorer
3. Registered Servers
4. Properties Window
5. Templates window

Most of these tools & windows, if not available, can be accessed from the View menu.

IMPORTANT: It is important to note that the SQL Server Management Studio is based off the Visual Studio platform. Therefore, the following usability features are directly inherited by the SSMS:

• Maximize the user workspace for development and management
• Reduce the number of windows open at a time
• Provides the ability to customize the user environment

Because it is based upon the Visual Studio shell, the SSMS allows the user to control the window size and therefore, the amount of space that the user wishes to allocate for different windows. The windows can be moved to different locations, undocked and even moved out of the SSMS frame. The query editor windows can be opened in the multi-document interface (MDI) modes also.

Pinal Dave (blog), a Microsoft Database & BI technology evangelist in India, wrote about the multi-monitor support capabilities in the upcoming release of SQL Server – SQL 2012 (code named: “Denali”). You can read about this feature here: http://blog.sqlauthority.com/2011/01/24/sql-server-2011-multi-monitor-ssms-windows/

Query Editor window

One of the most important parts of the SSMS environment is the Query Editor window, which is what will be used to write and execute scripts.

To launch a new query editor window, in addition to opening a pre-existing query, you can follow any one of the following navigation paths:

1. File -> New -> Database Engine Query
2. File -> New ->Query with current connection
3. File -> New ->Analysis Services MDX/DMX/XMLA

Getting comfortable with the Query Editor UI

Because this is one of the most important windows that we would be working with, it is important to get a feel of the various components of this window:

The major parts of the Query editor window are:

1. The script editor window
   • A feature-rich text editing environment supporting find and replace, bulk commenting, custom fonts and colors, and line numbering. Some types of editors contain additional features like outlining and auto-complete
2. Status bar
   • Connection state
   • Server Instance Name and version
   • Login Name and associated SPID number
   • Current database name
   • Time taken for last query/batch execution
   • Number of rows returned during last query/batch execution

Apart from the above components, the Query Editor window also has the following child-windows which are visible depending upon various factors such as user configuration, whether the query execution has taken place or not. These windows can be seen in tabbed, docked or floating modes. These windows are:

1. Results tab
   • Displays the result of a query execution. Results can be in grid or text format
   • This gets reset when the next execution of the query/batch is performed
2. Messages tab
   • Displays any messages that may be generated as part of query execution
   • This gets reset when the next execution of the query/batch is performed
3. Error List
   • Displays syntax and semantic errors as you edit the T-SQL scripts
   • This gets reset when the next execution of the query/batch is performed
4. Client Statistics
   • This window displays the query execution information grouped into categories
   • For multiple rounds of execution, this window shows the aggregated average information

We will take a look at these windows further down the article.

Intellisense & Error List windows

The following is an example of a basic T-SQL query based on the AdventureWorks2008R2 sample database.

USE AdventureWorks2008R2
GO
SELECT * FROM HumanResources.Employee
GO

After logging in to a particular SQL Server instance using SSMS, type in the given script. As you type along in the Query editor window, notice that the Query Editor window prompts you with a list of possible alternatives that it thinks you would type in next. This prompt is what we call – “Intellisense”.

The good thing about “Intellisense” is that the prompts not only contain the list of options, but there are small icons next to the objects that help the user identify the nature of the object, i.e. whether it is a database, schema name, variable name, a table or a column name, etc. Also, we can see some nice tooltips towards the side of the Intellisense prompt that contains a description about the object.

A user can navigate through the Intellisense prompt by using the arrow keys on the keyboard (which I find to be the fastest way to do so) or use the mouse. Once the desired object is selected, all the user has to do is press the Enter or the Tab key (when using the keyboard) or double-click on the mouse.

IMPORTANT:

• Intellisense is case-sensitive, making it a key feature for those working on case-sensitive collations
• For SQL Server 2008 R2, Intellisense only performs a “begins-with” search, whereas in SQL 2012 (code named: “Denali”), it performs a “contains” or a “like” search. Read my post on this here: http://beyondrelational.com/blogs/nakul/archive/2011/08/11/intellisense-in-sql-11-denali-uses-like-search-a-productivity-enhancement.aspx

Now, navigate out to the View menu, and choose to display the “Error List” window. Assume that you made a mistake during the typing of the query.

Launching the Error List window	Error List window in action

As you can see, the Error list window immediately detects syntax and semantic errors as you type along. Double-clicking on the error brings you to the concerned line that encountered the error. Once corrected, the error clears out from the Error List.

However, the Error List window has a few drawbacks. You can read about them in Aaron Bertrand’s post (blog | twitter) referenced in the Further Reading section below.

Query Regions

Prior to the Management Studio for SQL Server 2008 coming out, all code within the Query editor window was a giant block, which caused readability issues. Let’s say for example that you are working on a lengthy (approximately more than 500 lines) stored procedure/script consisting of several logical sections. After the development of one particular section is complete, you may not need to look at it again, and might prefer to selectively hide it such that it does not interfere with your working area on the query editor.

The SSMS for SQL Server 2008 introduced a new usability feature – Query Regions. A vertical line on the left edge of the editor window uses a square with a minus sign (-) to identify the start of each collapsible code region. When you click a minus sign, the text of the code region is replaced with a box that contains three periods (…), and the minus sign changes to a plus sign (+). Clicking on the (+) sign expands the code section.

When working on a database engine (T-SQL) query, the query editor generates outline regions in the following hierarchy:

1. Batches
   1. From the start of the file to the first GO command (or till the end of the file in case no GO commands are present)
2. Blocks of code grouped by BEGIN…END keywords. This includes the following:
   1. BEGIN…END
   2. BEGIN TRY…END TRY
   3. BEGIN CATCH…END CATCH
3. Multi-line statements

I will use the same example as BOL (http://msdn.microsoft.com/en-us/library/cc281835.aspx) to demonstrate the behavior.

USE AdventureWorks2008R2
GO
CREATE PROCEDURE Sales.SampleProc --Outline region 1
AS
BEGIN --Outline region 2 
  SELECT GETDATE() AS TimeOfQuery;
  SELECT * --Outline region 3
  FROM sys.transmission_queue;
  SELECT @@VERSION;
END;
GO

Fully expanded regions	Fully closed regions
Innermost region closed	As we close the higher-level regions, the inner levels are closed alongwith

Getting familiar with the Query Editor Toolbars

Now that we have written our first T-SQL query in the query editor, it is time for us to explore the SSMS toolbars.

To view a toolbar, you can:

1. Use the View -> Toolbar menu
2. Right-click on the toolbar space in the Query editor window and choose the required toolbar

The most important toolbars that I find are:

1. Standard
2. SQL Editor
3. Text Editor

The Standard Toolbar

The Standard toolbar allows you to perform the most of the generic activities that SSMS can perform. This includes:

1. Write a new Query (all types)
2. Perform basic file operations (open, save and print)
3. Launch a tool called “Activity Monitor” (to be discussed in future tutorial)

The SQL Editor Toolbar

1. Connect to or Disconnect from a SQL Server instance
2. Change the active connection
3. Execute, Debug or cancel execution of a query in the active query editor window
4. Parse the query in the active query editor window
5. Modify the query options for the particular connection
6. Toggle Intellisense state
7. Choose whether to display results in the grid/text or export to a file
8. Comment/Uncomment or adjust indentation of a given section of text
9. Display Execution plans and client statistics
10. Specify template parameters (to be covered in a future tutorial)

The Text Editor toolbar

In addition to allowing the user to navigate through the script, the text editor toolbar allows the user to:

1. Work with Intellisense options
   • Display an object member list
   • Display parameter info
   • Display quick info
   • Display word completion

Executing a query

Our very first T-SQL query has been written and we are now familiar with the toolbars on the SSMS window. It is now time for us to press the “Execute” toolbar button on the SQL Editor toolbar.

Results tab

One of the first things that you notice as you execute the query is that we now have an additional set of tabbed windows towards the bottom of the SSMS window. The first of these tabs is the “Results” tab, which contains the results of query that was executed.

Results are contained in grid format on the Results Tab. Based on your requirement, you can choose whether to display these in text or to export them to a file on disk.

TIP: There is a way to discard these results automatically. That is to say that the query will be executed, but no results would be returned to the calling SSMS connection, saving memory, while providing actual query execution information useful when performance tuning. You can find information on this in the “Exercises/How To…?” section.

Messages tab

Any messages that need to be displayed to the user (includes status, error and user generated messages) are displayed in the Messages Tab.

In addition, the system may also present the query results in the messages tab if the user has chosen to view the query results in text.

Client Statistics

Although this is not really in scope for this level of the discussion, an optional client statistics tabbed window can be invoked to obtain vital information of importance during performance tuning. It returns us the information about the query cost in terms of execution, network and time statistics.

To know more about this, refer the “Further Reading” section.

Practice Exercises/How To…?

There can be no learning without some exercises. Because we are dealing with SSMS and the Query Editor, these exercises will help you explore the various configuration options that you can use to create your very own, customized version of the SSMS environment.

SSMS

• How to quickly know the SSMS client tools version?
• How to a local or remote SQL Server instance via the Dedicated Administrator Connection?
• How to use multi-monitor support in the SQL 2012 SSMS?

Query Editor

• How to use the zoom feature in upcoming SQL2012 (Code Named: “Denali”)?
• How to use the Clipboard ring feature inherited from Visual Studio 2010?
• How to change the colour of the query editor status bar?
• How to use the Query editor to generate comma-separated or tab-separated output without BCP or query changes?
• Type in the following T-SQL query. Identify the colour coding scheme used to identify the various object types:
  • Database name
  • Keywords
  • Table/object names
  • System Function Names
• Use the Query editor options to modify the information shown on the default sections of the Query editor

IMPORTANT: Keep in mind that the colour coding scheme used by SQL Server 2008 is different from what is used in SQL Server 2012 (code named: “Denali”) CTP03 or RC0 releases.

Further reading

SSMS

• Know more about connection strings and get a downloadable PDF on writing connection strings
• SSMS shortcuts:
  • Comprehensive List
  • Concise List

Query Editor

• Aaron Bertrand’s (blog | twitter) post on the new SSMS in SQL 2012 (code named: “Denali”) – http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/23/sql-server-11-denali-the-new-vs-shell.aspx
• Default colour coding in the Code Editors of SSMS: http://msdn.microsoft.com/en-us/library/ms173443.aspx
• Client Statistics: http://blog.sqlauthority.com/2009/10/01/sql-server-sql-server-management-studio-and-client-statistics/

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

The properties and behaviour of each session to a SQL Server instance can be handled individually via use of various SET options. As we multi-task during the day, we tend to forget the exact SET options that we choose for a particular user session. The focus of the day is how to retrieve this information programmatically, i.e. through T-SQL.

Retrieving SET options for a given session

To quickly review the SET options for a given session, we have two methods:

1. Properties Window
   • The Properties window is available if we are in a SSMS session – this method is not applicable when attempting to fetch the SET options via code
   • I described this method in my previous post, August 01, 2011 (http://beyondrelational.com/blogs/nakul/archive/2011/08/01/ssms-properties-window-know-your-query-options-ansi-nulls-quoted-identifier-arithabort-and-other-set-options.aspx)
2. Using DBCC commands
   • For those would be interested to get these SET options via code, the DBCC comes to our rescue
   • Let’s take a quick look at this method to get the user options

Demo

Using the Properties window to get the user options involves executing a query workload and retrieving the actual execution plan. DBCC, on the other hand allows us to fetch the user options without any workload and without retrieving the execution plan.

For this demonstration, let’s make a small change to the default user options, and run the DBCC statement in the query below.

--Change the connection option
SET ANSI_NULLS OFF

--Retrieve the user options for this connection
DBCC USEROPTIONS
GO

Now, let’s roll back the change (or launch a new session by creating a new query editor window), and run the following statement.

--Change the connection option
SET ANSI_NULLS ON

--Retrieve the user options for this connection
DBCC USEROPTIONS
GO

Comparing the outputs:

Comparing the output from the two sessions, we can see that the user options that are turned OFF do not appear on the list.

ANSI_NULLS OFF		ANSI_NULLS ON
Set option	Value	Set option	Value
textsize	2147483647	textsize	2147483647
language	us_english	language	us_english
dateformat	mdy	dateformat	mdy
datefirst	7	datefirst	7
lock_timeout	-1	lock_timeout	-1
quoted_identifier	SET	quoted_identifier	SET
arithabort	SET	arithabort	SET
ansi_null_dflt_on	SET	ansi_null_dflt_on	SET
ansi_warnings	SET	ansi_warnings	SET
ansi_padding	SET	ansi_padding	SET
		ansi_nulls	SET
concat_null_yields_null	SET	concat_null_yields_null	SET
isolation level	read committed	isolation level	read committed

Reference

Books On line page for DBCC USEROPTIONS – http://msdn.microsoft.com/en-us/library/ms180065.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

Performance tuning & analysis during new development has it’s own unique challenge – on one hand as database developers, we need a clean procedure cache and on the other hand, we do not want to impact the performance of other databases on the same instance.

To start from a cold procedure cache, we know that we can use the DBCC FREEPROCCACHE. However, this would completely clear out the procedure cache – impacting others who may be working on the same instance, but on other databases. We can supply the plan/query handle or pool name to reduce the impact of the DBCC FREEPROCCACHE, but because the plan handle changes each time the plan is regenerated, a lot more manual steps become involved in the process.

To restrict the clearing of the procedure cache to the current database only, we can use the DBCC FLUSHPROCINDB command. The DBCC FLUSHPROCINDB command only accepts one parameter – the database ID.

For example, the following command will flush the procedures in cache for the AdventureWorks2008R2 database on my SQL Server instance.

/*
!!!!WARNING!!!!
This script is provided AS-IS and without warranty.
The author, Nakul Vachhrajani; the website, BeyondRelational.com
and Microsoft Corproation are not responsible for any damage caused
by misuse of this script
*/

--Fetch the DB_ID for the required database
USE AdventureWorks2008R2
GO
SELECT DB_ID() AS AdventureWorks2008R2DBID

--Clear out the procedure cache for the AdventureWorks2008R2 database ONLY
DBCC FLUSHPROCINDB (5)

References

• Plan caching in SQL Server 2008 – http://msdn.microsoft.com/en-us/library/ee343986(SQL.100).aspx

NOTE: To the best of my knowledge, this is an undocumented DBCC command. Please use it with utmost caution. If you find the official Books On Line documentation, please let me know and I will update the post to reflect the same.

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!