SQL Server 2000 Developer's Guide


"A great reference on SQL Server 2000, loaded with excellent code examples." ?Sean O'Farrell, Manager, Business Integration, Eli Lilly and CompanyThis book includes full coverage of Transact-SQL's DDL, and DML as well as ADO, DAO, and RDO. You'll learn to build scaleable SQL Server 2000 Web solutions and handle database management, backup, recovery, and security.
Read More Show Less
... See more details below
Available through our Marketplace sellers.
Other sellers (Paperback)
  • All (11) from $1.99   
  • New (3) from $34.50   
  • Used (8) from $1.99   
Sort by
Page 1 of 1
Showing All
Note: Marketplace items are not eligible for any BN.com coupons and promotions
Seller since 2014

Feedback rating:



New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

Brand New Item.

Ships from: Chatham, NJ

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
Seller since 2014

Feedback rating:


Condition: New
Brand new.

Ships from: acton, MA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
Seller since 2014

Feedback rating:


Condition: New
Brand new.

Ships from: acton, MA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
Page 1 of 1
Showing All
Sort by
Sending request ...


"A great reference on SQL Server 2000, loaded with excellent code examples." —Sean O'Farrell, Manager, Business Integration, Eli Lilly and CompanyThis book includes full coverage of Transact-SQL's DDL, and DML as well as ADO, DAO, and RDO. You'll learn to build scaleable SQL Server 2000 Web solutions and handle database management, backup, recovery, and security.
Read More Show Less

Product Details

  • ISBN-13: 9780072125696
  • Publisher: McGraw-Hill Companies, The
  • Publication date: 12/1/1900
  • Series: Database Professional's Library
  • Edition description: BK&CD-ROM
  • Edition number: 2
  • Pages: 1248
  • Product dimensions: 7.30 (w) x 9.11 (h) x 2.58 (d)

Meet the Author

Michael Otey is the Senior Technical Editor of SQL Server Magazine and Windows 2000 Magazine. He is also the President of TECA, a consulting and software development company in Portland, Oregon that specializes in Windows, SQL Server and AS/400 utilities.

Michael has published numerous articles in SQL Server Magazine and Windows 2000 Magazine. He is the co-author of the SQL Server 7 Developer's Guide and the author of VB Answers! published by Osborne/McGraw-Hill. He is also the author of AS/400 Client Server Programming and the AS/400 Application Developer's Guide published by 29th Street Press.

Michael has a B.S. in psychology from Lewis and Clark college and he enjoys running, playing video games and building computers in his spare time.

Paul Conte, is president of PCES of Eugene, Oregon, which provides educational and consulting services in SQL and Java application development. Paul has been a software developer and consultant for over twenty years, and has published over a hundred professional articles on database systems, database design, programming languages, and application development.

Paul is author or co-author of several books in addition to SQL Server 2000 Developer's Guide, including SQL Server 7.0 Developer's Guide published by Osborne/McGraw-Hill, and SQL/400 Developer's Guide, Database Design and Programming for DB2/400, and Common Sense C, all published by 29th Street Press. Paul has received numerous awards for technical writing, including the prestigious “Award of Excellence” from the Society for Technical Communication's international competition.

Paul has a B.S. in psychology from Georgia State University and an M.S. in computer science from the University of Oregon. His personal interests include fly fishing, wine tasting, and cooking.

Read More Show Less

Read an Excerpt

Chapter 1: An Overview of SQL Server 2000

In this chapter, you get an overview of the essential elements of Microsoft's SQL Server, starting with the big picture of SQL Server. In this first section, you see how SQL Server is used and how it fits into both small business and enterprise-level computing. Next, this chapter covers SQL Server's networked architecture. SQL Server is built using a modular architecture made up of a collection of core components. This section introduces you to these major objects that make up SQL Server and shows you how they are related. After that, an overview of SQL Server's database architecture is provided, followed by its major administrative components. Finally, this chapter concludes with a summary of the development history of SQL Server, along with the new features found in SQL Server 2000.

Microsoft SQL Server 2000 is a relational database system that is scaleable from small departmental networks to enterprise-wide networks. SQL Server maintains the core database files for use by custom database applications using development languages, such as Visual Basic and Visual C++, or by desktop applications, such as Microsoft Word, Excel, and Access.

SQL Server is a native 32-bit Windows database that benefits from its tight integration with the Windows operating system. For small-scale databases, SQL Server Personal Edition runs on simple Windows 9x systems with as little as 32MB of RAM. The SQL Server database engine is also the core of the standalone Microsoft Data Engine (MSDE) product distributed with the Microsoft Office Premium and Microsoft Office Develop editions as an alternative to Jet, the native Access database engine. For high-end databases, the SQL Server Enterprise Edition can take advantage of Windows 2000's support for symmetric multiprocessing (SMP) systems and failover clustering. Running on Windows 2000 Datacenter, the SQL Server 2000 Enterprise Edition can use up to 32 CPUs, up to 64GB of RAM, and be set up in a 4-node failover cluster configuration. For even larger database implementations, the 64-bit version of SQL Server can take advantage of the 16TB of RAM that is accessible by the 64-bit version of Windows 2000 running on the Intel Itanium processor.

SQL Server's Networked Architecture

SQL Server's distributed architecture splits the database access application from the database engine. SQL Server's core database server runs on a Windows-based server, which is typically connected to multiple client systems via an Ethernet local area network (LAN). The client systems are typically PCs running SQL Server client software. These PCs can be standalone desktop systems or they can be the platform for other network services, like an Internet Information Server (IIS) Web server. SQL Server supports the following client systems:
  • Windows NT/2000
  • Windows 9x
  • Remote Access Server (RAS)
  • Windows for Workgroups
  • Macintosh
  • DOS
  • OS/2
  • UNIX/Linux
Figure 1-1 illustrates the networked database access SQL Server provides. You can see that the client side of SQL Server is separate from the server portion. The SQL Server database engine runs on either a Windows NT or a Windows 9x server, but data is not generally accessed from the server system. Instead, users typically access the SQL Server database from the networked client systems. In other words, the SQL Server client component runs on each of the networked clients, while the database server component only runs on the SQL Server system.

Figure 1: SQL Server's networked architecture

The network is essential for connecting the client systems to the SQL Server system. SQL Server network connections make use of popular network topologies, such as Ethernet and Token Ring. SQL Server also provides protocol independence and is compatible with all the popular network protocols, including TCP/IP, NetBEUI, IPX/SPX, Banyan VINES, DEC Pathworks, and Apple's AppleTalk.

SQL Server's networked foundation gives it many advanced features not shared by traditional mainframe databases. Data access isn't restricted to an existing set of host database application programs. Instead, one of the primary advantages of SQL Server is its tight integration with both leading edge development tools, as well as desktop applications such as Microsoft Word, Access, and Excel. You can access a SQL Server database from custom applications built using Visual Basic, Visual C++, PowerBuilder, Delphi, SQL Windows, Visual FoxPro, and many other PC development environments. SQL Server is compatible with several data access interfaces that can be used by the popular development tools. For example, SQL Server databases can be accessed using the Microsoft JET Engine and Data Access Objects (DAO), Remote Data Objects (RDO), ActiveX Data Objects (ADO), OLE DB, ODBC, SQL Server's built-in DB-Library, and other third-party tools. For seamless desktop database access, SQL Server comes with an OLE DB driver and an ODBC driver that enable SQL Server data access from any ODBC- or OLE DB-compliant desktop application. OLE DB and ODBC database access open up the SQL Server database for ad hoc queries, data analysis, and custom reporting from hundreds of shrink-wrapped desktop applications. Desktop integration reduces the need for custom programming typically required by host environments for every new report or query. Instead, users can access database information using the desktop tool with which they are already familiar. Support for ODBC also enables SQL Server databases to be accessed by other platforms, such as the Macintosh or various UNIX systems.

Figure 1-2 provides a more detailed look at SQL Server's data access architecture. In the top portion of the figure, you can see how the various client applications use the data access application programming interfaces (APIs) SQL Server provides. SQL Server's four primary data access APIs are OLE DB, ODBC, the DB-Library, and Transact-SQL. For Windows clients, all these APIs are implemented as dynamic link libraries (DLLs) and they communicate to SQL Server via SQL Server's various client Net-Libraries. The client Net-Library uses a networked IPC (interprocess communication) method to communicate across the network to the server's network libraries residing on the SQL Server system.

Figure 2: SQL Server's data access architecture

The server's Net-Libraries receive the data packets sent from the client systems and pass them to SQL Server's Open Data Services (ODS), a server-side API that consists of a set of C++ functions and macros. SQL Server itself is an ODS application that accepts the ODS calls, processes them, and passes the results back to ODS. While it's possible to develop applications using the ODS API, this is typically only used to provide custom gateways to other database systems. The heterogeneous database support provided by the much-easier-to-use ODBC and OLE DB APIs has largely eliminated the need to develop applications using ODS.

SQL Server's Basic Components

Figure 1-3 presents a high-level view of SQL Server's four basic server components. As you saw earlier, SQL Server's Open Data Services component provides an interface between the server's Net-Libraries and the SQL Server engine (MSSQLServer).

The MSSQLServer service manages all the files that comprise the SQL Server database. It is also responsible for processing SQL statements and allocating system resources.

The SQLServerAgent service is responsible for scheduling SQL Server's jobs and alerts. In SQL Server terminology, a job is a predefined object consisting of one or more steps, where each step consists of a predefined data function like a Transact-SQL statement or batch of statements, while an alert is an action taken in response to a specific event. An alert can be set up to perform a variety of tasks, such as running jobs or sending e-mail.

The Microsoft Distributed Transaction Coordinator (MSDTC) is a transaction manager responsible for coordinating database transactions across multiple servers. MSDTC can be invoked either by the SQL Server database engine or directly from a client application.

On Windows NT/2000, these components are implemented as Services. On Windows 9x, they are implemented as standard executable programs.

Figure 3: SQL Server's high-level component hierarchy


Structured Query Language (SQL) is a high-level language originally developed by IBM to provide access to the data contained in relational databases. Since its origin, SQL has been widely adopted and now almost all modern databases can be accessed using SQL. With its widespread adoption, the SQL language has been standardized by the American National Standards Institute (ANSI-www.ansi.com). SQL Server uses a dialect of SQL called Transact-SQL. Transact-SQL is compliant with most ANSI SQL standards, but it also provides several extensions and enhancements. For instance, Transact-SQL contains several flow-control keywords that facilitate its use for developing stored procedures and triggers.

Transact-SQL is commonly used for database management tasks, such as creating and dropping tables and columns, as well as for writing triggers and stored procedures. You can also use Transact-SQL to change SQL Server's configuration or you can even use it interactively with SQL Server's Graphical Query Analyzer utility to perform ad hoc queries. Transact-SQL provides three categories of SQL support: Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL). SQL DDL commands are used for database management tasks, such as creating tables and views. SQL DML commands are used to query and update the data contained in the database. SQL DCL commands are used to control the database operations. Chapters 2 and 3 provide more detailed information on DDL and DC, while Chapters 5 and 6 provide more detailed information about using DML.

SQL Server Database Architecture

Now that you've had a high-level look at SQL Server's basic components, let's look more closely at the database architecture. Figure 1-4 presents SQL Server 2000's database architecture.

Figure 4: SQL Server 2000 database architecture...

Read More Show Less

Table of Contents

Acknowledgments xxiii
Introduction xxv
Part I Mastering Transact-SQL
1 An Overview of SQL Server 2000 3
SQL Server's Networked Architecture 4
SQL Server's Basic Components 8
Transact-SQL 9
SQL Server Database Architecture 9
Administrative Components 22
A Brief Development History of SQL Server 27
What's New with SQL Server 2000 28
Conclusion 33
2 Data Definition Language Basics 35
Introduction to SQL 36
Creating a Database 46
Creating a Table 50
Creating a View 70
Re-creating a View 78
Creating an Index 78
Dropping Databases, Tables, Views, and Indexes 80
The SQL Catalog and Information Schema 80
DDL Coding Suggestions 83
Conclusion 86
Additional Resources 86
3 Advanced Database Definition 87
Specifying Database File Locations and Sizes 88
Altering a Database 90
Defining Filegroups 90
Cascading Foreign Key Constraints 93
More View Examples 95
Indexes Over Computed Columns and Views 106
Using the Query Analyzer 109
Adding Extended Object Properties 117
Conclusion 120
4 Data Manipulation Language Basics 121
Introduction to DML 122
Retrieving Rows with the Select Statement 123
Using DML to Modify Table Data 154
DML Coding Suggestions 160
Conclusion 161
5 Advanced DML Statement Features 163
Complex Select Statements 164
Performance Hints 202
Concurrent Access and Resource Locks 203
DML Coding Suggestions 211
Conclusion 211
6 Writing Stored Procedures, Triggers, and Functions 213
Stored Procedures 214
Triggers 232
User-Defined Functions 245
Debugging Stored Procedures, Triggers, and User-Defined Functions 250
Conclusion 253
7 Developing Extended Stored Procedures 255
Extended Stored Procedures 256
OLE Automation Stored Procedures 276
Conclusion 283
Additional References 283
Part II Windows Applications
8 Using ADO 287
OLE DB and Universal Data Access 288
OLE DB Architecture Overview 288
ADO (ActiveX Data Objects) 291
OLE DB and ADO Files 293
ADO Architecture 293
Adding the ADO Reference to Visual Basic 295
Using ADO Objects with Visual Basic 296
Advanced Database Functions Using ADO 352
Conclusion 380
Additional References 380
9 Using the ODBC API 381
ODBC Architecture 383
Configuring an ODBC Data Source 385
Conclusion 426
Additional References 426
10 Using DAO 427
DAO Files 429
DAO Architecture 430
An Overview of Using DAO 432
Adding the DAO Reference to Visual Basic 432
Using DAO Objects with Visual Basic 433
General Performance Tips for DAO and ODBC 488
Conclusion 489
Additional References 489
11 Using ODBCDirect 491
ODBCDirect Files 493
ODBCDirect Architecture 493
Adding the DAO Reference to Visual Basic 494
An Overview of Using ODBCDirect 495
Creating the DBEngine Object 496
Creating an ODBCDirect Workspace Object 496
Ending the DBEngine Object 497
Connecting to SQL Server 497
Retrieving Data with ODBCDirect 501
Modifying Data with ODBCDirect 513
Advanced Database Functions Using ODBCDirect 521
Conclusion 528
Additional References 529
12 Using RDO 531
RDO Files 534
RDO Architecture 534
An Overview of Using RDO 535
Adding RDO 2.0 Objects to Visual Basic 536
Using the RDO Objects with Visual Basic 537
Advanced Topics 563
Conclusion 570
Additional References 571
Part III Web Applications
13 Using the Web Wizard 575
Web Integration Using the Web Assistant Wizard 576
Conclusion 591
14 Using ASP and ADO 593
Web and Database Integration Using ASP 594
The ASP Object Model 597
ASP Basics 599
Using ADO Objects with ASP 601
Advanced ADO-based Web Building Techniques 641
Conclusion 649
Additional References 650
15 Using XML 651
XML Basics 653
SQL Server 2000's Support for XML 655
Using XML Streams and ADO 684
Conclusion 692
Additional Resources 692
Part IV Management and Analysis Applications
16 Using SQL-DMO 695
Distributed Management Framework 696
SQL-DMO Files 705
Using SQL-DMO 705
Building the SQL-DMO Sample Application 713
Conclusion 734
17 Developing Applications Using DTS 735
An Overview of DTS 736
Creating DTS Packages with the DTS Designer 742
DTS Command Line Options 760
Programming with DTS 762
DTS Architecture 762
Conclusion 781
Additional References 781
18 Full-Text Search and English Query 783
Full-Text Catalogs and Searches 784
English Query 794
Conclusion 831
Part V Administration for the Developer
19 Managing Servers 835
Overview of a SQL Server Installation 836
Setting Enterprise Manager Properties 847
Registering and Unregistering a Server 849
Stopping, Starting, Pausing, and Continuing a Server 854
Setting Server Properties 855
Server Logins and Roles 874
Working with SQL Server Agent 875
Displaying Event Logs 891
Viewing and Creating SQL Server Messages 892
Working with Linked Servers 894
Other Enterprise Manager Facilities 897
Conclusion 897
20 Managing Databases 899
Managing Databases and Database Objects 900
Copy Database Wizard 927
Conclusion 933
21 SQL Server Security 935
Security Basics 936
Setting Up Database Users 947
Managing SQL Server Database Access 977
Using Stored Procedures 980
Permissions 984
The Grant, Revoke, and Deny SQL Statements 993
Role Permissions 998
Stored Procedures and Permissions 999
Ownership Chain 1000
Database Security Principles and Guidelines 1000
Conclusion 1002
22 Backup and Recovery 1003
Why Back Up the Database? 1004
Backing Up Database Objects 1005
Database Recovery 1025
Backup and Recovery Server Options 1035
Using a Standby Server 1036
Conclusion 1036
23 Database Performance Troubleshooting 1037
Selecting the Appropriate Tool 1039
SQL Profiler 1042
Windows 2000 Server System Monitor 1073
Enterprise Manager Current Activity Categories 1096
Index Tuning Wizard 1099
Showplan 1106
Other Performance Tools 1114
SQL Statement Hints 1118
Hardware Considerations 1125
Conclusion 1126
Part VI Relational Database Concepts
24 Introduction to the Relational Database Model 1129
Background: Database Management Systems 1130
The Relational Database Model 1135
Conclusion 1157
Additional Resources 1158
25 Introduction to Entity Relationship Diagrams (ERDs) 1159
ERDs--A Design Tool 1160
Basic ERD Concepts and Symbols 1160
Adding Properties to ERDs 1163
Multivalued Properties 1165
Representing Associations 1166
Exclusive Relationships 1167
Entity Subtypes 1168
When to Use ERDs 1169
SQL Server Database Diagrams 1171
Conclusion 1178
Additional Resources 1179
Part VII Appendixes
A Maximum Capacities 1183
System Capacities 1184
Database Capacities 1184
SQL Capacities 1185
B Creating and Editing UDL Files 1187
Creating UDL Files on Windows 9x and NT Using Explorer 1190
Creating UDL Files on Windows 2000 Using Explorer 1191
Creating UDL Files Using the Data Link API 1192
Conclusion 1195
Index 1197
Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)