Introduction p. 1 Why Read This Book? p. 1 What Will I Learn from This Book? p. 2 Conventions Used in This Book p. 3 Advanced Programming Techniques

(1)

Introduction p. 1

Why Read This Book? p. 1

What Will I Learn from This Book? p. 2

Conventions Used in This Book p. 3

Advanced Programming Techniques p. 5

Taking Advantage of the Tools p. 7

Microsoft Management Console p. 8

Managing SQL Server with Enterprise Manager p. 9

Querying SQL Server with Query Analyzer p. 15

Summary p. 19

Beyond the Basics of Data Manipulation Language p. 21

Naming Conventions p. 22

Using Subqueries and Inline Views p. 27

Outer Joins, Cross Joins, and Self-Joins p. 37

More Advanced DELETE and UPDATE Statements p. 45

Aggregate Functions p. 49

The GROUP BY and HAVING Clauses p. 53

Unions p. 54

Summary p. 54

Optimizing Queries p. 57

A Closer Look at Index Structures p. 58

Indexes, Transactions, and Data Manipulation p. 73

How Transactions and Indexes Affect Locking p. 75

Optimizing the Query Optimizer p. 79

Using the SQL Server Profiler p. 84

Summary p. 89

Advanced Transact-SQL Statements p. 91

Using CAST and CONVERT p. 92

Printing Information p. 93

The sysmessages System Table p. 96

Purposely Causing Errors p. 100

The CASE Expression p. 102

Distributed Transactions p. 109

Summary p. 121

Effective Use of Built-in Functions p. 123

Making Computations Work with Mathematical Functions p. 124

Calculating Dates with Date Functions p. 132

Using String Functions to Manipulate Strings p. 137

Using Text and Image Functions in SQL p. 144

Using SQL Functions to Retrieve System Information p. 145

(2)

Using Cursors p. 151

Cursor Declaration p. 152

Rules of Cursor Use p. 155

Opening, Closing, and Moving Through Cursors p. 156

Some Advanced Cursor Uses p. 161

Using Cursors to Modify Data p. 172

Summary p. 175

Stored Procedures p. 177

Why Use Procedures? p. 178

System Stored Procedures p. 178

Extended Stored Procedures p. 184

Creating Your Own Stored Procedures p. 186

Integrating Messages into Stored Procedures p. 191

Advanced Uses of Stored Procedures p. 193

Summary p. 203

Advanced String Manipulation and Bitwise Operators p. 205

Introduction p. 206

Advanced String Manipulation to Spell Currency p. 206

Binary Operators p. 219

Summary p. 225

Dynamic Execution p. 227

What Is Dynamic Execution? p. 228

Creating the Executable String p. 230

Using Cursors with Dynamic Execution p. 242

The Pros and Cons of Dynamic Execution p. 245

Summary p. 249

Security Issues p. 251

Implementing an Authentication Mode p. 252

Assigning Logins to Users and Roles p. 258

Assigning Permissions to Users and Roles p. 264

Planning Security p. 268

Managing Application Security p. 269

Summary p. 271

Specialized Triggers p. 273

Nesting, Recursion, and Trigger Basics p. 274

How to Use Triggers to Enforce Business Rules p. 284

Auditing Triggers p. 289

Storing System Information p. 290

Referential Integrity with Triggers p. 294

Cascade Delete Triggers p. 297

(3)

Summary p. 303

Updating Table Indexes and Statistics p. 305

Index Performance and Fine-Tuning p. 306

Creating a Utility to Optimize Table Indexes p. 309

Verifying When the Last Optimization Took Place p. 311

Running Tasks Automatically p. 312

Using DBCC Statements to Monitor Your Database p. 317

Summary p. 331

Crosstabulation p. 333

A Description of Crosstabulation p. 334

Verifying Object Existence p. 338

Checking Column Security p. 344

Generating the Column Header List p. 347

Generating Cross-tabbed Reports p. 352

Summary p. 362

SQL Server - Essential Information p. 363

Writing Effective Code p. 365

Simple, Effective Code Is Elegant Code p. 366

Modularity of Stored Procedures p. 388

Summary p. 389

Using Multiple Tiers and Client/Server Architecture p. 391

Understanding Client/Server Architectures p. 392

Evolution of Client/Server Computing Architectures p. 394

Web Browswers, Web Servers, and the Client/Server Model p. 400

Microsoft's Three-Tier Services Model p. 404

N-Tier Applications Using RAD Development Tools p. 406

Scalability and SQL Server p. 413

Summary p. 415

Replication p. 417

Replication Concepts for Programmers p. 419

Advanced Replication p. 429

Summary p. 440

Migrating to SQL Server: Data Conversion and Integration p. 441

Outgrowing Access p. 443

Determining the Need to Move from Access to SQL Server p. 444

Making the Move from Access to SQL Server p. 453

Summary p. 461

Making the Switch from Sybase SQL Server p. 463

Starting with 7.0, the Roads Diverge p. 464

Looking Forward p. 464

(4)

Differences Between Microsoft SQL 7.0 and Sybase Adaptive Server p. 466

Summary p. 483

Optimizing ODBC Using Visual C++ p. 485

ODBC Architecture p. 486

Database Abstraction p. 488

Why Use ODBC? p. 488

Security Issues in ODBC p. 490

Methods of Connecting with ODBC p. 492

Call-Level Interface p. 505

Simplifying ODBC with Microsoft Foundation Classes p. 549

Connecting to SQL Server from Visual Basic p. 563

Developing Efficient Visual Basic Applications for SQL Server p. 564

Using Open Database Connectivity (ODBC) p. 571

Data Access Objects p. 581

Remote Data Objects (RDO) p. 592

Summary p. 604

Using SQL-DMO via Visual Basic to Manage SQL Server p. 605

DMO Concepts p. 606

Implementing SQL-DMO Objects p. 607

Using SQL-DMO to Manage Databases p. 609

Using SQL-DMO to Manage Servers p. 610

Exploring the SQL-DMO Object Model p. 611

Common Practical Uses of SQL-DMO p. 626

Building Database Management Tools p. 631

Summary p. 632

Automating Web Publication of SQL Server Data p. 633

Components of the Web Assistant p. 634

Using the Web Assistant Wizard p. 635

Using xp_makewebtask and sp_makewebtask p. 636

Refining Web Pages with sp_makewebtask and xp_makewebtask p. 638 Automating Web Publishing with Job-Related Parameters p. 654

Summary p. 660

SQL Server, ADO, and the Web p. 661

The Evolution of Data Access p. 662

The ADO Object Model p. 662

The Connection Object p. 664

Using Stored Procedures with the Command Object p. 670

The Recordset Object p. 672

RDS: Moving ADO to the Browser p. 681

Summary p. 684

(5)

DCOM Architecture p. 686

Common Techniques p. 698

Summary p. 710

Why the Future Is Microsoft Transaction Server p. 713

Using Three-Tiered Architecture with the Internet p. 714

Understanding MTS p. 716

Programming MTS Transactions p. 718

Deploying MTS Components p. 721

The Importance of Stateless Environments p. 722

Maximizing Performance with MTS p. 728

Other Development Clients and SQL Server Connectivity p. 731

Delphi's Database Models p. 732

Programming Databases in Delphi p. 732

PowerBuilder and Microsoft SQL Server p. 741

Delphi Versus PowerBuilder: Which Should I Use? p. 760

Integrating SQL with Other Microsoft Products p. 761

Using Access as a Front-End to SQL Server 7.0 p. 762

Using Excel with SQL Server 7 p. 769

Using SQL Server with Internet Information Server and Internet Explorer p. 771

Integration with Microsoft Transaction Server p. 772

Summary p. 779

Scheduling Jobs and Using ActiveX Servers to Implement Business Solutions p. 781

Using the SQL Server Agent p. 782

Implementing Jobs p. 783

T-SQL Jobs p. 784

Running ActivX Servers Through VBScript Jobs p. 789

Putting It All Together p. 791

Summary p. 792

Programming a Line-of-Business Solution p. 793

Infrastructure p. 794

Source Code Management p. 794

Database Requirements p. 795

Design and Development p. 796

Programming for Performance p. 798

Testing the Database Application p. 801

Modifying Third-Party SQL Server Applications p. 802

Summary p. 803

Data Warehousing and Online Transaction Processing (OLTP) p. 805

SQL Server as a Data Warehouse p. 806

(6)

How to Scrub (Prepare) Data for Warehouses p. 819

Large Queries and Performance Optimization p. 832

Warehouse Maintenance Tasks p. 837

SQL Server and OLTP Systems p. 838

Optimizing SQL Server as an OLTP System p. 851

Monitoring Performance p. 853

Summary p. 854

Appendixes p. 855

The System Tables of the Master Database p. 857

Login Tables p. 858

Database Tables p. 861

Systemwide Tables p. 866

Special Tables p. 873

The System Tables of All Databases p. 875

Object Tables p. 876

User, Group, and Security Tables p. 888

Replication Tables p. 891

Common Error Messages p. 899

Error Message #103: Severity Level 15 p. 900

(7)

(8)