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\u2019t quite so yet. This includes all of the data structures like database, files, etc; the optimizer; some coding, etc.<\/p>\n
In my mind, this is the section of the book that separates users from data \u201cprogrammers\u201d. 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\u2019t 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\u2019t step on each other\u2019s 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.<\/p>\n
The proposed chapters for this section include:<\/p>\n
14. Concurrency \u2013 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<\/p>\n
15. Table and index structures \u2013 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\u2019s needs.<\/p>\n
Appendix A. Datatype Reference \u2013 I struggled whether or not to make this a full blown chapter, or an appendix, but in the end it doesn\u2019t really matter. I will categorize and list all of the datatypes along with example usages, discussion of compression\u2019s 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 \u201cchoosing the right type\u201d. 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.<\/p>\n
In previous editions, I had an appendix for Codd\u2019s 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\u2019t really know how it feels and it may again be pushed back to appendix status.<\/p>\n","protected":false},"excerpt":{"rendered":"
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……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82097","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82097","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82097"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82097\/revisions"}],"predecessor-version":[{"id":82283,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82097\/revisions\/82283"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82097"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82097"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82097"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82097"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}