Programming a Microsoft SQL Server 2000 Database

Target Audience
This course provides students with the technical skills required to program a database solution by using Microsoft SQL Server 2000.

Pre-Requisites
Before attending this course, students must have the following pre-requisites:Experience using the Microsoft Windows 2000 operating system or attendance onCourse 2151, Microsoft Windows 2000 Network and Operating System EssentialsandCourse 2152, Implementing Microsoft Windows 2000 Professional and Server.An understanding of basic relational database concepts or attendance onCourse 1609, Designing Data Services and Data Models.Knowledge of basic Transact-SQL syntax (SELECT, UPDATE, and INSERT statements) or attendance onCourse 2071, Querying Microsoft SQL Server 2000 with Transact-SQL.Familiarity with the role of the database administrator.

Purpose
At the end of the course, students will be able to describe the elements of SQL Server 2000, design a SQL Server enterprise application architecture, describe the conceptual basis of programming in Transact-SQL, create and manage databases and their related components, implement data integrity by using the IDENTITY column property, constraints, defaults, rules, and unique identifiers, plan for the use of indexes, create and maintain indexes, create, use, and maintain data views, implement user-defined functions, design, create, and use stored procedures, create and implement triggers, program across multiple servers by using distributed queries, distributed transactions, and partitioned views, optimize query performance, analyze queries by using Graphical Showplan, and manage transactions and locks to ensure data concurrency and recoverability.

Course outline

1. SQL Server Overview
2. Overview of Programming SQL Server
3. Creating and Managing Databases
4. Creating Data Types and Tables
5. Implementing Data Integrity
6. Planning Indexes
7. Creating and Maintaining Indexes
8. Implementing Views
9. Implementing User-defined Functions
10. Implementing Stored Procedures
11. Implementing Triggers
12. Programming Across Multiple Servers
13. Query Performance
14. Analyzing Queries Using Graphical SHOWPLAN
15. Managing Transactions and Locks

Module 1:SQL Server Overview

• What Is SQL Server
• SQL Server Integration
• SQL Server Databases
• SQL Server Security
• Working with SQL Server

• Describe SQL Server 2000 and its supported operating system platforms.
  
• Describe SQL Server Integration with Microsoft Windows 2000 and other server applications.
  
• Describe SQL Server databases.
  
• Describe SQL Server security.
  
• Describe SQL Server querying, implementation, administration, and data warehousing activities, as well as client application design options.
  

Module 2:Overview of Programming SQL Server

• Designing Enterprise Application Architecture
• SQL Server Programming Tools
• The Transact-SQL Programming Language
• Elements of Transact-SQL
• Additional Language Elements
• Ways to Execute Transact-SQL Statement

• Describe the concepts of enterprise-level application architecture.
  
• Describe the primary SQL Server programming tools.
  
• Explain the difference between the two primary programming tools in SQL Server.
  
• Describe the basic elements of Transact-SQL.
  
• Describe the use of local variables, operators, functions, control of flow statements, and comments.
  
• Describe the various ways to execute Transact-SQL statements.
  

Module 3:Creating and Managing Databases

• Creating Databases
• Creating Filegroups
• Managing Databases
• Introduction to Data Structures

• Create and configure a database.
  
• Create a filegroup.
  
• Manage a database and transaction log.
  
• Describe how SQL Server uses data structures to store data.
  

Module 4:Creating Data Types and Tables

• Creating Data Types
• Creating Tables
• Generating Scripts

• Create and drop user-defined data types.
  
• Create and drop user tables.
  
• Generate a script.
  

Module 5:Implementing Data Integrity

• Types of Data Integrity
• Enforcing Data Integrity
• Defining Constraints
• Types of Constraints
• Disabling Constraints
• Using Defaults and Rules
• Deciding Which Data Integrity Methods to Use

• Describe the types of data integrity.
  
• Describe the methods to enforce data integrity.
  
• Determine which constraint to use and create constraints.
  
• Define and use DEFAULT, CHECK, PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints.
  
• Disable constraints.
  
• Describe and use defaults and rules.
  
• Determine which data integrity enforcement methods to use.
  

Module 6:Planning Indexes

• Introduction to Indexes
• Index Architecture
• How SQL Server Retrieves Stored Data
• How SQL Server Maintains Index and Heap Structures
• Deciding Which Columns to Index

• Describe why and when to use an index.
  
• Describe how SQL Server uses clustered and nonclustered indexes.
  
• Describe how SQL Server index architecture facilitates the retrieval of data.
  
• Describe how SQL Server maintains indexes and heaps.
  
• Describe the importance of selectivity, density, and distribution of data when deciding which columns to index.
  

Module 7:Creating and Maintaining Indexes

• Creating Indexes
• CREATE INDEX Options
• Modifying Data
• Maintaining Indexes
• Introduction to Statistics
• Using an Index to Cover a Query
• Performance Considerations
• Using Index Tuning Tools to Improve Query Performance

• Create indexes and indexed views with unique or composite characteristics.
  
• Use the CREATE INDEX options.
  
• Describe how adding, updating, and deleting data physically affects SQL Server indexes.
  
• Use various tools and verification features to maintain indexes and enhance their optimal performance.
  
• Understand how the query optimizer creates, stores, maintains, and uses statistics.
  

Module 8:Implementing Views

• What Is a View?
• Advantages of Views
• Defining Views
• Partitioned Views and Scaling Out
• Security
• Modifying Data Through Views
• Performance Considerations

• Define a view with the CREATE VIEW statement.
  
• Alter and drop a view definition.
  
• Describe the characteristics and implications of a broken ownership chain.
  
• Locate view definition information.
  
• Create partitioned views.
  
• Optimize view security.
  
• Modify a source table by using a view.
  

Module 9:Implementing User-defined Functions

• Creating, Executing, and Altering User-defined Functions
• Using User-defined Functions with MDX
• UDF Permissions
• Using User-defined Functions with Dynamic/Static Filters
• Developing User-defined Functions for XML

• Create, execute, and alter user-defined functions.
  
• Create user-defined functions with the MDX language.
  
• Control security with Uniqueness Database File (UDF) permissions.
  
• Describe the use of dynamic and static filters.
  
• Develop user-defined functions for XML.
  

Module 10:Implementing Stored Procedures

• Introduction to Stored Procedures
• Creating, Executing, and Modifying Stored Procedures
• Using Parameters in Stored Procedures
• Executing Extended Stored Procedures
• Handling Error Messages
• Performance Considerations

• Describe how a stored procedure is processed.
  
• Create, execute, modify, and drop a stored procedure.
  
• Create stored procedures that accept parameters.
  
• Execute extended stored procedures.
  
• Create custom error messages.
  

Module 11:Implementing Triggers

• Introduction to Triggers
• Defining Triggers
• Examples of Triggers
• Performance Considerations

• Describe the purpose of triggers.
  
• Create, alter, and drop a trigger.
  
• Use nested, recursive, and INSTEAD OF triggers to enhance the ability to enforce data integrity and business rules.
  

Module 12:Programming Across Multiple Servers

• Security
• Distributed Queries
• Distributed Transactions
• Partitioned Views

• Design security for a multi-server environment.
  
• Construct and use distributed queries.
  
• Construct and use distributed transactions.
  
• Design and implement partitioned views.
  

Module 13:Query Performance

• Introduction to the Query Optimizer
• Obtaining Query Plan Information
• Indexing Strategies

• Explain the role of the query optimizer and how it works.
  
• Obtain query plan information to determine how the query optimizer processed a query.
  
• Validate that the most efficient query plan was generated.
  
• Implement indexing strategies to reduce page reads.
  
• Design indexes that can benefit from using query optimizer.
  

Module 14:Analyzing Queries Using Graphical SHOWPLAN

• Queries That Use the AND Operator
• Queries That Use the OR Operator
• Queries That Use Join Operations
• Evaluating Query Performance

• Analyze the performance gain of writing efficient queries and creating useful indexes for queries that contain the AND logical operator.
  
• Analyze the performance gain of writing efficient queries and creating useful indexes for queries that contain the OR logical operator.
  
• Evaluate how the query optimizer uses different join strategies for query optimization.
  

Module 15:Managing Transactions and Locks

• Introduction to Transactions and Locks
• Managing Transactions
• SQL Server Locking
• Managing Locks
• Deadlocks

• Describe transactions and locks.
  
• Manage transactions by executing, canceling, or rolling back.
  
• Identify locking concurrency issues.
  
• Identify resource items that can be locked and the types of locks.
  
• Describe lock compatibility.
  
• Manage locks by setting locking options and displaying locking information.
  
• Identify and minimize deadlocks.