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: schemas, tables, data integrity constraints, views, indexes and synonyms.
Evaluate and implement database security and be aware of best practices related to the use of security roles.
Create, test and debug 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.
Be aware of the wide array of XML-related features in SQL Server and be able to perform basic XML manipulation.
Create user-defined functions and understand which of the three function types is best suited to solve a particular problem.
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.
Be familiar with the nature and purpose of the other services available with SQL Server so you can leverage the full value of your investment.
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.
Management and Administration
Ideally there is 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 responsbile for maintenace and administration. This section 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.
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 section 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 sections.
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 section covers what every developer must know about the way security works with SQL Server.
SQL Server's built-in programming language is called Transact-SQL or T-SQL for short. This section 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.
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.
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 replacemnt for complex stored procedures and occupy the much-needed middle ground between views and stored procedures.
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 section starts with a conceptual foundation of what transactions are and why they are used, then moves into syntax and behavior.
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 commited. 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 schedma is changed. All types of triggers are discussed in this section along with a survey of the different types of design challenges triggers can help to resolve.
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 section 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. This 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.