Special Edition Using Microsoft SQL Server 6.5 Second Edition

Previous chapterNext chapterContents


- 1 -
Introducing Microsoft SQL Server

Transact-SQL is a subset standard of SQL that SQL Server uses to implement, maintain, and access databases.
SQL Server comes with several utilities that allow you to access its services. You can use these utilities locally or remotely to manage a SQL Server system.
With a system as complex as SQL Server, knowing every detail is very difficult. Online help is available in an easy-to-search book-type format.

As the computer industry moves to more distributed environments and moves its data from mainframe to servers, you need to understand the concepts behind a client/server database environment.

In several respects, server databases such as Microsoft SQL Server are identical to mainframe databases. The overwhelming majority of databases used on computer systems are relational databases. Also, server databases such as relational databases on mainframe or minicomputer systems support the use of Structured Query Language (SQL), as well as proprietary tools for accessing data.

Where you start to see differences in a PC-based client/server solution is in the architecture and physical implementation of the system. With a SQL Server solution, your users have intelligent client systems, such as personal computers. In a mainframe or minicomputer environ-ment, users are likely to use a terminal or a PC running terminal-emulation software. With more intelligent client systems, users can retrieve information from the server and manipulate it locally. This type of implementation optimizes the processing of the information, allowing each component to work on the information independently in the manner best suited for that component. The server focuses on the database processes, and the client focuses on the presentation of the information.

Making the Move to SQL Server

Two key features of a server database become important because of the client access to data. The first feature provides a single point of access to the data in the database. The second feature divides processing and manipulation between the client and server systems.

SQL Server permits client applications to control the information retrieved from the server by using several specialized tools and techniques, including options such as stored procedures, server-enforced rules, and triggers that permit processing to be done on the server automatically. You don't have to move all processing to the server, of course; you still can do appropriate information processing on the client workstation.

Because mainframe and minicomputer systems traditionally do all processing at the host side, it can initially be simpler to implement systems in this environment than in a true client/server implementation. It's simpler because users work at terminals that are directly connected to the mainframe or minicomputer and work directly with the database by using the processing power of the mainframe or minicomputer.

Although organizations routinely use SQL Server to manipulate millions of records, SQL Server provides several tools that help you manage the system and its databases and tables. The Windows- and command-line-based tools that come with SQL Server allow you to work with the many aspects of SQL Server. You can use these tools to

You also can use a command-line interface to perform all operations with SQL Server.

A dialect of the SQL language is used with SQL Server for interactive and application program access to data. SQL is the de facto standard for database operations, and every vendor's database product should include a version of it.


NOTE: The Microsoft Open Database Connectivity model (ODBC) uses SQL to connect to databases even when the underlying database doesn't natively support SQL. In those cases, SQL is translated into a set of commands that accomplish the requested call for the given database. After you master SQL, you can work with any ODBC data source that you need to access, and you can use ODBC to make the translations needed for the underlying database engine.

Although this book explains how to use the command-line tool for issuing interactive SQL commands, remember that you can perform most operations with the application tools that use the Windows Graphical User Interface (GUI). You can use either interface or both interfaces, depending on your interest. If you're familiar with another SQL dialect, you may initially find that issuing direct SQL commands from the command-line for all operations is simpler.

Understanding Relational Features of SQL Server

A key characteristic of SQL Server is that it is a relational database. You must understand the features of a relational database to effectively understand and access data with SQL Server. You can't construct successful queries to return data from a relational database unless you understand the basic features of a relational database.


See Chapter 2, "Understanding the Underlying Operating System, Windows NT," for more information about relational databases and establishing database structures, Chapter 2.

Dr. E.F. Codd designed the model for relational databases in 1970, as a means of storing, retrieving, and manipulating data more easily than in hierarchical and network databases. Hierarchical and network databases were difficult to design, and it sometimes was difficult to write proper queries for access to data.

Hierarchical and network databases were difficult to work with for several reasons. One reason was that the physical and logical definitions of data storage in hierarchical and network databases had to be written in a cryptic syntax. Another difficulty of working with data definitions came from the fact that different types of internal pointers, numeric references to data locations, and other low-level details had to be set up and stored through the database. The pointers were used for the subsequent direct retrieval of data.

In Codd's relational database model, the data is referenced as though it were stored in a two-dimensional table. The actual physical storage of the data--although significant for the time required to store, change, or retrieve data--is insignificant syntactically for reference. The two-dimensional table model permits data to be referenced as the rows and columns of the table.

In a relational database, data is referenced as the rows and columns of a table. You can easily visualize data stored as a table, because you often encounter data stored in tables in everyday life. You reference train and plane schedules as a table, for example, and you create typical worksheets as a table. See Figure 1.1.

FIG. 1.1
This is an example of a common table, showing flights out of the Tucson area.

The rows of a table are unordered in Codd's relational model. In the relational model implemented in Microsoft SQL Server, the rows of a database table are also unordered unless you create a clustered index for the table. After you create a clustered index for a table, the rows are stored in ascending order by the one or more columns that you use to create the index.


See Chapter 11, "Managing and Using Indexes and Keys," for more information about clustered indexes and the other types of indexes supported by SQL Server, Chapter 11.

It's important, however, that the statements you use in the retrieval language to access table rows be independent of the order of the rows. If you require the rows of a table to be retrieved and displayed in some order, the statement that you issue to retrieve the rows must specify the row order. The rows are sorted as they're retrieved for your query.

The original relational model required each row to be uniquely defined by at least one column of a table: the unique key. The unique-row requirement ensures that each row is accessed or changed independently and uniquely from other rows of the table. The query language used to access table rows can use only data stored within each row to separate one row from another.

SQL Server, however, doesn't require you to define unique table rows. You can create two or more rows of a table that can't be referenced separately from one another. Although you may not find a use for duplicate rows, some users feel that such rows are desirable in certain applications.

In Chapter 2, "Understanding the Underlying Operating System, Windows NT," you learn more about relational-design concepts and techniques. For now, though, it's important to understand what is possible, if not practical. If you want to prevent duplicates, you can add to tables a constraint that prevents duplicate rows.


CAUTION: As you see in Chapter 2, unless you're absolutely certain that you must allow the storage of duplicate rows, you should ensure that table rows are unique. In the absence of enforced uniqueness, accidentally adding one or more duplicates to the table is too easy. After you add duplicate rows, removing or updating them is difficult.

In the relational database model, data that's stored across tables in one or more databases is combined during the access of the rows during an inquiry. If the Employees table contains columns such as Name, Department, and Badge, for example, a second table named Pays can contain the columns Hours_Worked, PayRate, and Badge.

You can define the Badge column in both tables. This way, you can subsequently retrieve column values from both tables in a single query. You combine columns from multiple tables by using statements that call out the columns you need and specify (in the Where clause) the information that is common to both tables. You read more about the syntax of this operation later in this book. For now, it's important to understand only that this pulling together of information based on common values is known as a join.


See Chapter 7, "Retrieving Data with Transact-SQL," for more information on using Where clauses, Chapter 7.

The example in the Employees and Pays tables uses the relational capabilities of SQL Server to retrieve information from each table by using the corresponding badge numbers. Following is an example SELECT statement:

SELECT * from Employees, Pays where Employees.Badge = Pays.Badge

Not surprisingly, if you modify or delete a badge number in the Employees table, you must update the corresponding badge number in the Pays table. The process of ensuring that corresponding values of related tables are maintained to keep table relationships intact is called referential integrity. This process can even include deleting related information in other tables. For example, if you remove a master record, as would be the case if the Employees record were deleted and it referred to records in the Pays table.

Maintaining referential integrity is easiest if table rows are unique. This practice ensures that a second table will have only a single row. Make sure that you maintain the badge number if it is modified or deleted in the first table. If you don't, you'll end up with orphaned records, those with a detail record but no master record.

In the relational database model, the column, or set of columns, that uniquely defines the rows of a table is referred to as a key.


NOTE: A key that uniquely defines the rows of a table is called a primary key. If you add a column that is a primary key in one table to a second table, the column added to the second table is called the foreign key.

The column is a foreign key because the new columns referencing the first table are used only to allow the matching of corresponding rows between the tables. For more information, see Chapter 11, "Managing and Using Indexes and Keys."


In earlier database systems, internal pointers were created and maintained within the database to link the corresponding rows stored in the tables. The pointer mechanism created a problem however, because of the maintenance issues associated with keeping the pointers accurate.


NOTE: Older hierarchical and network databases don't use terms such as table and row. Hierarchical and network databases use their own terminology to describe data. The equivalent of a row in a relational database is a record, and the equivalent of a column in a relational database is a data item or field.

If you neglected to identify data that must be combined later, during retrieval, you couldn't do it after the database structure was created; you had to re-create the logical and physical structure of the database. The main problem involved with using hierarchical and network databases was that changes in data-retrieval combinations were impossible to make without redesigning the database.

In relational databases such as SQL Server, you can add a new column to a table at any time. This capability allows you to create relationships with other tables. Unlike typical hierarchical or network databases, a relational database doesn't need to be redefined if you add a new column to a table; only a single table must be redefined. You don't need to unload the rows of the table and later reload the table to add a new column; you can use SQL's ALTER TABLE statement to make modifications in existing tables.


See Chapter 8, "Adding, Changing, and Deleting Information in Tables," for more information about the ALTER TABLE statement, Chapter 8.

Exploring Client/Server Features of SQL Server

Client/server computing is a type of distributed model for data storage, access, and processing. In a distributed processing system, multiple computers collectively perform a set of operations. A client/server system uses at least two computers, one of which nearly always is a personal computer.


NOTE: Distributed processing was introduced in minicomputer systems to provide the capabilities of large mainframe computers. By working together, several minicomputers could match the data storage, access, and processing capabilities of a mainframe computer in some operations.

Each system in a client/server model performs one or more of the data storage, access, or processing operations. Client/server computing can't be done with a system that uses terminals or PCs running terminal emulators that are connected to another computer. In this arrangement, the terminal or the PC that's used as a terminal is simply too passive; it only sends and displays sets of characters.

When PCs and servers are connected, the overall processing should be divided between the server, mainframe, or minicomputer system and the client system. The client and the server each process work within its own capabilities--a form of teamwork that contributes to the efficiency and speed of the overall operation.

Client/server, as the name implies, also involves an unequal division of processing. The inequality results from the processing disparity between the server and the client. The larger and faster server computer transfers data faster, stores greater quantities of data, and typically per-forms more extensive processing than the client system.


NOTE: Client/server computing is quickly becoming the backbone of the Internet, for good reason. Client/server optimizes the processing on the side of the transaction that makes the most sense, whether it be displaying information to the end user or sorting through information that should be returned for display.
By using client/server technology on the Internet, many mainstream applications are coming online. At the same time, these applications are not causing the same level of bandwidth use that you would see in a completely client-side application environment because smaller amounts of data are transferred between the systems using the applications.

You can find out more about this application of the client/server approach on the Internet in Chapter 23, "Understanding SQL Server and the Internet."


Smaller PC systems are used as clients in client/server systems because the PCs perform proportionally less of the overall work, relying primarily on the server for heavy-duty data manipulation. Also, the PC's keyboard and monitor allow it to work as an input device, by generating commands and data, and as an output device by displaying data to the user.


NOTE: A client and a server also are defined by the direction of the data flow and operational responsibilities. A large, powerful PC system can function as a server if it receives commands and data from one or more PC systems, processes the data, and returns information to other PC systems. The server is the computer system that receives requests for processing or information from other computer systems.

You can use large, powerful PCs as servers with less powerful PCs as clients and still qualify as using client/server technology. In this environment, the servers usually are more powerful models, a fact that helps them process requests from many clients.


Microsoft SQL Server is a perfect example of a client/server system. The SQL Server database must be installed on the Windows NT platform. The Windows NT operating system provides an extremely broad range of processor systems that you can use as your server. Windows NT is supported on I86-processor-based systems, PPC, MIPS, and Alpha AXP RISC-processor-based systems.

SQL Server provides the server software that's installed on the server system and some client software that's installed on the client PC systems. Windows GUI application tools allow you to create, maintain, and access the database and all objects from the client.

The network software components required for the interconnection of clients and the server computer are built into the Windows NT system. Windows NT also provides a choice of network protocols for communication between the client and server systems. A client can run Windows 95, which also contains built-in network software for connection with the Windows NT server system. The Windows 95 client and Windows NT server systems support a wide range of network cards.

In a client/server system in which the server application is a database such as SQL Server, the server is responsible for creating and maintaining database objects such as the table and indexes. The server maintains referential integrity and security, and ensures that operations can be recovered in the event of numerous types of failures.

The client application performs all user interaction, including displaying information and allowing manipulation of the application with the graphical user interface. After rows of data are retrieved from the server, the application can create copies to be held locally, and the data can be manipulated. You also can control the type of access to the information. Read-only access often is an excellent option, insulating users from the master copies of the information that they work with on the server.

If you work with local copies of the information, you can work with the information locally without communicating with the server. After you complete your work, you can send changes back to the server or (if you used the information only for review) simply discard the working databases. You can manipulate the data directly in the SQL Server database from the client, of course, if necessary and if your application allows. You must be sure to update the server with all changes so that other users can access the most recent data in the database.

You also can access SQL Server directly from the server. Direct server access is convenient, especially for administrative operations, but it isn't a client/server approach, because the operations occur locally rather than across the network. Microsoft SQL Server comes with 32-bit versions of the SQL Windows application tools for the Windows NT server that are normally used on the clients.

Although you can have client applications validate new or updated data, the validation should optimally be done at the server. A column such as Badge, for example, can be checked to ensure that each new or updated badge number is unique and falls within a specified range. It's safer for the data to be validated at the server as a part of a SQL Server-defined mechanism. If the validation is defined at the server, it is always in force, whether or not the connected client performs a validation.


NOTE: A big benefit of using server-side validation is that it protects the database from access by applications that may access the database in nonstandard manners. These include appli-cations such as Excel, Access, and Word, which can connect to the database by using ODBC. In each case, rules and integrity checks that you implement on the server are still enforced, even though the client application may be unknown.

If you rely on client applications to validate data before the data is sent to the server, you must ensure that all the client applications do it consistently. You also must ensure that changes aren't made directly at the server, where no validation mechanism has been defined.

A simple, reliable solution is to implement server-side validation, especially given the current industry-wide push to standardize interfaces and to allow third-party applications to access company information systems for additional analysis.


TIP: You also can perform validation in client applications. Client validation can be specific to the client application and should include those checks that aren't enforced by server validation mechanisms. When the updates are sent to the server, it still enforces its own validation.

Examining SQL Server Features and Components

The core components of SQL Server are the relational database and its structure. SQL Server is a powerful, comprehensive database environment. This section points out certain parameters for using SQL Server.

SQL Server allows you to define up to 32,767 databases. If you realize that a database is a centralized repository for the storage of information, being overly constrained by the 32,767-database limit is difficult; you're not likely to encounter any situation in which you need to define more than this very liberal limit. If you do, consider adding servers to your network to help balance the load. In a typical production installation, fewer than five application-oriented databases (and often, only one) are in service on any given server.

You can define up to two billion tables within each of your 32,767 databases. You're not likely to need anywhere near two billion tables in a database, however. With most typical systems, you have no more than several hundred tables in a database.

You can define up to 250 columns for each table. In Chapter 4, when you learn about database design, you see that when you normalize your database tables, you largely overcome this limitation. SQL Server allows you to combine columns from as many as 16 tables in a single query.

The number of rows in a table is effectively unlimited for SQL Server. You're limited in practice by the capacity of the storage medium on which tables are stored; databases and their tables can be stored across multiple physical disks. SQL Server allows databases to expand to include up to 32 physical disks.


NOTE: The 32-disk limit is imposed indirectly through subordinate logical structures. You store SQL Server databases, transaction logs, indexes, and tables in logical structures called segments. You can expand a database by adding segments that are created on devices. A database can include up to 32 segments. See Chapter 5, "Creating Devices, Databases, and Transaction Logs," for information about creating databases and segments.

For each table, you can define up to 250 indexes, only one of which can be defined as a clustered index. An index is a structure that allows table rows to be retrieved quickly. In a clustered index, the table rows are sorted and maintained in storage in a physically ordered state--that is, rows that are sorted before and after one another are stored in that sorted order. An index is often defined for the columns that are referenced in retrieval statements. You should find that 250 indexes provide fast retrieval of table rows.

Indexes require additional storage space in the database for the index structure that must be created and stored. One performance recommendation is to define only as many indexes as you need because of the space that indexes take up. You need to define enough indexes to allow for the rapid retrieval of rows, however. You should define the minimum number of indexes that you require. Needing more than 250 for a single table would be unusual.

Devices and Databases

You store databases and all the objects within them in disk files. SQL Server calls your database files devices, but they're logical units rather than physical devices. You create a database in the logical devices. Remember that you can create up to 32,767 databases.

Each database is created with a set of system tables in which SQL Server records data about the database objects, such as tables or indexes that you subsequently create. Like a relational-database product, SQL Server keeps the control information about your database objects in a relational database, which is the set of system tables.

Transact-SQL

Structured Query Language (SQL), the query language developed by IBM in the 1970s, has become the de facto standard database query language for relational databases. The dialect of SQL that you use with SQL Server is Transact-SQL, which Microsoft implements as a core component of SQL Server.

Transact-SQL adds to the original SQL keywords for the formation of data retrieval, storage, and manipulation statements. When SQL Server's implementation of SQL was put into place, Microsoft, like other database vendors, added features and extensions to the language.

Compared with other vendors' SQL dialects, Transact-SQL has less unique syntax. Arguably, the SQL dialect SQL-PLUS, used with the relational database Oracle, has the most vendor-specific, additional unique syntax. Although the large set of unique syntax in some SQL dialects is useful, the use of dialect-specific syntax makes stored sets of SQL commands nonportable and can cause you many headaches when you move your systems between RDBMS systems.


NOTE: Remember that some SQL dialect is used with all relational databases. If you work with more than one relational database, or if you must convert from one to another, using the SQL syntax that's the most generic is easiest. Stored sets of SQL statements, if they use generic syntax, can be easily converted or used across relational databases.

Transact-SQL is best characterized as lean and mean. You have just enough enhancements to basic SQL to write functional queries. Transact-SQL contains statements that create logical storage units (the devices), as well as the databases that reside on the devices. You can also use Transact-SQL statements to create the objects, such as tables, that are stored within the databases.

Not surprisingly, you can use Transact-SQL statements to add and manipulate data and other database objects. Four keywords are used to form statements that perform all basic data storage, retrieval, and manipulation:

The INSERT, DELETE, UPDATE, and SELECT statements, as well as other statements, use a generic form of SQL for data manipulation. The extensions to Transact-SQL principally provide flow control to direct the execution order of statements. Use flow-control statements in organized sets of SQL statements that are stored as objects within your database.

Stored sets of Transact-SQL statements contained in the SQL Server database are called stored procedures, which are compiled so that they execute SQL statements rapidly. You can use stored procedures in addition to programs for database access and manipulation because the procedures can use variables and parameters, return errors and status, and use flow control to control the execution order of SQL statements.


See Chapter 14, "Managing Stored Procedures and Using Flow-Control Statements," for more information on stored procedures, Chapter 14.

A trigger is a special type of stored procedure used to maintain referential integrity in a SQL Server database. You can create insert, delete, and update triggers to control addition, deletion, or updates in corresponding rows of related tables for which the trigger is defined. Triggers are an excellent way to maintain referential integrity, because they give you complete control of the operations that they perform and because they're server-based.

You also use several additional objects including rules, defaults, and constraints to help control or to apply values to table columns automatically.

You use a default to supply a value to a column of a database table when the insertion of a new row doesn't specify a value for the column. A rule constrains the values that can be entered in the column of a table. A constraint defines a characteristic of a table column, such as requiring unique values.

Two Windows NT processes are part of the SQL Server set of components. You issue Transact-SQL statements that are conveyed to and performed by the two server processes. You can use a tool such as the Performance Monitor to display the characteristics of the SQL Server processes.

Figure 1.2 shows the working set for the SQL Server processes SQLSERVR and SQLEXEC. (The working set of a process is the percentage of processor time that's directly allocated to a process.)

FIG. 1.2
The Performance Monitor displays the characteristics of the SQL Server processes and helps you watch for out-of-the-ordinary system-level occurrences.

The SQLSERVR process is divided into multiple threads for execution. Threads are the separate units of execution on a Windows NT system. The division of one of the two SQL Server processes into multiple threads allows multiple execution in a multiprocessor environment such as Windows NT. If your server has more than one processor, different operations on the SQL Server databases can be executed simultaneously.


TIP: You can buy a wide range of computer systems with multiple processors and increased memory to enhance the Windows NT version of SQL Server and to increase the speed of transactions.

You can use Windows NT's Performance Monitor not only to monitor the performance of SQL components, but also to learn about the function of components. You can perform different Transact-SQL statements and use the Performance Monitor to determine which component is activated to perform the operation.

Command-Line Applications

You can issue SQL statements through the Interactive Structured Query Language (ISQL) utility. ISQL/w is the Windows tool that allows you to use Transact-SQL with SQL Server from a graphical interface (for more information, see "ISQL/w" later in this chapter). If you become familiar enough with Transact-SQL syntax, or if you prefer working at the DOS command line, you can perform all operations on your databases through ISQL command lines.

From a command-line session, you invoke ISQL with the command isql. You can use several parameters in the ISQL command line. You can enter the user name and password after ISQL to take you directly to an ISQL command session, for example.

The following example shows the initiation of a command session:

isql /Usa /P<password> /S<server>
1>

The command prompt is successively numbered until the execution (GO) command is entered.

You can use the -? or /? switch to display the syntax for the ISQL command, as shown in the following example:

usage: isql [-U login id] [-e echo input]
      [-p print statistics] [-n remove numbering]
      [-c cmdend] [-h headers] [-w columnwidth] [-s colseparator]
      [-m errorlevel] [-t query timeout] [-l login timeout]
      [-L list servers] [-a packetsize]
      [-H hostname] [-P password]
      [-q "cmdline query"] [-Q "cmdline query" and exit]
      [-S server] [-d use database name]
      [-r msgs to stderr] [-E trusted connection]
      [-i inputfile] [-o outputfile]
       [-b On error batch abort]
       [-O use Old ISQL behavior disables the following]
            <EOF> batch processing
            Auto console width scaling
            Wide messages
            default errorlevel is -1 vs 1
      [-? show syntax summary (this screen)]


NOTE: ISQL command-line parameters are case-sensitive. Be sure to observe the uppercase and lowercase indications provided by the help from ISQL, also detailed in Table 1.1.

Table 1.1 summarizes the functions of the parameters. Each parameter (also called a switch in the Microsoft SQL Server documentation) is preceded by a forward slash (/) or a hyphen (-). The command isql /? displays the hyphens (-), although the hyphen or forward slash can be used. The use of the hyphen in command-line ISQL is inherited from the Sybase version of SQL Server.

Table 1.1 ISQL Command-Line Parameters

Parameter Function
a packet_size Indicates packet size for data transfer 512 through 65535; NT default is 8192
b On error batch abort
c cmdend Specifies the command terminator; default is GO
d dbname Issues a USE dbname command on entry into ISQL
E Uses trusted connection
e Echoes input
H wksta_name Specifies the workstation name
h headers Provides the number of rows to print between column headings
i inputfile Specifies an input batch file for execution
L Lists the local and remote servers
l timeout Sets the login timeout
m errorlevel Sets error-level displays to this level or higher
n Omits prompt line numbers
O Uses old behavior
o outputfile Specifies file to which statement output is directed
P password Specifies password; prompted for if not specified
p Displays performance statistics
Q "query" Executes an .SQL batch file and exits the ISQL session
q "query" Executes an .SQL batch file
r [0|1] Controls redirection of error-level messages
S servername Specifies the server name; default is local
s colseparator Sets column separator; default is blank
t timeout Sets the command timeout in seconds; default is no timeout
U login_id Provides the case-sensitive SQL Server user account name
w columnwidth Sets column width; default is 80
? Shows syntax

Table 1.2 lists the set of commands used after you enter ISQL. You must use these commands at the beginning of a command line, which is a 1> prompt.

Table 1.2 ISQL Commands

Command Purpose
GO Default command terminator; executes a statement
RESET Clears statements before execution
ED Invokes the default system editor
!! command Executes a Windows NT command
QUIT or EXIT() Exits ISQL
Ctrl+C Terminates a query without exiting ISQL
SHUTDOWN Stops the SQL Server and exits ISQL


TIP: You can use the command-line recall feature of Windows NT--the [arrowup] key--to recall commands that you have entered within ISQL. Your ISQL commands are limited to 1,000 characters per line.

Applications

Four GUI applications are available for accessing and managing SQL Server installations:

Each of these is covered in the next sections.

ISQL/w A Windows version of ISQL called ISQL/w issues Transact-SQL statements. You enter Transact-SQL commands in a separate query window inside the ISQL/w main window. You can cut, copy, paste, print, save, and edit previous queries more easily in ISQL/w than you can through an ISQL command line. After you start the ISQL/w application from the SQL Server 6.5 program group, you sign in to SQL Server by indicating your user name, your password (if necessary), and the server that you want to use. SQL Server maintains its own list of users who can connect to a server from a client system by using a valid login ID and user name.


See Chapter 20, "SQL Server Security," for more information on implementing and managing SQL Server security, Chapter 20.

Figure 1.3 shows a SELECTSELECT statement used to retrieve all rows from the system table SysDatabases, which contains a list of all defined databases. The tabular list of columns of information kept for the databases is displayed in the Results window.

FIG. 1.3
SQL statements are entered in the Query page of ISQL/w. Press Ctrl+E to execute the statements.


TIP: If you have several statements in the Query page, you can select the statements that you want to execute and then press Ctrl+E to execute them. Only the selected statements are submitted to SQL Server.

Your query output is displayed in a separate Results window, which you can reach by clicking the Results tab. In addition, when you submit a query, ISQL/w automatically switches the current view to the Results tab. The Results tab shows information such as the rows returned by query and error messages. You can use the scroll bars to view the entire query output. Figure 1.4 shows the tables that are created automatically when SQL Server is installed.

The SQL Client Configuration Utility The SQL Client Configuration Utility defines the Net-Library and DB-Library used for communication between the client and server. Figure 1.5 shows the SQL Server Client Configuration Utility dialog box. Click Locate to check for multiple copies of client libraries on your client or server system. You must consult the documentation of your client product to confirm that the correct version of the DB-Library is selected.

FIG. 1.4
You can use ISQL/w from both the client and server systems to submit configuration commands, as well as query statements.

FIG. 1.5
Establish the client configuration in the appropriate tab of the setup dialog box.


NOTE: You should use the latest version of the network and database libraries on your system. If you have a mixed-version environment, consider upgrading all servers to the most recent server software or attempting to use previous-version drivers with the newer servers. Although this method works in most cases, you may experience some problems in using new features.

Net-Library is set to Named Pipes when you install the SQL Server client application tools on a client system. Named pipes is the default communication mechanism used for communication between the client application and the SQL Server system. You can choose a different network library to use an alternative communication mechanism from named pipes. As Figure 1.6 shows, you can choose alternative mechanisms for the TCP/IP, IPX/SPX, and Banyan VINES transport protocols.


NOTE: The SQL Client Configuration Utility is helpful because a client may need to connect to more than one server. If you install all the necessary protocols, the client workstation can access the routines located in network and database libraries.

FIG. 1.6
You can choose among the most popular network protocols when you set up the software.

SQL Server Books Online The SQL Server Books Online help facility contains the contents of 13 books and a glossary on SQL Server (see Figure 1.7). Like ISQL/w and the SQL Client Configuration Utility, the SQL Server Books Online application can be installed automatically on a client or server system. You'll find it extremely convenient to have quick access to such extensive documentation without leaving your computer system. These books include the entire text of the SQL server documentation provided by Microsoft.

FIG. 1.7
You can print or copy pages or complete sections of the Books Online information.
SQL

Service Manager The SQL Server Manager is one of the only utilities likely to be most valuable to you when physically working on the server. The SQL Service Manager application starts, stops, or pauses the SQL Server processes. You must start SQL Server before you can perform any operations with the databases. The SQL Service Manager is the easiest way to start either a local or remote server. Figure 1.8 shows the SQL Service Manager dialog box after the MSSQLServer service is started.

FIG. 1.8
If you pause the MSSQLServer service, no additional client or server connections are permitted.


NOTE: Services in Windows NT, such as the MSSQLServer and SQLExecutive of SQL Server, are system processes that run in the background within NT. These background processes are used by SQL Server and client systems that require their functions.

The traffic-light metaphor simplifies starting, stopping, and pausing SQL Server. Double-click Stop or the red-light symbol to stop SQL Server. Double-click Pause or the yellow-light symbol to pause SQL Server if the server has been started.


TIP: You can minimize the SQL Service Manager and still observe the traffic lights to determine whether server service is stopped, as with a red light, or started, with a green light.

The icon that represents the Service Manager displays the appropriate traffic light.


SQL Enterprise Manager SQL Enterprise Manager is the server application that you use to perform nearly all administrative operations with local or remote servers. You can even use SQL Enterprise Manager to start and stop both SQL Server services mentioned earlier, rather than use the SQL Service Manager. You also use SQL Enterprise Manager to do the following:

You also may find it convenient to perform queries by using Transact-SQL commands from within SQL Enterprise Manager. Click the SQL Query Tool toolbar button in SQL Enterprise Manager's main window to display a window in which you can issue ISQL statements, as shown in Figure 1.9. Unlike ISQL/w, you won't have to connect and log in, because you connected when you started SQL Enterprise Manager.

FIG. 1.9
In this window, you can issue ISQL statements directly by clicking the Query Analyzer button.

Although you can perform all the administrative operations for SQL Server through ISQL, SQL Enterprise Manager allows you to perform the operations by using pull-down menus and dialog boxes rather than a command line. In Figure 1.10, the grouping of server objects in folders has been expanded to display several types of server entities.

FIG. 1.10
Server objects are displayed in hierarchical fashion in the Server Manager window.

SQL Performance Monitor The SQL Performance Monitor is a standard administrative application of the Windows NT operating system. SQL Server was written to allow SQL Server objects and counters to be displayed in the Performance Monitor with Windows NT object counters. For convenience, an additional icon has a predefined set of objects and counters that you can use to monitor SQL Server. Figure 1.11 shows a chart of several important SQL counters that you can use to monitor the performance of SQL Server on your system. The integration of the SQL Server objects, counters--like those shown for the cache hit ratio--and user connections enables you to select and display SQL Server statistics with NT objects and counters.

FIG. 1.11
The Performance Monitor is used to display statistics on the performance of both the system and SQL Server.

You can dynamically switch to display the counter information as a vertical-bar graph rather than a chart. You can also record object counters in a log for later display, or you can display the information as a tabular report.

The Performance Monitor also allows you to set threshold values for SQL Server counter values. When the threshold value is reached, an alert is displayed within an Alert view of the Performance Monitor. Also, an alert message can be sent to you even if you're working on a client workstation.


See Chapter 21, "Optimizing Performance," for more information on performance-monitoring techniques.

SQL Security Manager The SQL Security Manager administers SQL Server accounts. Choose one of the following security types to implement on your system during the installation of SQL Server:

Figure 1.12 shows the SQL Security Manager dialog box. The Security Mode is standard for the Windows NT domain PACKBELL. Login access through named pipes has been granted to users who are members of the SQL Server group Administrators.

FIG. 1.12
Integrated security provides the simplest account management in SQL Server, but it also requires the most comprehensive user-setup.

The SQL Security Manager graphically maps Windows NT groups and users to SQL Server accounts and passwords. You can also use the SQL Security Manager to find SQL Server access permissions for mapped accounts.

Distributed Transaction Coordinator The Distributed Transaction Coordinator (DTC) can break up transactions across servers, allowing you to implement truly distributed applications and database integration. Although transactions are a standard part of a SQL server-based application, using transactions across more than one physical server has traditionally been a problem. With the Distributed Transaction Coordinator, or DTC you can control these types of transactions, and you have the tools to diagnose performance and bottleneck situations that arise. In addition, you can roll-back failed transactions that include different servers.


See Chapter 18, "Using the Distributed Transaction Coordinator," to find out more about the Distributed Transaction Coordinator.

Other Utilities Many other utilities are helpful with your SQL Server installation. With Microsoft's Office products, for example, you can use the Microsoft Query (MSQUERY) application to execute queries against tables managed by SQL Server. This utility is much like the query function in Microsoft Access, allowing you to build and see the results of your SQL statements. MSQUERY also is a key tool for learning to use the SQL language. You can visually build the query that produces the result you need and then have the application show you the SQL statements that are used to create that result.

Microsoft Excel, Microsoft Access, and Visual Basic also work closely with Microsoft SQL Server, as do other third-party languages. Borland's Delphi development environment provides close-knit connectivity with SQL Server. You can use each of these products as a foundation for developing client/server applications for your users.


See Chapter 26 for more information on upsizing your application from Access to SQL Server.

See Chapter 25 for information on using other applications to access SQL Server, including different development environments.


From Here...

Now that you know how to implement SQL Server in your organization, you're ready to begin working with the topics that really make SQL Server work for you. (Be sure to review Chapter 3, "Installing and Setting Up the Server and Client Software," so that you understand what will be installed and where it will be located on your system.)

For more information about selected topics, see the following chapters:


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.