Chapter 15 Written review

Chapter 15 has three sections about indexes and statistics.

Lesson 1: This lesson deals with indexes, but before it does that it talks about the different ways that data is stored.  A heap is one way that the data is stored, I think of this as a book, with a table of contents at the beginning known as a Index Allocation Map. Here is more info on heaps from the experts, I looked and stared at the diagrams (pictures) in the book for a long time as I read through this stuff.  Then there are balanced trees that have levels of roots, intermediates, and leafs(data).  Think of an upside down tree for this one, with the intermediates being the branches.  The main idea in this lesson after explaining heaps and balanced trees (b-tree?) was that you can create clustered indexes on B-Trees that can seek data (you can only have one per table), and non-clustered indexes that can point to data.  Here are a couple of important things to remember about clustered indexes vs non clustered: Rakesh Blog.  I think of them as a clustered index is great for finding and seeking data, non-clustered can help in finding data in a heap, but it can also be helpful if you need to input data since clustered indexes are not so good at that.  This lesson also dealt with indexed views, which is (as it sounds) a view with an index on it.  These can be useful in ETL transactions.

Lesson 2: Discussed SARG's (search arguments), and introduced the reader to the idea that you can use indexes when querying data and it will find your data faster.  No brainer right?  Well it also explains, when the optimizer decides to use a query or not, and where it is a good idea to put an index.  Simply put, it's best to have a clustered index on a column that is a unique identifier, or INT, make sure that it is short though so that it can easily seeked.  This lesson discusses the Where clause too, and how it can be a powerful tool in using an index during a query which makes sense if you remember the logical way that SQL will execute a query from, WHERE, group by, having, select, order by.  I will add, that of course it's not a good idea to have a function on your where clause because when a calculation is performed SQL can no longer seek to a value, it would need to perform the calculation then check each row whether or not that value matches, which is a scan by definition.  This lesson does not talk about query hints which was a little disappointing to me, I think that's in chapter 17.

Lesson 3:  Was interesting because it talked about statistics, which from what I understand is a HUGE topic.  From what I've gathered I don't think I would ever mess with the statistics generated by SQL.  The book tries to give examples of when you would want to manage or create your own statistics, but geez.. I find that VERY advanced at least for my level.  The basics of statistics is that SQL is always creating and updating statistics on the distribution of key values.

All in all this was a very informative chapters, the index stuff was good but I enjoyed learning about the heap & B-trees.

Cheers!