Section 2.1. SQL Server Management Studio

2.1. SQL Server Management Studio

SQL Server Management Studio is an integrated environment for accessing, configuring, managing, and administering SQL Server and for developing SQL Server objects. Management Studio works with all SQL Server components, including Report Services, Data Transformation Services, SQL Server Mobile, and Notification Services.

SQL Server Management Studio combines the features of Enterprise Manager, Query Analyzer, and Analysis Manager in SQL Server 2000 and adds new functionality.

Launch SQL Server Management Studio by selecting Start All Programs Microsoft SQL Server 2005 SQL Server Management Studio from the taskbar. The Connect to Server dialog box opens, prompting you for server information and credentials. Fill in the required information and click the Connect button. Figure 2-1 shows SQL Server Management Studio.

SQL Server Management Studio displays two panes by default:

Object Explorer
Document (initially a single Summary Page)

These and other windows can be added using the View menu. The various windows are described in the following subsections.

Figure 2-1. SQL Server Management Studio

2.1.1. Registered Servers

The Registered Servers window lists servers that you have previously registered, optionally organized into server groupsa hierarchical structure used to help manage registered servers. A registered server preserves connection information and lets you easily determine whether the servers are running, access Object Explorer and Query Editor for the servers, and provide user-friendly names together with detailed descriptions for the servers.

The toolbar below the main menu lets you switch between the five types of registered servers that you can manage (listed in order from left to right):

Database Engine: Stores, processes, and secures data
Analysis Services: Online analytical processing (OLAP) and data-mining functionality
Reporting Services: Web-enabled reports that connect to a variety of data and content sources, publish reports in various formats, and manage security and subscriptions
SQL Server Mobile: Relational-database functionality in a compact footprint suitable for mobile devices with a programming model consistent with SQL Server 2005
Integration Services: Packages that extract, transform, and load (ETL) data for use by data integration solutions and data warehousing

You can also switch the type of registered server by using the View Registered Server Types menu item.

The Registered Servers context menu has the options described in Table 2-1.

Table 2-1. Registered Server context menu items
Menu Item	Description
Connect	Adds the selected server to the Object Explorer window.
Start	Starts the selected SQL Server instance.
Stop	Stops the selected SQL Server instance.
Pause	Pauses the selected SQL Server instance. A paused instance lets connected users complete tasks but does not allow new connections.
Resume	Resumes the paused SQL Server instance.
Restart	Restarts the selected SQL Server instance.
Start/Stop Service and Change Accounts	Lets you configure the SQL Server service.
SQL Server Configuration Manager	Opens SQL Server Configuration Manager (described later in this chapter).
New	Lets you create a new server group or register a server.
Edit	Lets you configure either a server group or a registered server.
Update Local Server Registration	Automatically registers all local server instances.
Move To	Moves a registered server to another server group.
Delete	Removes a registered server.
Import	Imports a previously exported server information file.
Export	Exports registered server information to a file for either a single server or a group of servers.
Previously Registered Servers	Imports servers registered by SQL Server 2000.

2.1.2. Object Explorer

Object Explorer connects to Database Engine, Analysis Services, Integration Services, Reporting Services, and SQL Server Mobile instances. It organizes all objects on the database instance into a tree hierarchy and lets you manage them. Object Explorer is visible by default. Select View Object Explorer if it is not visible.

The toolbar at the top of the Object Explorer window has five buttons (described from left to right):

Connect

Connects a server instance to Object Explorer. Click the Connect button and select a server type from the drop-down menu. The Connect to Server dialog box opens, prompting for server information and credentials. Fill in the required information and click the Connect button in the dialog box. Alternatively, you can right-click a server name in the Registered Servers window and select Connect Object Explorer from the context menu. You can also double-click a server instance in the Registered Servers window to connect it to Object Browser.

Disconnect

Disconnects a server instance from Object Explorer. Select the server instance to disconnect and click the Disconnect button.

Stop

Stops the current Object Explorer action.

Refresh

Refreshes a tree node. Alternatively, you can right-click the node and select Refresh from the context menu, or select View Refresh from the SQL Server Management Studio menu. Object Explorer items do not automatically refresh, to improve performance and to conserve system resources.

Filter

Returns a subset of items in a folder. When you click the Filter button, the Object Explorer Filter Settings dialog is displayed, as shown in Figure 2-2. The Filter button is enabled only when an object type (folder) node is selected in Object Explorerit is not enabled when a specific object, such as a server instance, database, or table, is selected.

Figure 2-2. Object Explorer Filter Settings dialog box

The dialog box in Figure 2-2 is displayed when you select the Tables node of the AdventureWorks database and click the Filter button. Setting the Schema to HumanResources in the filter criteria limits the tables displayed in the Tables node of the AdventureWorks database to those in the HumanResources schema, as shown in Figure 2-3.

Figure 2-3. Filtered table node

The word "filtered" is displayed in parentheses to indicate that the table list is filtered. Also, when the Tables node is filtered, the Filter button on the Object Explorer toolbar is depressed. To clear the filter, click the Filter button and then click the Clear Filter button in the Object Explorer Filter Settings dialog box. You can also right-click the filtered node and select Filter Remove Filter from the context menu.

Only one item can be selected at a time in the Object Explorer tree view. The Summary Page lets you select multiple items and perform actions on the selected group. Open the Summary Page by selecting View Summary from the main Management Studio menu or by clicking the Summary button on the Standard toolbar.

To hide system objects in Object Explorer, select Tools Options from the Management Studio menu, select Environment General from the tree in the left panel of the Options dialog box, and then check the Hide System objects in Object Explorer checkbox. You must restart SQL Server Management Studio for the changes to take effect.

The following subsections describe the types of nodes in Object Explorer and the activities that you can perform at each type of node.

2.1.2.1. Database server instance

The database server instance represents a SQL Server installation. The context menu for the database server instance lets you connect to and disconnect from a server, register a new server, create a query, manage the SQL Server service for the instance, refresh the tree hierarchy, and manage the properties for the server.

2.1.2.2. Databases node

The Databases node contains system databases, database snapshots, and user databases. Database snapshots are new in SQL Server 2005 and let you create a read-only static view of a database. Create a snapshot by using the CREATE DATABASE T-SQL statement with the AS SNAPSHOT clause.

Figure 2-4 shows the hierarchy of objects under the Databases node.

From the Databases node you can create a new database, attach a database by selecting its database files, or restore a database, file, or file group.

You can create a query from a named database node. Right-click on the database node and select New Query from the context menu to launch Query Editor . Query Editor opens a code pane in which you can enter T-SQL statements. After you execute the statement by selecting Query Execute from the main menu, by right-clicking in the Query Editor code pane and selecting Execute from the context menu, or by pressing F5, two additional tabs are displayed:

Results: Displays the result of a query
Messages: Displays information and error messages

Query Editor is shown in Figure 2-5.

Query Designer is a visual tool that lets you design SELECT, INSERT, UPDATE, and DELETE DML statements. After you open Query Editor, you can launch Query Designer either by selecting Query Design Query in Editor from the SQL Server Management Studio menu or by right-clicking the Query Editor code pane and selecting Design Query in Editor from context menu. The Add Table dialog box is displayed, letting you select tables to query; you can also add or remove tables while you are designing the query. Once you click the Close button, Query Designer shows three panesdiagram, criteria, and SQLas shown in Figure 2-6.

You can create the query by using the three panes at the same time. Right-click in the diagram area (top pane) to open a context menu that lets you change the query type, add tables, and change the grouping of results. Relationships are created automatically if they exist in the database. You can also drag one or more columns from one table to another to create relationships between tables. Select columns to display in the query by using the checkboxes next to the column names.

Once you have completed the query design, click the OK button to transfer the generated T-SQL to Query Editor, where you can execute the query. Select the T-SQL in Query Editor, open Query Designer, and the selected T-SQL will be parsed automatically into a diagram.

Figure 2-4. Object Explorer Databases node hierarchy

Figure 2-5. Query Editor

Figure 2-6. Query Designer

You can create database diagrams from the Database Diagrams node (refer to Figure 2-4). Select New Database Diagram from the context menu to launch Database Diagram Designer, a visual tool that lets you create, edit, and delete tables, columns, indexes, constraints, and relationships. You can automatically create one or more diagrams for an existing databaseSQL Server keeps diagrams synchronized with the database. Figure 2-7 shows part of a database diagram for the AdventureWorks database.

If you are creating a diagram for the first time in a database, you are prompted with "This database does not have one or more of the support objects required to use database diagramming. Do you wish to create them?" You must click Yes to create database diagrams.

Figure 2-7. Database Diagram Designer

You can also create a new table or edit an existing table by using Table Designer . Select New Table or Modify, respectively, from the Table context menu. Table Designer has two panes. The upper pane is a grid in which each row describes a column in the table. The lower pane shows additional column properties for the column selected in the upper grid. Figure 2-8 shows Table Designer for the Person.Contact table in the AdventureWorks database.

You can use Table Designer to modify indexes, constraints, and relationships by selecting the appropriate option from the context menu.

Figure 2-8. Table Designer

You can view and modify data in the table by selecting Open Table from the table's context menu. This brings up a data grid for the table. Figure 2-9 shows the data grid for the Person.Contact table in the AdventureWorks database.

Figure 2-9. Table data viewer and editor

Other nodes in the Databases hierarchy allow you to create, manage, and drop the objects within the node as well as perform other object-specific tasks.

2.1.2.3. Security node

The Security node lets you manage server logins, server roles, and credentials. Figure 2-10 shows the hierarchy of objects under the Security node.

Figure 2-10. Object Explorer Security node hierarchy

2.1.2.4. Server Objects node

The Server Objects node lets you manage backup devices, endpoints, linked servers, and server DDL triggers. Figure 2-11 shows the hierarchy of objects under the Server Objects node.

Figure 2-11. Object Explorer Server Objects node hierarchy

2.1.2.5. Replication node

The Replication node lets you manage local publications and subscriptions. Figure 2-12 shows the hierarchy of objects under the Replication node.

Figure 2-12. Object Explorer Replication node hierarchy

2.1.2.6. Management node

The Management node lets you manage maintenance plans, server logs, Database Mail, the Distributed Transaction Coordinator, Full-Text Search, and legacy objects. It also provides access to Activity Monitor, which provides information about processes and locks. Figure 2-13 shows the hierarchy of objects under the Management node.

2.1.2.7. Notification Services node

The Notification Services node lets you manage Notification Services instances.

2.1.2.8. SQL Server Agent node

The SQL Server Agent node lets you manage jobs, alerts, operators, and proxies and provides access to SQL Server Agent error logs and the SQL Server Agent Job Activity Monitor. Figure 2-14 shows the hierarchy of objects under the SQL Server Agent node.

Figure 2-13. Object Explorer Management node hierarchy

Figure 2-14. Object Explorer SQL Server Agent node hierarchy

2.1.3. Template Explorer

Script templates contain T-SQL statements for performing a variety of common tasks. Open Template Explorer by selecting View Template Explorer from the main menu or by clicking the Template Explorer button on the Standard toolbar. Figure 2-15 shows the Template Explorer window.

Figure 2-15. Template Explorer

Template Explorer uses a tree structure to organize templates into folders. The bottom of the window saves a list of recently used templates.

The toolbar at the top of the Template Explorer window has three buttons that let you select templates for Database Engine, Analysis Server, or SQL Server Mobile, respectively.

Templates are parameterized to help you customize the code. Parameter definitions follow the format <parameter_name,data_type,value>, with values as follows:

parameter_name: The name of the parameter
data_type: The data type of the parameter
value: The value used to replace every instance of the parameter in the script

The create database template (from the Database folder) follows:

     -- =============================================     -- Create database template     -- =============================================     USE master     GO     -- Drop the database if it already exists     IF  EXISTS (         SELECT name             FROM sys.databases             WHERE name = N'<Database_Name, sysname, Database_Name>'     )     DROP DATABASE <Database_Name, sysname, Database_Name>     GO     CREATE DATABASE <Database_Name, sysname, Database_Name>     GO

This script has a single parameter, <Database_Name, sysname, Database_Name>. To replace the parameter, select Query Specify Values for Template Parameters from the main menu, or click the corresponding button on the SQL Editor toolbar. This displays the Specify Values for Template Parameters dialog box, shown in Figure 2-16.

Figure 2-16. Specify Values for Template Parameters dialog box

Replace the value of the Database_Name parameter with the value ProgrammingSqlServer2005 and click the OK button. The script is updated for the parameter value as follows:

     -- =============================================     -- Create database template     -- =============================================     USE master     GO     -- Drop the database if it already exists     IF  EXISTS (         SELECT name             FROM sys.databases             WHERE name = N'ProgrammingSqlServer2005'     )     DROP DATABASE ProgrammingSqlServer2005     GO     CREATE DATABASE ProgrammingSqlServer2005     GO

Note that once you set a parameter value, it is permanently replaced and can no longer be accessed.

You can create custom templates in Template Explorer, as well. Follow these steps:

Select the node in which you want to create the template.
Right-click in the Template Explorer window and select New Template from the context menu.
Right-click the template and select Edit from the context menu (or double-click the template). Complete the required information in the Connect to Database Engine dialog box and click the Connect button.
Create the script. Insert parameters using the <parameter_name,data_type,value> syntax described earlier in this section. You can leave blank data_type and value (i.e., <myParameter, >).
Save the template by selecting File Save from the main menu or by clicking the Save button on the Standard toolbar.

2.1.4. Solution Explorer

The Solution Explorer window of SQL Server Management Studio lets you view and manage items associated with a script project. Open Solution Explorer by selecting View Solution Explorer from the main menu.

SQL Server Management Studio lets you create three types of projectsSQL Server Scripts, Analysis Services Scripts, and SQL Server Mobile Scripts. You can group multiple projects into a solution. A project contains itemsconnection information, queries, scripts (sets of T-SQL statements stored in a file), and miscellaneous files relevant to the project. Solution Explorer lets you open each item in an appropriate editor. The property window located by default in the window below the Solution Explorer window lets you view and manage item properties.