In Part 1 of this series, the new SQL Server 2005 Management Studio was introduced. The new studio combines many of the features found in SQL 2000 Enterprise Manager and Query Analyzer, in addition to many new additional features. Some of the topics covered in the previous article included using the Studio for Registering Servers, exploring Objects, and creating Queries, designing Maintenance Plans, developing Solutions, and monitoring Activity. This article will begin by graphically managing Indexes and Foreign Key relationships. The following statements will create a database with two tables; one will require a Unique Index, the other a Foreign Key, which we will add manually with through the Studio.
CREATE DATABASE Test;
CREATE TABLE Sales
CREATE TABLE Parts
Foreign Keys are a type of constraint used to insure that values for a column exist in some other table. This insures that the constrained column will only contain valid values. In the example tables above, the Sales column PartID should only contain items found in the Parts table. The first step is to add a Unique Index to the Parts table because Foreign Keys point to unique indexes. To create the index from the Management Studio, expand the newly created Test database, then expand tables, next right click the Parts table and choose Modify as shown below.
By default, the first column, PartID in this case, will have focus. Click the tab above it labeled “Table-dbo.Parts” to set the focus to the entire table rather than just one column. Above the tab, on the menu bar, should be an icon of a gold key as shown below. If the icon isn’t visible, click an empty space anywhere on the menu bar and then check “Table Designer”.
Click it to set the Primary Key to the PartID column. Click the Save icon. Next, we’ll add the Foreign Key to the Sales table. Right click the Sales table and select modify to bring it into the work area. Next select the Relationships icon (three linked tables); this will bring up the Foreign Key Relationships window. Click the Add button at the bottom. Next, click the “Tables and Columns Specification” to give it focus; now click the button displaying three dots as shown below.
This will bring up the “Tables and Columns” window. Select Parts as the Primary Key table. Sales will automatically be filled in as the Foreign Key table. Next, select PartID directly under each table as show below.
Click OK then Close. The Foreign Key has been created.
Stored Procedures can also be created from the Management Studio. For these examples, we will use the Microsoft test database AdventureWorks rather than insert example data into the Test database. To begin, in the left pane labeled Object Explorer, expand the AdventureWorks database folder, then expand the Programmability folder, next right click “Stored Procedure,” then select “New Stored Procedure.” If AdventureWorks is not installed on the SQL Server, it can be downloaded from Microsoft at http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en . Replace the default text with the statements shown below:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE testProc
WHERE City = @City
This will create a stored procedure that will accept a city as an input parameter. The procedure can be tested by executing it in a new query window. Click the “New Query” button located in the left top corner of the menu bar. In the query window, run the following statement to test the stored procedure:
EXEC testProc “Dallas”
Seven rows should be returned. If the procedure had a problem and wouldn’t execute, we could debug it for additional help. Debugging of Stored Procedures is not done in the Management Studio; instead, the new Business Intelligence Development Studio is used.
The Business Intelligence Development Studio (BIDS) is new for SQL 2005. BIDS is a
Visual Studio tool for developing database import routines, Reporting Server projects, Analysis projects, and Database projects. We’ll use this last one for debugging Stored Procedures. To start BIDS, select it from the Windows menu by clicking Start, All Programs, Microsoft SQL Server 2005, and then Business Intelligence Development Studio. Once BIDS is open, click File, New, and then Project. In the New Project window, select “Other Project Types,” then Database.
Specify a project name and location, and then click OK. The “Add Database Reference” window will appear next. Click the “Add New Reference” button. This is where we’ll point our new project to the AdventureWorks database. Enter your server name, then towards the bottom, in the “Connect to database” area, click “Select or enter a database name,” then pick AdventureWorks from the drop down menu. Test the connection, and then click OK. The “Add Database Reference” window will appear with the newly created connection. Make sure its highlighted then click OK.
The Server Explorer window should now be visible inside the new project. If not, click View from the top menu, then “Server Explorer.” Expand the AdventureWorks folder, and then the Stored Procures folder; our new “testProc” should be visible as shown below.
To debug the procedure, right click the procedure name then choose “Step Into Stored Procedure”. Because our procedure had an Input parameter, a prompt will appear asking its value; enter “Dallas.” Now the usual debug tools such as Breakpoints and Watch windows will function.
There are many new features in the SQL 2005 Management Studio. The Studio combines many of the features that were found in the SQL 2000 Query Analyzer and Enterprise Manager. The Business Intelligence Development Studio, also new in SQL 2005, can be used for debugging stored procedures. An area that wasn’t discussed in this article, but is worth investigating, is the “Database Diagrams” functionality found in the Management Studio. These diagrams can be used to graphically manage databases and their relationships.