Cover image for Microsoft SQL Server 2000 programming by example
Microsoft SQL Server 2000 programming by example
Guerrero Guerrero, Fernando.
Publication Information:
Indianapolis, Ind. : Que, [2001]

Physical Description:
xvii, 771 pages : illustrations ; 24 cm + 1 computer optical disc (4 3/4 in.)
Title Subject:
Format :


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

On Order



SQL Server 2000 Programming by Example provides clear and concise examples of basic programming techniques used in SQL Server 2000 primarily using Transact-SQL. Even if you're a complete novice to SQL Server 2000 programming, you will benefit by reading through the book's logical process. SQL Server 2000 is introduced and explored, database structure is designed and built, and finally a variety of programming techniques are studied and reviewed. If you are already familiar with SQL Server 2000 programming, you may use the book as a quick reference guide that provides plenty of pertinent examples.

Author Notes

Carlos E. Rojas is the Database Administrator at Planeta Networks, in Miami, Florida. Prior to this DBA role, he was a consultant and trainer with Manapro in Caracas, Venezuela. He has participated as a speaker in various technet conferences in Venezuela.

Carlos has experience in developing Internet (n-tier) applications using Microsoft technologies, and implementing and administering SQL Server databases.

He is a Most Valuable Professional (MVP), Microsoft Certified Systems Engineer + Internet (MCSE+I), Microsoft Certified Database Administrator (MCDBA), and Microsoft Certified Sales Specialist (MCSS). He is also a voting member and volunteer of PASS, the professional association for SQL Server.

Fernando G. Guerrero is a Principal Technologist and SQL Server Product Consultant for QA Group Ltd., U.K. His main role involves IT training, course development, and internal mentoring. Fernando writes for SQL Server Magazine .

He has also presented a session on SQL Server 2000 at TechEd 2000 Europe, and has been accepted to speak at PASS, VBUG, Technology Week, VSLive, and SQL2THEMAX conferences during the year 2001.

He holds seven Microsoft Professional Certifications including MCSE+Internet, MCSD, MCDBA, and MCT, and has been awarded with the MVP (Most Valuable Professional) status on SQL Server.

Table of Contents

Introductionp. 1
1 Relational Database Management Systems and SQL Serverp. 7
Database Modelsp. 8
The Relational Modelp. 8
A Brief History of SQL Serverp. 10
Basics of SQL Server Architecturep. 12
Server Componentsp. 14
Client Toolsp. 16
Database Components (Objects)p. 28
Security Modelp. 29
Client/Server Applications Designp. 54
2 Elements of Transact-SQLp. 59
Data Definition Language (DDL)p. 60
Rules for Identifiersp. 62
Transact-SQL Programming Conventionsp. 64
Data Manipulation Language (DML)p. 65
Data Control Language (DCL)p. 66
Data Typesp. 69
Creating Customized Data Types: User-Defined Data Typesp. 71
Data Type Selection Criteriap. 73
Additional Elementsp. 76
Variablesp. 76
Operatorsp. 78
Control of Flow Statementsp. 84
Commentsp. 87
Programming Scripts and Batchesp. 88
The GO Statementp. 89
3 Working with Tables and Viewsp. 91
Creating and Altering Tablesp. 92
Types of Tablesp. 93
Creating Tablesp. 97
Altering a Table's Definitionp. 105
Creating and Altering Viewsp. 107
Benefits of Viewsp. 108
Creating and Dropping Viewsp. 109
Altering a View's Definitionp. 117
4 Querying and Modifying Datap. 121
Querying Datap. 122
The SELECT Statementp. 122
The FROM Clausep. 127
The WHERE Clausep. 130
Data Aggregation and the GROUP BY Clausep. 136
The ORDER BY Clausep. 141
The TOP N Clausep. 143
Using Dynamic Queriesp. 146
Modifying Datap. 148
The INSERT Statementp. 148
The DELETE Statementp. 152
The UPDATE Statementp. 153
The SELECT INTO Statementp. 155
5 Querying Multiple Tables: JOINSp. 159
ANSI SQL-92 Syntaxp. 160
Self Joinsp. 181
The UNION Operatorp. 182
6 Optimizing Access to Data: Indexesp. 187
Introduction to Indexesp. 188
Benefits of Indexesp. 189
Using Indexes on Point Queriesp. 190
Using Indexes in Range Queriesp. 191
Using Indexes to Search for Foreign Key Values to Solve Join Operationsp. 193
Using Indexes to Speed Up the Execution of Hash and Merge JOIN Operationsp. 194
Using Indexes That Cover a Queryp. 195
Using an Index to Enforce Uniquenessp. 196
Using Indexes to Help Produce Ordered Outputp. 197
How to Create Indexesp. 198
How SQL Server 2000 Stores Datap. 200
How SQL Server 2000 Modifies Datap. 203
Index Enhancements in SQL Server 2000p. 203
Accessing Data Without Indexes: Table Scanp. 204
Types of Indexesp. 205
Clustered Indexesp. 207
Nonclustered Indexesp. 211
Covered Queries and Index Intersectionp. 215
Index Maintenancep. 218
Rebuilding Indexesp. 219
Index Fragmentationp. 221
Index Statisticsp. 222
Getting Information About Indexesp. 226
Indexes on Computed Columnsp. 227
Indexed Viewsp. 229
Index Tuning Wizardp. 232
Summaryp. 237
7 Enforcing Data Integrityp. 239
Types of Data Integrityp. 240
Domain Integrityp. 240
Entity Integrityp. 241
Referential Integrityp. 241
User-Defined Integrityp. 242
Enforcing Integrity: Constraints (Declarative Data Integrity)p. 242
Primary Keysp. 242
Unique Constraintsp. 249
Check Constraintsp. 254
Default Definitionsp. 264
Foreign Keysp. 270
Cascading Operations: Cascaded Declarative Referential Integrityp. 276
Transact-SQL-Specific Integrity Structuresp. 284
8 Implementing Business Logic: Programming Stored Proceduresp. 295
Benefits of Using Stored Proceduresp. 296
Types of Stored Proceduresp. 297
System Stored Proceduresp. 297
User-Defined Stored Proceduresp. 298
Temporary Stored Proceduresp. 300
Extended Stored Proceduresp. 301
Creating and Dropping Stored Proceduresp. 301
Using Parametersp. 305
Altering Stored Procedure Definitionsp. 308
The Return Statementp. 310
Executing Stored Proceduresp. 311
Using Query Analyzer's Object Browser to Execute Stored Proceduresp. 317
Stored Procedure Recompilationp. 318
Handling Errorsp. 320
Nesting Stored Proceduresp. 322
Application Security Using Stored Proceduresp. 325
9 Implementing Complex Processing Logic: Programming Triggersp. 329
Benefits of Triggersp. 330
Using Triggers to Enforce Complex Domain Integrityp. 330
Using Triggers to Maintain Denormalized Datap. 333
Trigger Enhancements in SQL Server 2000p. 342
Inserted and Deleted Tablesp. 343
Types of Triggers According to Their Orderp. 351
Instead of Triggersp. 351
After Triggersp. 361
Creating and Dropping Triggersp. 361
Deciding the Order of Executionp. 364
Checking for Updates on Specific Columnsp. 366
Multiple-Row Considerationsp. 367
Altering Trigger Definitionsp. 371
Disabling Triggersp. 372
Nesting Triggersp. 373
Recursive Triggersp. 382
Security Implications of Using Triggersp. 385
Enforcing Business Rules: Choosing Among Instead of Triggers, Constraints, and After Triggersp. 385
10 Enhancing Business Logic: User-Defined Functions (UDF)p. 389
Benefits of User-Defined Functionsp. 390
Built-In User-Defined Functionsp. 391
Types of User-Defined Functions According to Their Return Valuep. 398
Scalar Functionsp. 398
Inline Table-Valued User-Defined Functionsp. 426
Table-Valued Functionsp. 437
Dropping User-Defined Functionsp. 448
Preventing the Alteration of Dependent Objects: The Schemabinding Optionp. 448
Deterministic and Nondeterministic Functionsp. 453
Altering User-Defined Functions Definitionp. 454
Security Implications of Using User-Defined Functionsp. 455
Applying User-Defined Functionsp. 456
Converting Stored Procedures into User-Defined Functionsp. 456
Converting Views into User-Defined Functionsp. 459
Using User-Defined Functions in Constraintsp. 460
11 Using Complex Queries and Statementsp. 463
Subqueriesp. 464
Scalar Subqueriesp. 469
List Subqueriesp. 473
Array Subqueriesp. 477
Correlated Subqueriesp. 484
Derived Tablesp. 488
The Case Functionp. 489
The Compute Clausep. 493
The Cube and Rollup Operatorsp. 498
Using Hintsp. 502
12 Row-Oriented Processing: Using Cursorsp. 507
Row-by-Row Versus Set-Oriented Processingp. 508
Types of Cursorsp. 516
Forward-Onlyp. 517
Staticp. 517
Dynamicp. 521
Keyset-Driven Cursorsp. 522
Steps to Use Cursorsp. 524
Declaring Cursorsp. 524
Opening Cursorsp. 529
Fetching Rowsp. 531
Closing Cursorsp. 540
Deallocating Cursorsp. 540
Scope of Cursorsp. 540
Local Cursorsp. 541
Global Cursorsp. 541
Using Cursor Variablesp. 542
Using Cursors to Solve Multirow Actions in Triggersp. 546
Application Cursorsp. 548
13 Maintaining Data Consistency: Transactions and Locksp. 553
Characteristics of Transactions (ACID)p. 554
Using Transactionsp. 555
Begin Tranp. 558
Commit Tranp. 561
Rollback Tranp. 564
Using Implicit Transactionsp. 568
Transactions and Runtime Errorsp. 570
Concurrency Problemsp. 574
Lost Updatesp. 575
Uncommitted Dependency (Dirty Read)p. 576
Inconsistent Analysis (Nonrepeatable Read)p. 578
Phantom Readsp. 580
Isolation Levelsp. 582
Read Committedp. 583
Read Uncommittedp. 585
Repeatable Readp. 586
Serializablep. 587
Types of Locksp. 588
Shared Locksp. 594
Exclusive Locksp. 598
Update Locksp. 601
Intent Locksp. 604
Schema Locksp. 606
Key-Range Locksp. 608
A Serious Problem to Avoid: Deadlocksp. 611
14 Transferring Data to and from SQL Serverp. 617
The Need for Transferring Datap. 618
Tools for Transferring Data Using SQL Server 2000p. 620
The Bulk Insert Statement and bcpp. 621
Using the bcp Command-Line Utilityp. 623
Using the Bulk Insert Statementp. 628
Using Data Transformation Servicesp. 640
Transfer Objects Between Two SQL Server 2000 Databasesp. 641
Export a SQL Server Table to an Access Databasep. 648
The Copy Database Wizardp. 656
15 Working with Heterogeneous Environments: Setting Up Linked Serversp. 665
Distributed Queriesp. 666
Ad Hoc Queriesp. 666
Linked Serversp. 681
Distributed Transactionsp. 695
A Using SQL Server Instancesp. 703
Installing SQL Server Instancesp. 704
Connecting to Instancesp. 713
System Functions Used in Multi-Instance Installationsp. 717
Current Limitationsp. 718
B Using SQL Query Analyzerp. 721
Installing SQL Query Analyzerp. 722
The Query Analyzer Workplacep. 723
The Editor Panep. 724
The Object Browserp. 726
The Object Searchp. 728
The Results Panep. 729
Managing User and Connection Optionsp. 731
Customizing SQL Query Analyzerp. 731
Connection Settingsp. 735
Defining and Using Templates for Query Analyzerp. 738
Analyzing Queriesp. 740
Obtaining Information About Query Executionp. 740
Analyzing Query Execution Planp. 742
Managing Indexes from Query Analyzerp. 743
Working with the Transact-SQL Debuggerp. 744
Summaryp. 747
Indexp. 749