Synopses & Reviews
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.
Provides a practical understanding of the programming concepts through examples of of basic programming techniques used in SQL Server 2000 primarily using Transact SQL. The book is suitable for the complete novice to SQL or may be used as a reference guide for those familiar with it.
About the Author
Carlos Eduardo Rojas is a program manager with Planeta Networks, an Internet company headquartered in Coral Gables, Florida that provides broadband applications to Internet service providers in Ibero-America. He specializes in the design of n-tier applications, database implementation, and administration of SQL Server databases. Prior to this role, he was a consultant and trainer with Manapro in Caracas, Venezuela, where he is originally from. Also, he has participated as a speaker in various TechNet conferences in Venezuela. Carlos earned a B.S. degree in Computer Science from University Simón Bolívar, Caracas, Venezuela. He is a Microsoft Certified Systems Engineer + Internet (MCSE+I), Microsoft Certified Database Administrator (MCDBA), Microsoft Certified Sales Specialist (MCSS), and has been awarded with the MVP (Most Valuable Professional) status on SQL Server. He is also a voting member and volunteer of PASS, the professional association for SQL Server. Carlos can be reached at email@example.com.
Fernando G. Guerrero is a principal technologist and SQL Server product consultant in QA, United Kingdom. His main role involves IT training, course development, and internal mentoring.
He writes for SQL Server Magazine (http://www.sqlmag.com), presented a session on SQL Server 2000 at TechEd 2000 Europe, and has accepted to speak at PASS2001, TechEd Europe 2001, VBUG 2001, VBITS 2001, VSLive, and SQL2THEMAX conferences during the year 2001.
He is a Civil and Hydrologic Engineer with almost 20 years' experience in software development and design, in many cases applied to engineering environments.
He holds seven Microsoft Professional Certifications, including MCSE+Internet, MCSD, MCDBA, MCT, and has been awarded with the MVP (Most Valuable Professional) status on SQL Server. He is also a voting member and volunteer of PASS, the professional association for SQL Server.
His professional experience covers six years (1981-1987) as a lecturer in the Valencia's Polytechnic University (Spain, http://www.upv.es), where he was lecturing on surveying, photogrammetry, technical drawing, and applied numerical methods in the Civil Engineering School and the Agricultural Engineering School. He built his own software company in Spain, TOU S.A., focused on desktop publishing and graphical tools for the professional market, and was technical director in that company for four years (1987-1991).
Before joining QA (Nov. 1998), he spent eight years (1991-1998) as an international consultant for a prestigious Spanish engineering firm (http://www.inypsa.es), living in different countries, designing, developing, and implementing information systems for projects financed by the World Bank, the European Union, and the European Investment Bank. Fernando can be reached at firstname.lastname@example.org.
Table of Contents
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.