The Server Explorer window contains two nodes—Data Connections and Servers (see Figure 7.1). If you expand the Data Connections folder, you’ll see a list of existing database connections. If you expand the Servers folder, you’ll see a list of servers on your network.

Figure 7.1. Working with the Server Explorer window.

You can use either the Data Connections or Servers node to browse the contents of a database. You can expand a database to view its tables, views, stored procedures, and functions. If you expand a table, you can view all of the table’s columns. If you expand a view, stored procedure, or function, you can view all of its parameters (see Figure 7.2).

Figure 7.2. Browsing columns and parameters in Server Explorer.

You can control which database objects appear in Server Explorer from the Options dialog box. You can hide or display system objects and you can hide or display objects owned by other users by doing the following:

Database system objects contain the tables that hold information that SQL Server needs to function correctly. Typically, it is not a good idea to display database system objects. Modifying a system object can corrupt your database.

If you right-click the name of a database table in Server Explorer, you have the option to Retrieve Data from Table. Selecting this option retrieves all the rows from the table and displays the rows in a grid (see Figure 7.3). You can also invoke this command simply by double-clicking the name of the table.

Figure 7.3. Retrieving database records.

After you retrieve the contents of a database table, you can edit the data. Simply select a cell in the grid and modify the cell’s value. As soon as you leave the cell, the data is automatically updated in the underlying database.

You also can use the grid to add a new row to a database table. Enter the new row at the bottom of the grid (it’s marked with an asterisk). After you leave the new row, the row is automatically added to the underlying database table.

Finally, you can delete a row of data by selecting the row and selecting Delete from the Edit menu.

When you start building a new database-driven Web application, you might need to create a new database. You can use Server Explorer to add a new database to an existing Microsoft SQL Server database server.

You can add a new database in two places within Server Explorer. If you right-click the Data Connections node and select Create New SQL Server Database, you can add both a new database to your database server and a new connection to the database to the Server Explorer window. Alternatively, you can expand the Servers node, and then the SQL Servers folder, right-click a database server, and select New Database. Either method of creating a new database opens the Create Database dialog box (see Figure 7.4).

Figure 7.4. The Create Database dialog box.

You can use the Server Explorer window to create new database tables. To add a new database table, expand a database in the Server Explorer window, right-click the Tables node and select New Table. Selecting New Table will open the Table Designer (see Figure 7.5).

Figure 7.5. Opening the Table Designer.

You can use the Table Designer to create a new database table by specifying the properties for the table’s columns. In the top part of the Table Designer window, you can list the name, data type, length, and nullability of each column.

The bottom part of the Table Designer window enables you to specify additional properties for a column, such as whether the column is an identity column.

When you are finished designing a database table, select Save Table Name from the File menu. The Choose Name dialog box appears enabling you to enter a name for the new table.

After you create a table, you can modify the design of the table any time in the future by right-clicking the name of the table in the Server Explorer window and selecting Design Table. This opens the same Table Designer that you used to initially create the table.

A database view enables you to create a virtual table from other tables and views. You can use views to simplify your database queries. For example, if you discover that you commonly need to combine the results of two tables when performing queries, you can represent the contents of the two tables in a single view, or you can use a view to represent a subset of data in a database table.

You create a view by supplying a single SQL Select statement. For example, to create a view that represents all the seafood products from the Products table, you would execute the following Create View statement:

Create View SeafoodProducts
As
Select * From Products
Where CategoryID = 8

After you create a view, you can treat it just like a table. For example, you can retrieve all the rows from the view as follows:

Select * from SeafoodProducts

To create a new view with the Server Explorer window, right-click the Views node and select New View. When you create a new view, the Query and View Designer opens (see Figure 7.6).

Figure 7.6. The Query and View Designer.

When you first open the Query and View Designer, the Add Table dialog box appears. You can use this dialog box to indicate the tables, views, and functions that you need to reference in your view. When you are finished with the Add Table dialog box, click Close.

The Query and View Designer contains four panes:

The Diagram, Grid, and SQL panes provide you with three alternatives for designing a query. Changes that you make to a query in one pane are automatically reflected in the other two panes.

The Results pane enables you to test your query while you are building it. To execute the query, select Run from the Query menu (you can also click the red exclamation mark on the Toolbar).

When you are finished designing a view, select Save View Name from the File menu. When you save the view, you are provided with the opportunity to name the view.

After you create a view, you can modify it at any time in the future by right-clicking the view and selecting Design View. You can also test the view by selecting the menu option Retrieve Data from View.

You can use a stored procedure to package one or more SQL commands into a single statement. A stored procedure is similar to a subroutine in that it enables you to reuse a set of statements.

You can create a new stored procedure in Server Explorer by right-clicking the Stored Procedures node and selecting New Stored Procedure. Selecting this menu option opens a page that contains the following Create Procedure script:

CREATE PROCEDURE dbo.StoredProcedure1
/*
    (
        @parameter1 datatype = default value,
        @parameter2 datatype OUTPUT
    )
*/
AS
    /* SET NOCOUNT ON */
    RETURN

You can use this script as the skeleton for your stored procedure. For example, to create a stored procedure that adds a new entry into the Products table, you would enter the following code:

Create Procedure AddProduct
(
  @ProductName Varchar( 40 ),
  @UnitPrice Money,
  @Discontinued BIT
)
As
Insert Products
(
  ProductName,
  UnitPrice,
  Discontinued
) Values (
  @ProductName,
  @UnitPrice,
  @Discontinued
)

After you save a stored procedure, the stored procedure appears under the Stored Procedures node in the Server Explorer window. Furthermore, the code for the stored procedure is automatically updated in the editor. An Alter Procedure statement appears instead of a Create Procedure statement.

While building a stored procedure in the editor, you can take advantage of the Query and View Designer. For example, to add a new query to the body of a stored procedure, right-click the editor surface and select Insert SQL. This option will open the Query and View Designer.

When you are finished designing a query with the Query and View Designer, save the query and close the window. The query will appear in the stored procedure editor with a blue box around it (see Figure 7.7). If you want to modify the query, you can right-click within the blue box and select Design SQL Block.

Figure 7.7. Designing a stored procedure.

After you create a stored procedure, you can execute it by right-clicking the stored procedure in the Server Explorer window and selecting Run Stored Procedure. If the stored procedure requires input parameters, a dialog box appears that enables you to enter values for the parameters.

A trigger is a stored procedure that executes whenever the contents of a database table are changed. You can create triggers that execute whenever a row is inserted, updated, or deleted.

For example, suppose that you want to create an audit table that keeps track of every record that has been deleted from the Products table. Whenever a record is deleted from the Products table, a copy of the record is added to the ProductsAudit table. To copy deleted rows from one table to the other, you would create a trigger on the Products table that looks like the following:

CREATE TRIGGER Products_Audit
ON dbo.Products
FOR DELETE
AS
Insert ProductsAudit ( ProductName, UnitPrice, Discontinued )
Select ProductName, UnitPrice, Discontinued
From Deleted

This code declares a Delete trigger. The Insert statement executes whenever a row is deleted from the Products table. Notice that the Insert statement refers to a table named Deleted. In the context of a trigger, the Deleted table contains any rows deleted from the table associated with the trigger. Within a trigger, you can also use the inserted table to retrieve the new values added to the table.

You create triggers in the Server Explorer window by right-clicking the name of a table and selecting New Trigger. After you select this option, the following code appears in the editor window:

CREATE TRIGGER Products_Trigger1
ON dbo.Products
FOR /* INSERT, UPDATE, DELETE */
AS
    /* IF UPDATE (column_name) ...*/

This code contains the schema for creating a trigger.

When you save a trigger, the trigger appears under the Tables node of a database (triggers appear with a lightning bolt icon). You can modify a saved trigger by right-clicking the name of the trigger and selecting Edit Trigger.

While creating or editing a trigger, you can use the Query and View Designer. Right-click the editor surface and select Insert SQL. SQL statements appear within the editor surrounded by a blue box. You can right-click within the blue box, select Design SQL Block, and modify an SQL statement with the Query and View Designer.

User-defined functions, new with SQL Server 2000, enable you to create procedures that return either a single value or a table. For example, you can define a function that performs a standard tax calculation and use the function in your Select statements, or you can create functions that build a new table from multiple tables.

There are three types of user-defined functions:

Scalar-valued functions are useful when you need to perform a calculation. For example, the following Create Function statement creates a function that multiplies a parameter by the fixed value .03:

CREATE FUNCTION dbo.CalculateTax
(
  @UnitPrice Money
)
RETURNS Money
AS
BEGIN
  RETURN @UnitPrice * .03
END

After you create this function, you can use it within your queries. For example, the following query returns the UnitPrice and tax amount for each product in the Products table:

Select UnitPrice, dbo.CalculateTax( UnitPrice ) As Tax
From Products

Because functions can return tables, you can create functions as an alternative to creating database views. A database view can contain only a single SQL Select statement, and a view cannot contain any parameters. On the other hand, a function can contain multiple statements and it can accept multiple parameters.

For example, imagine that you need to work with different subsets of the rows contained in the Products table. Sometimes you want to display all the seafood products and other times you want to display all the produce products. In this case, you’ll need to create a function instead of a view because you need to return subsets of rows based on a parameter.

You can create the necessary function with the following Create Function statement:

Create FUNCTION dbo.SubProducts
    (
    @CategoryName NVarchar( 15 )
    )
RETURNS TABLE
AS
    RETURN
    Select Products.*
    From Products, Categories
    Where Products.CategoryID = Categories.CategoryID
    And Categories.CategoryName = @CategoryName

This statement declares an inline function. This function is an inline function because the function returns a table based on a single Select statement.

After you create this function, you can retrieve all the seafood products by executing a Select statement like the following:

Select * From SubProducts( 'seafood' )

Because the function is an inline function, you can also use it in statements other than a Select statement. For example, if you want to double the price of all the produce products, you can execute the following statement:

Update SubProducts( 'produce' )
Set UnitPrice = UnitPrice * 2

Finally, a table-valued function, like an inline function, returns a table. However, a table-valued function contains multiple Select statements.

For example, suppose that product information is stored in two database tables named Products1 and Products2 in your database. You can treat these tables as a single table by creating the following table-valued function:

CREATE FUNCTION dbo.CombineProducts
()
RETURNS
@CombineProductsTable TABLE
(
  ProductName Varchar( 40 ),
  UnitPrice Money
)
AS
BEGIN
INSERT INTO @CombineProductsTable
Select ProductName, UnitPrice from Products1

INSERT INTO @CombineProductsTable
Select ProductName, UnitPrice from Products2
RETURN
END

After you create this function, you can retrieve a list of products from both the Products1 and Products2 tables by executing the following Select statement:

Select * from CombineProducts()

Because this function retrieves data from multiple tables, you cannot use this function in an Update or Insert statement. Table-valued functions cannot be updated.

You can create new user-defined functions within the Server Explorer window by right-clicking the Functions node and selecting New Scalar-valued Function, New Inline Function, or New Table-valued Function. Each option will automatically create the skeleton for the necessary function.

When creating or editing a function, you can use the Query and View Designer to visually create SQL statements. To launch the Designer, right-click the editor surface and select Insert SQL. SQL statements appear in the editor window surrounded by a blue box. You can edit any SQL statement by right-clicking within the blue box and selecting Design SQL Block.

After you build a function, you can test it by right-clicking the name of the function in the Server Explorer window and selecting Run Scalar-valued Function, Run Inline Function, or Run Table-valued Function. If a function has parameters, you’ll be prompted in a dialog box to enter values for the parameters.

You can return to a function and edit it at any time by right-clicking the name of the function in Server Explorer and selecting Edit Scalar-valued Function, Edit Inline Function, or Edit Table-valued Function.

You create new database diagrams within the Server Explorer window. You can create database diagrams under either the Data Connections or the Servers nodes.

In either case, expand a database, right-click the Database Diagrams node, and select New Diagram.

When you create a new database diagram, you are prompted with the Add Table dialog box to add one or more existing tables to the diagram. For example, Figure 7.8 illustrates a database diagram that contains the Categories and Products database tables.

Figure 7.8. Creating a new database diagram.

After you open a database diagram, you can add additional existing tables to the diagram by selecting Add Table from the Diagram menu.

As already mentioned, you can use database diagrams not only when working with existing tables but also to create new tables. To create a new table in a database diagram, select New Table from the Diagram menu. When you add a new table, you’ll be prompted for the name of the new table. Next, the table will appear in the diagram.

You can add columns to the new table by supplying the column name, data type, length, and specifying whether the column allows Null values. You can enter these column properties directly onto the Designer surface.

If you need to set more advanced column properties, such as a column’s default value or whether a column is an identity column, you must open the property pages for the table. You can open a table’s property pages by selecting Property Pages from the View menu or by right-clicking the table and selecting Property Pages.

The Property Pages dialog box contains five tabs (see Figure 7.9):

Figure 7.9. Opening a table’s property pages.

Finally, you can specify the primary key for a table by selecting a column and choosing Set Primary Key from the Diagram menu. When you add a primary key, a key symbol appears next to the column in the database diagram.

Until you save your changes to the table, the name of the table will appear followed by an asterisk character. You can save your changes by saving the current selection or saving the entire database diagram. Both options are available from the File menu.

You can modify how tables are displayed in a database diagram by selecting Table View from the Diagram menu. You can select from the following options:

For example, Figure 7.10 illustrates how the Categories table appears with each of the first four options.

Figure 7.10. Different table views of the Categories table.

Notice that the last table view option enables you to specify a custom view. You indicate the column properties that you want displayed in a custom view by opening the Diagram menu, pointing to Table View, and selecting Modify Custom.

You should also notice that there are a couple of other options for controlling how tables are displayed under the Diagram menu. If you select one or more tables and select Autosize Selected Tables from the Diagram menu, the selected tables are automatically resized to fit their content. If you select Arrange Tables from the Diagram menu, the tables are rearranged in an orderly manner on the diagram surface.

You can visually specify relationships between the columns of tables within a database diagram. You can create one-to-many relationships, one-to-one relationships, and many-to-many relationships.

For example, to specify a one-to-many relationship between the Categories and Products table in the Northwind database (see Figure 7.11), do the following:

Figure 7.11. Creating a one-to-many relationship.

By default, the names of the relationships are not displayed in the database diagram. If you want the names of the relationships to appear, you should select Show Relationship Labels from the Diagram menu.

You can add text annotations to a database diagram. Text annotations can be used to add any text you want to a diagram. For example, you can use text annotations to document the purpose of a group of tables.

To add a text annotation, open the Diagram menu, point to New, and select Text Annotation. This menu option adds a label to the diagram that you can resize and drag across the diagram surface (see Figure 7.12). You can specify the font to use with the annotation by selecting Diagram, Set Text Annotation Font.

Figure 7.12. Adding a text annotation.

Because one purpose of a database diagram is for documenting the structure of a database, being able to control how a diagram is printed is important. You don’t want page breaks appearing in the wrong places.

There are two menu options that you can use to handle page breaks in a database diagram. The Recalculate Page Breaks option on the View menu enables you to recalculate page breaks after you have rearranged tables in a diagram. The View Page Breaks option, also on the View menu, enables you to see page breaks on the designer surface. This option also displays line numbers and the right margin of the page.

Typically, you add a Database Project to an existing Solution. For example, if you are developing a database-driven Web site, you would create a solution that contains both an ASP.NET Web Application Project and a Database Project.

To add a Database Project to an existing solution, perform the following steps:

After you complete these steps, the new Database Project will appear in the Solution Explorer window. When the Database Project is first created, it contains three folders—Change Scripts, Queries, and Database References.

Before you can execute the SQL scripts that you create in a database project, you first need to create a database reference. A database reference contains connection information to a database.

A database reference is similar to the database connections that appear in the Server Explorer window. However, unlike a database connection, a database reference is stored in a particular project and is saved with the solution.

To create a new database reference, do the following:

After you complete these steps, the new database reference appears under the Database References folder in Solution Explorer. You can add as many database references to this folder as you want.

You can set the default database reference for an entire Database Project by right-clicking a database reference and selecting Set as Project Default. The icon for the default database reference will appear in red.

You can also set the default database reference for particular folders in a Database Project. When you specify a default database reference for a folder, all SQL scripts and queries contained in the folder will execute against the database reference by default. To set a default database reference for a particular folder, right-click the folder and select Set Default Reference.

You create new SQL scripts in a Database Project by right-clicking a folder and selecting Add SQL Script. You can add any of the following types of scripts:

Program FilesMicrosoft Visual Studio .NETCommon7ToolsTemplatesDatabase Project Items

After you create a script, you can execute it in one of three ways. You can right-click the name of the script in the Solution Explorer window and select Run. Selecting Run will execute the script using the default database reference.

If you want to execute the script against a database other than the one indicated by the default database reference, you can right-click the script and select Run On. The Run On command will open a dialog box that enables you to select a particular database reference or database connection.

Finally, you can simply drag and drop a script onto a particular database reference in the Solution Explorer window. When you drop a script onto a reference, the script will execute against the database represented by the reference.

If you prefer to use the Query and View Designer to build your database commands (see Figure 7.14), you can add a query to a Database Project. To add a query to a Database Project, right-click a folder and select Add Query.

Figure 7.14. Using the Query and View Designer.

When you create a query, the default database reference is used to retrieve a list of tables that you can use while building your query. Consequently, you should check that the default database reference is set appropriately for a folder before adding a new query to the folder.

When you are ready to test a query, you can execute the query directly from the Query and View Designer. Select Run from the Query menu. Alternatively, you can click the Run Query button (the red exclamation mark) on the Toolbar.