Skip Ribbon Commands
Skip to main content
SQL Server for Developers
Course Number:(ALTO-2073)
Course Length:3 Day
Course Price:$1,595
Or Register by Phone 1-888-600-ALTO (2586)

 Upcoming Classes

Start DateEnd DateClass Held At

 Course Description

This three-day, hands-on course provides students with the technical skills required to program a database solution using Microsoft SQL Server.

 Course Outline

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 module provides an overview of all the major administrative functions, which include: creating databases and transaction logs, managing the file system, server and database configuration, and 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 module covers key concepts and syntax for creating and maintaining: schemas, tables, data integrity constraints, views, indexes and synonyms. Stored procedures, functions and triggers are covered in separate modules.

3: Security

Most administrative activities required for the health of a database are isolated and all but hidden (and rightly so) from the eyes of software developers. Application design does not and should not depend on factors involving the server’s use of the file system, backup or recovery strategy. Security, however, is another matter entirely. While developers are not normally responsible for the safety and security of databases, they most certainly must contend with decisions that have been made and should be consulted and relied upon for security policy and configuration. This module covers what every developer must know about the way security works with SQL Server.

4: Stored Procedures

SQL Server’s built-in programming language is called Transact-SQL or T-SQL for short. This module covers everything you need to know to create and maintain stored procedures that use variables and parameters, IF..ELSE branches, WHILE loops, and error handling.

5: Non-Relational Features

SQL Server is fundamentally a relational database management system. However, not all problems can be solved by normalizing data into tables and manipulating it through SELECT, INSERT, UPDATE and DELETE statements. We’ll cover two important departures from the strict relational model: 1) a cursor is a row-at-a-time processing feature that offers an important alternative to the set-oriented statements. 2) XML is designed as a generic and extensible way to store data and meta-data. SQL Server has extensive support for XML, so much in fact that we can only cover this topic at an introductory level in this course.

6: User-Defined Functions

User-defined functions are often over-looked by 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.

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 module 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 a 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. SERVER triggers run when the database schema is changed. All types of triggers are discussed in this module along with a survey of the different types of design challenges triggers can help to resolve.

9: Other Services

A SQL Server license includes far more than just the relational database engine features detailed in this course. Your organization’s investment in SQL Server includes numerous valuable services that should be leveraged when designing and implementing solutions. This module provides a quick introduction to Reporting Services, Integration Services, and Analysis Services. Each topic is covered in detail in a separate multiple-day training course. The goal in this course is to make you aware of the breadth and depth of the functionality provided along with a sense of how easy it is to get started and make productive use of these tools.