In this last section of the book, we will shift focus to the physical abstraction layer optimization. By this I mean the little bits and pieces of the design that is specifically there for performance and are actually part of the relational engine (read: the part of the SQL Server experience that ideally is hidden from you completely, but in 2010 reality it isn’t quite so yet.  This includes all of the data structures like database, files, etc; the optimizer; some coding, etc.

In my mind, this is the section of the book that separates users from data “programmers”. Understanding how your code executes is what a programmer does, and this is no different.  SQL itself is mostly simple, and even really complex problems don’t require an amazing amount of programming prowess. What makes SQL easy to write is that the engine team does an amazing amount of work to translate your simple looking statement into an optimized method of executing the query, and even having to deal with multi-user issues, making sure that multiple users don’t step on each other’s work and that all of their queries execute. Understanding of how that happens give you as architect the ability to design a physical access layer that works fast exploiting the way SQL Server works.

The proposed chapters for this section include:

14. Concurrency – Concurrency is truly a two part consideration. It is partially a coding issue, and it is partially a physical abstraction concern. Generally speaking though, it is not a major coding effort to deal with concurrency issues. In this chapter I will cover in some detail, locks, latches and how to deal with them

15. Table and index structures – In this chapter I will cover at a moderately high level, the structures that make up the database. In fact, there are a surprisingly large number of layers between the file system and the actual tables where data is stored. Having these layers gives you a lot of flexibility to set up your system in a manner that is fastest for the hardware you own (and possibly to purchase more!), giving you lots of capability to organize the data in a manner that suits the SQL Server engine’s needs.

Appendix A. Datatype Reference – I struggled whether or not to make this a full blown chapter, or an appendix, but in the end it doesn’t really matter. I will categorize and list all of the datatypes along with example usages, discussion of compression’s effects on certain data, and any concerns with their use (read: the evils of the money, text, and image types.)  In chapter 10, I will include some of this information already, but most of the discussion there will be limited to a discussion of implementing a domain, with a list of datatypes and advice/examples of “choosing the right type”. I feel including a full coverage of types would just really kill the flow of the book, but eliminating this from the book entirely would be just as bad.  Hence the Appendix based inclusion in the final section.

In previous editions, I had an appendix for Codd’s 12 rules, but I am really feeling like that needs to be promoted to a slightly shorter section in the first chapter. Of course, before I see it, I won’t really know how it feels and it may again be pushed back to appendix status.