You are previewing Microsoft® SQL Server™ 2000 Programming by Example.
O'Reilly logo
Microsoft® SQL Server™ 2000 Programming by Example

Book Description

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.

Table of Contents

  1. Copyright
  2. About the Authors
  3. Acknowledgments
  4. Introduction
  5. Relational Database Management Systems and SQL Server
    1. Database Models
    2. A Brief History of SQL Server
    3. Basics of SQL Server Architecture
    4. Client/Server Applications Design
  6. Elements of Transact-SQL
    1. Data Definition Language (DDL)
    2. Data Manipulation Language (DML)
    3. Data Control Language (DCL)
    4. Data Types
    5. Additional Elements
    6. Programming Scripts and Batches
  7. Working with Tables and Views
    1. Creating and Altering Tables
    2. Creating and Altering Views
  8. Querying and Modifying Data
    1. Querying Data
    2. Modifying Data
  9. Querying Multiple Tables: JOINs
    1. ANSI SQL-92 Syntax
    3. OUTER JOINs
    4. CROSS JOINs
    5. Self Joins
    6. The UNION Operator
  10. Optimizing Access to Data: Indexes
    1. Introduction to Indexes
    2. Benefits of Indexes
    3. How to Create Indexes
    4. How SQL Server 2000 Stores Data
    5. How SQL Server 2000 Modifies Data
    6. Index Enhancements in SQL Server 2000
    7. Accessing Data Without Indexes: Table Scan
    8. Types of Indexes
    9. Covered Queries and Index Intersection
    10. Index Maintenance
    11. Indexes on Computed Columns
    12. Indexed Views
    13. Index Tuning Wizard
    14. Summary
  11. Enforcing Data Integrity
    1. Types of Data Integrity
    2. Enforcing Integrity: Constraints (Declarative Data Integrity)
  12. Implementing Business Logic: Programming Stored Procedures
    1. Benefits of Using Stored Procedures
    2. Types of Stored Procedures
    3. Creating and Dropping Stored Procedures
    4. Using Parameters
    5. Altering Stored Procedure Definitions
    6. The RETURN Statement
    7. Executing Stored Procedures
    8. Stored Procedure Recompilation
    9. Handling Errors
    10. Nesting Stored Procedures
    11. Application Security Using Stored Procedures
  13. Implementing Complex Processing Logic: Programming Triggers
    1. Benefits of Triggers
    2. Trigger Enhancements in SQL Server 2000
    3. Inserted and Deleted Tables
    4. Types of Triggers According to Their Order
    5. Creating and Dropping Triggers
    6. Altering Trigger Definitions
    7. Disabling Triggers
    8. Nesting Triggers
    9. Recursive Triggers
    10. Security Implications of Using Triggers
    11. Enforcing Business Rules: Choosing Among INSTEAD of Triggers, Constraints, and AFTER Triggers
  14. Enhancing Business Logic: User-Defined Functions (UDF)
    1. Benefits of User-Defined Functions
    2. Built-In User-Defined Functions
    3. Types of User-Defined Functions According to Their Return Value
    4. Dropping User-Defined Functions
    5. Preventing the Alteration of Dependent Objects:The SCHEMABINDING Option
    6. Deterministic and Nondeterministic Functions
    7. Altering User-Defined Functions Definition
    8. Security Implications of Using User-Defined Functions
    9. Applying User-Defined Functions
  15. Using Complex Queries and Statements
    1. Subqueries
    2. Correlated Subqueries
    3. Derived Tables
    4. The CASE Function
    5. The COMPUTE Clause
    6. The CUBE and ROLLUP Operators
    7. Using Hints
  16. Row-Oriented Processing: Using Cursors
    1. Row-by-Row Versus Set-Oriented Processing
    2. Types of Cursors
    3. Steps to Use Cursors
    4. Scope of Cursors
    5. Using Cursors to Solve Multirow Actions in Triggers
    6. Application Cursors
  17. Maintaining Data Consistency: Transactions and Locks
    1. Characteristics of Transactions (ACID)
    2. Using Transactions
    3. Concurrency Problems
    4. Isolation Levels
    5. Types of Locks
    6. A Serious Problem to Avoid: Deadlocks
  18. Transferring Data to and from SQL Server
    1. The Need for Transferring Data
    2. Tools for Transferring Data Using SQL Server 2000
    3. The BULK INSERT Statement and bcp
    4. Using Data Transformation Services
    5. The Copy Database Wizard
  19. Working with Heterogeneous Environments: Setting Up Linked Servers
    1. Distributed Queries
    2. Distributed Transactions
  20. Using SQL Server Instances
    1. Installing SQL Server Instances
    2. Connecting to Instances
    3. System Functions Used in Multi-Instance Installations
    4. Current Limitations
  21. Using SQL Query Analyzer
    1. Installing SQL Query Analyzer
    2. The Query Analyzer Workplace
    3. Managing User and Connection Options
    4. Defining and Using Templates for Query Analyzer
    5. Analyzing Queries
    6. Working with the Transact-SQL Debugger
    7. Summary
  22. Index