SQL Server Magazine

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.

   Prev. page   [1] 2     next page