Transcript
Welcome to ‘How Keys and Included Columns Work in SQL Server’. I’m Kendra Little from SQLWorkbooks.com.
In this course, we’re talking about disk-based rowstore indexes in SQL Server
These are the classic indexes that we use for most of our tables to help our queries get great performance.
In this session, you’ll see demos where we look at these indexes using undocumented commands.
- How are these structured?
- How does SQL Server lay out these indexes?
- How does it treat keys vs. includes?
- What does that mean about how we can access data efficiently in SQL Server?
We’ll finish up with a quick quiz.
Our demos focus on a nonclustered index in SQL Server, but for disk-based rowstore indexes there are a lot of similarities for clustered indexes as well
When you create a clustered index on a table– and you can only create one on a table– you are sorting the table itself by the key column or columns that you choose for that clustered index.
The clustered index IS the table itself. You pick the key or keys to sort it by, but all of the other columns in the table are also still in the table. They are in that structure. They are automatically included– you don’t have to decide to include them or not. You’ve already put them in the table so they’re definitely coming along for the ride, and they are in that structure no matter what!
When it comes to nonclustered indexes…
When you create a nonclustered index you create a new structure: a secondary helper structure to find data in that table. You define what columns are in that nonclustered index. You have to pick at least one key column, and you may also choose to have one or more included columns in a nonclustered index as well, but they’re optional. You do not have to have included columns.
We’ll be looking at a nonclustered index example: where are those keys? If you put includes in, where are those includes? How is the whole thing sorted?
Let’s dive into our demo and see: Hey, let’s look around at an index structure.