|
This three-day, hands-on course provides students with the technical skills required to program a database solution using Microsoft SQL Server 2005.
This course is intended for:
- Database application developers who integrate SQL Server resources in their
software.
- Project managers who must have a basic understanding of what their
development team is up to when working with SQL Server.
- Database administrators who want to know what to expect from the applications connecting to their servers.
By the end of this course students will be able to:
- Perform fundamental database administration tasks at a level suitable for maintaining development/test platforms.
- Create and maintain server-side objects that are common to all database application development projects: tables, data integrity constraints, views, and indexes.
- Create Transact-SQL statement batches that manipulate data.
- Create stored procedures that use variables and parameters, IF..ELSE branching and WHILE looping, and handle errors.
- Design and implement solutions involving cursors and be aware of the many cursor-related options.
- Create user-defined functions and understand when each of the three function types are best suited.
- Understand transactions and when (and when not) to use explicit transaction syntax.
- Understand the nature and purpose of triggers and be able to implement both the after and instead-of variety.
- Identify common sources of performance problems and how to rectify them.
Course Outline
The following is a brief summary of how the course is structured and what you should expect from each module and associated lab exercises.
1. Management and Administration
Ideally there’s a clean separation of responsibilities between those who utilize database server resources in application software and those who manage, administer, maintain and protect those resources. In practice, however, developers are often expected to contribute to (if not totally handle) management and administration duties. Additionally, programmers are strongly encouraged to run their own development/test servers, for which they’ll be responsible for maintenance and administration. This chapter provides an overview of the major administrative functions: creating databases and transaction logs, managing the file system, backup and recovery.
2. Creating Server Objects
While developers are not typically called upon to install, configure and maintain database servers, they ought to be able to handle all aspects of creation and maintenance for server-side objects accessed by their software applications. This chapter covers key concepts and syntax for creating and maintaining: tables, data integrity constraints, views and indexes. Stored procedures, functions and triggers are covered in separate chapters.
3. Transact-SQL Foundation
SQL Server’s built-in programming language is called Transact-SQL or T-SQL for short. This chapter covers the foundation concepts upon which stored procedures, cursors and triggers are based. This includes variables and data types, assignment expressions, and null handling.
4. Stored Procedures
This chapter builds on the Transact-SQL foundation and adds syntax for creating and calling stored procedures with input and output parameters, return values and error handling.
5. Cursors
A cursor is a row-at-a-time processing feature that offers an important alternative to the set-oriented processes that form the SQL query and update statement behaviors. This chapter outlines the basic syntax and then dives into the details of the various types of cursors and cursor options.
6. User-Defined Functions
User-defined functions are often overlooked by many designers and architects. This is a pity because when used correctly user-defined functions are a simple replacement for complex stored procedures and occupy the much-needed middle ground between views and stored procedures. This chapter provides guidance for applying user-defined functions to real-world problems along with the syntax for creating and calling them.
7. Transactions
Of all the subjects in this course, transactions are possibly the most misunderstood and consequently misused feature of SQL Server. Worse, poorly-crafted transactions can be a considerable drain on performance that no amount of indexing, tuning and additional CPU processing power and memory can overcome. This chapter starts with a conceptual foundation of what transactions are and why they are used, then moves into syntax and behavior.
8. Triggers
A trigger is a stored procedure that runs when an insert, update and/or delete statement is issued against a table or view. AFTER triggers run in the context of the transaction, meaning they run after the modifications have been made but before the changes have been committed. INSTEAD OF run instead of the requested insert/update/delete statement and can be defined for tables or views. Both types of triggers are discussed in this chapter along with a survey of the different types of design challenges triggers can help to resolve.
9. Optimization
At some point every developer will be faced with an operation that performs too slowly and must be improved. This chapter examines the root causes of performance problems and illustrates the various techniques that are used to detect and resolve performance problems.
|
