Using SQL Server Surface Area Configuration

The Sac utility is located in the Program Files\Microsoft SQL Server\90\Shared directory, and you customize the execution by using the different command line arguments that can be seen by using the argument ?.

Using SQL Server Management Studio

SQL Server Management Studio is a new graphical tool within SQL Server 2005 that combines and extends the features introduced by Enterprise Manager, Analysis Manager, and Query Analyzer in SQL Server 2000. The tool is built on top of a new integrated development environment (IDE), which is shared by Visual Studio 2005 Development Studio, SQL Server Management Studio, and SQL Server Business Intelligence Development Studio.

Administering Servers with SQL Server Management Studio

By using SQL Server Management Studios you can connect to any existing SQL Server system in your network for which you have the appropriate credentials (depending, of course, on the remote connections configured on the servers).

Connecting to a Server

1. Start SQL Server Management Studio from the Start menu by choosing All Programs Microsoft SQL Server 2005 SQL Server Management Studio. The application will request the name of the server to which you would like to connect. Select a server from the Server Name drop-down list and click the Connect button.

   

   Once you are working within SQL Server Management Studio, you can connect to another server by clicking the Connect toolbar button at the top of the left tree.

   

2. Select the type of server you want to connect to and enter the appropriate information.

   

   If you typically work with more than one server, it is useful to register them in SQL Server Management Studio.

Registering a Server in SQL Server Management Studio

1. From the View menu, choose Registered Servers, or press Ctrl+Alt+G.

   

2. Right-click Database Engine and select New Server Registration from the context menu.

   

3. Expand the Server Name drop-down list and select <Browse For More >.

   

4. Select local or network servers as needed.

5. Expand the Database Engine node and select the server you want to register. Click the OK button.

   

6. If you would like to connect to a named instance, add a backslash (\) followed by the name of the instance in the Server Name drop-down listbox.

   

7. Select the Connection Properties tab to define any special connection attribute that you need.

   

Using Object Explorer in SQL Server Management Studio

You can use Object Explorer to manage any of the SQL Server objects. Object Explorer presents them in a tree-view fashion, as shown in Figure 3-13.

Figure 3-13: Object Explorer pane in SQL Server Management Studio.

Using the Database Node

You will find all of your databases under the Database node. Databases are divided into three groups:

• System databases, which are those that SQL Server uses internally

• Database snapshots

• User databases, which are implemented as folders that appear directly under the Database node

If you expand one of the User Database nodes, you will obtain a set of Grouping nodes, as shown in Figure 3-14.

Figure 3-14: Grouping nodes within a User Database node in Object Explorer.

From within a Grouping node, you can access database objects. For example, if you expand the Tables node, you will see all of the tables in the database. You will notice one node that groups the system tables, which are those that SQL Server 2005 uses internally to manage database- related information, including its structure. If you right-click a table, you receive a context menu with several actions that you can perform on that table, as shown in Figure 3-15.

Figure 3-15: Table actions in Object Explorer.

When you select the Modify option from the context menu, you can alter the table structure; add, remove, or rename columns ; and perform many other definition tasks using a graphical interface. Moreover, you can perform all of the changes you wish and then generate the Transact-SQL (T-SQL) script to replicate it in other databases (see Figure 3-16). This feature is very important for replicating the changes made during the development process into the production server.

Figure 3-16: Table modification and script generation in Object Explorer.

Another important task that you can perform with tables is to open them.

Creating SELECT Queries

1. Right-click a table and select Open Table from the context menu to view the contents of the entire table.

2. Use the four toolbar buttons in the upper-left corner of the Object Explorer pane to choose which columns in the table to display (Show Diagram Pane toolbar button), filter data in the columns (Show Criteria Pane toolbar button), view the T-SQL sentence used to retrieve information from the table (Show SQL Pane toolbar button), and show or hide the actual information (Show Results Pane toolbar button).

3. You can right-click the Diagram pane to add more tables or views to your query or to define the grouping mode for the query.

   

4. Select the checkbox to the left of a column name in the Diagram pane to add that column to your query.

   Important

   By default, the asterisk (*) column (which means all columns) appears in the Criteria pane. Using the all columns indicator is not a good practice. Right-click the asterisk column in the Criteria pane and choose Delete from the context menu to delete it. You can then choose exactly the columns that you need.

   Once you have added a column, you can assign a filter to it by adding the condition in the Filter column.

5. Select the Sort Type and Sort Order values for a column to assign the order that you want for your data.

Querying More than One Table

It is possible to perform complex query operations by using the Open Table context menu.

Creating More Complex SELECT Queries

1. Open SQL Server Management Studio by clicking Start All Programs Microsoft SQL Server 2005 SQL Server Management Studio.

2. Connect to your local server.

   Note	The way that you specify your local server may differ depending on your installation. If you have a default instance, it can be simply (local) . SQL Server Management Studio typically offers you the default connection properties.

3. In Object Explorer, expand the Server node, the Database node, the AdventureWorks database, and the Tables node.

4. Locate the Production.Product table. Right-click the table and select Open Table from the context menu.

5. Click the Show Diagram Pane toolbar button, the Show Criteria Pane toolbar button, and the Show SQL Pane toolbar button.

6. Right-click the asterisk (*) in the Criteria pane and choose Delete from the context menu.

7. Right-click the blank surface of the Diagram pane and select Add Table from the context menu.

8. In the Add Table dialog box, scroll through the list of tables to find the ProductCategory (Production) table. Select the table and click the Add button.

   Repeat this step to add the ProductSubcategory (Production) table.

9. Click the Close button to close the Add Table dialog box.

   Tip

   Because the last two tables added to the diagram are both related to the Production table, between them you will see pipes demonstrating these relationships. You will learn more about relationships in Chapter 5, Designing a Database to Solve Business Needs, and Chapter 7, Selecting the Data You Need.

10. In the Diagram pane, check the Name column in both the ProductCategory table and the ProductSubcategory table. Check the Name, ProductNumber, ListPrice, and Size columns in the Product table.

11. In the Criteria pane, change the alias for the Name column of the ProductCategory table to Category Name, the alias for the Name column of the ProductSubcategory table to Subcategory Name, and the alias for the Name column of the Product table to Name.

12. In the Sort Type column, select Ascending for the Category Name, Subcategory Name, and Name columns in that order. Notice that the Sort Order column is updated automatically as you set the Sort Type column.

13. In the Filter column for the ListPrice row, type >10 to display only those rows that contain a price greater than 10.

14. Right-click the Diagram pane and select Execute SQL to see the results.

    

Managing Other Objects

Each object under the Database node has different options in the context menu. The options for stored procedures are shown in Figure 3-17.

Figure 3-17: Stored procedure actions in Object Explorer.

Using Database Diagrams

Another feature included with Object Explorer is the Database Diagrams node. In this node, you can add diagrams representing your tables and their relationships, modify or create table structures, and manage other table-related definitions, such as indexes and triggers. You can use database diagrams to better understand your database implementation and alter it if necessary.

Creating a Database Diagram

1. Right-click the Database Diagram node and choose New Database Diagram from the context menu.

   If this is the first time you have accessed this node, it is possible that SQL Server Management Studio will ask you to add the support objects for the diagrams, as shown in the figure below. Click the Yes button to add the necessary objects.

   

2. In the Add Table dialog box, select all of the tables you want to analyze. For this example, select only the Product (Production) table, click Add, and then click the Close button.

3. Right-click the Title bar, click the Table View context menu, and select another view, which will give you a different perspective. You can experiment here with various views.

4. Right-click the Title bar again and choose Add Related Tables from the context menu. This will add all of the tables related to the Product table, of which there are many.

5. The Arrange Tables toolbar button is located to the left of the Zoom drop-down list. Click it to arrange the tables in the diagram surface.

   You can zoom in or out of the view by selecting another percentage value in the Zoom drop-down list.

Writing Scripts in SQL Server Management Studio

As mentioned earlier, you can create SELECT queries by using the Open Table option in the Table context menu. However, you can create many other T-SQL sentences in SQL Server Management Studio. To facilitate this task, SQL Server Management Studio contains a Query Editor.

Starting a Query Using the New Query Toolbar Button

1. Open SQL Server Management Studio from the Start menu by choosing All Programs Microsoft SQL Server 2005 SQL Server Management Studio.

2. Connect to your local server.

3. Click the New Query button in the first toolbar at the left of the screen.

4. In the second toolbar that now appears, expand the Database drop-down list and select the database against which you want to execute the query. Begin to write your query in the New Query window to the right of the Object Explorer pane.

   

An alternative method of creating a new query is to create it from the Database context menu.

Starting a New Query Using the Database Context Menu

1. In Object Explorer, right-click your destination database and select New Query from the context menu.

2. When the Editor window appears, begin to write your query.

   Your query may contain as much T-SQL language as necessary. As you enter your query in the New Query window, your T-SQL syntax will be examined by SQL Server Management Studio and various syntax elements will be appropriately colored on the screen.

   Tip	After creating a query, you may want to use the same query script in the future. To ensure that the query is always executed in the proper database, start your query with USE <databaseName>; GO; to establish a connection to your database.

Retrieving Information about Your Queries

You can obtain information about your queries other than simply receiving the results, such as query statistics and the way in which SQL Server 2005 performs the query.

Obtaining Query Information

1. In the New Query window, write the following T-SQL code (included in the sample files as \Ch03\SQLQuery1.sql).

   USE AdventureWorks
   GO
   SELECT
       Production.ProductCategory.Name AS [Category Name],
       Production.ProductSubcategory.Name AS [SubCategory Name],
       Production.Product.Name,
       Production.Product.ListPrice,
       Production.Product.Size
   FROM
       Production.Product
       INNER JOIN
           Production.ProductSubcategory
       ON
       Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSub
   categoryID
           INNER JOIN
               Production.ProductCategory
           ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategor
   y.ProductCategoryID
   WHERE
       (Production.Product.ListPrice > 10)
   GO
   

2. Click the Execute button. You will receive the results in the lower pane.

3. Click the Display Estimated Execution Plan button, which is the third button to the right of the Execute button, as shown below.

   

   In the Display pane, you will receive a graphical representation of how SQL Server 2005 decides to execute the query. Each step will be described, and the proportional cost of each task will be listed.

   More Info

   Estimated execution plans are discussed fully in Chapter 5, Computing Aggregates, in Microsoft SQL Server 2005: Applied Techniques Step by Step .

Retrieving Statistics

By clicking the Include Client Statistics button shown in Figure 3-18 and re-executing the query, you will obtain another tab in the Results pane that contains statistical information about times and resources utilized.

Figure 3-18: Include Client Statistics button in Object Explorer.

Writing Solutions and Projects in SQL Server Management Studio

You may need to create a group of queries, modifications, and other actions for one or more databases. SQL Server Management Studio allows you to create projects to manage all scripts, connections, and other files that you may need.

Creating a Project in SQL Server Management Studio

1. Open SQL Server Management Studio from the Start menu by choosing All Programs Microsoft SQL Server 2005 SQL Server Management Studio.

2. When SQL Server Management Studio prompts you for a connection, click Cancel.

3. From the File menu, choose New Project. A dialog box will appear like that shown in the following figure, where you can create several types of projects.

   

4. Select the SQL Server Scripts template and type a name for the solution in the Name textbox.

5. From the View menu, choose Solution Explorer, or press Ctrl+Alt+L to display Solution Explorer.

6. In the Server Explorer pane, right-click the Connections folder and choose New Connection from the context menu.

7. Accept the default values for your local server, or choose another server if desired.

8. In the Server Explorer pane, right-click the Queries folder and select New Query.

9. Click the Connect button in the Connect To Database Engine dialog box.

10. Write your desired query and close the window. When prompted, click Yes to save the query.

11. In the Server Explorer pane, right-click  SQLQuery1.sql and select Rename to type a clearer name for the query.

12. Repeat Steps 811 to add additional queries.

13. Close SQL Server Management Studio. When you do so, you will be asked to save the project. You will be prompted to save the project file (with the . ssmssqlproj extension) and the solution file (with the . ssmssln extension).

Using SQL Server Management Studio Templates

Another available feature in SQL Server Management Studio is the Template Explorer. By using templates, you can accelerate many standard tasks within your databases.

Performing Actions with Templates

1. From the View menu, choose Template Explorer, or press Ctrl+Alt+T.

2. Expand the folder of the particular template that you want to use. For example, expand the Database folder.

3. Right-click the desired template (e.g., Create Database), and choose Open from the context menu. Click the Connect button in the Connect To Database Engine dialog box.

4. Click the Specify Values For Template Parameters button or, from the Query menu, choose Specify Values For Template Parameters. Alternatively, you can press Ctrl+Shift+M.

   

5. In the Specify Values For Template Parameters dialog box, complete the values to fill in the template and click OK.

   

   You will receive something similar to the following script.

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

Using SQL Server Management Studio Express

SQL Server Management Studio Express is a reduced version of SQL Server Management Studio that can be obtained as a free download with the SQL Server 2005 Express Edition, Service Pack 1. It allows you to manage the Express version of SQL Server 2005 just as you would using SQL Server Management Studio, but it has disabled all of the actions and operations that the SQL Server Express Edition does not support. If you are already familiar with SQL Server Management Studio, then you are already acquainted with SQL Server Management Studio Express as well.