SQL Server Management Studio - T-SQL

SQL Server Management Studio completely replaces Enterprise Manager and Query Analyzer. It also replaces some of the functionality formerly found in Analysis Manager. It does an excellent job of both replacing the old tools and exceeding them in almost every possible way.

The SQL Server Management Studio interface looks a lot like the Visual Studio IDE and is in actuality a Visual Studio shell. The Visual Studio shell brings many very useful tools and features to the creation and organization of database objects, as well as the full feature set of the old tools.

If you have SQL Server 2000 or SQL Server 7 experience, you will notice that when SQL Server Management Studio is first launched, the default view is a great deal like the old Enterprise Manager, with a slight Query Analyzer influence (see Figure IN The Below).

SQL Server Management Studio

Many different windows can be viewed in Management Studio, so the management of screen real estate becomes critical. Most of the windows have the ability to either be pinned open or configured to fly out when the mouse pointer is placed over the menu bar or auto hide when the mouse cursor is placed elsewhere. If you are familiar with the Visual Studio IDE, this will be very familiar; if not, it may take a little while to get used to.

For those of you who are unfamiliar with the Visual Studio interface, the following bit of instruction is offered: Any window that supports the pinned and unpinned option will have a pin at the top right of the window. When the window is pinned, the pin will appear vertically oriented. When the window is unpinned, it will be horizontal (see Figure IN The Below) and the toolbar will auto hide or fly out, depending on the mouse cursor location.

SQL Server Management Studio

Most of the toolbars and windows also support the ability to be repositioned or configured to float. To reposition a tool window, you can just grab it by the title bar using the mouse pointer, and then drag to undock and move it around the design surface (see Figure IN The Below). Something interesting happens when you do this.

SQL Server Management Studio

When a window is undocked and you drag it around the main window, guide diamonds are displayed, like points of a compass, to assist with the docking window placement (see Figure IN The Below).

SQL Server Management Studio

When you hover over one of the guide diamonds, the docking target area of the window is designated with a translucent shaded rectangle. As you see in Figure IN The Below, you can also use the guide diamonds in the center cluster of the window. If a window is already docked in that area, using the center guide will dock your window adjacent to the existing window.
Microsoft SQL Server Management Studio

SQL Server Management Studio

If you hover over an existing docked window, a separate set of guides will appear, allowing you to dock within this space or to create tabbed documents where the windows share screen real estate with other windows in the same space (see Figure IN The Below).

Microsoft SQL-Server-Management-Studio

If you get into trouble and can't place a window where you want it, click to set focus to the window and then use the Window menu on the standard menu bar to toggle the window back to either Floating or Dockable. This should allow you to reposition the window as you like. If you don't have any experience with Microsoft's development environment, the Visual Studio interface may take a little getting used to, but once you do, it is hard to imagine any interface that works as well. It offers the advantage of being able to hide windows when you don't need them but make them visible when you do, without having to reconfigure the interface. This conserves a great deal of screen real estate without having to click on several menus to expose the features you want.

Tool Windows

SQL Server Management Studio offers many different tool windows that facilitate the development and modification of database objects as well as the effective management of SQL Server. The various views are accessible from the View menu as well as the Standard toolbar. Each window can be configured as dockable, which is the default, but it can also be configured as a tabbed document or a floating window.

Object Explorer

Object Explorer is more than just a way to explore the database objects on a server; it is also a tool that can be used to create basic template scripts for selecting, inserting, updating, and deleting data. As shown in Figure (IN The Below), Object Explorer is arranged in a standard tree view with different groups of objects nested in folders.

Object Explorer

Object Explorer's functionality is exposed through the context menu. Right-clicking on any object or folder within Object Explorer exposes the list of available options. For example, right-clicking on a table exposes the context menu shown in Figure (IN The Below). Putting the mouse over the Script Table As option exposes additional options for creating basic scripts for that table, as also shown in Figure (IN The Below).

Object Explorer

The context menu also presents the ability to create scripts that manipulate the object. For example, right-clicking on a table exposes a context menu that allows the user to either view or modify the table structure through the graphical interface. This functionality exists for virtually every object that is visible in Object Explorer.

Another great feature of SQL Server Management Studio that is exposed through Object Explorer and other areas of the studio interface is the ability to create scripts based on actions performed in the graphical designers. For instance, right-clicking on the table folder and choosing to create a new table launches a graphical interface where the table structure can be defined. Once the table design is complete, you can either save the table, which creates it, or you can click the Generate Change Script button on the Table Designer toolbar (see Figure (IN The Below)), which will write the appropriate T-SQL to complete the task.

SQL-Server-Management-Studio

Likewise when working with other objects in Management Studio, a Script button will appear at the top of the respective designer, which will cause the actions performed in the designer to be scripted to a new editor window. This feature is especially useful when several different objects of the same type are to be created. The first one can be designed in the designer, the script generated for it, and that script modified to create the remaining objects. It is also very useful to learn the syntax for creating and modifying objects.
In this example you will use Object Explorer to create a script to select data from the Human Resources. Department table.

  1. In Object Explorer, click the + symbol to the left of Databases to expand the database folder.Then expand the Adventure Works database folder and then the Tables folder.
  2. Right-click the HumanResources.Department table.
  3. On the context menu, click the Select Top 1000 rows item. Management Studio will generate a new query window with the script and execute it. The new query window and the query results are displayed to the right of the Object Explorer (see Figure (IN The Below)). Notice that the script generator places brackets around all the object names. This isn't really required for the objects in this query, but the script generator defaults to this behavior in case an object has an embedded space or reserved word in it. We'll discuss the particulars of object delimiting in Chapter (Introducing the T-SQL Language).

SQL-Server-Management-Studio

Code Editor

SQL Server Management Studio's Code Editor provides the ability to open, edit, or create new queries. When you click New Query, the query window that opens is also known as the Code Editor. The Code Editor supports the following types of queries:

  • Database engine queries— Database engine queries are written in T-SQL.
  • Data mining queries— Data mining queries are created by using extensions to the SQL language called DMX, or Data Mining Extensions. DMX queries are written to return information from data mining models created in SQL Server Analysis Services.
  • Multidimensional queries— Multidimensional queries are written using Multidimensional Expressions (MDX). MDX queries are used to retrieve information from multidimensional cubes created in SQL Server Analysis Services.
  • XML for analysis queries — XMLA queries are built to create, manage, and manipulate Analysis Server objects.
  • SQL Server Compact Edition queries— Compact edition queries are used in mobile applications hosted by the Windows Mobile operating system and SQL Server Compact Edition.

The Code Editor is essentially a word processor. It provides color coding of syntax, multiple query windows, and partial code execution by highlighting the desired code and clicking the execute button or pressing F5. SQL Server documentation refers to the Code Editor as the Query Editor, the Text Editor, or simply the Editor, depending on what aspect of SQL Server you are reading about.

In SQL Server 2008, the basic functionality that the Code Editor brings is the same for all the possible types of queries it supports. For example, the Code Editor provides support for basic IntelliSense functions and code completion such as those found in Visual Studio. In SQL Server 2005 the code-completion and IntelliSense features are not available with T-SQL queries. IntelliSense and code-completion are discussed later in this chapter. The Code Editor window also provides direct access to a graphical query builder. Right-clicking on the Code Editor window, when that window is associated with a database engine query, results in a context menu that includes the Design Query in Editor option (see Figure (IN The Below)). The Query Designer is very useful when writing queries against databases that you are not familiar with. At the end of this chapter is an exercise to show you how to use this useful tool to quickly create T-SQL queries.

Code Editor

Solution Explorer

Before SQL Server 2005, organizing queries and object definitions was completely left to the DBA or database developer. The ability to organize and group scripts together or to check them in to a source control system was completely manual. SQL Server Management Studio takes full advantage of Visual Studio's solution system by providing the means of grouping one or more projects that contain various connection objects and scripts into a single solution called a SQL Server Management Studio solution. Each solution can have one or more projects associated with it. For example, if you are developing several objects for a new application that includes both database engine and analysis engine objects, you can create a new solution that links them all together by creating a SQL Server Management Studio solution and creating both a SQL Server Scripts and Analysis Server Scripts project in that solution. You do this, oddly enough, not by creating a new solution, which there is no option for, but instead by creating a new project by clicking the File menu and choosing New Project, which launches the New Project dialog (see Figure (IN The Below)).

Solution Explorer

If no solution is currently open, Management Studio will create a new solution. If a solution is presently open, you will be given the choice of adding the project to the existing solution or closing the present solution and creating a new one. As you can see in Figure (Above), there are three types of projects from which to choose:

  • SQL Server Scripts projects contain T-SQL database engine queries.
  • Analysis Services Scripts projects contain MDX, DMX, and XMLA analysis queries.
  • SQL Server Compact Edition Scripts projects contain queries for the Compact Edition of SQL Server that is used in Windows Mobile devices.

The solution is managed through a solution file with an .ssmssln extension. The example shown in Figure (IN The Below) created a new solution folder called Adventure Works Web App that contains a project folder called Product Catalog. By default the solution folder and the first project folder will have the same name, so it is generally a good idea to change the name of the solution. The Create directory for solution option can also be cleared and a base solution folder specified. In this way only a project folder will be created in the specified directory. If a solution is already opened, creating a new project can add the project to the solution or be configured to create a whole new solution and close the open one.

In the solution folder are two files. One file is the solution file, which in this case is called AdventureWorksWebApp.ssmssln and contains a list of all the projects in the solution and their location. The second file is the solution options file, called Adventure Works WebApp. sqlsuo. The solution options file contains information about the options that customize the development environment.

The solution folder will contain a project folder for every project added to the solution. The project folder contains all the project files including the project definition file. The project definition file is a XML file with the .ssmssqlproj extension. In the previous example this file is called ProductCatalog.ssmssqlproj. The project definition file contains the connection information as well as metadata about the remaining files in the project.

A rather annoying aspect of the Solution Explorer is that new queries are not automatically added to the solution if they are not added from the Solution Explorer itself. For example, suppose that you have created a new project and then click on the New Query button at the top left of Management Studio. A new query window will open, but the query will not be part of your open project. To ensure that new queries are added to the project, you need to right-click on the Queries folder and select New Query.

Properties Window

As shown in Figure (IN The Below), the Properties window is linked to the Solution Explorer and simply displays the properties for the currently selected item in the Solution Explorer window. Editable properties will be bolded. If the Properties window is not visible, you can open it from the View menu or by pressing F4.

Properties Window

Registered Servers

Multiple servers can be registered and connected to with Management Studio. This allows the DBA to manage multiple servers in a single environment. The Registered Servers window is not visible by default. To display it, click the View menu and choose Registered Servers, or press Ctrl+Alt+G. Right-clicking anywhere in the Registered Servers window will expose a context menu that allows for the addition of new server registrations. It also allows for the creation of server groups. If you have multiple servers in your organization, server groups can be very useful. For instance, server registrations can be segregated so that all the test and development servers are in one group and the production servers are in another, or servers could be grouped based on function or department. Instances of the database engine, analysis services, reporting services, integration services, and SQL Server Compact Edition can be registered in the Registered Servers window. Once registered, the Registered Servers window provides the ability to manage the associated services or quickly connect Object Explorer to that server instance, as shown in Figure ((IN The Below)).

Registered Servers

Bookmark Window

When working with very large scripts in the Code Editor, it is very useful to be able mark a location in the script. Bookmarks enable this functionality. The Bookmark window is made visible with the View menu and is enabled when working with any SQL Server script type. Any number of bookmarks can be created and then renamed with an intuitive name that identifies the bookmark. If the script is part of a solution, the bookmarks are saved with the solution in the solution options file. Bookmarks can be organized in to multiple folders for each project. Figure ((IN The Below)) shows the bookmark window open and a bookmark called Catch Block.

Bookmark Window

Toolbox

The toolbox contains maintenance plan tasks that can be dragged to the Maintenance Plan designer used by database administrators to create maintenance plans for routine scheduled maintenance, such as database backups and index maintenance operations.

Object Explorer Details

The Object Explorer Details pane is displayed by default and is a great deal like the list or detail view in Windows Explorer.

Web Browser

Your default web browser can be launched from within SQL Server Management Studio to minimize the number of open applications and to allow direct access to Internet content from within the Management Studio application. This is done from the View menu by choosing Web Browser.

Template Explorer

The Template Explorer contains hundreds of SQL Server, Analysis Server, and SQL Mobile scripts. Each script is grouped into folders based on their function. The template scripts can be opened by being dragged on to an open query window. If no query window is open, the templates can be opened through a double mouse click, the Edit menu, or right-click context menu, all of which cause a new query window to open. Once a template is open in the Query Editor, the parameters of the template can be replaced with actual values by launching the Specify Values For Template Parameters dialog. This dialog can be launched from the SQL Editor toolbar or through the Query menu.

Error List

The Error List window is available only with SQL Server 2008 and contains a list of all errors detected in the open query window (see Figure (IN The Below)).

Error List

Toolbars

SQL Server Management Studio provides several different toolbars that expose features from various menus. Each toolbar can be displayed or hidden in the typical Windows toolbar method of selecting View ■=> Toolbars and choosing which toolbars to display. In addition, you can customize the toolbars to display only the buttons most often used by right-clicking the toolbar and choosing Customize.

Database Diagram Toolbar

The Database Diagram toolbar exposes a great deal of functionality for use on database diagrams. The toolbar is not used just for diagramming the database, but also for modifying or creating database objects from within the diagram interface. The toolbar is not displayed by default when working with database diagrams. It must be selected through the View ■=> Toolbars menu. In addition, a menu item labeled Database Diagram will appear on the menu bar when a database diagram is open. The menu has the same options as the toolbar. The Database Diagram toolbar features are described in the following table.

Database Diagram Toolbar

Database Diagram Toolbar

Help Toolbar

The Help toolbar provides a very easy and convenient mechanism for consulting online help articles while using Management Studio.

Query Designer Toolbar

The Query Designer toolbar is enabled when a table is opened with SQL Server 2005's Object Explorer or when the top 200 rows are opened for edit in SQL Server 2008.
To open a table in SQL Server 2005:

  1. Right-click the table you want to open in Object Explorer.
  2. Click Open Table.

To open the top 200 rows for edit in SQL Server 2008:

  1. Right-click the table you want to open in Object Explorer.
  2. Click Edit Top 200 Rows.

If the Query Designer toolbar was not visible, it will be when the table is opened. If it was visible, it will now be enabled. Although opening a table in a test and development environment is probably acceptable, opening a table in this manner in a production environment is not recommended. Opening a table with Object Explorer dumps the data from the table into a memory object called an update able scrollable cursor. What this means is that while the table data is exposed in the results window, any change to the displayed data is made to the underlying data in the table. This can be very dangerous. Displaying the entire contents of the table also can consume a great deal of server resources if the table is large. As a general rule, if the entire contents of a table need to be exposed the best way is to write a query with no filters, such as:
SELECT * FROM Person.Address
This exposes the same information as opening the table but does not populate an updateable cursor, so the results are read-only. If the data in that table needs to be updated, an update command (which is covered in Chapter Transactions) is more appropriate than modifying the data in an open table results window. Because the ability to open a large table is generally not a good idea, SQL Server 2008 replaced that ability with the new Edit Top 200 Rows feature.
The following table describes the Query Designer toolbar features.

Query Designer Toolbar

Source Control Toolbar

The Source Control toolbar is enabled when working with scripts and a Source Control plug-in has been configured, such as Visual Source Safe or Visual Studio Team System. The following table describes the toolbar options.

Source Control Toolbar

SQL Editor Toolbar

The SQL Editor toolbar becomes visible, or is enabled if already visible, when a new SQL query window is opened. The toolbar provides the most common features used by SQL programmers and DBAs. The supported features are described in the following table.

SQL Editor Toolbar

SQL Editor Toolbar

SQL Compact Edition Toolbar

The SQL Compact Edition toolbar becomes visible, or is enabled if already visible, when a new SQL Compact Edition query window is opened. The tools on the toolbar are a subset of the SQL Editor tools showing only those that are applicable for SQL Compact Edition queries.

SQL Server Analysis Services Editor Toolbar

The Analysis Services toolbar also becomes visible, or is enabled if already visible, when a new analysis query is opened or created. The tools on this toolbar are also a subset of the SQL Editor tools, but contain only those tools applicable to Analysis Services queries (DMX, MDX, XMLA).

Standard Toolbar

The Standard toolbar provides buttons to execute the most common actions, such as opening and saving files. It also provides buttons that will launch new queries and expose different tool windows.

Table Designer Toolbar

The Table Designer toolbar becomes visible, or is enabled if already visible, when either a new table is created using Table Designer or an existing table is modified using the Table Designer. The Table Designer is launched by right-clicking on the table node in the Object Explorer and choosing New Table from the context menu, or by right-clicking on an existing table in the table node of Object Explorer and choosing Design. The Table Designer toolbar has buttons that enable the creation or deletion of primary keys on a table, as well as launching dialogs for creating and managing indexes, constraints, and table relationships.

Text Editor Toolbar

As previously described, Management Studio supports a few different languages, with each language having its own specific toolbar. The Text Editor toolbar offers additional shortcuts to those provided in the other language-specific editors. The features are described in the following table.

Text Editor Toolbar

View Designer Toolbar

The View Designer toolbar is almost exactly like the Query Designer toolbar, with the exception of being limited to writing SELECT queries. In addition, queries written with the View Designer are saved as views and not just as query scripts.

SQL Server Management Studio Configuration

Management Studio's look and feel can be customized through the Tools ■=> Options menu. The Options dialog, shown in Figure (See In Figure Below), enables you to customize the Management Studio IDE. The configuration options are divided into the following seven areas.

SQL Server Management Studio Configuration

Environment

The Environment section is broken down into the following four sub-areas:

  • General — The General section provides startup options and environment layout, such as tabbed windows versus MDI (Multiple Document Interface) windows and how the windows behave.
  • Fonts and Colors — The fonts and colors used in the text editor are extraordinarily customizable in this area. The color and font used for reserved words, stored procedures, comments, and background colors are just a sampling of what can be changed.
  • Keyboard — For those database administrators who are used to Query Analyzer's keyboard shortcuts, this configuration area enables the setting of the keyboard shortcuts to the same ones used in Query Analyzer. The keyboard configuration area also allows for the addition of custom keyboard shortcuts.
  • Help — The Help area enables the integration of Help into a Management Studio window or the ability to launch Help externally. It also enables you to customize local and online help resources.

Text Editor

The Text Editor section, which enables you to customize various text editors, is divided into the following four sub-areas:

  1. File Extension — File extensions for all the possible script and configuration files can be configured in the File Extension area. Known file extensions such as .sql, .mdx, .dmx, and .xml are not listed but are automatically associated with their respective editors. They can be reassigned with a "with encoding” option so that Management Studio will prompt for specific language encoding every time an associated file type is opened. Custom file extensions can also be added.
  2. All Languages — The All Languages area is divided into two parts — General and Tabs — and provides configuration settings for IntelliSense features, word-wrap, line numbers, and indentation for all script languages. Keep in mind that IntelliSense options have no impact on SQL scripts in SQL Server 2005. IntelliSense for T-SQL scripts is new to SQL Server 2008.
  3. Plain Text — The Plain Text configuration settings are for plain text documents not associated with a particular scripting language.
  4. XML — This area provides configuration settings for XML documents. These settings consist of the same settings from the All Languages area as well as XML-specific settings such as automatic formatting and schema download settings.

Query Execution

The Query Execution section provides configuration options for how queries are executed as well as connection properties and timeout settings. The Query Execution section is divided into two sub-areas:

  • SQL Server — The SQL Server area has configuration options that control the maximum row count and the maximum amount of text or Unicode text that is returned to the Management Studio results window. This area also has options to specify a batch delimiter other than GO and to specify query execution time-out settings. Batch delimiters are discussed in Chapter (Introducing the T-Sql Language). There is also an advanced and ANSI area that provides for the configuration of specific connection- level options.
  • Analysis Services — This area provides configuration setting to control the execution timeout setting for Analysis Services queries.

Query Results

The Query Results section provides configuration options for how query results are formatted and is also divided into the same two sub-areas as the Query Execution settings.

  • SQL Server — The SQL Server section has configuration options to specify the default location for query results — to a grid, as text, or to a file — as well as the default location for results sent to a file.
  • Analysis Services — Configuration settings for Analysis Services query results include showing grids in separate tabs and playing the default windows beep when the query completes. Both settings are disabled by default.

SQL Server Object Explorer

SQL Server 2008 provides an additional section that enables you to configure the amount of records displayed by default when querying the audit log, as well as the amount of records returned in SQL Server 2008's new context menu choices of Edit Top <n> Rows and Select Top <n> Rows (see Figure (IN The Below)). The new choices are shown when right-clicking a table in Object Explorer, as shown in Figure (IN The Below). This section also provides the ability to configure scripting defaults. The scripting menu is also available in SQL Server 2005.

SQL Server Object Explorer

SQL Server Object Explorer

Designers

The Designers section provides configuration options for the graphical designers used in Management Studio. The Designers section is divided into three sub-areas:

  • Table and Database Designers — The Table and Database Designers area allows for the configuration of specific designer behavior.
  • Maintenance Plans — The Maintenance Plans options determine the way new shapes are added to the maintenance plan design area.
  • Analysis Services Designers — The Analysis Services Designers page provides options to set the connection timeout for the Analysis designers and the colors for the Data Mining Model viewer.

Source Control

The Source Control configuration section allows for the integration of a source control plug-in, such as Visual Source Safe or Visual Studio Team System.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

T-SQL Topics