3 4
SQL Server 2000 provides a set of tools that enable you to use Transact-SQL to interact with SQL databases and their data. This set of tools includes SQL Query Analyzer and the isqlw, isql, and osql command-prompt utilities. In this lesson, you will be introduced to each of these tools and provided with an overview of how and when these tools are used. In the lessons that follow, you will learn how to work with Transact-SQL in order to manage SQL Server databases and their data.
NOTE
SQL Query Analyzer is a graphical user interface (GUI) that enables you to design, test, and execute Transact-SQL statements, stored procedures, batches, and scripts interactively. You can run SQL Query Analyzer from inside SQL Enterprise Manager or run it directly from the Start menu. You can also launch SQL Query Analyzer from the command prompt by executing the isqlw utility. (The isqlw utility is discussed in more detail later in this lesson.)
NOTE
Figure 2.1 SQL Query Analyzer displaying the Query window on the right and the Object Browser window on the left.
The functionality within SQL Query Analyzer can be described in terms of the interface layout. SQL Query Analyzer includes a number of windows, dialog boxes, and wizards that help you to perform the tasks necessary to manage SQL Server databases and the data stored within those databases. This section discusses many of these interface objects and the functions that can be performed when you access them. For more details about any of the objects that are discussed here or any objects within the interface, refer to SQL Server Books Online.
When you launch SQL Query Analyzer, the Connect To SQL Server dialog box appears. You must specify which instance of SQL Server that you want to access and which type of authentication to use when connecting to the database. Once you have entered the appropriate information in the Connect To SQL Server dialog box and then clicked OK, SQL Query Analyzer appears and displays the Query window and the Object Browser window, as shown in Figure 2.1.
NOTE
The Query window is divided into two panes: The Editor pane and the Results pane. When you first open SQL Query Analyzer, only the Editor pane appears, as shown in Figure 2.1. The Results pane appears automatically when you run a Transact-SQL query. You can also open and close the Results pane manually by clicking the Show Results Pane button on the toolbar.
You can customize the window and control the behavior of the Editor pane and the Results pane. The Options dialog box, which you can access from the Tools menu, enables you to control the look and behavior of the Query window. In addition, you can specify which fonts are used for text in the window and you can change the relative size of the Editor pane and the Results pane by dragging the split bar up and down. You can also scroll through the panes (up and down or left and right) as necessary.
The Editor pane is a text-editing window used to enter and execute Transact-SQL statements. You can use one of the following methods to enter code in the Editor pane:
The Editor pane in SQL Query Analyzer provides various tools to help you create and edit Transact-SQL statements, including the standard editing commands Undo, Cut, Copy, Paste, and Select All. You can also find and replace text, move the input cursor to a particular line, insert and remove indentation, force case, and insert and remove comment marks.
In addition, you can view Transact-SQL reference topics at SQL Server Books Online and copy the syntax example from the reference into the Editor pane, in order to help create a Transact-SQL statement. You can also save query definitions and other SQL scripts for reuse, and you can create templates (which are boilerplate scripts for creating objects in a database).
The code entered in the Editor pane is colored by category. The following table lists the default colors and what they indicate:
Color | Category |
---|---|
Red | Character string |
Dark red | Stored procedure |
Green | System table |
Dark green | Comment |
Magenta | System function |
Blue | Keyword |
Gray | Operator |
NOTE
You should use the color coding as a guide to help eliminate errors in your Transact-SQL statements. For example, if you type a keyword and it is not displayed in blue (assuming that you retained the default settings), the keyword might be misspelled or incorrect. Or, if too much of your code is displayed in red, you might have omitted the closing quotation mark for a character string.
You can either execute a complete script or only selected SQL statements in SQL Query Analyzer:
When executing a stored procedure in the Editor pane, enter the statement to execute the stored procedure and then press F5. If the statement that executes the procedure is the first in the batch, you can omit the EXECUTE (or EXEC) statement; otherwise, EXECUTE is required.
When you execute a Transact-SQL statement, the query output (result set) is displayed in the Results pane. The Results pane can include a variety of tabs. The options that you select in the interface determine which tabs are displayed. By default, only the Grids tab, which is the active tab, and the Messages tab are displayed.
The Grids tab displays the result set in a grid format, as shown in Figure 2.2. The grid format is displayed much like a table and enables you to select individual cells, columns, or rows from the result set.
Figure 2.2 Grids tab displaying the result set generated by the executing Transact-SQL statement.
The Grids tab is always accompanied by the Messages tab, which displays messages relative to the specific query.
The Results tab, like the Grids tab, displays the result set generated by executing a Transact-SQL statement. In the Results tab, however, the result set is displayed as text (refer to Figure 2.3), rather than in a grid format.
Figure 2.3 Results tab displaying the result set generated by executing a Transact-SQL statement.
The Messages tab is not displayed when the Results tab is used. Any messages pertinent to the query that has been executed are displayed in the Results tab after the result set (unless the query generated an error, in which case the Results tab contains only the error message).
You can display either the Results tab or the Grids tab, but not both. Because the Grids tab is displayed by default, you must configure SQL Query Analyzer to display the Results tab. The Results tab is then used to return queries until the end of the session or until you reconfigure SQL Query Analyzer to display the result set in the Grids tab. After you close SQL Query Analyzer and then restart it, the Grids tab will again appear in the Results pane.
To set up SQL Query Analyzer to display the Results tab, select the Execute Mode button on the toolbar, then select Results In Text. The result set from the next query that you run will be displayed in the Results tab until you select the Results In Grid option or you restart SQL Query Analyzer.
NOTE
An important feature of SQL Query Analyzer is its set of tools that help you analyze your queries in order to ensure optimal performance. One of these tools is the Execution Plan tab of the Results pane. The Execution Plan tab displays a graphical representation of the execution plan that is used to execute the current query. The display uses icons to represent the execution of specific statements and queries, rather than the tabular representation produced by the SET SHOWPLAN_ALL or SET SHOWPLAN_TXT statements. Figure 2.4 shows what this graphical representation looks like when you execute a simple SELECT statement.
Figure 2.4 Execution Plan tab displaying a graphical representation of the executed Transact-SQL statement.
By default, the Execution Plan tab is not displayed in the Results pane. To display the Execution Plan tab, select the Execute Mode button on the toolbar, then select Show Execution Plan. The next time you execute a query, the Execution Plan tab will be available—and it will show the graphical representation of the execution plan for that query. The tab will be available until you deselect the Show Execution Plan option or until you close SQL Query Analyzer.
The Trace tab, like the Execution Plan tab, can assist you with analyzing your queries. The Trace tab displays server trace information about the event class, subclass, integer data, text data, database ID, duration, start time, reads and writes, and Central Processing Unit (CPU) usage, as shown in Figure 2.5. The Trace tab provides information that you can use to determine the server-side impact of a query.
Figure 2.5 Trace tab displaying server trace information about the executed Transact-SQL statement.
By default, the Trace tab is not displayed in the Results pane. To display the Trace tab, select the Execute Mode button on the toolbar, then select Show Server Trace. The next time you execute a query, the Trace tab will be available, and it will show the server trace information. The tab will be available until you deselect the Show Server Trace option or until you close SQL Query Analyzer.
The Statistics tab provides detailed information about client-side statistics for execution of the query. The output result set displays the name of the counter, the value of the counter, and a running average from the beginning of the connection or since the last manual reset. Figure 2.6 shows the statistics that are generated after running a simple SELECT statement.
Figure 2.6 Statistics tab displaying statistics information about the executed Transact-SQL statement.
The statistics include three groups of counters: Application Profile, Network, and Time. The statistics are displayed only when a query result is generated.
By default, the Statistics tab is not displayed in the Results pane. To display the Statistics tab, select the Execute Mode button on the toolbar, then select Show Client Statistics. The next time you execute a query, the Statistics tab will be available, and it will show the client statistics information. The tab will be available until you deselect the Show Client Statistics option or until you close SQL Query Analyzer.
The Messages tab displays messages about the Transact-SQL statement that you executed (or that you tried to execute). If the query ran successfully, the message will include the number of rows returned, as shown in Figure 2.7, or it will state that the command has completed successfully. If the query did not run successfully, the Messages tab will contain an error message identifying why the query attempt was unsuccessful.
The Messages tab is available in the Results pane only if the Grids tab is displayed. If the Results tab is displayed, messages appear in that tab.
Figure 2.7 The Messages tab displaying a message about the executed Transact-SQL statement.
The Estimated Execution Plan tab displays information about the execution plan that would be used for a particular query if that query were executed. Like the Execution Plan tab, the display uses icons to represent the execution of specific statements and queries. Figure 2.8 shows the estimated execution plan for a simple SELECT query. If you were to scroll down the Results pane, you would see a graphical representation similar to what you would see on the Execution Plan tab if this query were executed.
Figure 2.8 Estimated Execution Plan Tab displaying information about the execution plan for a Transact-SQL statement.
Object Browser is a tree-based tool used to navigate among the objects in a database. In addition to navigation, Object Browser offers object scripting, stored procedure execution, and access to table and view objects.
The Object Browser window contains two tabs:
SQL Query Analyzer comes equipped with a Transact-SQL debugger that enables you to control and monitor the execution of stored procedures. The debugger supports traditional functions, such as setting breakpoints, defining watch expressions, and single-stepping through procedures.
The Transact-SQL debugger in SQL Query Analyzer supports debugging against SQL Server 2000, SQL Server 7.0, and SQL Server 6.5 Service Pack 2.
NOTE
You can run Transact-SQL Debugger only from within SQL Query Analyzer. Once started, the debugging interface occupies a window within that application, as shown in Figure 2.9.
Figure 2.9 Transact-SQL Debugger window showing the result of debugging the CustOrderHist stored procedure in the Northwind database.
When the Transact-SQL Debugger starts, a dialog box appears prompting you to set the values of input parameter variables. It is not mandatory for these values to be set at this time. You will have the opportunity to make modifications once the Transact-SQL Debugger window appears. In the dialog box, click Execute to continue with your session.
NOTE
Due to connection constraints, it is not possible to create a new query while the debugger window is in the foreground. To create a new query, either bring an existing query window to the foreground or open a new connection to the database.
The Transact-SQL Debugger window consists of a toolbar, a status bar, and a series of window panes. Many of these components have dual purposes, serving as both control and monitoring mechanisms.
Only limited functionality might be available from some of these components after a procedure has been completed or aborted. For example, you cannot set breakpoints or scroll between entries in either of the variable windows when the procedure is not running.
The Open Table window displays the columns and rows from a table in a grid. You can modify the data in the grid, and you can also insert and delete rows. Figure 2.10 shows the contents of the Customers table in the Northwind database.
Figure 2.10 Open Table window displaying the contents of the Customers table in the Northwind database.
The Object Search window helps you to find objects in the current database server. Object Search is accessible through a button on the toolbar and through the Object Search command on the Tools menu. Figure 2.11 shows the result of searching for all user table objects in the Northwind database.
Figure 2.11 Object Search window displaying the result of an object search of the Northwind database.
The isqlw utility (SQL Query Analyzer) enables you to enter Transact-SQL statements, system stored procedures, and script files. You can set up a shortcut or create a batch file to launch a pre-configured SQL Query Analyzer.
You can use the isqlw utility with or without a user interface. To run isqlw without a user interface, specify valid login information (an instance of SQL Server 2000 with a trusted connection or a valid login ID and password) and input and output files. The isqlw utility executes the contents of the input file and saves the result in the output file.
If input and output files are not specified, isqlw runs interactively (starting the SQL Query Analyzer). If valid login information is specified, isqlw connects directly to an instance of SQL Server 2000. If the information specified is insufficient for a connection, the Connect To SQL Server dialog box appears.
The isqlw utility and SQL Query Analyzer use the Open Database Connectivity (ODBC) application programming interface (API), which uses the SQL Server ODBC driver default settings for SQL-92.
The isql utility enables you to enter Transact-SQL statements, system procedures, and script files. The utility uses DB-Library to communicate with SQL Server 2000.
Like most DB-Library applications, the isql utility does not set any connection options by default. You must issue SET statements interactively or in scripts if you want to use specific connection option settings.
The isql utility is started directly from the operating system, along with case-sensitive switches. After starting, isql accepts Transact-SQL statements and sends them to SQL Server 2000 interactively. The results are formatted and printed on the standard output device (the screen). Use QUIT or EXIT to exit from isql.
If you do not specify a username when you start isql, SQL Server 2000 checks for the environment variables and uses those. For example, isqluser=(user) or isqlserver=(server) would apply. If no environment variables are set, the workstation username is used. If you do not specify a server, the name of the workstation is used.
If neither the -U nor -P options are used, SQL Server 2000 attempts to connect by using Windows Authentication mode. Authentication is based on the Windows account of the user who is running isql. The results are printed once at the end of execution. With isql, there is a limit of 1,000 characters per line. Large statements should be spread across multiple lines.
The osql utility enables you to enter Transact-SQL statements, system procedures, and script files. This utility uses ODBC to communicate with the server.
The osql utility is started directly from the operating system with the case-sensitive options listed previously. After osql starts, it accepts SQL statements and sends them to SQL Server interactively. The results are formatted and displayed on the screen. Use QUIT or EXIT to exit from osql.
Like the isql command-prompt utility, if you do not specify a username when you start osql, SQL Server 2000 checks for the environment variables and uses those. The osql utility uses the ODBC API, as well as the SQL Server ODBC driver default settings for the SQL Server 2000 SQL-92 connection options.
In this exercise, you will navigate through SQL Query Analyzer, execute a Transact-SQL statement, and then view the result of that query. To perform this exercise, you should be logged into your Windows 2000 Server computer as Administrator.
NOTE
As SQL Query Analyzer starts to open, the Connect To SQL Server dialog box appears.
The SQL Query Analyzer interface appears and displays two windows: the Object Browser window and the Query Window.
NOTE
The Object Browser window appears.
The Query window is now divided into two panes: the Editor pane and the Results pane.
The Results pane disappears, and the Editor pane fills the Query window.
Notice that the Results In Grid option and the Show Execution Plan option are both checked. The Results In Grid option is checked by default. The options listed beneath the Execute Mode button affect how a query result is displayed and affect what information is displayed.
The Object Search window appears.
The search result appears at the bottom of the Object Search window. Notice that all entries in the db name column are listed as Northwind and that all entries in the object type column are listed as either user table or system table.
The Open Table window appears, listing the contents of the Employees table in the Northwind database.
The Debug Procedure dialog box appears.
The Transact-SQL Debugger window appears as the debugging operation is executed against the CustOrderHist stored procedure in the Northwind database. An error message is displayed in the bottom pane indicating that the @CustomerID parameter needs to be supplied for this stored procedure to run.
The Editor pane of the Query window should now be the only window displayed.
USE northwind SELECT * FROM customers
Notice that the USE, SELECT, and FROM all appear in blue on the screen to show that they are Transact-SQL keywords. The USE keyword changes the database context to the specified database.
The Results pane appears and displays the query result set on the Grids tab. Notice that there are four other tabs: Execution Plan, Trace, Statistics, and Messages. The Messages tab is available by default when the Grids tab is displayed. The other tabs are optional and appear now because you configured the Execute Mode options previously in this exercise.
A graphical representation of the execution plan is displayed.
For each icon, a pop-up menu appears, showing the result of a clustered index scan.
Server trace information about the executed Transact-SQL statement is displayed.
Client-side statistics for the executed query are displayed.
A message about the executed query appears.
Notice that the Messages tab in the Results pane is active. An error message is displayed, saying that custom is an invalid object name.
The result set is displayed in the Grids tab.
The Estimated Execution Plan tab and the Messages tab appear in the Results pane. The Estimated Execution Plan tab displays the estimated execution plan for the query in the Editor window.
The result set is displayed in text form on the Results tab of the Results pane. Notice that the Messages tab is no longer present.
The message related to the execution of this query appears beneath the result set. If the query attempt had generated an error, the error would have appeared on the Results tab.
Notice that the only option now checked is Results In Text.
The result set is displayed in the Grids tab.
NOTE
SQL Server 2000 provides a set of tools that enable you to use Transact-SQL to interact with SQL databases and their data. These tools include SQL Query Analyzer and the isqlw, isql, and osql command-prompt utilities. SQL Query Analyzer is a GUI that enables you to design, test, and execute Transact-SQL statements, stored procedures, batches, and scripts interactively. The functionality within SQL Query Analyzer can be described in terms of the interface layout. SQL Query Analyzer includes a number of windows, dialog boxes, and wizards that help you to perform the tasks necessary to manage SQL Server databases and the data stored within those databases. The main window that you use to execute Transact-SQL statements and to view a query result is the Query window. The Query window is divided into two panes: the Editor pane and the Results pane. SQL Query Analyzer also includes the Object Browser window, which enables you to navigate the objects in the database, and the Transact-SQL Debugger window, which enables you to control and monitor the execution of stored procedures. In addition to SQL Query Analyzer, SQL Server includes the isqlw, isql, and osql command-prompt utilities. Each of these utilities enables you to enter Transact-SQL statements, system procedures, and script files at a command prompt. You can also use the isqlw utility to launch SQL Query Analyzer.