Transcript

We’re going to walk through a couple quick diagrams of this in the slides.

This does use pseudo-code. I’ve shortened a lot of things. It’s not T-SQL that will actually run, just for the sake of keeping the code small.

Example query 1 - seeking

We want the EmployeeKey where the LastName equals G and the FirstName equals A.

SQL Server identifies, “Hey, I’ve got a great index for this.” It has three key columns, but the first key column is LastName, our priority in the query is the FirstName key column, so this is a great match for this.

The index also has EmployeeKey in it, and it has FirstName. We can get all the data from this index: the query is ‘covered’ by this index. I’m going to go to the root page of the index, and then I’m going to use the guideposts on there.

If the LastName is G and the FirstName is A, that’s before the guideposts I have for LastName G, FirstName M. So I can go to the page before that, I can seek to the leaf and I can get the data that I need.

Example query 2 - scanning a non-leading key

if I want the LastName where the FirstName is A, the optimizer will look around and say, “Hey, do I have an index that leads on FirstName? Where the keys lead on FirstName?” But I don’t.

I do have an index where everything is sorted first by LastName and the FirstName column is in there– but it isn’t the first column listed in the key.

So, it’s not great, but it’s probably better than scanning the base table if I compare how large they are. This index, I figured out, it is smaller. So, I’ll go to the root of the index.

These guideposts don’t help me a whole lot because everything is sorted first by LastName. FirstName = A could be all over this table, or there could be no rows for it, as far as I know. I’m going to go to the first page in the leaf and then I am just going to follow the pointers through the leaf, read all of the pages in the leaf and feed the rows into a filter– and check all the rows and be like, “Is your FirstName A?” “Is your FirstName A?” “Is your FirstName A?”

The great news about this is, our index isn’t designed to handle this query perfectly, but SQL Server can still use the index

I think that’s fantastic that it’s flexible enough to do that.

Example query 3- scanning an included column

Similarly, imagine we have an included column whose name is Include. I said it was pseudo-code. So, imagine that this table has an included column named Include and we’re searching on that. Well, it will try to see: do I have an index where Include is not an included column, where it’s the first column in the key?

And we do not. But this index has the included column. The root page doesn’t have any information to help us. It doesn’t even have included columns. But actually it does have information to help us. It’ll help us get to the first page in the leaf and then, we can scan all the pages in the leaf which have the included columns.

Among those included columns is the column named Include, and we can run a filter and check for each row. Is Include = E? Is Include = E?

Very flexible and very smart, and I love that it can use those indexes in those ways.