This column aims to help readers who are new to database management or new to Microsoft's SQL Server database management system (DBMS). The standard language for SQL Server database management is Structured Query Language (SQL). Transact SQL (T-SQL) is Microsoft's version of SQL. This month's column will tell you about SQL and T-SQL and give you a tour of SQL Server Query windows, which you use with T-SQL to retrieve, change, and add data to databases. In future columns, I'll discuss complex queries and performance issues and I'll give you tips for using SQL Server.
Origins of T-SQL
IBM developed SQL, which has become a standard for databases from mainframes to PCs. The American National Standards Institute (ANSI) has supported SQL's standardization. The ANSI SQL committee has released a series of specifications for SQL; the most recent specification in common use is ANSI-96 SQL.
Because ANSI makes each specification available for discussion and evaluation before users implement it, most DBMSs are at least one version behind the current specification. Each company adds features that go beyond the standard for reasons of performance or because the company believes them to be necessary or desirable, and different levels of compliance exist within a single standard, such as ANSI-92. Future versions of the standard will include some of these added features; the ANSI SQL committee might modify some of these features as it devises different ways of achieving the same results. The most basic SQL code will run on almost any DBMS, but as the code becomes more sophisticated, it becomes less portable among the various SQL dialects.
Microsoft's version of SQL is fairly close to the ANSI-92 standard, but has some enhancements that are not part of the standard. Microsoft deviates from the strict standard, mainly for reasons of backward compatibility. A radical T-SQL departure from the ANSI standard for SQL Server 6.5 and earlier is that columns in tables you create with T-SQL are not null by default. Most other T-SQL variations are enhancements to rather than departures from the standard. SQL Server is evolving toward using standard ANSI SQL. Current versions support SQL statements written in either T-SQL or ANSI SQL. You can write a join, for example, in either format. (If you plan to take the exams for MCSE and MCSD certification, Microsoft expects you to be familiar with T-SQL and the ANSI standard SQL.)
T-SQL is a powerful programming language. It appears deceptively simple because it has few commands. However, because it is a high-level language, you need only a few commands to make significant changes—good or bad—to your database.
SQL Query Window
The SQL Query window is the interface that you use to write, edit, and run SQL queries. This interface will figure in most of the topics in this column during the next few months. I'll give an introductory tour of SQL Query windows when we talk about writing queries.
When I refer to a query in this article, I do not mean only SELECT statements to retrieve data. I mean any SELECT, UPDATE, INSERT, or DELETE operation, or any other SQL code that you can run from this window, including stored procedures and Database Consistency Checker (DBCC) commands (which I will discuss in future articles).
You bring up the Query window in one of two ways. The first is the ISQL/w interface, which you see in your program group as ISQL _ W because of limitations in the Windows 95 or Windows NT interface. From the SQL Server program folder, click ISQL/w to bring up the window you see in Screen 1 on page 74. To connect, type the name of your server in the Server text box. You must provide a user login and password if you are using standard security (SQL Server 6.x or SQL Server 7.0 security system). If you are using integrated security (the NT security system), you will log in using your NT logon account. After the system has validated your login, the ISQL/w window, similar to the window in Screen 2, will open.
The second way to open an SQL Query window is to start the SQL Server Enterprise Manager, which is in the SQL Server program folder. If you have not already done so, you must register the server in the Enterprise Manager, which means you must supply the server name, a user login ID, and a password at an Enterprise Manager prompt if you are using standard security. If you have set up integrated security on the server, supply only the name of the server and select the Trusted Connection check box. After you are in Enterprise Manager, click Tools on the menu bar and select the SQL Query Tool option. This action will open a Query window that is identical to the ISQL/w window in Screen 2.
If you are working while logged on at the server, rather than remotely, you can register the computer either as (local)—with parentheses—or by typing in a period. Either method connects you to the local server.
Running a Query
Let's try running a simple query. In the Query window, type
use pubs select * from authors
The actions this query performs are as follows: use pubs changes the database focus to the pubs database, and select * from authors selects all columns and all rows from the authors table.
The Query tab is selected in the Query window Screen 2 shows. Click on the green arrow in the upper right corner. The query will run, and you will see the results under the Results tab, as Screen 3 shows. To return to your query, select the Query tab. (Another way of running a query is to press Ctrl+E—the E is for execute. You can move between the Query tab and the Results tab by clicking them or pressing Alt+Y or Alt+R.) The third tab on Screen 3, Statistics I/O, generates information about how to optimize and run a query. The Statistics I/O tab will be the subject of a future column.
SQL Server automatically installs the pubs database, a small example database of book publishers, with authors, book titles, and bookstores. Pubs occupies only about 3MB and is a useful diagnostic tool. I have written pubs in lowercase letters in this example. My installation of SQL Server uses a case-insensitive sort order, which was a choice I made in the setup process. Had I chosen a case-sensitive sort order, I would have to pay attention to case in my queries. Just for clarity, I will sometimes write a query as
USE pubs SELECT * FROM authors
in which the query words are uppercase and the object names are lowercase.
Choosing the Database
At the top of the Query window is a list of all the databases that are available on this server. Make sure you have selected the correct database before you start the SQL code, especially if you are creating or dropping objects. Even better, get in the habit of typing the USE databasename statement as the first line of each script to avoid any doubt about which database you are operating on. An added benefit of this statement is that, when you save the script in a file and want to reuse the script later, you won't have to figure out which database this script belongs to.
More Features
Now let's quickly tour other Query window features. You just ran a simple query. Open a Query window again, and type
use pubs SELECT * FROM authors SELECT * FROM titles
If you run this script, both queries will run, and the output result set will contain the output from the first query and then the output from the second query. This result might not be what you want. If you prefer to run the first SELECT statement alone, highlight just the first two lines of the script, then press Ctrl+E or click the green arrow. Only the highlighted commands will run. Next, highlight the third line, and run it.
If you watch closely, as you run a query, the green arrow changes to a red box, then back to an arrow as the query completes. If the query continues to run, either because it is a large, complex query or because it contains an error, click the red square to stop the query.
Saving the Query
When you have a query that you might want to run again or develop further, save it as a file. The convention is to save SQL scripts with an .sql extension. The Query window File menu offers the customary options Save, Save As, and Open, in case you want to read the file back in and run it again.
Query Window Toolbar
Screen 4 shows the Query window toolbar. The first icon on the left end of the toolbar will open a new Query window. You might open a new Query window when you are running a large query in a window and want to run a separate quick query. Also, when you are building a complex query in a window, you might open a second window to retrieve some information without disturbing your flow in the first window.
Be careful, however, about opening multiple windows just to separate multiple short queries. Each open window is another connection to the SQL Server system. If you open too many windows, you'll run out of user connections, or you'll make other users wait for their results. A better technique when running many short queries is to write them one after another in the same window, then highlight and run each query as described earlier and step through each query individually.
The second icon on the toolbar lets you read an SQL script that you have stored as a file on disk. When you develop a script to perform a task or query, you can save the script using the third icon on this toolbar (or by selecting File, Save As), then retrieve the script by using the second icon or by selecting File, Open.
The fourth icon, which looks like a wrench, brings up the Query window configuration options. Screen 5 shows configuration options on the Query Flags tab of the Query Options window. On the Query Flags tab, you can set options for how SQL Server will handle arithmetic errors, and whether to display how many records the query returns or affects. The No Execute option compiles the query but does not run it. The Parse Query Only option checks a query for syntax, but does not compile or run the query. The last three options are for performance analysis. Show Query Plan causes the query output to include information about how the query was optimized and whether the query used an index or a table scan. Show Stats Time provides information about how long each part of the query takes to run. Show Stats I/O displays the number of scans, logical reads, and physical reads necessary to run this query.
The Format Options tab controls how to display the query's output. When you run a query in the Query window, you typically use the default format, which is column-aligned. However, if you want to send the output to another program, you can choose comma- or tab-delimited output. The Verbose Prints option on the Format Options tab includes the output for any print statements and the query results. The Output Query option shows the first three lines of the query on the same page as the results, which might be useful if you are saving the results to a file.
To the right of the configuration icon (the wrench) on the toolbar is the text box that shows the database in use. Next is a text box showing the Query window in use. While a query is running, you will see a rotating globe in the Queries text box.
To the right of the text boxes is an icon that lets you close the current Query window. When you click this icon, a message on the screen will prompt you to make sure that you mean to close the window—in case you have results you have not saved. A green arrow and a red stop symbol are icons for running or stopping a query.
In the lower left corner of the Query window, you see on the status bar an icon that displays a circle with a line through it, the universal symbol for no. This icon toggles the No Execute option on or off. Its neighbor, which resembles a small bar chart, toggles the Statistics I/O option on or off. The bar-chart icon has no effect if you selected Statistics I/O from the Query window configuration options.
The Connections box on the lower right side of the status bar shows the number of active connections to SQL Server. In a box to the right of the Connection box, a character number and line count identify the cursor position. Knowing the cursor position might be useful when you are editing a query. You can use the usual editing functions, including Cut, Copy, and Paste, and you can copy and paste SQL code from one Query window to another or within a Query window.
Tips and Tricks
As you begin using SQL Server Query windows, you will find helpful features in addition to those I've described. Here's a head start in finding some of these tips and tricks.
Undo. When you are editing a query, the Ctrl+Z combination acts as an undo feature. The editor stores your last nine commands and reverses them as you press this key combination.
Help. Sometimes you want help with the command's syntax. Suppose you want help with a particular stored procedure such as sp_helpdevice. You can go to the Help menu and search for this command in the Help system. Or, you can highlight the command and press Shift+F1. This command will open the Help menu. Be sure you highlight the entire command. (You can refer to Books Online—BOL—for in-depth help if you need it.)
Object Help. A similar Help feature is available for help with the columns and properties of a table. Suppose you are building a query and want to know the column names in a particular table. You can run the stored procedure sp_help tablename. However, if you already have the table name in your Query window, highlight it and press Alt+F1. This key combination runs the sp_help procedure on the table. The Results window shows the information you need, as you see in Screen 6.
No data returned. When you parse a query without running it or use an SQL command to set an option, you might receive this message: This command did not return any data, and it did not return any rows. In this case, the command ran correctly; it did not return any errors, data, or rows.
What's Next
In future columns, I will discuss T-SQL in more detail. I'll start with simple queries and build to more elaborate code.
