3

Tools for Accessing SQL Server

It's said that a craftsman's work is only as good as his tools. To some degree, I agree that this principle applies to SQL Server. However, many database professionals from the old school choose not to use sophisticated tools, just as many craftsmen use tools (chisels, carving knives, and so on) to do the work that is often simplified through automation. Many would even argue that the results are different, perhaps even better, when you remove automation from the equation. Regardless of the ideals to which you subscribe, a number of tools and applications are available that you can use to create and debug queries. What tools do you need? This depends a great deal on what you need to do.

Here's a breakdown of some of the common tasks you may need to perform with SQL Server:

  • Administrative Tasks
    • Creating databases
    • Creating and managing server logins and database roles and users
    • Granting and managing security permissions
    • Scheduling backups
    • Auditing and error checking
    • Diagnosing failures and application errors
    • Performance tuning
    • Configuring data replication
    • Managing disk space and data files
  • Database Management Tasks
    • Adding and managing tables, views, stored procedures, and functions
    • Creating indexes
    • Creating views, stored procedures, and functions
    • Importing, exporting, or transforming data
  • Data Operations
    • Inserting, updating, and deleting records
    • Supporting application features
    • Defining business rules
    • Selecting records from a table or multi-table join

Whether you are using SQL Server 2000 or SQL Server 2005, this chapter walks you though similar exercises for each version of the product. I'm assuming that you have SQL Server installed on your local computer with all of the server and client tools. This is the default setting when you run the setup. If your database server is on another computer, you will need to install the client tools on your local computer to follow these directions. I am also assuming that you are using Integrated Windows authentication and that your Windows account has sufficient permissions to create objects and run queries against the database server. If you have installed SQL Server on your local computer with default options, this should be the case.

If you are working with a remote database server, you should talk to your system administrator and make sure you have the client tools correctly installed and that you have the appropriate permissions to run queries. As you work through these exercises, the only difference will be that you will be connecting to a remote server rather than the local server.

Tools for SQL Server 2000

If you have installed the client tools for SQL Server on your computer, the Microsoft SQL Server menu will appear on your Start menu with some or all of the shortcuts shown in Figure 3-1.

images

Figure 3-1

The following table provides a brief overview of these tools, and then you'll take a look at those related to Transact-SQL in detail.

Menu Option Description
Books Online SQL Server Books Online (BOL) is the online help system for all SQL Server tools and features. Books Online opens in a separate window with options to search keywords and browse the index of topics. To launch Books Online from within a SQL Server tool, press F1. Context-sensitive help is available from within Query Analyzer when you highlight a keyword and then press Shift+F1.
Configure SQL XML Support in IIS This option will only be available if you have Internet Information Services installed. It allows a SQL Server database to be configured as a web folder, accessible through HTTP requests. Data may be queried using a URL and through a variety of XML-based techniques. Data is typically returned as XML to be used within a web page or an XML transform script.
Enterprise Manager Database server administrators and database developers use Enterprise Manager to perform a variety of useful tasks. This is the central management interface for most database management activity.
Import and Export Data This shortcut launches the Data Import/Export Wizard. This is actually a simplified interface for creating and running Data Transformation Services (DTS) packages and tasks. It can be used to copy and move practically any database objects and data from and to most any standard data source (including text files, dBase, FoxPro, Excel, Access, Paradox, SQL Server, and other ODBC-compliant sources).
Profiler The SQL Server Profiler is an extensive troubleshooting and optimization tool. It can be used to monitor a broad range of database activity, or to pinpoint specific events. Operations can be captured and recorded for later playback. Events and activities can be recorded as scripts or logs to text files or to a database.
Query Analyzer This ad-hoc query utility is the tool of choice for most SQL-savvy database users. It gives database designers, developers, and administrators an unconstrained free-form environment to test and run SQL script in a multi-window interface, connected to multiple database servers. SQL scripts can be generated for nearly all database objects from the object browser. Commands can be saved to script files and can be used to build database objects in different databases and on different servers.
Server Network Utility The client and server network utilities are used to install and configure database network libraries, which provide low-level, network protocol-specific connectivity to database servers.
Service Manager This simple utility provides a convenient tool for managing the Windows services, which comprise the features of SQL Server. It is also accessible from the Windows System Tray, in the lower-right corner of the desktop.
Reporting Services SQL Server Reporting Services is an add-on, server-based, enterprise reporting product from Microsoft that integrates with SQL Server. It is freely available to licensed owners of SQL Server to be used on the same server and requires a separate installation. This shortcut leads to another menu with Reporting Services features.

Enterprise Manager

This is the administrative console for SQL Server 2000. It's actually a snap-in for the Microsoft Management Console (MMC) so it may look familiar if you have worked with other Windows administrative tools. Later, you'll see how to customize the MMC so you can have all of your tools in one place.

To open Enterprise Manager, select the shortcut from the start-up menu by clicking the Start button and then select All ProgramsimagesMicrosoft SQL ServerimagesEnterprise Manager (see Figure 3-2).

images

Figure 3-2

Assuming that you have the appropriate level of security access to get to your databases, it's just a simple matter to drill down to the database on your registered server. Here's a quick tour to get you started. On the left side of the window, you will see a tree view pane that lets you navigate through various nodes and folders that represent feature areas and server and database objects. You can resize this pane as needed. The right pane shows items related to the selection on the left. This view can be modified to show more or less detail by using options on the View menu. Figure 3-3 shows the initial view after opening Enterprise Manager. Note that on my system, I have groups and registrations for several servers and databases. You will only see those that have been set up on your system. The demonstrations here will be using only the local database server. If you have installed the client tools on your local computer and SQL Server is on a different computer, you will need to create a registration for the remote server. You can easily do this by right-clicking the node labeled SQL Server Group and selecting New SQL Server Registration.

One anomaly of this tool is that it opens with one window inside the other. For simplicity, I recommend that you maximize the inner snap-in window so it fills the console window. To do this, click the rectangular-shaped icon in the upper-right corner of the smaller window.

images

Figure 3-3

In the left pane, click the tree node labeled Microsoft SQL Servers. This will display groups used to organize remote connections to any SQL Server database servers available to you. The default group is called SQL Server Group. If you click the little plus sign icon next to any item in the tree, it will expand to show related items under that node. Expand the SQL Server Group to show any registered servers. Your local server should be registered by default. You can also add registrations for additional servers by right-clicking the group and choosing New Server Registration. The local server will either be labeled (local) or with the name of the SQL Server 2000 instance specified during installation. Expand this node to reveal databases and administrative items for this server. Expand the Databases node and select the AdventureWorks2000 sample database.

Enterprise Manager is not a query-editing tool, but it contains some features that use or generate Transact-SQL script. You can use Transact-SQL in a few different ways in the Enterprise Manager. You can enter the Transact-SQL Query Designer by choosing to create a new view or to return records from a table. For writing complex queries containing multi-table joins and groupings, this is a very useful technique even if you don't plan to save the script as a view.

You can also create stored procedures and user-defined functions from Enterprise Manager and just type the SQL directly into the related editor window.

You learn how to create these database objects in Chapter 10.

Using Query Designer Window

This section takes a brief look at the Query Designer tool. I haven't discussed the components of SQL statements yet but I want to show you the mechanics of this tool. This tool is available in several different Microsoft products including Visual Studio 6, Visual Studio.NET, SQL Server Reporting Services, and Microsoft Access Data Projects.

Try It Out

  1. Using Enterprise Manager, expand the nodes in the left pane. If you haven't done so already, start with Microsoft SQL ServersimagesSQL Server Groupimages(local). Note that this node may also be labeled (local) (Windows NT) depending on the operating system.
  2. The next node to expand is Databases. Expand the AdventureWorks2000 database and right-click the icon labeled Views.
  3. From the right-click Action Menu, select New View.

Now you should be looking at a new window that contains four panes arranged vertically. This is the Query Designer window. Figure 3-4 shows the initial view before you add tables.

images

Figure 3-4

How It Works

The Transact-SQL Query Designer is actually a utility (separate from Enterprise Manager) that can be called from a few different places within Enterprise Manager, including the Database Diagram tool and on the Action menu in the Tables node of Enterprise Manager. The easiest way to open the Query Designer window without actually having to retrieve data from a table is to choose the option to create a new view. You don't have to save the expression as a view. In fact, this is one of the most convenient techniques to use when you just need to quickly create a SQL expression to copy and paste into another tool or window. In the following Try It Out, you use this technique to create a simple multi-table query. After you build the query, I will show you a few of the features and toolbar options.

The Query Designer contains four panes that can be resized and scrolled individually. Each of these panes can be hidden and shown using buttons on the toolbar. The top area is the diagram pane. It graphically displays tables and views included in the query. Joins are depicted as lines between each table window.

The second pane is the grid pane and is for managing the columns for the tables in the query. The grid pane allows you to specify column aliases, calculations and expressions, output, and sorting options.

The third pane is the SQL pane. SQL syntax will be generated automatically from selections and settings in the tables and columns panes and placed in the SQL pane. SQL expressions can also be typed or changed directly in the third pane. As long as the SQL syntax is supported by the graphical view, the tables and columns pane content will be updated to reflect these changes. There are a few expressions that the Query Designer can't represent graphically. These include unions and some types of subqueries. Query Designer is a very smart tool and, with these few exceptions, will handle almost anything else you can throw at it.

On the toolbar, the right-most icon is used to add tables to the query. Click this icon to open a window listing all of the tables in the AdventureWorks2000 database. The same dialog can be accessed by right-clicking the diagram pane and selecting Add Table from the resultant Action Menu. For future reference, note that this dialog (shown in Figure 3-5) can be used to add views and functions as well.

images

Figure 3-5

Now add the Product and ProductSubCategory tables to this query. Click ProductSubCategory to select it from the list and click the Add button. Now, do the same for the Product table—select it from the list and click Add. Both of these tables should have been added to the top-most pane in the Query Designer and a thick line intersected by a diamond should be visible, as shown in Figure 3-6.

images

Figure 3-6

The line between the two tables represents a join. The Query Designer assumes there should be a join between these tables because a relationship was designated between these tables when the database was created. If you need to, you can use the mouse to move the tables and resize them in the designer for clarity. This won't actually affect anything other than your ability to see what's going on. The line end on the ProductSubCategory table side shows a key icon because this table contains the primary key column in the join. The ProductSubCategoryID column is used to ensure that there can be only one subcategory with a particular CustomerID value. The little infinity symbol on the Product table end of the line means that for one ProductSubCategory there can be many products (usually based on tables having a one-to-many relationship). The ProductSubCategoryID column in the Product table is a foreign key. Its value may be duplicated but a related ProductSubCategoryID must exist in the ProductSubCategory table. The diamond shape indicates that this is an inner join. This means that related records must exist in both of the tables participating in the join. In other words, subcategories that don't have products won't be included in the query's result set. If it were permissible in the design of this database, products without related subcategories also would not be included. Due to a foreign-key constraint that the database designer used to define this relationship, this condition isn't allowed.

The next step is to choose the columns you'd like to output from the query. Use the check boxes in each of the table windows. Check the Name, ProductNumber, Color, and ListPrice columns for the Product table and the Name column for the ProductSubCategory table. Note that this places these column names into the grid in the second pane, or column list.

You will notice that because the Name column has been selected in both tables, the Query Designer has created an Alias for the Name column from the ProductSubCategory table. The Query Designer does this automatically any time a duplicate name appears in the column list for a SELECT statement. The Alias that the Query Designer chooses, “Expr1,” is probably not what you want. This is easy to correct. Either in the SQL pane or the Column pane, change Expr1 to SubCategory. The other columns can also be aliased as desired to make the column headers more intuitive for anyone who runs this query in the future. For this query, alias the Name column from the Product table as well as to Product.

In the third pane, you will see the actual SQL expression. The fact is that the SQL expression is the only thing you're building. Everything else in this designer is derived from this expression. Figure 3-7 shows the designer window thus far.

Notice the text in the Alias column for the Name field in the Product table. Because this field name is the same as the Name field in the ProductSubCategory table, an alias should be defined to make these column names more readable. You can address this by defining a meaningful alias for both the product name and the subcategory name. Place the cursor in the alias column on the first row, representing the ProductSubCategory Name field, and type SubCategoryName. This will be the name of this field. Now do the same for the second row, representing the Name field for the Product table: replace the text Expr1 with ProductName. The Query Designer also allows you to change the sort order for your result list by specifying a Sort Type and Sort Order. For your query you want the results ordered by the ProductSubCategory, Product, and ListPrice in that order, but you want the ListPrice to be sorted from the most expensive to the least expensive. The resultant query designer should look like that shown in Figure 3-8.

images

Figure 3-7

images

Figure 3-8

The Query Designer also allows for executing the query to view the results. You can do this by clicking the dark red exclamation point icon on the toolbar. This executes the query and displays records in the results pane grid at the very bottom of the Query Designer window. An important aspect of this particular feature to keep in mind is that when executing a query in Query Designer you are actually opening an updateable cursor to the underlying data objects. Any changes to the data in the results pane are immediately applied to the underlying tables. This may sound very useful, but in reality it is quite dangerous and has the added downside of consuming very large amounts of server resources. I would strongly recommend not executing the query in Query Designer. Instead, copy the query to Query Analyzer or the query window in SQL Server Management Studio and execute it there. The results window in these latter tools does not hold any locks or create cursors to hold the data. Figure 3-9 shows the Query Designer with the results pane populated (which again, is not recommended).

If you leave this window open and have a large number of rows returned from a query, you may be prompted by the designer to clear these results and free up memory on the SQL Server.

To finish this short tour of the Query Designer tool, take a look at the toolbar to see some additional features. First of all, you can launch the Query Designer in a few different ways. Further in this section, you open a table and use the Query Designer to filter and sort rows. Figure 3-10 shows the Query Designer toolbar.

Because the Query Designer is a multipurpose tool that has been incorporated into different products for different reasons, some of these features may not be enabled. For example, Cancel Filter isn't enabled in this environment. In some applications, buttons may be added or hidden. In Microsoft Access, sorting buttons are added to the toolbar. On the toolbar (as with most Microsoft products), if you hover the mouse pointer over a button, a pop-up tooltip displays a short caption describing the button's feature.

images

Figure 3-9

images

Figure 3-10

The toolbar options are described in the following table.

images

images

Using the Query Designer to View a Table

There are a few different ways to use features of the Query Designer. Another method, in Enterprise Manager, is to view records from a table in a grid. Simply right-click any table and choose Open Table from the menu. Selecting any of the three submenu options (Return All Rows, Return Top, or Query) will show the Query Designer window in a customized view. Remember, however, that returning data with the Query Designer does not come without risk or cost.

Try It Out

Using Enterprise Manager, drill down to the Product table in the AdventureWorks2000 database. Right-click the table icon and, from the pop-up window, choose Open Table. A new menu option is displayed. From this menu, select Return All Rows. Figure 3-11 shows the results pane filled with product records.

For simplicity and to avoid redundancy, this section doesn't step you through the same exercise just used. In this case, you will modify a SQL expression and then view the outcome in the columns pane. Click the SQL Pane toolbar button (the one on the left labeled SQL). The SQL pane is displayed above the results grid and contains the following expression:

SELECT         *
FROM          Product

Spaces and carriage returns are ignored so don't be concerned with these. The designer makes it a point to format expressions and try to make them more readable. This isn't a concern right now. For this example, this expression reads as follows:

SELECT * FROM Product

images

Figure 3-11

You can add text to a new line or just append it to the existing text on the same line as long as there is at least one space between each word. Modify this statement so it reads as follows:

SELECT *
FROM Product
WHERE StandardCost > 100

Click the Run button (the exclamation mark) on the toolbar and the results should be updated to show only products with a cost greater than $100. Figure 3-12 shows these results.

images

Figure 3-12

The Query Designer did a couple of things. The first thing it did was reformat the query text, placing parentheses around the text following the word WHERE. Don't be concerned with this. The Query Designer is just trying to be helpful. The results were also refreshed to show only records where the StandardCost field values met the criteria.

The last step is to modify the SQL expression (see Figure 3-13) by adding the ORDER BY clause to the end, and then click the Run button on the toolbar.

You can see that the designer added parentheses. This was actually not necessary in this simple query but it doesn't hurt anything. You can leave these on or off for the next step.

For a little variety, close the inner console window and right-click the Product table again. Choose the Open Table menu as before, but this time choose Query from the submenu, as shown in Figure 3-14.

images

Figure 3-13

images

Figure 3-14

You should see the Query Designer window with four panes displayed.

Now add one more bit of text to the expression that will sort the list by the ProductNumber column. Again, add text to the expression so it looks like this:

SELECT     *
FROM         [dbo].[Product]
ORDER BY ProductNumber

Click the Run button to view the results. Figure 3-15 shows the records sorted by the product number.

Finally, take a look at the query from another viewpoint. Click the Diagram Pane (second toolbar button) and the Column Pane (third toolbar button) and you will see that the Query Designer is able to decipher your SQL expression into a graphical form. Granted, this is a very simple expression but later in the book you will see how this tool can be used to work with more complex, multi-table queries—which will save you a lot of time and effort. Figure 3-16 shows the Query Designer with all of the panes visible.

images

Figure 3-15

images

Figure 3-16

How It Works

Using the toolbar, you can show any combination of the diagram pane, columns pane, and/or SQL pane. If you were to show the SQL pane after displaying a table's rows in this manner, you would see that the expression, SELECT * FROM (table name), had been executed for you. To sort or filter the rows for this table, you can use the Query Designer options as if you were creating a query from scratch.

Note how the Query Designer represents the WHERE clause and the ORDER BY clause in the columns grid. The placement of these expressions in the columns criteria grid can be very useful when working with complex operations. The SQL WHERE clause can often be a little difficult to read without a lot of practice. The Query Designer makes it a point to add sets of parentheses to separate logical expressions (even in cases where you might not choose to use them). As you work with complex WHERE conditions, you may find it beneficial to copy and paste queries into the designer window to see how the designer parses and interprets the logic.

Query Analyzer

This tool has evolved and matured as SQL Server has grown up over the years. Previously known as the ISQL Windows Client, Query Analyzer is the main console for talking to SQL Server 2000.

To launch Query Analyzer, you can either choose SQL Query Analyzer from the Tools menu in Enterprise Manager or Query Analyzer from the Microsoft SQL Server program group on the Windows Start menu.

Although the look and feel is much like Enterprise Manager, Query Analyzer is not a snap-in for the MMC. A query window is opened by default and, like Enterprise Manager, is not maximized inside the main application window. Multiple query windows can be opened and each can be used to open or save to a script file.

Here's a quick tour. The main menu bar and toolbar gives you access to all of Query Analyzer's features. Again, the purpose here isn't to undertake a comprehensive discussion of these features, but you should be familiar with this application. This section lists each feature as it is arranged on the main menu bar.

On the File menu, you will find options to manage connections to database servers. If you initially open Query Analyzer from the Start menu, you will be prompted to connect to a server. Choosing the menu option to create a new connection will open the same dialog window. You will also find options to open and save SQL script files. A script file is a text file containing SQL commands and expressions.

On the Edit menu, you will find the standard clipboard options: Cut, Copy, and Paste. These features are useful when working with text and can also be accessed using the right-click menu and standard keyboard shortcuts. Below the standard edit features are Find, Replace, Go to line, and Bookmark features that are invaluable when debugging or editing large SQL scripts. The Edit menu also contains two template options. The first one inserts template syntax into the query window and the second gives you the ability to replace template placeholders with appropriate values. Templates are useful to give designers a standard starting place. For example, you may establish a standard template for creating stored procedures that include a block header and corporate contact information. Templates are stored as text files with the .TQL extension and can be created and saved from Query Analyzer. Several standard templates come installed with SQL Server.

The Query menu includes options to change the active database for a connection, execute, or just parse the active query window. Results can be output to unformatted text, to a grid, or to a text file. The text option uses a monospaced font. Variable-length columns are formatted in columns to use their maximum width. The Display Estimated Execution Plan or Show Execution Plan translates individual query operations into graphical icons, depicting the precedence order and data flow between each step. The Current Connection Properties menu option allows you to set query behavior options that will be applied only to the current connection.

On the Tools menu, you can manage indexes, statistics, and set program options. The Manage Indexes option allows you to create and drop indexes. The Manage Statistics option is used to create, update, or delete column statistics. The query optimizer uses statistics to construct the execution plan when a query is executed. The statistics managed by this option are not index statistics, but column statistics. Index statistics are created by SQL Server to determine whether an index is useful for a particular query. Column statistics can be created by the database administrator or automatically by SQL Server if the Auto Create Statistics database option is turned on. These column statistics help the query optimizer create optimal query plans without the overhead incurred by an index. For more information on this particular feature check out Books Online under the topic “statistical information, creating.”

SQL Script and Batch Conventions

Query Analyzer can execute SQL in two ways. With either method SQL expressions are simply typed directly into the query window. You can then either execute all script in the window or select part of the script and execute only the selected statements.

You are going to use Query Analyzer to write and execute the same query as you did using the Query Designer in the previous exercise. After opening the Query Analyzer, you will connect to the local database server and then designate Northwind to be the active database. In the following Try It Out you'll enter a couple different queries and then execute them, one at a time and then all at once.

Try It Out

Open Query Analyzer from the Windows Start menu. You should find the shortcut in the Microsoft SQL Server group. You will be prompted to connect to a database server. If your database server is not installed locally, select or type the server name and then indicate whether you are using Integrated Windows security or supply a username and password. The connection dialog window is shown in Figure 3-17. To connect to your local server using integrated security you can simply type a period in the SQL Server drop-down list and then select the Windows authentication option under the Connect using option. Note that in the drop-down list labeled SQL Server, there is a default entry. At first it may not be apparent but a single period (.) signifies the local database server. This has the same meaning as (local) and LocalHost on most systems. If your SQL Server is installed locally, make no changes to these settings and click OK.

images

Figure 3-17

Query Analyzer is a multi-document interface, which means that the larger parent window contains one or more child windows. As you can see in Figure 3-18, the inner query window is freestanding and can be repositioned within the parent window space. This is useful if you need to manage multiple queries or different database connections. For this example, maximize this window so it occupies all of the available space. To do this, click the small rectangular maximize button in the top-right corner of the smaller window.

images

Figure 3-18

In the middle of the toolbar, you will see a drop-down list. If you drop it down, you'll see that it has a yellow drum icon next to each database name. Don't do anything with this. I just want you to know where it is. Notice that it shows that you are currently using the Master database.

Two ways exist to indicate the database with which you want to work. One method is to select the database from this drop-down list and the other is to execute the SQL statement USE (database name). In the database window, type the following text:

USE AdventureWorks2000
GO

One database is always active. This code changes the active database to AdventureWorks2000. The GO statement means all of the previous statements must finish executing before any more statements can run. This is known as a batch directive. SQL code before or after a batch directive is called a batch. There will be more on this later.

Add some more text to the query window. Enter a carriage return and then the following SQL text:

SELECT * FROM Product WHERE StandardCost < 4

Note the various colors applied to the text. Blue text represents key words and commands that Query Analyzer recognizes. Object names are in gray text, and red text is used for literal text values. These colors are selected by default, but they can be changed to anything you want from the Options dialog on the Tools menu. On the toolbar, just to the left of the current database drop-down list, you will find a green arrow. The pop-up tip should display Execute Query (F5). Click this button or press F5 to execute the query. You will see a results grid displayed at the bottom of the Query Analyzer window.

Add another GO statement and then another SELECT statement. The entire query window content should now look like this:

USE AdventureWorks2000
GO
SELECT * FROM Product WHERE StandardCost < 4
GO
SELECT LocationID, Name FROM Location
GO

Because you've already executed the first set of SQL statements, you'll see that the current database is now AdventureWorks2000. Therefore, it's not necessary to run this again. Highlight the last SELECT line only and click the Execute button again. The results are shown in Figure 3-19.

images

Figure 3-19

As you can see, only the highlighted expression was executed. Now deselect the highlighted text by clicking anywhere else in the query window and click the Execute button again. As Figure 3-20 shows, without any query text highlighted, both queries are executed and the results are displayed in separate result panes at the bottom of the Query Analyzer window.

Query Analyzer is where I live. After I've opened it once, I leave it open because I'm going to go back. In my opinion, the only query functionality missing from Query Analyzer is the Query Designer you looked at in Enterprise Manager. If you would like to create a query in the Query Designer and then analyze and massage the script in Query Analyzer, this is easy enough to do. Create the query in Enterprise Manager using the previous technique and then copy and paste the SQL into the query window in Query Analyzer.

images

Figure 3-20

Object Browser

The most recent addition to Query Analyzer, since SQL Server 7.0, is the object browser. If you are an application developer and you have worked with Microsoft development tools, you should know that this has nothing to do with the object browser in Visual Studio or Visual Basic for Applications. However, it's a very useful feature that will provide a lot of help and save you a significant amount of work.

The object browser can be used to find practically any database object in both the system catalog and any user databases. If you need help with a system function, view, or stored procedure, this is a convenient way to learn about the input arguments and data types you will need to call or use these objects. You can also generate the calling script for any object.

Let's step through a few scenarios. These are not complete walk-through exercises, just simple examples.

Hypothetically, say that I know my database contains a table with a name containing the word “sales.” I could search the system tables if I knew how they were structured and what columns to look at. Fortunately, this isn't necessary. Located in the Master database is a set of system views with names prefixed with INFORMATION_SCHEMA. Using the object browser, I drill down into the Master database and browse through the views. I know that the Information Schema views are used to return easy-to-read metadata about various database objects. I find the INFORMATION_SCHEMA.TABLES view and expand this node to see the columns. This tells me that the name of the table can be found in the Table_Name column. Armed with this information, I type a query expression into Query Analyzer: SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE ‘%sales%’. The results show any tables with a name including the word “sales” and their associated properties.

I would like to insert rows into the Products table but I don't know all of the column names. I use the object browser to find the Products table in the appropriate database, right-click the table name, and select Script Object to Clipboard as Insert from the pop-up menu. This generates SQL script and places it on the in-memory clipboard. Next, I place the cursor in the query window and use the keyboard shortcut, Control+V, to paste the script into the query window. The script includes placeholders for the literal values I replace to perform the insert operation.

Using Books Online

Books Online is the user documentation and help system for SQL Server 2000. From the graphical tools for SQL Server, such as Enterprise Manager or Query Analyzer, just press the F1 key to open Books Online. In Query Analyzer, you can also highlight key words in your SQL script and press Shift+F1 to navigate to the specific help topic related to the key word.

Here's a little-known secret: Books Online was updated extensively after the release of SQL Server 2000 and is not updated along with the service packs. To update Books Online you must download the latest version from the Microsoft SQL Server web site at www.microsoft.com/sql. If you don't have the update, I recommend that you download and install it. The updated version has corrected many inconsistencies and added a very large amount of new data, especially about the extended XML capabilities that have been added to SQL Server 2000.

OSQL Command-line Utility

The OSQL utility is a command-line interface used to run scripts and queries for SQL Server 2000. This program can be used at a command prompt in any folder. Like most command-line utilities, it is self-documenting. To run OSQL, open a command prompt window. One way to do this is to click the Windows Start button and then select Run from the program menu. In the Run dialog, type CMD and click OK. A list of all command-line options can be displayed by using the -? Switch (type OSQL -?). Figure 3-21 shows the complete help listing.

images

Figure 3-21

To execute a query, first create a connection to the local SQL Server using Windows integrated security with the command OSQL −E. The E stands for Enterprise security, which means the same thing as Windows integrated security. This returns a numbered batch prompt. This is OSQL's way of saying “First command, please.” You can see this in Figure 3-22.

images

Figure 3-22

Each prompt will be enumerated until you issue a GO command. At this point, all commands in the preceding batch will be executed and then a new batch begins at line 1. Any SQL statements may be executed at the OSQL prompt. It is necessary to set the current database before working with data. This is done with the USE statement, followed by the GO command, as shown in Figure 3-23.

images

Figure 3-23

Note how the batch line numbers start over after the GO command is issued. This example uses a simple SELECT statement so you can see the return values from a query. The command window before I press Enter is shown in Figure 3-24.

images

Figure 3-24

Figure 3-25 shows the command window after I enter the second GO command and press the Enter key.

images

Figure 3-25

As you see, this is a no-frills environment. It's not as elegant as the Query Analyzer but it's also very simple and uncluttered. System and database administrators often go to the command prompt to run scripted maintenance tasks. Executing a script file is quite easy. As you've seen, scripts are most easily created from Enterprise Manager and Query Analyzer. You could also use Notepad to create a script file. After saving the SQL text to a script file, simply execute OSQL and pass the script file as a parameter, like this:

OSQL −E −I C:MyScript.sql

If you are using SQL Server authentication rather than Integrated Windows security, the command line would use the −U and −P parameters followed by username and password, like this:

OSQL −Uusername −Ppassword

To close the OSQL utility, use the EXIT command. If you executed OSQL from a command prompt window, this will return control to the command prompt. The EXIT command can also be used to close this window.

Tools for SQL Server 2005

If you have installed the client tools for SQL Server 2005, you will have a cascading menu on the Start menu for SQL Server 2005 containing some or all of the shortcuts in Figure 3-26.

images

Figure 3-26

Some of these items are installed with optional SQL Server components. We're concerned only with the iconized shortcuts in the lower section of this menu. These are described in the following table.

Menu Option Description
Business Intelligence Development Studio This tool uses a set of project templates in the Microsoft Development Environment (the same interface as Visual Studio.NET 2005). It is used to create and manage database queries and objects, Reporting Services reports, Analysis Services cubes, and Integration Services packages (formerly called DTS).
Database Tuning Advisor The Tuning Advisor is the successor to the Index Tuning Wizard in SQL Server 2000. It takes the features of the SQL Server Profiler to the next level by actively monitoring database sessions. These sessions are analyzed and then the Advisor suggests configuration changes and enhancements to improve database efficiency and performance. The new interface simplifies the complex process of running workload query scripts and profiler traces to test, among other elements, index usage, execution plan efficiency, caching, and I/O costing.
Profiler The SQL Server Profiler is an extensive troubleshooting and optimization tool. It can be used to monitor a broad range of database activity, or to pinpoint specific events. Operations can be captured and recorded for later playback. Events and activities can be recorded as scripts or logs to text files or to a database.
Report Manager This is the main web browser interface for Reporting Services. It is used for both report and folder management and for viewing reports. Report server administrators use this application to define security and server configuration settings. Users can browse and view reports, create subscriptions and report snapshots, and export reports in various formats.
SQL Computer Manager This management console replaces several utilities in earlier SQL Server versions. With it, administrators can configure network libraries, services, and maintenance tasks.
SQL Server Books Online SQL Server Books Online (BOL) is the online help system for all SQL Server tools and features. Books Online opens in a separate window with options to search keywords and browse the index of topics. To launch Books Online from within a SQL Server tool, press F1. Context-sensitive help is available from within Query Analyzer when you highlight a keyword and then press Shift+F1.
SQL Server Management Studio Management Studio combines the best features of Enterprise Manager, Query Analyzer, and Analysis Services Manager with the new capabilities of SQL Server 2005. This is the central management and design interface for all SQL Server, databases, objects, and various types of queries.

SQL Computer Manager

The SQL Computer Manager combines the functionality of the Server Network Utility, Client Network Utility, and Service Manager from SQL Server 2000 into one central tool. With this Microsoft Management Console (MMC) snap-in, the database administrator can start, stop, and pause any SQL Server–related service without having to scroll through the huge list of services that are presented with the standard Windows Services management console. The SQL Computer Manager can also be used to manage server and client network libraries by enabling and disabling supported libraries and specifying the individual settings for the libraries such as TCP port assignments and IP listeners.

The SQL Computer Manager is a very straightforward interface. Each SQL Server–related service and network configuration is listed in the tree view, as shown in Figure 3-27, and can be controlled using right-click menu selections.

SQL Server Management Studio

The Management Studio is specifically used for SQL Server and offers more functionality and greater flexibility than Enterprise Manager did in earlier versions. The Microsoft Management Console interface, used by Enterprise Manager and several other administrative utilities, is a very generic approach to system management that has been outgrown by an application as robust as SQL Server.

When you open the Management Studio, you are prompted to connect to a server. When working with the local server using standard Windows security, you can simply leave the default settings and connect with the Connect button.

images

Figure 3-27

The Amazing Floating, Docking, Hiding Tool Windows

At first glance, this interface looks a bit like Visual Studio (with dockable tool windows that can auto-show and hide themselves, and can be pinned down using the familiar thumbtack icons). That's because Microsoft has designed the Management Studio to use the Visual Studio 2005 shell. SQL Server 2005 and Visual Studio 2005 have been jointly developed so that there is seamless interaction between the two environments. This new developer-centric view of database management comes as quite a shock to many database administrators who have considered themselves more of an engineer than a developer. However, once the initial shock wears off, most have learned to love the new interface for the amount of control and flexibility it brings to database administration. When you open the Microsoft SQL Server Management Studio in the default view, you will see two tool windows docked on the left side. These can be undocked, moved, and hidden as you please. Note the three little icons in the top-right corner of each window (see Figure 3-28):

  • Down arrow—If you click the down arrow, the pop-up menu will display options for working with this window.
  • Thumbtack—The thumbtack is used to pin the window down (when the tack is positioned vertically). If a window is unpinned, it will auto-hide when the mouse pointer is moved away, leaving a small icon on a vertical tab. To show a hidden window, simply hover the mouse pointer over the tab. I generally will pin a window down if I'm going to use it frequently and then unpin to hide it when I need the screen space for other things. With a little practice, I think you'll find that this can be a convenient feature, especially if you are working on a small monitor.
  • X—The familiar X icon will close the tool window entirely. Rather than closing windows, I recommend that you use the auto-hide feature so you can easily show the window when you need it in the future.

If you do close a window, or you need one of the windows that is not shown in the designer by default, these can be opened from the View menu.

To reposition a tool window, you can also just grab it by the title bar using the mouse pointer, and then drag to undock and move it around the design surface. Something interesting happens when you do this. Before showing you how to use the docking tools, allow me to provide some context for this feature.

Between 1997 and 2001, I spent a good deal of my time traveling around the United States teaching programming classes at Microsoft certified training facilities. Visual Basic had become wildly popular and I taught five or six Visual Basic courses on a regular basis. One particular course, the introductory-level VB course, became very routine for me. On the first day of the class, I would introduce the Visual Studio Integrated Development Environment and walk students through some exercises to get them accustomed to the interface. I explained that the various tool windows could be undocked by dragging them around with the mouse. When you dragged the window close to the edge of the container window, it would stick to that edge of the window. It took about 2 minutes to show the students how to do this and then another 20 minutes to go around the room and help everyone put everything back. It was a little difficult to get the windows to stick back in their original location.

images

Figure 3-28

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 3-29). When you hover over one of the guide diamonds, the docking target area of the window is designated with a translucent shaded rectangle.

images

Figure 3-29

As you see in Figure 3-30, 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.

images

Figure 3-30

The result of the previous docking selection is shown in Figure 3-31.

images

Figure 3-31

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 3-32).

images

Figure 3-32

Now my tool windows are back where they started (see Figure 3-33). 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 however you wish.

images

Figure 3-33

What's in a Project?

The project and solutions paradigms have become commonplace among application developers using Microsoft programming tools. These same concepts are now extended into the database design space. These concepts are actually quite simple. A project is simply a collection of files that support a common application or purpose. In the programming world, project files typically consist of class files, code modules, forms, and configuration files. In a database project, common files can include scripts, diagrams, documentation, and Integration Services packages. The project is simply the glue that binds all of these files together.

A solution is simply a catalog of one or more projects. Often, an application is constructed in different phases and there may be multiple designers or developers in charge of different components, perhaps even different versions of the same application or database. Each of these may equate to a project that rolls-up into a larger solution.

You can create a new project by selecting NewimagesProject from the File menu. This opens the New Project dialog shown in Figure 3-34. Select the icon to create a SQL Server Scripts project, give the project a name, and either verify or change the path. I typically keep all of my projects in a special folder on a network share. By default, files are saved in folders created under My DocumentsSQL Server Management StudioProjects.

images

Figure 3-34

It's important to note that this path is user profile–specific. In other words, if another user were to log in to the same computer, these files would not be available at the same path. If you plan to share projects between users, it's advisable to use a network share or common folder.

Object Explorer

This window displays a tree view of database objects, somewhat similar to the Enterprise Manager for SQL Server 2000. Connections can be made to multiple database servers and each server is displayed at the root level of the tree with a small icon to indicate the state of the server. In Figure 3-35, the small arrow (green on screen) indicates that SQL Server is running. Folders represent groups of like objects or special features. Click the plus sign icon to expand a folder to view the items in this container. Expanding a specific database folder reveals folders representing the related Tables, Views, and other objects. Right-click any folder to create an object of that type and right-click any object for options related to that object.

If you continue to expand, drilling down to a table and then the columns for that table, you will see that summary information is displayed for each column (see Figure 3-36).

images

Figure 3-35

images

Figure 3-36

Detailed information is available for the selected column as well as for a variety of other objects. Right-click the item in the tree view to see all of the related options (see Figure 3-37). Select the Properties menu item to view and modify the properties. With a little experience, you should find this interface very intuitive and easy to use.

Writing Queries

A number of different types of queries are used to return results from different types of data sources and data structures. Each of these specialized query types is now brought into one tool. This includes the following:

  • Database Engine Queries in Transact-SQL
  • Analysis Services queries using Multi-Dimensional Expressions (MDX)
  • Analysis Services Mining Model queries using Data Mining Expressions (DMX)
  • Analysis Services configuration scripts using XML for Analysis (XMLA)

Creating a query in SQL Server Management Studio involves defining a connection to a data source and providing a design window. A query can be saved to a script file or simply used as a temporary workspace. To create a new SQL query, use the New Query drop-down button. From the menu, select Database Engine Query, as shown in Figure 3-38.

images

Figure 3-37

images

Figure 3-38

This opens another connection dialog for the query. Even if you intend for the query to run against the same data source that you specified earlier for the main Management Studio interface, you are prompted for this information when creating a new query.

With the new query window open, you must specify the database you would like to use. You can do this can in one of two ways. With a query window open, the SQL Editor toolbar is displayed with a drop-down list of available databases from the current data source (server). Drop this list down and select the database. All of the examples in this book for SQL Server 2005 use the AdventureWorks2000 database (see Figure 3-39). Using the database drop-down list has the same effect as typing the SQL command USE (database name).

images

Figure 3-39

Now for a simple query: To list all of the columns in all of the rows in the Product table, type the following SQL expression:

SELECT * FROM Product

Go ahead and type this text into the query window and click the Execute button on the SQL Editor toolbar. That's the button with the red exclamation mark. When you do this, your computer will go to work and look up about 500 product records. You'll probably see the hard disk light come on for a few seconds while SQL Server performs this action. While the query is running, a small, animated icon of a spinning world is displayed in the status bar at the bottom of the window. When it's done, summary information will be displayed with the running time and the number of records returned. The first few rows are displayed in a new window pane at the bottom of the Management Studio window, as shown in Figure 3-40.

Anything else that you would do to retrieve data will likely be an extension of this simple exercise.

images

Figure 3-40

Scripting Options

SQL statements can be very verbose and often require a lot of typing. Much of this work can be minimized by letting the Management Studio do the work for you. Most common actions can be scripted automatically using a few simple menu selections. There are several different methods for scripting a query and many actions to perform so I'm not going to demonstrate them all. The menu selections are self-explanatory for the most part. The following Try It Out should get you started.

Try It Out

Using the Object Explorer, expand the AdventureWorks2000 database and then the Tables folder. Under this folder, you will see a list of all of the tables in the database. You want to generate script to return all of the columns in the Contact table. Scroll down until you see dbo.Contact. Right-click this item and navigate through the menus, as you see in Figure 3-41. You want to generate a SELECT statement to read and return rows from the table. You also want to display the script in a new query window, so choose New Query Editor Window.

images

Figure 3-41

The SELECT statement is generated and displayed in a new window (see Figure 3-42). You should know that many options when using SQL can affect the way a query looks on the screen, but don't matter that much to SQL Server. The automated script is formatted to make it easy to read. You should note a couple things about the format of this SQL script. First of all, the square brackets containing column names are optional. This is done so SQL Server can use names containing spaces. There are none here so it really doesn't matter. Also, take a look at the last line starting with the word FROM. The script includes the database name, schema name, and then the table name separated by periods. The database name is optional when you have elected to set this as a current database. Auto-generated script is generally very descriptive.

images

Figure 3-42

Finally, execute this query using the Execute button on the SQL Editor toolbar. After a few seconds, results will be displayed in a grid at the bottom of the window. Look at the status bar in Figure 3-43: nearly 20,000 contact rows were returned in about 2 seconds. Of course, the time it takes to run a query will depend on several factors, so your results may be different from mine.

Using the Graphical Query Designer

The single-table queries written so far are fairly simple. Now you can start building a more complex query. To use the graphical query designer, right-click the query window and choose Design Query in Editor… from the menu. If you need to edit existing query text, you can highlight the SQL and use this same technique to make changes using the query builder. Another method for invoking the graphical query designer is to start creating a new view. You won't actually save your query as a view. If you read the previous section about the SQL Server 2000 design tools, I'm going to repeat myself a bit here. The following Try It Out demonstrates building a query using the AdventureWorks sample database, but the mechanics of this tool are identical to those used in Enterprise Manager with some minor enhancements. For consistency, I'll use the first technique in the following example.

Try It Out

Using the Object Explorer, right-click the Views folder under the AdventureWorks2000 database. On the pop-up menu, select New View, as shown in Figure 3-44.

images

Figure 3-43

images

Figure 3-44

The query window changes to a window split into four different panes. I'll explain how each of these panes is used as you continue. On top of the new designer window, the Add Table dialog (shown in Figure 3-45) is displayed to prompt you for the tables to be used in this query. Note that the schema names are displayed in parentheses following the table names. This is of little consequence since all of the tables in the AdventureWorks2000 database are in the dbo schema. You can either double-click each table one at a time or hold down the Ctrl key and click to select multiple tables. For this exercise, choose the Product, ProductCategory, and ProductSubCategory tables to be added to this query. Click the Add button to add these tables to the query and then click Close when you're done.

images

Figure 3-45

Four panes in the designer window, each represents the query in a different way:

  • Diagram pane
  • Columns pane
  • SQL pane
  • Results pane

The first three panes are synchronized, and changes made to the query in any one of these panes will be reflected in the others. A window graphically representing each table is placed into the top-most diagram pane of the designer. The graphical query designer draws lines from column names in each of these tables, with a diamond on each. This represents an inner join derived from the existence of corresponding relationships that exist in the database design. For example, in the definition for the ProductCategory table, a relationship, or foreign key constraint, is defined between the ProductCategoryID column and the ProductCategoryID column in the ProductSubCategory table. The Query Designer is smart enough to translate this relationship into a join statement, between these tables. The diamond tells you that this is an inner join and that records will only be returned if corresponding values exist in the joined columns for both of these tables.

One thing that can be a little confusing when discussing your interaction with the columns pane is the use of the word Columns. Each column that is to be returned from the query is displayed as a row in the columns pane grid. It would be convenient if they could be referred to as fields rather than columns; however, the tool makes reference to “Columns” in the first column of the grid. This means that in our conversations regarding this interface, we are left to distinguish the columns (or fields) of the query from the columns in the grid, which represent attributes or characteristics to the query columns.

The View Designer toolbar is displayed above this window. If you have used the graphical query designer in Enterprise Manager, Access, or earlier versions of Visual Studio, you'll probably notice that the toolbar icons have been given a facelift and that a new button has been added. The right-most button will add a derived table expression. You'll learn about derived tables and subqueries in Chapter 6. The View Designer toolbar is shown in Figure 3-46.

images

Figure 3-46

Hover the mouse pointer over each button to show a pop-up tooltip and display a short caption describing the button's feature. The toolbar options are described in the following table.

images

To choose columns to be returned, check the boxes in the table windows in the order that they appear in Figure 3-47. As you do this, these column names will be added to the columns pane and to the SELECT clause in the SQL pane. Note that there are three different Name columns between the three tables. Because the column names in a query must be unique, the designer creates aliased names for the ProductSubCategory and ProductCategory Name columns as Expr1 and Expr2. This satisfies this rule but the aliases' names aren't exactly optimal.

images

Figure 3-47

I'd prefer to use more intuitive names for the three Name columns. In the columns pane (second section of the designer), add or replace the text with the alias names from Figure 3-48. Call the ProductCategory.Name Category. The ProductSubCategory.Name is SubCategory, and the Product.Name will be known as ProductName. Also, under the Sort Order column, type or select the values 1, 2, and 3 for these three columns.

images

Figure 3-48

The query is now ready to return data. The Execute button has a red exclamation mark icon. You can also use keyboard shortcuts: Ctrl+E or the F5 key. Click the Execute button and you should see rows returned in the grid in the fourth pane of the designer window (see Figure 3-49).

images

Figure 3-49

The query results are displayed in the Results grid, in the fourth section of the designer window (see Figure 3-50). If you leave this window open and have a large number of rows returned from a query, you may be prompted by the designer to clear these results and free-up memory on your workstation.

You can experiment by adding and removing columns and changing the sort order and alias names. I didn't intend to save this query as a view or script file so if you close the query window, just indicate that you don't want to save changes. Remember that to get to the graphical query designer, you told the SQL Management Studio that you wanted to create a view.

Views and other database programming objects are discussed in Chapter 10.

images

Figure 3-50

Using Templates

Unless you have a perfect memory, there will be many times in your journey with SQL Server that you will need some assistance. I'd say that about ninety-eight percent of the SQL I write is from memory because the vast majority of the time, I need to do fairly common things: Select, Insert, Update, Delete, and so on. The rest of the time, I'll either need to jog my memory or learn to use a command I haven't had to use before. I have enough trouble just remembering the names of my kids let alone how to rebuild an index with a specific fill-factor. So, I'll either need to look this up in Books Online and/or go find an example. Most of the time, it's more helpful to just to see the script than it is to learn about the command and exactly how it affects the mechanics of the database engine.

Script templates simply provide a starting point for queries. A template is really just a piece of script saved to a file that you open in the SQL Query Designer and then modify to suit your needs. The Templates Explorer window is optional. Use the View menu or Templates Explorer button on the toolbar to enable this window if it isn't already visible (see Figure 3-51). Templates are organized into categories. Simply expand the folder icons on the tree view to find the template you are looking for.

images

Figure 3-51

Now, create a query to add columns to an existing table. This is easy to do using a template. Find the folder for table-related templates, expand it, and then find the template labeled Add Column. Right-click this item to display the pop-up menu and select Open to use the template in a new query, as shown in Figure 3-52.

images

Figure 3-52

This action opens a new query editor window with a copy of the template. Note the color-coding used to help distinguish keywords, commands, and comments in the SQL text (see Figure 3-53). With the skeleton of the query written for you, it's a fairly simple matter to replace the generic placeholders with your own text and then execute the query.

images

Figure 3-53

Business Intelligence Development Studio

SQL Server 2005 offers one more tool in its arsenal of design and solution development applications. This one is the granddaddy of them all and represents the final merger of database administration and software development tools. Whereas the Management Studio is implemented in a Visual Studio shell, the Business Intelligence Studio is an actual instance of Microsoft Visual Studio and is a very robust application used by application developers to create desktop software, web site solutions, and business components. So, what's it doing in the suite of tools installed with SQL Server? To answer this question effectively, you need to understand Microsoft's vision for the present and future state of business solutions. Since the inception of Microsoft Visual Basic in the very early 1990s, Bill Gates has promoted the notion of making software development capabilities accessible to just about anyone who could drive a computer mouse. He has evangelized empowering the information worker by bringing advanced design and development capabilities to the desktop and making them intuitive and easier to use. In the latter part of the last decade (and century) we saw disparate programming language tools merge into toolkit suites such as Visual Studio, for use by many types of application developers. Now we see the same tool being placed into the hands of database professionals.

I think that this stage in the evolution of this tool is, in large part, a reflection of our changing industry. Not long ago, the lines between programmer, architect, and database administrator were well defined. We stayed out of each other's way and didn't tread on another's turf. The IT industry shakedowns following the dot com bust and economic recession, along with maturing technology, have redefined professional roles. Now we wear more hats and are driven to do more with less. I'd submit that we have the opportunity to do more with more: more information, more computing power, better-utilized talent, and, yes; more bandwidth. We all have more bandwidth to move information around but we're expected to use more of our mental bandwidth to take on more and get more done more effectively than before. Regardless of the reasons, here we are and the industry demands tools that let us do it all from our desktops.

What can you do with this amazing tool? Just about anything related to designing and developing software and database solutions. In a standard installation of SQL Server 2005, you will have the Business Intelligence Projects item you see in the left-most pane of Figure 3-54. After installing SQL Server 2005, I also installed Visual Studio, which has added additional project type templates to the integrated development environment.

images

Figure 3-54

You have several opportunities to apply your SQL skills in application development and data presentation solutions that you create using this environment. One such opportunity is to design reports with SQL Server Reporting Services. Reports can use parameterized queries to filter data and provide advanced reporting features (see Figure 3-55). The report designer contains an integrated SQL query designer that will enable you to create, debug, and test queries for reporting.

Chapter 12 leads you, step-by-step, through the process of creating a report in the Business Intelligence Studio. This is an effective means to quickly take the results of a query and present them to users in the right format (see Figure 3-56). Reporting Services enables you to transform data into useful information, delivered to a user's web browser, desktop, and Office applications. Reports may be viewed on demand or by automated e-mail or file share subscriptions.

images

Figure 3-55

SQLCMD Command-line Utility

There are times when a system administrator just needs to get the job done and execute SQL script without the frills of a desktop user interface. SQL Server 7 and 2000 had this capability in a simple command-line utility called OSQL. OSQL also ships with SQL Server 2005 for backward compatibility and is part of the standard installation package. However, OSQL doesn't support some of the new capabilities introduced in SQL Server 2005. The recommended tool for command-line scripting is now called SQLCMD. This can be executed in a command prompt window for any folder on your database server. This is most definitely the old-school method for executing SQL, but sometimes it's actually easier than using newer, more sophisticated tools.

To use SQLCMD, open a command prompt window. One way to do this is to click the Windows Start button and select Run from the program menu. In the Run dialog, type CMD and click OK. It doesn't matter what path you see at the command prompt. To see the list of available commands, just type SQLCMD -? and press Enter (see Figure 3-57). Although the SQLCMD command itself is not case-sensitive, the switches used with it are; for example, the switch “-e” specifies that the SQLCMD commands should be echoed back, but the switch “-E” indicates using trusted security.

images

Figure 3-56

images

Figure 3-57

Just about any SQL command can be executed using this utility, but it's typically used to execute batch scripts for system maintenance. Here's a quick example of a SELECT query to show how results are returned.

Because SQLCMD isn't a particularly interactive environment, some actions must be performed using a single-line command. When you launch SQLCMD, you must provide login information—either a username and password for SQL Server authentication, or a switch to indicate that you want to use integrated Windows security. This example uses integrated security by using the –E switch (this stands for Enterprise security.) Note that the documentation for the –E switch shows that this means trusted connection. When you press Enter, a new prompt is displayed:

1>

This indicates that you are now working in the SQLCMD environment rather than at the command prompt. It also lets you know that this is the first line in a batch process. SQLCMD runs all commands in batch mode and doesn't actually execute any commands until you explicitly tell it to, using the GO command. I'll continue to enter SQL commands and then type GO when I'm ready to execute the entire batch (see Figure 3-58).

images

Figure 3-58

In the SQL SELECT command, I only asked to return two columns and the results used up most of my screen real estate. Each character type column will use the maximum number of allocated characters. This means that if you have a column defined as VarChar(255), even if the actual data doesn't take up this much space, this column will require 255 characters of screen space, not allowing much room for anything else (see Figure 3-59). Another drawback to using this interface for returning data is that little of the result set is held in memory after the query runs. You can scroll the command window up to view some text but this is very limited.

The EXIT command is used to leave SQLCMD and return to a command prompt. Type EXIT again to close the command prompt window.

images

Figure 3-59

Summary

I could have written a complete book about the features of the SQL Server 2005 client and management tools. In this chapter you learned what you will need to know to write SQL queries and choose the best tool for the job. Depending upon your role and objectives, you will use different tools to do different things.

If you are a database administrator, you will likely spend much of your time using the SQL Computer Manager to configure servers and manage the services that comprise SQL Server. You will likely spend most of your time using the SQL Server Management Studio to secure databases, create, monitor, and tune various database objects, and you may use the SQLCMD utility to run SQL scripts on the command line.

If you are an architect or solution designer, you may use the SQL Server Management Studio to diagram and generate database objects and the SQL Query Editor to create stored procedures, functions, and views to optimize database access. You may also use Microsoft Visio and the advanced solution design tools in Visual Studio and the Business Intelligence Studio to design and manage database projects.

If you are an application developer, you will likely spend most of your time developing software and creating integrated stored procedures and views in the Visual Studio integrated database design environment, and using SQL Server Management Studio to create and manage database objects. You may also use these tools to design and deploy reports using SQL Server Reporting Services.

It's often difficult to draw a clear distinction between these roles anymore. This is why these tools are tightly integrated and contain overlapping features and capabilities. Fortunately, they have been designed to work together and provide a consistent user experience. Once you have mastered the Management Studio, for example, you should be able to open the Business Intelligence Studio and work comfortably with the menus, toolbars, tool windows, and similar features.

As you read on, I will make reference to some of these tools but you will spend most of your time using the SQL Server Management Studio for SQL Server 2005 and the Query Analyzer for SQL Server 2000. To use the Transact-SQL language, you don't need to be concerned about the features of a particular design application, but you will find these features useful when you go to design entire database solutions, debug queries, and tune database objects.

Exercises

The exercises for this chapter and Chapter 4 provide numbered, step-by-step instructions. The solutions for these exercises are the final query, commands, or result. The exercises for subsequent chapters provide less-detailed instructions. You should use the material in each chapter to determine the appropriate steps and to find the solutions.

Exercise 1

  1. Using Enterprise Manager or SQL Server Management Studio, create a new view in the AdventureWorks2000 database. You won't actually save the view, but use this option to open the graphical query designer.
  2. Add the Product table to the designer.
  3. Select the ProductID, Name, and ListPrice columns by checking the corresponding boxes in the table window.
  4. Sort the results by the Name column in ascending order using the Sort Type option.
  5. Check the SQL expression in the third pane of the graphical query designer with the solution.

Exercise 2

  1. Using Enterprise Manager or SQL Server Management Studio, create a new view in the AdventureWorks2000 database as you did in Exercise 1.
  2. Add two tables: the Product table and the ProductSubCategory table.
  3. For the ProductSubCategory table, select the Name column and create an alias for it as SubCategory. For the Product table, select the Name column and create an alias for it as ProductName. Also select the ListPrice column from this table.
  4. Sort the query by the ProductSubCategory Name column and then the Product table Name column, both in ascending order.
  5. Execute the query and scroll through the results using the fourth pane in the designer. Check the SQL expression displayed in the third pane of the designer with the solution.

Exercise 3

Write a simple query using the query editor window in Query Analyzer or the SQL Server Manager Studio using the following steps:

  1. Open Query Analyzer or SQL Server Management Studio.
  2. If you are using Query Analyzer for SQL Server 2000, you will be prompted for connection information. Enter localhost for the server name or the name of your server if connecting remotely. If you are using SQL Server 2005, create a new query and provide this same connection information.
  3. Select the AdventureWorks2000 database from the database selection list in the toolbar.
  4. Enter the following SQL script in the query window:

    SELECT * FROM Product WHERE ListPrice > 4000
  5. To find out how many products have a list price greater than $4000, execute this query and check the row count in the status bar.

Exercise 4

Execute a simple query using command-line utilities:

  1. If you are using SQL Server 2000, open a command prompt and execute the OSQL utility to utilize Windows Integrated (Enterprise) security. If you are using SQL Server 2005, use the SQLCMD utility to utilize Windows Integrated (Enterprise) security. Each statement should be followed by a batch delineation command.
  2. Indicate that you want run statements using the AdventureWorks2000 database.
  3. Execute the following SQL statement and view the results:

    SELECT ProductCategoryID, Name FROM ProductCategory
  4. Exit the command-line utility and then the command window.
  5. Check your statements with the solution.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.143.4.181