Next chapterContents


Special Edition Using Microsoft SQL Server 6.5 Second Edition

Written by Stephen Wynkoop

Dedication

This book is dedicated to Brennan and Caitlin. I hope you continue to find computers more fun than work!

About the Authors

Stephen Wynkoop is an author and lecturer working extensively with Microsoft-based products and technologies, with an emphasis on Internet and client/server technologies. Stephen has been developing applications and consulting in the computer industry for more than 15 years. He is author or co-author of several other titles from Que and other publishers, including Que's Running a Perfect Web Site Second Edition, Special Edition Using Windows NT Server 4.0, and The BackOffice Intranet Kit. Stephen is a regular speaker at Microsoft's technical conferences and has written books on Microsoft Access and Microsoft Office development and integration. Stephen is also a Microsoft Certified Professional. You can reach Stephen via the Internet at swynk@pobox.com, or visit his Web site at http://www.pobox.com/~swynk.

Chris Lester is a software support engineer in Microsoft Premier Support. He has dabbled in UNIX-based databases, XBase, dBase, and data design (as well as several programming languages). He has extensive experience with LAN-based messaging systems and Microsoft Exchange in particular. When Chris is not working he can be found eating, sleeping, or dreaming about a Porsche.

Acknowledgments

The crew on this book has been fantastic to work with. Nelson Howell, diligently pointing out my errors and making suggestions on the technical side, Kelly Marshall and Mike La Bonne at Que moving the project forward as quickly as possible, and the many, many copy editors and layout teams have been a lot of fun to work with. Without such a team, these projects are impossible.

I'd also like to say "Thank You!" to my wife, Julie, and family, Brennan and Caitlin. They put up with a number of tirades when things didn't work as expected, and Julie in particular makes sure the work is actually readable, generally a good thing in books! This book gains a whole level of better understandability with her involvement in the editing cycles.

Finally, but certainly not lastly, this Second Edition of the Special Edition Using SQL Server 6.5 book would surely not be possible without the prior efforts of the original author team of Bob Branchek, Peter Hazlehurst, and Scott L. Warner. The work put forward on the original version shows through on this new expanded edition and shows how well these folks know their stuff.

Stephen Wynkoop

We'd Like to Hear from You!

As part of our continuing effort to produce books of the highest possible quality, Que would like to hear your comments. To stay competitive, we really want you to let us know what you like or dislike most about this book or other Que products.

Please send your comments, ideas, and suggestions for improvement to:

The Expert User Team

Email: euteam@que.mcp.com
CompuServe: 72410,2077
Fax: (317) 581-4663

Our mailing address is:

Expert User Team
Que Corporation
201 West 103rd Street
Indianapolis, IN 46290-1097

You can also visit our Team's home page on the World Wide Web at:

http://www.mcp.com/que/developer_expert

Thank you in advance. Your comments will help us to continue publishing the best books available in today's market.

Thank You,

The Expert User Team

Introduction

All of data processing is involved with the operations of storing and retrieving data. A database, such as Microsoft SQL Server, is designed as the central repository for all the data of an organization. The crucial nature of data to any organization underlines the importance of the method used to store it and enable its later retrieval.

Microsoft SQL Server uses features similar to those found in other databases and some features that are unique. Most of these additional features are made possible by SQL Server's tight integration with the Windows NT operating system. SQL Server contains the data storage options and the capability to store and process the same volume of data as a mainframe or minicomputer.

Like most mainframe or minicomputer databases, SQL Server is a database that has seen an evolution from its introduction in the mid-1960s until today. Microsoft's SQL Server is founded in the mature and powerful relational model, currently the preferred model for data storage and retrieval.

Unlike mainframe and minicomputer databases, a server database is accessed by users--called clients--from other computer systems rather than from input/output devices, such as terminals. Mechanisms must be in place for SQL Server to solve problems that arise from the access of data from perhaps hundreds of computer systems, each of which can process portions of the database independently from the data on the server. Within the framework of a client/server database, a server database also requires integration with communication components of the server in order to enable connections with client systems. Microsoft SQL Server's client/server connectivity uses the built-in network components of Windows NT.

Unlike a stand-alone PC database or a traditional mainframe or minicomputer database, a server database, such as Microsoft SQL Server, adds service-specific middleware components--such as Open Database Connectivity (ODBC)--on top of the network components. ODBC enables the interconnection of different client applications without requiring changes to the server database or other existing client applications.

SQL Server also contains many of the front-end tools of PC databases that traditionally haven't been available as part of either mainframe or minicomputer databases. In addition to using a dialect of Structured Query Language (SQL), GUI applications can be used for the storage, retrieval, and administration of the database.

Now, with the addition of new database-aware components, you can also use your SQL Server with your Internet-based applications. Tools such as the Internet Database Connector, or IDC, and the Advanced Data Connector, or ADC, are available that will help you integrate SQL Server database information into your Web pages. Depending on the tool or approach you select, you'll have access that ranges from static Web pages to dynamic, Visual Basic-enhanced Web pages. These exciting tools are making Web-based applications a reality. n

Who Should Use This Book

This book is written for users of Microsoft SQL Server--from database users and developers to database administrators. It can be used by new users to learn about any feature of Microsoft SQL Server. It can also serve as a reference for experienced users who need to learn to use a feature of the product that they haven't yet employed.

The CD-ROM that accompanies this book offers example Transact-SQL statements and demonstration applications provided by many third-party vendors to give you a feeling for many of the products and services available in the market, as well as ideas for using SQL Server in your organization. You'll also find additional links to tools, articles, and other resources on the Web at http://www.pobox.com/~swynk, the author's site, or at the Que Publishing site, http://www.quecorp.com, where you can find additional books online as well.

In addition, you can use the electronic version of this book included on the CD. The electronic version will enable you to reference the information in the book on your monitor alongside an actual SQL Server database.

How to Use This Book

This book is divided into six sections. The sections are intended to present the use of SQL Server as a logical series of steps in the order in which the reader would most likely use the product. Ideally, you will go through the chapters and their sections in sequence.

Part I: Understanding the Basics

Part I discusses the basic features of Microsoft SQL Server and provides an overview of its capabilities. In this part, you'll learn about necessary information related to Microsoft SQL Server. The background information about SQL Server is recommended for all new and current users of SQL Server who may be unfamiliar with it.

In Chapter 1, "Introducing Microsoft SQL Server," you'll learn the origin and evolution of SQL Server and its implementation as a relational database. You'll also learn about the components of SQL Server, including the installation and configuration of the database and its client components.

Chapter 2, "Understanding the Underlying Operating System, Windows NT," explains the features of the operating system that SQL Server takes advantage of to obtain optimal performance. The SQL Server database components are designed solely for implementation on the Windows NT system.

Chapter 3, "Installing and Setting Up the Server and Client Software," covers the physical installation and configuration of the server engine and the tools you'll use with it. This covers both the server installation and the setup of the tools from the client system.

In Chapter 4, "Data Modeling, Database Design, and the Client/Server Model," you'll learn how to design a database. Moreover, you'll become familiar with the terminology associated with a relational database, such as SQL Server.

Chapter 5, "Creating Devices, Databases, and Transaction Logs," explains how to create the storage areas on a disk where you create your database and backups of your database.

Chapter 6, "Creating Database Tables and Using Datatypes," provides you with instructions on how to create database tables and choose the datatypes of the table columns. You can also read about the considerations involved in your choice of table and table column characteristics.

Part II: Up and Running with SQL Server

In Part II, you'll learn how to start working with SQL Server, extracting the information in your tables to make the best use of it. You'll also learn to manipulate the stored data, including combining data from multiple sources. If you are already familiar with SQL or the previous version of SQL Server, you'll still want to read the chapters in this section to learn how this version differs from the earlier version. You'll also want to learn about the additions that support the ANSI SQL standard.

Chapter 7, "Retrieving Data with Transact-SQL," provides instruction in the use of the SELECT statement and the addition of clauses to the SELECT statement for controlling the retrieval of targeted data.

Chapter 8, "Adding, Changing, and Deleting Information in Tables," continues the instruction on retrieving data begun in Chapter 7. Included in the discussion is instruction on how to combine data from multiple tables.

Chapter 9, "Using Functions," provides a comprehensive treatment of the functions you can use in Transact-SQL. The examples presented in the chapter are simple and direct, which facilitate the understanding of how to use the functions.

In Chapter 10, "Managing and Using Views," you'll learn the definition and use of stored SELECT statements subsequently used like an actual table. You'll also learn about the problem of disappearing rows, a phenomenon that occurs with the storage of rows through a view.

In Chapter 11, "Managing and Using Indexes and Keys," you'll learn how to define the database objects used to ensure fast retrieval of the rows of database tables. In addition, you'll learn to use the database object that is a basis for ensuring referential integrity in a database.

Chapter 12, "Understanding Transactions and Locking," provides an understanding of the synchronization mechanism used by SQL Server to ensure the integrity of database tables and operations.

Part III: Server-Side Logic and Capabilities

The client/server model requires that you have intelligence on the part of both sides of the equation. SQL Server provides several different tools in this arena, including the most prominent--stored procedures. In Part III, you'll learn about these and other capabilities managed and executed by the server-side of the client/server equation.

Chapter 13, "Managing and Using Rules, Constraints, and Defaults," contains information on how to restrict the values that may be inserted into database tables and other database structures.

Chapter 14, "Managing Stored Procedures and Using Flow-Control Statements," and Chapter 15, "Creating and Managing Triggers," discuss the capability of creating a set of Transact-SQL statements that can be stored and subsequently executed as a group. You can use flow-control statements, including conditional statements, to effectively write a SQL program that manipulates your database. In addition, you'll learn to create a set of SQL statements that are automatically activated when SELECT, INSERT, UPDATE, or DELETE statements are executed on a database.

Chapter 16, "Understanding Server, Database, and Design Query Options," allows you to configure your server and database for various uses and situations. In addition, you'll also learn to configure your queries against the database.

Chapter 17, "Setting Up and Managing Replication," discusses how you implement the automatic creation and maintenance of multiple copies of a database to enhance performance in the access of data.

Chapter 18, "Using the Distributed Transaction Coordinator," tells you how to extend the capabilities included in transactions across servers. This chapter explains the setup, use, and troubleshooting associated with these extended transaction capabilities.

Part IV: SQL Server Administration Topics

When your server is up and running, and during the forming of your ongoing maintenance plans, you'll find that several topics will come up. The chapters in this section will include management of security and backup approaches, as well as the art of optimizing your server.

Chapter 19, "SQL Server Administration," provides you with information about how to keep data consistently available to the users of client systems. Availability of data from a database is ensured by a combination of fault-tolerant mechanisms and the duplication of data before it's lost.

Chapter 20, "SQL Server Security," continues the discussion of maintaining data availability by explaining the implementation of proper security for SQL Server and its databases.

Chapter 21, "Optimizing Performance," explains how you can enhance a database and the retrieval of information from the database by using important calculations based on different storage factors. Moreover, you'll learn to use the built-in monitoring tool of the Windows NT system to monitor the performance of SQL Server components and applications.

Part V: Developing Applications and Solutions

This part of the book focuses entirely on development issues. This ranges from the use of db-lib to how you access SQL Server using the Advanced Data Connector technology just arriving on the development scene. These chapters will give you a great look at the different ways you can go about working with some of the more popular tools available for client-side applications development.

Chapter 22, "Developing Applications to Work with SQL Server," provides you with information about the client and server components used for the interconnection of systems. The network components and protocols are the basis on which SQL Server and client applications depend for communication.

"Understanding SQL Server and the Internet" is the subject of Chapter 23, which offers full coverage of how to work with the Internet Database Connector (IDC) and a look at emerging technologies that let you create more dynamic Web pages for your site.

Chapter 24, "Creating and Using Cursors," provides you with instruction on the use of a feature of SQL Server that enables you to perform selection operations on individual rows of a database table. You can also randomly access an individual row and manipulate it without affecting other rows.

Chapter 25, "Accessing SQL Server Databases Through Front-End Products," discusses the access of a SQL Server database through programming and non-programming applications. The chapter uses representative examples of the most prevalent client products.

Chapter 26, "Upsizing Microsoft Office 97 Applications to SQL Server," goes into detail on how you can migrate your application from Access 97 to SQL Server. This includes setting up views from queries, working with tables, and working through the details associated with the up-sizing effort.

Part VI: Appendixes

The eight appendixes provide additional information on supporting topics that you'll want to know for your installation. From RAID configurations to working with the software included on the CD, the information is all here.

There is also a complete reference to the Data Access Objects, or DAO. This comprehensive guide is useful regardless of the host language you choose to develop in.

Conventions Used in This Book

Que has over a decade of experience developing and publishing the most successful computer books available. With that experience, we've learned what special features help readers the most. Look for these special features throughout the book to enhance your learning experience.

The following font conventions are used in this book to help make reading it easier:


TIP: Tips present short advice on a quick or often overlooked procedure. These include shortcuts.


NOTE: Notes present interesting or useful information that isn't necessarily essential to the discussion. A note provides additional information that may help you avoid problems or offers advice that relates to the topic.


CAUTION: Cautions warn you about potential problems that a procedure may cause, unexpected results, or mistakes to avoid.

This icon indicates that you can also find the related information on the enclosed CD-ROM.

Syntax Guidelines

It's important to have a clearly defined way of describing Transact-SQL commands. In this book, the following rules apply:

{DISK | TAPE}

Finally, cross-references are included that refer you quickly to specific sections throughout the book.


Next chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.