|
Provided by: Last Minute Training 2073 Programming a Microsoft SQL Server 2000 Database-acend |
 |
Training
Provided by Last Minute Training
This course provides students with the technical skills required to program a database solution by using Microsoft SQL Server 2000.
|
|
||||||||||
2073 Programming a Microsoft SQL Server 2000 Database-acend
Module 1: SQL Server Overview
The following topics are covered in this module:
What Is SQL Server?
SQL Server Integration
SQL Server Databases
SQL Server Security
Working with SQL Server
The following lab is covered in this module:
SQL Server Overview
At the end of this module, you will be able to:
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 administration and implementation activities, as well as SQL Server application design options.
Module 2: Overview of Programming SQL Server
The following topics are covered in this module:
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
The following lab is covered in this module:
Overview of Transact-SQL
At the end of this module, you will be able to:
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
The following topics are covered in this module:
Creating Databases
Creating Filegroups
Managing Databases
Introduction to Data Structures
The following lab is covered in this module:
Creating and Managing Databases
At the end of this module, you will be able to:
Create a database.
Create a filegroup.
Manage a database.
Describe data structures.
Module 4: Creating Data Types and Tables
The following topics are covered in this module:
Creating Data Types
Creating Tables
Generating Column Values
Generating Scripts
The following lab is covered in this module:
Creating Data Types and Tables
At the end of this module, you will be able to:
Create and drop user-defined data types.
Create and drop user tables.
Generate column values.
Generate a script.
Module 5: Implementing Data Integrity
The following topics are covered in this module:
Types of Data Integrity
Enforcing Data Integrity
Defining Constraints
Types of Constraints
Disabling Constraints
Using Defaults and Rules
Deciding Which Enforcement Method to Use
The following lab is covered in this module:
Implementing Data Integrity
At the end of this module, you 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.
Module 6: Planning Indexes
The following topics are covered in this module:
Introduction to Indexes
Index Architecture
How SQL Server Retrieves Stored Data
How SQL Server Maintains Index and Heap Structures
Deciding Which Columns to Index
The following lab is covered in this module:
Determining the Indexes of a Table
At the end of this module, you 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.
Module 7: Creating and Maintaining Indexes
The following topics are covered in this module:
Creating Indexes
Creating Index Options
Maintaining Indexes
Introduction to Statistics
Querying the sysindexes Table
Setting Up Indexes Using the Index Tuning Wizard
Performance Considerations
The following labs are covered in this module:
Creating and Maintaining Indexes
Viewing Index Statistics
At the end of this module, you 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 table.
Describe how the Index Tuning Wizard works and when to use it.
Describe performance considerations that affect creating and maintaining indexes.
Module 8: Implementing Views
The following topics are covered in this module:
Introduction to Views
Advantages of Views
Defining Views
Modifying Data Through Views
Optimizing Performance by Using Views
Performance Considerations
The following lab is covered in this module:
Implementing Views
At the end of this module, you will be able to:
Describe the concept of a view.
List the advantages of views.
Define a view with the CREATE VIEW statement.
Modify data through views.
Optimize performance by using views.
Module 9: Implementing Stored Procedures
The following topics are covered in this module:
Introduction to Stored Procedures
Creating, Executing, Modifying, and Dropping Stored Procedures
Using Parameters in Stored Procedures
Executing Extended Stored Procedures
Handling Error Messages
Performance Considerations
The following labs are covered in this module:
Creating Stored Procedures
Creating Stored Procedures Using Parameters
At the end of this module, you 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.
Execute extended stored procedures.
Create custom error messages.
Module 10: Implementing User-defined Functions
The following topics are covered in this module:
What Is a User-defined Function?
Defining User-defined Functions
Examples of User-defined Functions
The following lab is covered in this module:
Creating User-defined Functions
At the end of this module, you 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.
Module 11: Implementing Triggers
The following topics are covered in this module:
Introduction to Triggers
Defining Triggers
How Triggers Work
Examples of Triggers
Performance Considerations
The following lab is covered in this module:
Creating Triggers
At the end of this module, you will be able to:
Create a trigger.
Drop a trigger.
Alter a trigger.
Describe how various triggers work.
Evaluate the performance considerations that affect using triggers.
Module 12: Programming Across Multiple Servers
The following topics are covered in this module:
Introduction to Distributed Queries
Executing an Ad Hoc Query on a Remote Data Source
Setting Up a Linked Server Environment
Executing a Query on a Linked Server
Managing Distributed Transactions
Modifying Data on a Linked Server
Using Partitioned Views
The following lab is covered in this module:
Using Distributed Data
At the end of this module, you will be able to:
Describe distributed queries.
Write ad hoc queries that access data that is stored in a remote SQL Server 2000 or in an OLE DB data source.
Set up a linked server environment to access data that is stored in a remote 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.
Module 13: Optimizing Query Performance
The following topics are covered in this module:
Introduction to the Query Optimizer
Obtaining Execution Plan Information
Using an Index to Cover a Query
Indexing Strategies
Overriding the Query Optimizer
The following lab is covered in this module:
Optimizing Query Performance
At the end of this module, you 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 they can determine how the query optimizer processed a query and validate that the most efficient query plan was generated.
Create indexes that cover queries
Identify indexing strategies that reduce page reads.
Evaluate when to override the query optimizer.
Module 14: Analyzing Queries
The following topics are covered in this module:
Queries That Use the AND Operator
Queries That Use the OR Operator
Queries That Use Join Operations
The following labs are covered in this module:
Analyzing Queries That Use the AND and OR Operators
Analyzing Queries That Use Different Join Strategies
At the end of this module, you will be able to:
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
The following topics are covered in this module:
Introduction to Transactions and Locks
Managing Transactions
SQL Server Locking
Managing Locks
The following lab is covered in this module:
Managing Transactions and Locks
At the end of this module, you 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 2000 uses dynamic locking.
Set locking options and display locking information.
The following topics are covered in this module:
What Is SQL Server?
SQL Server Integration
SQL Server Databases
SQL Server Security
Working with SQL Server
The following lab is covered in this module:
SQL Server Overview
At the end of this module, you will be able to:
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 administration and implementation activities, as well as SQL Server application design options.
Module 2: Overview of Programming SQL Server
The following topics are covered in this module:
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
The following lab is covered in this module:
Overview of Transact-SQL
At the end of this module, you will be able to:
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
The following topics are covered in this module:
Creating Databases
Creating Filegroups
Managing Databases
Introduction to Data Structures
The following lab is covered in this module:
Creating and Managing Databases
At the end of this module, you will be able to:
Create a database.
Create a filegroup.
Manage a database.
Describe data structures.
Module 4: Creating Data Types and Tables
The following topics are covered in this module:
Creating Data Types
Creating Tables
Generating Column Values
Generating Scripts
The following lab is covered in this module:
Creating Data Types and Tables
At the end of this module, you will be able to:
Create and drop user-defined data types.
Create and drop user tables.
Generate column values.
Generate a script.
Module 5: Implementing Data Integrity
The following topics are covered in this module:
Types of Data Integrity
Enforcing Data Integrity
Defining Constraints
Types of Constraints
Disabling Constraints
Using Defaults and Rules
Deciding Which Enforcement Method to Use
The following lab is covered in this module:
Implementing Data Integrity
At the end of this module, you 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.
Module 6: Planning Indexes
The following topics are covered in this module:
Introduction to Indexes
Index Architecture
How SQL Server Retrieves Stored Data
How SQL Server Maintains Index and Heap Structures
Deciding Which Columns to Index
The following lab is covered in this module:
Determining the Indexes of a Table
At the end of this module, you 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.
Module 7: Creating and Maintaining Indexes
The following topics are covered in this module:
Creating Indexes
Creating Index Options
Maintaining Indexes
Introduction to Statistics
Querying the sysindexes Table
Setting Up Indexes Using the Index Tuning Wizard
Performance Considerations
The following labs are covered in this module:
Creating and Maintaining Indexes
Viewing Index Statistics
At the end of this module, you 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 table.
Describe how the Index Tuning Wizard works and when to use it.
Describe performance considerations that affect creating and maintaining indexes.
Module 8: Implementing Views
The following topics are covered in this module:
Introduction to Views
Advantages of Views
Defining Views
Modifying Data Through Views
Optimizing Performance by Using Views
Performance Considerations
The following lab is covered in this module:
Implementing Views
At the end of this module, you will be able to:
Describe the concept of a view.
List the advantages of views.
Define a view with the CREATE VIEW statement.
Modify data through views.
Optimize performance by using views.
Module 9: Implementing Stored Procedures
The following topics are covered in this module:
Introduction to Stored Procedures
Creating, Executing, Modifying, and Dropping Stored Procedures
Using Parameters in Stored Procedures
Executing Extended Stored Procedures
Handling Error Messages
Performance Considerations
The following labs are covered in this module:
Creating Stored Procedures
Creating Stored Procedures Using Parameters
At the end of this module, you 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.
Execute extended stored procedures.
Create custom error messages.
Module 10: Implementing User-defined Functions
The following topics are covered in this module:
What Is a User-defined Function?
Defining User-defined Functions
Examples of User-defined Functions
The following lab is covered in this module:
Creating User-defined Functions
At the end of this module, you 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.
Module 11: Implementing Triggers
The following topics are covered in this module:
Introduction to Triggers
Defining Triggers
How Triggers Work
Examples of Triggers
Performance Considerations
The following lab is covered in this module:
Creating Triggers
At the end of this module, you will be able to:
Create a trigger.
Drop a trigger.
Alter a trigger.
Describe how various triggers work.
Evaluate the performance considerations that affect using triggers.
Module 12: Programming Across Multiple Servers
The following topics are covered in this module:
Introduction to Distributed Queries
Executing an Ad Hoc Query on a Remote Data Source
Setting Up a Linked Server Environment
Executing a Query on a Linked Server
Managing Distributed Transactions
Modifying Data on a Linked Server
Using Partitioned Views
The following lab is covered in this module:
Using Distributed Data
At the end of this module, you will be able to:
Describe distributed queries.
Write ad hoc queries that access data that is stored in a remote SQL Server 2000 or in an OLE DB data source.
Set up a linked server environment to access data that is stored in a remote 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.
Module 13: Optimizing Query Performance
The following topics are covered in this module:
Introduction to the Query Optimizer
Obtaining Execution Plan Information
Using an Index to Cover a Query
Indexing Strategies
Overriding the Query Optimizer
The following lab is covered in this module:
Optimizing Query Performance
At the end of this module, you 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 they can determine how the query optimizer processed a query and validate that the most efficient query plan was generated.
Create indexes that cover queries
Identify indexing strategies that reduce page reads.
Evaluate when to override the query optimizer.
Module 14: Analyzing Queries
The following topics are covered in this module:
Queries That Use the AND Operator
Queries That Use the OR Operator
Queries That Use Join Operations
The following labs are covered in this module:
Analyzing Queries That Use the AND and OR Operators
Analyzing Queries That Use Different Join Strategies
At the end of this module, you will be able to:
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
The following topics are covered in this module:
Introduction to Transactions and Locks
Managing Transactions
SQL Server Locking
Managing Locks
The following lab is covered in this module:
Managing Transactions and Locks
At the end of this module, you 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 2000 uses dynamic locking.
Set locking options and display locking information.
About The Training Provider: Last Minute Training
Last Minute Training - Last Minute Training Corp. works similar to last minute discounted travel. We sell training seminars at deep discounts on a last minute basis. If you want to save 30 - 60 off high quality training seminars and have the flexibility to attend with only a few days notice check out our website and see if the training you are looking for is on sale. While your there set up and free account...
