Running Out of INT

 Welcome to the exciting world of Indexes!

I truly do love indexes! I started intentionally learning and diving into index creation and maintenance a few years back taking Brent Ozar's Fundamentals course. Recently I completed his full suite of Fundamentals and Mastering courses. Fun tip, he's got a Black Friday sale coming up and I strongly recommend getting that training if you have the budget for it.

Onwards though! Today, I'm writing about tables and their indexes (or lack thereof). To illustrate my point and keep within my weightlifting motif, we're going to be using this weight rack as an example.

Using the above graphic for illustration, imagine this weight rack is our table (dbo.dumbbells). Tables in SQL Server store data in 8K pages, but in the gym today, our data is stored in 8K shelfs. Each dumbbell here is a record in our table.

Heaps

The data in our table is unstructured; weight sizes are all mixed together, there's no rhyme or reason to their order. Just however the data went in is how it's store. There's even some gaps in here where data has been deleted.

This is known in the SQL world as a Heap. No index, no structure, just a mound of data. So if you walk up to the weight rack and you need a set of 35 lb dumbbells, you have to scan the entire rack until you come across them. Just like in the database world, if you need a particular record from a table with no index and no primary key (my next topic), the engine has to start reading the whole table until the desired record is located.

In small tables, probably not a huge deal. But once you start growing your data, this can drastically slow down your database performance. Just like it takes time to find the set of dumbbells I need for my workout.

And index is a way for the database engine to seek into a table and find a specific value. Think of it just like an index in a book. You want to find information from our table where "Weight=35". The index will be able to tell you what shelf (8K page) the requested value is on. There are two types of indexes at our disposal.

Clustered Indexes

One way to combat a heap and improve performance is with a Clustered Index. A clustered index is often, but not always, based on the primary key of a table. A static, ever increasing value that will be unique to your table. My weights analogy falls apart a little bit here since dumbbells come in pairs but stick with me.
What makes a clustered index? This is where the data is stored in some order. For the simplest example, let's say our table has an identity field that begins a 1 and then increments by 1 for every record inserted. The clustered index works because the data will always be in order. When it comes time to look for a record, we can zip right to the record we need!

Non Clustered Indexes

A non clustered index is where the data may be all over the place inside the table, but we're keeping it sorted in different orders. You might want an index on color, size, date, etc. So for our weight rack example, we would have an index on weight size. Clearly from our picture, the data is mixed up, but our index keeps in the order we want.

Notice that our index keeps the weight values in the order we want, but the data itself is located on the different shelves. So if I want the 45lb, the engine can seek right to the row we want, it doesn't need to read 5, 10, 30, etc. It skipped right to that row, found it on shelf 1 and returned to me.

These are very simplistic examples, but I hope they get your brain churning as to how tables and indexes work. Just imagine a gigantic weight rack with weights all mixed up and out of order. If we have no way of knowing where a dumbbell exists, we have to start at the front and read the whole thing until we locate the desired value.

But with an index, be it clustered or non, we can seek right to the spot in the weight rack where retrieve our dumbbell (data).

Here's me doing some shoulder presses with some data I found using my index!