Programming a Microsoft SQL Server 2000 Database
Course
Inhouse
Description
-
Type
Course
-
Methodology
Inhouse
-
Duration
5 Days
Thiscourse provides students with the technical skills required to program adatabase solution by using Microsoft SQL Server 2000.
About this course
Before attending this course, students must have: Experience using the Microsoft Windows® 2000 operating system to: Connect clients running Windows 2000 to networks and the Internet. Configure the Windows 2000 environment. Create and manage user accounts. Manage access to resources by using groups. Configure and manage disks and...
Reviews
Course programme
At Course Completion
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; and manage transactions and locks to ensure data concurrency and recoverability.
Prerequisites
Before attending this course, students must have:
· Experience using the Microsoft Windows® 2000 operating system to:
· Connect clients running Windows 2000 to networks and the Internet.
· Configure the Windows 2000 environment.
· Create and manage user accounts.
· Manage access to resources by using groups.
· Configure and manage disks and partitions, including disk striping and mirroring.
· Manage data by using the NTFS file system.
· Implement Windows 2000 security.
· Optimize performance in Windows 2000.
For students who do not meet this prerequisite, the following courses provide students with the necessary knowledge and skills:
· Course 2151, Microsoft Windows 2000 Network and Operating System Essentials
· Course 2152, Implementing Microsoft Windows 2000 Professional and Server
· An understanding of basic relational database concepts, including:
· Logical and physical database design.
· Data integrity concepts.
· Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many, many-to-many).
· How data is stored in tables (rows and columns).
For students who do not meet this prerequisite, the following course provides students with the necessary knowledge and skills:
· Course 1609, Designing Data Services and Data Models
· Knowledge of basic Transact-SQL syntax (SELECT, UPDATE, and INSERT statements).
For students who do not meet this prerequisite, the following course provides students with the necessary knowledge and skills:
· Course 2071, Querying Microsoft SQL Server 2000 with Transact-SQL
· Familiarity with the role of the database administrator.
The course materials, lectures, and lab exercises are in English. To benefit fully from the instruction, students need an understanding of the English language and completion of the prerequisites.
Microsoft Certified Professional Exams
This course will help the student prepare for the following Microsoft Certified Professional exam:
- Exam 70-229,Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition
Course Materials
The course materials are yours to keep.
The student kit includes a comprehensive workbook and other necessary materials for this class.
The following software is provided for use in the classroom: Microsoft SQL Server 2000 Enterprise edition.
Course Outline
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
- 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.
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
- Overview of Transact-SQL
- 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.
The following topics are covered in this module:
- Creating Databases
- Creating Filegroups
- Managing Databases
- Introduction to Data Structures
- Creating and Managing Databases
- Create a database.
- Create a filegroup .
- Manage a database.
- Describe data structures.
The following topics are covered in this module:
- Creating Data Types
- Creating Tables
- Generating Column Values
- Generating Scripts
- Creating Data Types and Tables
- Create and drop user-defined data types.
- Create and drop user tables.
- Generate column values.
- Generate a script.
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
- Implementing Data Integrity
- 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.
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
- Determining the Indexes of a Table
- 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.
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
- Creating and Maintaining Indexes
- Viewing Index Statistics
- 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.
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
- Implementing Views
- 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.
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
- Creating Stored Procedures
- Creating Stored Procedures Using Parameters
- 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.
The following topics are covered in this module:
- What Is a User-defined Function?
- Defining User-defined Functions
- Examples of User-defined Functions
- Creating User-defined Functions
- Describe the three types of user-defined functions.
- Create and alter user-defined functions.
- Create each of the three types of user-defined functions.
The following topics are covered in this module:
- Introduction to Triggers
- Defining Triggers
- How Triggers Work
- Examples of Triggers
- Performance Considerations
- Creating Triggers
- Create a trigger.
- Drop a trigger.
- Alter a trigger.
- Describe how various triggers work.
- Evaluate the performance considerations that affect using triggers.
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
- Using Distributed Data
- 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.
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
- Optimizing Query Performance
- 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.
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
- Analyzing Queries That Use the AND and OR Operators
- Analyzing Queries That Use Different Join Strategies
- 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.
The following topics are covered in this module:
- Introduction to Transactions and Locks
- Managing Transactions
- SQL Server Locking
- Managing Locks
- Managing Transactions and Locks
- 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.
Programming a Microsoft SQL Server 2000 Database