Cover image for Microsoft SQL Server 7 secrets
Microsoft SQL Server 7 secrets
Rensin, David K.
Personal Author:
Publication Information:
Foster City, CA : IDG Books Worldwide, [1999]

Physical Description:
xxvi, 714 pages : illustrations ; 24 cm + 1 computer optical disc (4 3/4 in.
General Note:
Includes index.

Title Subject:
Format :


Call Number
Material Type
Home Location
Item Holds
QA76.9.D3 R456 1999 Book and Software Set Central Closed Stacks

On Order



This package provides experienced database professionals and system managers with the hardcore details they need to tape the power of Microsoft's SQL Server 7. It is loaded with tips and tricks, focusing on the newest and most powerful product features, as well as its most challenging problems.

Author Notes

David K. Rensin is the chief technology officer for Riverbed Technologies. A regular contributor to Pen Computing magazine, he has also written for other prominent industry publications.
Andrew M. Fedorchek manages the Distribute Web Architectures Practice of Noblestar Systems, speaks nationally on SQL Server at TechNet, and has cowritten several other books on Microsoft technologies.
William C. Amo is a database consultant specializing in Microsoft SQL Server and the author of Transact-SQL: Programming Microsoft SQL Server 7.0.

Table of Contents

Prefacep. vii
Acknowledgmentsp. x
Part I Getting Startedp. 1
Chapter 1 Choices: Hardware, OS, and SQL Serverp. 3
Server Requirementsp. 3
Buying and Configuring a Drive Arrayp. 12
Configuring Windows NTp. 16
Chapter 2 Installing and Upgrading SQL Serverp. 21
Overview of SQL Server 7 Installation Featuresp. 21
What Do I Need to Install SQL Server 7?p. 23
SQL Server 7 Editions and Installation Typesp. 25
Fast and Easy Installation on Windows 9xp. 26
Fast and Easy Installation on Windows NTp. 33
Customizing SQL Server Installationsp. 42
Problems and Solutionsp. 48
Unattended and Remote Installation of SQL Serverp. 48
The Easiest SQL Server Upgrade Yetp. 49
Uninstalling SQL Server 7p. 52
Chapter 3 Basic Server Setupp. 55
Choosing Configuration Optionsp. 55
SQL-Based Configuration with sp_configurep. 63
Windows NT Configuration for SQL Serverp. 74
Part II Administrative Interfaces and Database Development Toolsp. 79
Chapter 4 SQL Server Query Analyzerp. 81
Chapter 5 Microsoft English Queryp. 95
The Components of the English Query Environmentp. 95
A Tour of the English Query Domain Editorp. 99
Using the Test Application Toolp. 110
Implementing Your English Query Projectp. 114
Using the Question Builderp. 117
Part III Server Administrationp. 121
Chapter 6 SQL Mail, Alerts, Jobs, and Database Maintenancep. 123
Understanding SQL Agent Terminologyp. 123
Setting Up SQL Mailp. 124
Setting Up Alertsp. 133
Using Jobsp. 142
Database Maintenancep. 147
Chapter 7 Securityp. 159
Logins and Authenticationp. 159
Usersp. 167
User Roles, NT Groups, and Fixed Rolesp. 167
User Rolesp. 168
Predefined Rolesp. 168
Application Rolesp. 170
Grant, Deny, and Revokep. 170
Permissionsp. 171
Ownership Chainsp. 174
Grants on System Tablesp. 175
Security and the Internetp. 178
What About Setuser and Aliases?p. 179
Part IV SQLp. 181
Chapter 8 Introduction to Transact-SQLp. 183
About SQLp. 183
Where to Edit and Run Transact-SQLp. 184
How Transact-SQL Is Structuredp. 185
Getting Started with Queryingp. 185
Running a Query: The Select Statementp. 186
The Where Clause: Limiting Your Resultsp. 195
Computed and Renamed Columnsp. 199
Group By and Havingp. 202
Chapter 9 More SQL!p. 209
Selected Again!p. 209
Bitwise Operatorsp. 225
Controlling the Flow of Executionp. 226
Transactionsp. 231
Chapter 10 Transact-SQL versus ANSI SQL-92p. 237
A Quick Historyp. 238
Data Typesp. 239
A Different View of the Worldp. 241
Table Column Alterationsp. 243
Data Retrieval Differencesp. 244
Where Transact-SQL Beats SQL-92p. 247
Beyond SQL-92: SQL3p. 247
Medians in Transact-SQLp. 249
Part V Databases and Objectsp. 253
Chapter 11 Creating and Managing Databasesp. 255
Database Architecturep. 255
File Storagep. 255
Designing Files for Your Databasep. 256
Creating Databasesp. 257
Altering Databases and Adding Filegroupsp. 259
Compressing Your Databasep. 259
Chapter 12 Database Designp. 261
Using the Relational Modelp. 262
Normalizationp. 272
Integrity Constraintsp. 274
Referential Integrityp. 283
Chapter 13 Tables, Views, and Indexesp. 291
Tables: A Formal Definitionp. 291
The Basicsp. 292
The Not-So-Basicsp. 300
Viewsp. 310
Indexesp. 315
Chapter 14 Triggersp. 319
The Client/Server Modelp. 319
Beyond Client/Server: Three-Tier Partitioningp. 320
Triggersp. 322
Part VI SQL Server Programmingp. 337
Chapter 15 Stored Proceduresp. 339
Who Should Read This Chapterp. 339
What Is a Stored Procedure and Why Do I Care?p. 340
Application Partitioning with Stored Procedures and Why It Matters to Youp. 340
Running a Stored Procedurep. 342
Creating Your Own Stored Procedures with Create Procedurep. 342
Delayed Name Resolutionp. 356
Chapter 16 Transact-SQL and OLEp. 359
Transact-SQL Support for OLEp. 359
Destroying Your Object and Stopping the Shared OLE Execution Environmentp. 364
Examplesp. 365
Chapter 17 Creating Extended Stored Proceduresp. 373
What Is an Extended Stored Procedure?p. 373
How to Install an Extended Stored Procedurep. 373
How XPs Workp. 374
Open Data Services (ODS)p. 374
Creating XPs with ODSp. 374
ODSTest.dllp. 391
Chapter 18 Programming with SQL-DMOp. 401
What Are the SQL Distributed Management Objects (SQL-DMO)?p. 402
Getting Startedp. 402
Executing Queries and Examining Result Setsp. 406
Extending SQL Server with SQL-DMOp. 410
SPBrowser Source Codep. 413
Chapter 19 Practical SQL-DMO--The Secrets Snap-In Controlp. 421
What the Snap-In Control Doesp. 422
Registering the Controlp. 422
Installing the Snap-Inp. 422
The Basic Construction of the Controlp. 424
A Narrative of the Codep. 424
CSnapInDlg--The Heart of the Controlp. 431
Executing Your SQL--CSQLWindowDlgp. 437
Part VII SQL Server under the Hoodp. 441
Chapter 20 SQL Server System Tables and Proceduresp. 443
Preliminary Observationsp. 444
Essential System Table Knowledgep. 451
The Rest of the Database Catalogp. 461
The System Catalogp. 467
Chapter 21 Locks and Transactionsp. 475
Transaction Modesp. 476
The Transaction Logp. 476
Nesting Transactionsp. 477
DBCC Opentranp. 478
Distributed Transactionsp. 478
Lockingp. 482
Chapter 22 Internal Data Storage Structurep. 493
Files and Directories Usedp. 493
B-Tree Indexesp. 512
Distribution Statisticsp. 514
Lockingp. 516
Chapter 23 Tuning for SQL Server Optimizerp. 521
Statistics Used by the Optimizerp. 522
Viewing the Execution Planp. 527
Using Optimizer Hintsp. 532
Parallel Queriesp. 534
A Troubleshooter's Checklistp. 535
Tools to Help You Track Down the Problemp. 537
Chapter 24 Peeking under the Covers with DBCCp. 543
DBCC in Generalp. 544
Finding Flawsp. 548
Part VIII Transferring Datap. 567
Chapter 25 The Bulk Copy Programp. 569
Who Can Run BCP?p. 570
Where to Run BCPp. 570
To Log or Not to Log, That Is the Questionp. 571
Nuts and Boltsp. 571
BCP a la Cartep. 572
Format Files: A Descriptionp. 576
Format Files: Some Examplesp. 579
Using Temporary Tablesp. 584
Empty Trailing Fieldsp. 585
Chapter 26 Scripting and Transferring Databasesp. 587
What Is a SQL Script File?p. 587
Schema Scripts with SQL Server Enterprise Managerp. 587
Transact-SQL Scriptsp. 589
Data Transformation Servicesp. 590
Part IX Replicationp. 601
Chapter 27 Replication Overviewp. 603
Why Bother with Multiple Database Servers?p. 604
What Is Replication?p. 605
The Basic Replication Paradigmp. 606
Replication Typesp. 609
What Really Happens: An Overviewp. 613
Customizing Replicationp. 615
Database Design Considerationsp. 622
Replication Topology Designp. 625
Chapter 28 Setting Up Replicationp. 629
Installing a Distribution Databasep. 629
Setting Up Subscribers and Publishing Databasesp. 633
Setting Up the Subscription Databasep. 636
Defining Publications and Articlesp. 638
Publication Permissions for DBOsp. 645
Chapter 29 Monitoring and Administering Replicationp. 647
Using Some SQL to Save Workp. 647
Building Scripts from Your Publishing Databasesp. 650
Using Your Own Replication Stored Proceduresp. 652
Working around the Foreign Key Problemp. 655
Monitoring Replication and Changing Monitoring Propertiesp. 656
Troubleshooting Replicationp. 657
Uninstalling Publishingp. 661
Chapter 30 Replication to Heterogeneous (ODBC) Databasesp. 663
The Rules of Engagementp. 663
How to Replicate to ODBC Data Sourcesp. 664
Replication to an Unsupported RDMS: SQL Server 4.21p. 671
Replicating from Heterogeneous Databasesp. 677
Appendix: What's on The CD-ROMp. 679
Indexp. 681
End-User License Agreementp. 715
CD-ROM Installation Instructionsp. 718