Microsoft SQL Server 2000 Programming by Example

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.

Introduction.


1. Relational Database Management Systems and SQL Server.


Database Models. A Brief History of SQL Server. Basics of SQL Server Architecture. Client/Server Applications Design.



2. Elements of Transact-SQL.


Data Definition Language (DDL). Data Manipulation Language (DML). Data Control Language (DCL). Data Types. Additional Elements. Programming Scripts and Batches.



3. Working with Tables and Views.


Creating and Altering Tables. Creating and Altering Views.



4. Querying and Modifying Data.


Querying Data. Modifying Data.



5. Querying Multiple Tables: JOINs.


ANSI SQL-92 Syntax. INNER JOIN. OUTER JOINs. CROSS JOINs. Self Joins. The UNION Operator.



6. Optimizing Access to Data: Indexes.


Introduction to Indexes. Benefits of Indexes. How to Create Indexes. How SQL Server 2000 Stores Data. How SQL Server 2000 Modifies Data. Index Enhancements in SQL Server 2000. Accessing Data Without Indexes: Table Scan. Types of Indexes. Covered Queries and Index Intersection. Index Maintenance. Indexes on Computed Columns. Indexed Views. Index Tuning Wizard. Summary.



7. Enforcing Data Integrity.


Types of Data Integrity. Enforcing Integrity: Constraints (Declarative Data Integrity).



8. Implementing Business Logic: Programming Stored Procedures.


Benefits of Using Stored Procedures. Types of Stored Procedures. Creating and Dropping Stored Procedures. Using Parameters. Altering Stored Procedure Definitions. The RETURN Statement. Executing Stored Procedures. Stored Procedure Recompilation. Handling Errors. Nesting Stored Procedures. Application Security Using Stored Procedures.



9. Implementing Complex Processing Logic: Programming Triggers.


Benefits of Triggers. Trigger Enhancements in SQL Server 2000. Inserted and Deleted Tables. Types of Triggers According to Their Order. Creating and Dropping Triggers. Altering Trigger Definitions. Disabling Triggers. Nesting Triggers. Recursive Triggers. Security Implications of Using Triggers. Enforcing Business Rules: Choosing Among INSTEAD OF Triggers,. Constraints, and AFTER Triggers.



10. Enhancing Business Logic: User-Defined Functions (UDF)


Benefits of User-Defined Functions. Built-In User-Defined Functions. Types of User-Defined Functions According to Their Return Value. Dropping User-Defined Functions. Preventing the Alteration of Dependent Objects: The SCHEMABINDING Option. Deterministic and Nondeterministic Functions. Altering User-Defined Functions Definition. Security Implications of Using User-Defined Functions. Applying User-Defined Functions.



11. Using Complex Queries and Statements.


Subqueries. Correlated Subqueries. Derived Tables. The CASE Function. The COMPUTE Clause. The CUBE and ROLLUP Operators. Using Hints.



12. Row-Oriented Processing: Using Cursors.


Row-by-Row Versus Set-Oriented Processing. Types of Cursors. Steps to Use Cursors. Scope of Cursors. Using Cursors to Solve Multirow Actions in Triggers. Application Cursors.



13. Maintaining Data Consistency: Transactions and Locks.


Characteristics of Transactions (ACID). Using Transactions. Concurrency Problems. Isolation Levels. Types of Locks. A Serious Problem to Avoid: Deadlocks.



14. Transferring Data to and from SQL Server.


The Need for Transferring Data. Tools for Transferring Data Using SQL Server 2000. The BULK INSERT Statement and bcp. Using Data Transformation Services. The Copy Database Wizard.



15. Working with Heterogeneous Environments: Setting Up Linked Servers.


Distributed Queries. Distributed Transactions.



Appendix A. Using SQL Server Instances.


Installing SQL Server Instances. Connecting to Instances. System Functions Used in Multi-Instance Installations. Current Limitations.



Appendix B. Using SQL Query Analyzer.


Installing SQL Query Analyzer. The Query Analyzer Workplace. Managing User and Connection Options. Defining and Using Templates for Query Analyzer. Analyzing Queries. Working with the Transact-SQL Debugger. Summary.



Index.