Companion content for Chapter 6 (Index Internals) of SQL Server 2008 Internals

Recently, the SQL Server 2008 Internals title was released (and only in the past few days have people actually received their copies)! In fact, I still haven't seen the book in person… soon though!

Anyway, Kalen, Paul, Conor, Adam and I worked to create a comprehensive resource on SQL Server 2008 internals and to supplement the written content, many of us created demo scripts and examples. I've now gone back and created a sample script based on ALL of the code in the entire chapter (and in many cases I extended the code in the samples). And, while this updated content is going to be located on the companion content site, I thought I’d also release it here with some notes.

The zip contains a solution with 3 projects, each with a few scripts:

I know the names seem a bit strange but everything is ordered EXACTLY as it is shown in the book. And, in the book, I referenced "a" script called EmployeeCaseStudy-AnalyzeStructures.sql but that script was so big that I broke it down into 7 parts (hence the naming convention of 06…01, 06…02, etc.). Regardless, each script contains a brief header and a few details about the script. To get the most from the script, do not just open the script and execute it. If you really work your way through the script, you should see all of the comments and they will help you to make instance specific changes so that everything runs without error. Just take your time and really try to step back and think about each command (and what you're expecting the output to be) to test yourself while your working through the results. Taking your time and really grapsing all of these internals is what makes it fun!

Quite a bit of this content can stand alone but it's really best as companion content to the title as there's a lot more "text" and detailed information in the book. But, the scripts are really a great way to dive deeper, learn documented/undocumented commands and really get to know what the heck is really going on internally.

Finally, here it is: 20120328-IndexInternals-Chapter6-Resources.zip (6.13 mb). For other companion content, you can use the links from the book! 

And, certainly, if you find a typo or anything that you think needs more clarification, let me know! I'm more than happy to post updates (see below) to this content. 

Have fun,
kt

UPDATES/ERRORLOG:

2012-Mar-28: Changed the restore script to work for versions higher than 2008 (2008 R2 or 2012). You'll still need to correct your path, etc. but the restore script will not fail on versions higher than 2008/10.

2010-Jun-28: Fixed a typo in the table definitions. However, this code is never executed so it doesn't create errors. Just a typo in the script. Here's this version: 20100628-IndexInternals-Chapter6-Resources.zip (6.13 mb).

2009-Aug-10: Added a :CONNECT option inside the IndexInternals restore script AND, cleaned up the zip as it had an extra copy of the IndexInternals database in it.

2009-Apr-13 (4:30pm): Ha… guess what arrived today. Yes – our copies of the book. Wow, it's great to see it in person. Again, enjoy!

2009-Apr-13 (8am): Updated the zip after remembering in my sleep (yes, sad, but true!) that one of my comments that referenced some line numbers didn't get updated in the final version. So, no errors and if you don't get this update, it's not going to break anything. But, the script that's been tweaked is script: 05_EmployeeCaseStudy-TableDefinition.sql. Enjoy! kt

4 thoughts on “Companion content for Chapter 6 (Index Internals) of SQL Server 2008 Internals

  1. Hi Kimberly Tripp, I have just watch following video, it’s very nice easy to understand. Thanks you,
    http://technet.microsoft.com/en-us/sqlserver/gg508878.aspx

    I have small question you have mention in your video in “[IndexInternals].[dbo].[Employee]” table row size is 400bytes but it has only 393bytes as following calc,

    [EmployeeID] [int], 4 bytes
    [LastName] [nchar](30),60 bytes
    [FirstName] [nchar](29), 58 bytes
    [MiddleInitial] [nchar](1), 2 bytes
    [SSN] [char](11), 11 bytes
    [OtherColumns] [char](258), 258 bytes
    ——————————————————————————–
    Did i missed anything?

    1. Yes, you’re missing the internal row overhead… it’s a bit too much to try and do in a comment but I’ll give you a quick overview:
      4 bytes for the row “header” = 4 bytes
      Fixed width are exactly that: 4 + 60 + 58 + 2 + 11 + 258 = 393 bytes
      Null values are tracked for every column of the table regardless of whether or not it allows nulls so this is a MINIMUM of 2 bytes + 1 bit per column (6 columns can be stored in one byte) = 3 bytes

      = 400 bytes.

      Hope that helps!
      Kimberly

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Wow! Wow! Wow! THANK YOU!

I announced my retirement from SQL/tech here and your comments on my blog, on LinkedIn, and on Facebook were overwhelming and humbling! I’m so touched

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.