Module 1: Overview of Programming SQL Server
Lessons
- Introducing SQL Server Databases
- Working With SQL Server Programming Tools
- Understanding Transact-SQL Elements
- Programming Language Elements
- Executing Transact-SQL Statements
Lab: Overview of Transact-SQL
After completing this module, students will be able to:
- Describe SQL Server databases.
- Describe the primary SQL Server 2000 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.
Top
Module 2: Creating and Managing Databases
Lessons
- Defining Databases
- Using Filegroups
- Managing Databases
Lab: Creating and Managing Databases
After completing this module, students will be able to
-
Create a database.
- Work with filegroups.
- Manage a database.
Top
Module 3: Creating Data Types and Tables
Lessons
- Working with Data Types
- Working with Tables
- Generating Column Values
- Generating Scripts
Lab: Performing Connected Database Operations
After completing this module, students will be able to:
- Create and drop user-defined data types.
- Create and drop user tables
- Generate column values.
- Generate a script.
Top
Module 4: Implementing Data Integrity
Lessons
- Introducing Data Integrity
- Defining Constraints
- Understanding Constraint Types
- Disabling Constraints
- Using Defaults and Rules
- Deciding Which Enforcement Method to Use
Lab: Implementing Data Integrity
After completing this module, students will be able to
- 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.
Top
Module 5: Planning Indexes
Lessons
- Introducing Indexes
- Understanding Index Architecture
- Retrieving Stored Data with SQL Server
- Maintaining Index and Heap Structures in SQL Server
- Deciding Which Columns to Index
Lab: Determining the Indexes of a Table
After completing this module, students will be able to
- 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.
Top
Module 6: Creating and Maintaining Indexes
Lessons
- Creating Indexes
- Understanding Index Creation Options
- Maintaining Indexes
Lab: Creating and Maintaining Indexes
After completing this module, students will be able to
- Create indexes and indexed views with unique or composite characteristics.
- Use the CREATE INDEX options.
- Describe how to maintain indexes over time.
- Describe how the query optimizer creates, stores, maintains, and uses statistics to optimize queries.
- Query the sysindexes system table.
- Describe how the Index Tuning Wizard works and when to use it.
- Describe performance considerations that affect creating and maintaining indexes.
Top
Module 7: Implementing Views
Lessons
- Introducing Views
- Defining and Using Views
- Using Views to Optimize Performance
Lab: Troubleshooting an ADO.NET Application
After completing this module, students will be able to
- Describe the concept of a view.
- List the advantages of views.
- Define a view by using the CREATE VIEW statement.
- Modify data through views
- Optimize performance by using views.
Top
Module 8: Implementing Stored Procedures
Lessons
- Using Parameters in Stored Procedures
- Handling Error Messages
- Working with Stored Procedures
Lab: Creating Stored Procedures Using Parameters
After completing this module, students will be able to
- Describe how a stored procedure is processed.
- Create, execute, modify, and drop a stored procedure.
- Create stored procedures that accept parameters.
- Create custom error messages.
- Use dynamic SQL in stored procedures.
- Execute extended stored procedures.
Top
Module 9: Implementing User-Defined Functions
Lessons
- Introducing User-Defined Functions
- Implementing User-Defined Functions
Lab: Creating User-Defined Functions
After completing this module, students will be able to
- Describe the three types of user-defined functions.
- Create and alter user-defined functions.
- Create each of the three types of user-defined functions.
Top
Module 10: Implementing Triggers
Lessons
- Introducing Triggers
- Creating, Altering, and Dropping Triggers
- Working with Triggers
- Implementing Triggers
Lab: Creating Triggers
After completing this module, students will be able to
- Create a trigger.
- Drop a trigger.
- Alter a trigger.
- Evaluate the performance considerations that affect using triggers.
Top
Module 11: Programming Across Multiple Servers
Lessons
- Introducing Distributed Queries
- Setting Up a Linked Server Environment
- Working with Linked Servers
- Using Partitioned Views
Lab: Using Distributed Data
After completing this module, students will be able to
- Describe distributed queries.
- Write ad hoc queries that access data that is stored on a remote computer running Microsoft® SQL ServerT 2000 or in an object linking and embedding database (OLE DB) data source.
- Set up a linked server environment to access data that is stored on a remote computer running SQL Server 2000 or in an OLE DB data source.
- Write queries that access data from a linked server.
- Execute stored procedures on a remote server or linked server.
- Manage distributed transactions.
- Use distributed transactions to modify distributed data.
- Use partitioned views to increase performance.
Top
Module 12: Optimizing Query Performance
Lessons
- Introducing the Query Optimizer
- Tuning Performance Using SQL Utilities
- Using an Index to Cover a Query
- Overriding the Query Optimizer
- Understanding Indexing Strategies and Guidelines
Lab: Optimizing Query Performance
After completing this module, students will be able to
- Explain the role of the query optimizer and how it works to ensure that queries are optimized.
- Use various methods for obtaining execution plan information so that students can determine how the query optimizer processed a query and can validate that the most efficient execution plan was generated.
- Create indexes that cover queries.
- Identify indexing strategies that reduce page reads.
- Evaluate when to override the query optimizer.
Top
Module 13: Performing Advance Query Analysis
Lessons
- Analyzing Queries That Use the AND and OR Operator
- Analyzing Queries That Use Join Operations
Lab: Analyzing Queries That Use the AND and OR Operators, Analyzing Queries That Use Different Join Strategies
After completing this module, students will be able to
- Analyze the performance gain of writing efficient queries while creating useful indexes for queries that contain the AND logical operator.
- Analyze the performance gain of writing efficient queries while creating useful indexes for queries that contain the OR logical operator.
- Evaluate how the query optimizer uses different join strategies for query optimization.
Top
Module 14: Managing Transactions and Locks
Lessons
- Introducing Transactions and Locks
- Managing Transactions
- Understanding SQL Server Locking Architecture
- Managing Locks
Lab: Managing Transactions and Locks
After completing this module, students will be able to
- Describe transaction processing.
- Execute, cancel, or roll back a transaction.
- Identify locking concurrency issues.
- Identify resource items that can be locked and the types of locks.
- Describe lock compatibility.
- Describe how SQL Server uses dynamic locking.
- Set locking options and display locking information.
Top
|
