Logo
CAR REVIEW
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
PREGNANCY
 
 
Windows Server

Microsoft SQL Server 2008 R2 : SQL Server Management Studio - The Integrated Environment

2/27/2012 4:25:54 PM
If you have been working with SQL Server for a long time, you may remember the SQL Enterprise Manager that came with SQL Server 6.5. In some respects, with SSMS, Microsoft has moved back to the paradigm that existed then. Like the SQL Server 6.5 Enterprise Manager, SSMS provides an integrated environment where developers and DBAs alike can perform the database tasks they need. Say goodbye to Query Analyzer, Analysis Manager, and a number of other desparate tools used in SQL Server 2000 and say hello to SSMS, which provides “one-stop shopping” for most of your database needs.

Window Management

Figure 1 shows a sample configuration for the SSMS main display. The environment and windows displayed are completely customizable, with the exception of the document window area. Figure 1 shows the document window area displaying the Object Explorer Details page. The Object Explorer Details page is the default, but other pages, such as a query editor window, can take the focus in this tab-oriented section of the SSMS display.

Figure 1. The SSMS main display.

The dialogs that form the rest of the SSMS display are referred to as components and include the Registered Servers and Object Explorer windows shown in Figure 4.1, as well as a number of other components that can be displayed via the View menu found at the top of the SSMS display. You can configure each of the component windows in a number of ways; for example, you can have them float, or you can hide, dock, autohide, or display them as tabbed documents in the document window area.

The configuration that you choose for your SSMS display depends on the type of work you do with SQL Server as well as the type of person you are. The Auto Hide feature causes the component window to shrink to a tab along the left or right side of the display. When you mouse over the tab, the window automatically expands and stays expanded as long as the mouse cursor remains in the component window area. Auto Hide helps maximize the working real estate available in the document window for query development and the like. Docking many windows can clutter the screen, but this feature allows you to view many different types of information all at once. This is a matter of personal preference, and SSMS has made it very easy to change.

Tip

You can reposition the component windows by dragging and dropping them to the desired locations. When you are in the middle of a drag and drop, rectangular icons with arrows are displayed at different locations on the SSMS window surface. If you mouse over one of these arrowed icons to select the window location, you see the window destination highlighted. If you release your mouse button while the destination is highlighted, the window docks in that position.

Some users at first ignore the arrow icons and keep hovering the window over the location where they want the window to go. Hovering the window over the desired location does not allow you to effectively dock it. You should save yourself some time and aggravation and use the arrow icons for drag-and-drop positioning.


The SSMS window environment include nonmodal windows that are sizable. The nonmodal windows allows you to perform multiple tasks at one time without needing to open another instance of the SSMS application. In SQL Server 2000, the Enterprise Manager users were forced to open another instance of the application during many administrative tasks to be able to continue with other work. With SSMS, you can launch a backup with the Back Up Database dialog and then continue working with the Object Explorer or other components in SSMS while the backup is running. This capability is a great timesaver and helps improve overall productivity.

Your ability to size the dialog boxes is another user-friendly feature that may seem minor but is quite handy on certain windows. For example, the SQL Server 2000 Enterprise Manager Restore dialog had a fixed size. Viewing the backup set information in this relatively small (nonsizable) dialog box was a challenge. The Restore dialog in SQL Server 2008’s SSMS can contain a slew of information related to the backup sets available for restore. The capability to size the windows allows for much more information to be displayed.

The tabbed document window area provides some usability improvements as well. This area, as described earlier, is fixed and is always displayed in SSMS. Component windows can be displayed in this area, along with windows for the Query Editor, diagrams, and other design windows. If desired, you can change the environment from a tabbed display to multiple-document interface (MDI) mode. In this mode, each document is opened in its own window within the document window. The MDI mode manages windows like the SQL Server 2000 Query Analyzer and may be more user-friendly for some people. You can change to MDI mode by selecting Tools, Options and then selecting MDI Environment from the General page.

One particularly useful window that can be displayed in the document window is the Object Explorer Details page. This new window displays information relative to the node selected in the Object Explorer and includes options to produce detailed reports and graphs. The Object Explorer Details page is displayed in the document window by default when SSMS is launched, but you can also display it by pressing F7 or choosing Object Explorer Details from the View menu.

The Object Explorer Details page has been vastly improved in SQL Server 2008. If you’re familiar with the previous version, you can see in Figure 4.1 that there is much more information displayed in SQL Server 2008 than there was in 2005. The nice part is that you can customize the information that is displayed and save those changes so that they are used the next time you open SSMS. For example, when you right-click on a column heading (such as Name), you see all the columns available for display. Only a handful are displayed by default, but more than 30 available columns relate to databases. The columns that are available depend on the type of object selected in the Object Explorer window.

Tip

You can copy some or all of the information shown in the Object Explorer Details window and paste it into another application such as Excel for a quick and easy report. For example, you can select the Databases node in Object Explorer, highlight the data shown in the Object Explorer Details page, press Ctrl+C to copy the data, and then paste it into Excel. All the columns related to database (including Headings) are captured and give you an easy way to review information about all your databases.


Another significant change in the Object Explorer Details page is the Object Search box. The Object Search box, located at the top of the Object Explorer Details page (next to the Search label), allows you to search for objects by name. You can use wildcards (for example, Product%), or you can type a specific name you are looking for. The results are displayed in the Object Explorer Details page. Keep in mind that the objects that are searched depend on what is selected in the Object Explorer window. For example, if you highlight the Databases node, you search all the databases on your SQL Server instance. If you select a specific database, only that database is searched.

Tip

In SQL Server 2000, you could select multiple objects for scripting by selecting the items from the Object Explorer tree in Enterprise Manager. You cannot use the Object Explorer tree to perform this operation with SQL Server 2008, and this has generated some confusion. The solution is the Object Explorer Details page, which provides a means for performing multiple selections of the objects it displays. You can hold down the Ctrl key and click only those items you want to script. After you select the items you want, you simply right-click one of the selected items and choose the preferred scripting method. This method also works with scheduled jobs displayed in the Object Explorer Details page. SQL Server 2000 did not offer this capability.


Integrated Help

SSMS offers an expanded set of help facilities as well as improved integration into the application environment. The Help sources have been expanded to include both local and online resources. Local help is similar to the Help resources available in past versions and references files installed on your machine during the installation process.  Local help files are static and are updated only if another documentation installation is run on the local machine.

Online help provides access to content that is not static and can be updated with the latest changes. Three default online resources are provided by default:

  • MSDN Online— MSDN Online contains the latest version of the MSDN documentation, including the latest quarterly releases.

  • Codezone Community— Codezone Community includes a set of third-party websites that have partnered with Microsoft and provide a wealth of information from sources outside Microsoft.

  • Questions— The Questions option allows you to search the forum archives for answers to questions that others have already asked. It also allows you to post your own questions.

The help resources you use on your machine are configurable. You can choose to search online resources first, followed by local help, or you can choose an option that searches local help resources first, followed by online resources. You can also choose specific Codezone online resources to search, or you can eliminate the search of all online resources. Figure 2 shows the online help Options window, which allows you to configure your Help options. You access this dialog by selecting Tools, Options.

Figure 2. Setting Help options.

The Help resources you select are used when you search for content within the Help facility. When you use both local and online resources options, you see results from multiple locations in your search results. Figure 3 shows a sample Books Online Document Explorer window with results from a search on “Management Studio.” Notice that the panel on the right side of the window lists entries under Local Help, MSDN Online, Codezone Community, and Questions. Each of these sections contains search results that you can access by simply clicking on that area. The number of search results for each section is displayed in parentheses after the section name.

Figure 3. A Books Online search.

One other significant change to the help facilities in SSMS is the addition of Dynamic Help. Dynamic Help is a carryover from the Visual Studio environment. It is a help facility that automatically displays topics in a Help window that are related to what you are doing in SSMS. For example, if you are working in a query window and type the word SELECT to start your query, the Dynamic Help window displays several topics related to the SELECT statement. If you are working in the Object Explorer, it displays Help topics related to the Object Explorer.

Note

There is some processing overhead associated with Dynamic Help. You may find that your SSMS environment runs a bit slower when you use this feature.


Dynamic Help is one of the component windows that you can dock or position on the SSMS surface. To use Dynamic Help, you select Help, Dynamic Help. Figure 4 shows an example of the SSMS environment with the Dynamic Help window docked on the right side of the window. The Dynamic Help topics in this example are relative to the SELECT keyword that is typed in the query editor window in the middle of the screen.

Figure 4. Dynamic Help.
Other -----------------
- Managing Exchange Server 2010 Clients : Using Mail Profiles to Customize the Mail Environment
- Managing Exchange Server 2010 Clients : Managing the Exchange Server Configuration in Outlook
- System Center Configuration Manager 2007 : Configuration Manager and WMI (part 6) - WMI on Configuration Manager Servers
- System Center Configuration Manager 2007 : Configuration Manager and WMI (part 5) - The Configuration Manager Client WMI Namespace
- System Center Configuration Manager 2007 : Configuration Manager and WMI (part 4) - Hardware Inventory Through WMI
- System Center Configuration Manager 2007 : Configuration Manager and WMI (part 3) - Looking Inside the CIMV2 Namespace
- System Center Configuration Manager 2007 : Configuration Manager and WMI (part 2) - Managing WMI
- System Center Configuration Manager 2007 : Configuration Manager and WMI (part 1) - WMI Feature Set and Architecture
- System Center Configuration Manager 2007 : Active Directory Integration
- Deploying and Managing BizTalk Applications : Deploying a BizTalk Solution (part 2)
- Deploying and Managing BizTalk Applications : Deploying a BizTalk Solution (part 1) - Steps in Deploying a BizTalk Application
- Microsoft Dynamics CRM 4.0 Accelerators : Event Management Accelerator (part 3) - Publish the CRM Workflow & Testing the Portal
- Microsoft Dynamics CRM 4.0 Accelerators : Event Management Accelerator (part 2) - Install CRM Reports & CRM Customizations
- Microsoft Dynamics CRM 4.0 Accelerators : Event Management Accelerator (part 1) - Install CRM Customizations
- Microsoft Systems Management Server 2003 : Disaster Recovery - Using the SMS Site Repair Wizard
- Microsoft Systems Management Server 2003 : Disaster Recovery - Using the Recovery Expert
- SQL Server 2008 : Data Collector and MDW - Data collection
- SQL Server 2008 : Data Collector and MDW - Setup and configuration
- Microsoft Content Management Server : Adding a Search Page to the MCMS Site (part 4) - Building the MSQuery XML String
- Microsoft Content Management Server : Adding a Search Page to the MCMS Site (part 3) - Building the Microsoft SQL Full-Text Query
 
 
Most view of day
- Microsoft OneNote 2010 : Using the Research and Translate Tools (part 3) - Translating Text with the Mini Translator
- Windows Phone 7 : 3D Game Development (part 1) - 3D Game Concepts
- Conducting Research in OneNote 2010 : Translating Text
- Microsoft Access 2010 : Using Reports to Print Information - Opening and Viewing a Report
- Using Micrsosft Outlook 2010 with SharePoint and OCS : Using SharePoint Document Libraries in Outlook
- How to Troubleshoot Disk Problems (part 1) - How to Prepare for Disk Failures, How to Use Chkdsk
- Windows Server 2012 : Provisioning and managing shared storage (part 1) - Provisioning shared storage - Creating a storage pool
- Microsoft PowerPoint 2010 : Assigning Transitions to Slides
- SharePoint 2013 Request Management (part 2) - Request Management Administration
- Windows Server 2012 : Installing and Managing Hyper-V in Full or Server Core Mode - Installing Windows Server 2012 and Microsoft Hyper-V Server 2012
Top 10
- Microsoft Project 2010 : Linking Tasks (part 8) - Auditing Task Links,Using the Task Inspector
- Microsoft Project 2010 : Linking Tasks (part 7) - Creating Links by Using the Mouse,Working with Automatic Linking Options
- Microsoft Project 2010 : Linking Tasks (part 6) - Creating Links by Using the Entry Table
- Microsoft Project 2010 : Linking Tasks (part 5) - Creating Links by Using the Task Information Dialog Box
- Microsoft Project 2010 : Linking Tasks (part 4) - Entering Leads and Lags, Creating Links by Using the Menu or Toolbar
- Microsoft Project 2010 : Linking Tasks (part 3) - Using the Start-to-Start Relationship,Using the Finish-to-Finish Relationship
- Microsoft Project 2010 : Linking Tasks (part 2) - Using the Start-to-Start Relationship,Using the Finish-to-Finish Relationship
- Microsoft Project 2010 : Linking Tasks (part 1) - Defining Dependency Links
- Microsoft Project 2010 : Defining Task Logic - Manipulating Your Schedule
- Microsoft Lync Server 2013 : Director Troubleshooting (part 3) - Synthetic Transactions,Telnet
 
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro