Category: How to Think Like the SQL Server Engine

In our last cliffhanger episode, I said that if we ran this query: [crayon-6a4717e195e15889477831/] And we had this index: [crayon-6a4717e195e1b569103938/] Then we would have to do all of these things: Look up user #643 on the clustered index by doing a seek - because thankfully our kind developer included the clustered primary key in the…

In our last episode, in between crab rangoons, I had you create either one of these two indexes:
[crayon-6a4717e196031971574403/]
And I said that the leaf pages of either index would look the same:

In terms of the space they take up on the leaf pages, it doesn't matter whether columns are in the keys of an index or in the includes. It's the same amount of space on the leaf pages. To see it, run sp_BlitzIndex focused on that table:

In our last episode, we were running into problems with these two queries: [crayon-6a4717e196568167056640/] When SQL Server saw the function in the second query's WHERE clause, it punted out with a 1-row estimate, which caused us problems. SQL Server did an index seek + key lookup where it wasn't appropriate. Well, if we're not allowed…

In our last couple of queries, we've been using a simple query to find the Ids of everyone who accessed the system since mid-2014:
[crayon-6a4717e196f27699198937/]
But Ids alone aren't all that useful - so let's add a few more columns to our query:
[crayon-6a4717e196f2c452961098/]
Now think about how you're going to execute this query plan in plain English, as a human being. You have two copies of the table: the nonclustered index (black pages) with LastAccessDate, Id:

When we left off in the last post, our users kept running this query, and they want it to be really fast:
[crayon-6a4717e197457207130904/]
Let's pre-bake the data by creating a copy of the table sorted in a way that we can find the right rows faster:
[crayon-6a4717e19745c221360778/]
This builds a separate copy of our table (also stored in 8KB pages) that looks like this: