Book Outline

Below is a detailed outline of what this book covers, chapter by chapter.

Chapter 1 – Stored Procedure Overview

We begin by examining what constitutes a stored procedure and discuss different types of stored procedures. Then we will look at the difference between system stored procedures and user stored procedures, creating and designing stored procedures, using parameters and best practices, flow control statements, recursion, and return values.

Chapter 2 – Optimizing and Debugging

In this chapter, we will discuss optimizing SQL Server's stored procedures. Here, our focus will not be on optimizing the SQL statements themselves, but instead, how SQL statements can be optimized within the context of a stored procedure. We will also discuss error handling, signaling error conditions, and debugging stored procedures.

Chapter 3 – Cursors, Transactions, and Concurrency

Initially, these are three topics that might seem unrelated; however, upon deeper discussion, we will discover that they are related. Cursors are mechanisms to code non-set-based operations into T-SQL statements. In general, their use is considered bad practice, but can be useful when building stored procedures if used properly. In this chapter we will look at accessing them from code and their typical uses. Transactions are used to group together similar operations into one atomic logical operation, and we will look at look at simple, nested, and distributed transactions, and their use from stored procedures. Concurrency is related to multi-tasking of operations. In databases, we are concerned with the number of queries that can be executed at the same time, especially making use of the same resources and data. We will look at locks, which are the markers used by SQL Server to decide whether the commands can be executed concurrently or not, and also discuss methods to maximize concurrency by using programming schemes to limit SQL locking between read-only and read-write sessions.

Chapter 4 – Typical Uses of Stored Procedures

In this chapter, we will focus on the purposes of stored procedures within our applications, such as encapsulating logic, improving application performance, easing administration, abstracting the underlying base tables away from developers, simplifying code updates, and improving database security and data validation.

Chapter 5 – System Stored Procedures

In this chapter we will look at system stored procedures, and their implementation by Microsoft. We will also look at some of the undocumented functionalities provided with SQL Server, which Microsoft uses to make certain system stored procedures work. Along the way, we'll be giving you tips and tricks on when to use (and on avoiding) their quite useful, but obviously ‘unsupported’, undocumented functionalities.

If you manage a SQL Server as a part of your day-to-day tasks, you will find this chapter helpful. There's a section on mining system stored procedures that will be especially useful to those involved in T-SQL coding.

Chapter 6 – User Defined Functions

User defined functions, known more commonly as UDFs, are routines called from T-SQL code to return either a single value or a rowset, providing the developer with the ability to create their own customized functions, which can then be used from other parts of their T-SQL code.

UDFs have some features that are common to stored procedures but there are a number of restrictions, as well. In this chapter, we will begin by looking at what a UDF actually is, and then move onto look at the different types of UDFs, building and calling functions that return different types of data, using schema binding with UDFs, and how UDFs differ from stored procedures.

Chapter 7 – Triggers

Triggers are best used for enforcing business rules as well as performing validation or data modifications, when other methods are not sufficient. Triggers are typically used in two areas: creating audit records and reflecting changes to crucial business tables, and validating changes against a set of business rules coded in T-SQL. In this chapter we will look at performance considerations when working with triggers, the different types of triggers, transactions and triggers, and recursive triggers.

Chapter 8 – Security

In this chapter we will first analyze security threats and the core security requirement of SQL Server – allowing data access only to explicitly authorized users. Then, we will look at the proper implementation of stored procedures for adequately countering these threats. Finally, we will examine some general methods for securing our SQL Server implementation, giving special emphasis on SQL Server stored procedures and the system stored procedures that Microsoft ships with SQL Server.