Picture the Index: T-SQL Tuesday #10 – Indexes

I don’t really have the time to write this month, but indexes are such a key feature of databases that I thought I should.

A whole post would take more time than I have. Instead I’m going to mention something that Rob Farley (of LobsterPot Solutions in Australia) said in a SARGability talk for the AppDev Virtual Chapter of PASS a few months back.

Rob talked about the way that we find entries in an index using a Seek operation, and compared to using a phonebook. In particular, he described the bit at the top corner of the phonebook, which we use to find the right page before looking at each record. This is very much how an index works. An index is stored in a b-tree, with the levels at the top being like the corner sections in the phonebook, and the leaf level of the tree being like all the records there. 

A quick bit of searching flickr found an image at http://www.flickr.com/photos/blinky5/376596220/ which describes this perfectly. If you’re looking for Wilma Todd, you can use the top corner to figure out which page she’s on and then find her record on the page very easily.  So what’s my point?: The next time that you’re picking a data type for a column think of this.  If you use a data type that’s twice as large as what you really needed (int vs. smallint, nvarchar vs varchar) and then you realize you need to put an index on it, it would be be like doubling the font in this phone book thereby requiring twice as many pages to print the book.  May not seem like much but when you have to scan for data you’re now going to have to physically touch twice as many pages.  And hey, now the books twice as heavy as it needed to be just like your backups are going to be! 

Anyway, I don’t have any more time to spend on this post, gotta get my code ready in case I need to fill in tomorrow for someone during the 24 Hours of PASS, so I’ll just publish it and wish you all a Happy T-SQL Tuesday.