Hey everyone, welcome back to part six of topic seven in our database class. And in this video, I'm going to provide several examples that demonstrate how you can implement indexes in SQL Server. Let's get started. Okay, so we're out here on SQL Server Management Studio. And we're familiar with this employees database now. We've used it several times. So just as a brief reminder, it looks like this. We just got our four tables. And they connect departments to employees to employee skills. As shown here. Now note the primary keys. Employee ID for the employee table department ID for the department table, the combination of employee ID and skill ID for the employee skill table. And of course the skill ID for the skill table. All right. Now if we go over here to our object explorer, which all just pin up here so we can keep it open. And we open up any of these tables like department as a fine example. You will find in here an item named indexes. And if we expand that out. Oh, look at that. There's an index in here. You'll see we have an index in here named pk underscore department. And we're told in parentheses this is a clustered index. So we didn't do anything to create this. It was created automatically when the table was created. And if we look at any of these other tables, we will find similar results. All right. So here's the employee table. In this case, I have an additional one that I created. I mean delete that for now. So we have our clustered index here automatically created in the employee table as well. If I look at employee skill. I'll see out here that I have a clustered index there. And same thing if I look at my skill table. So I did nothing to create these. Except for create the table when I created the table. These indexes were created automatically. You can mine. But there is no difference here between a clustered index and the table. Clustered index is the table. So it makes sense that there's one and only one per table. All right. So with that said, let's take a look at what these indexes look like. So this is our clustered index for the employee skill table. And you'll see it has these two columns in it. It's got the ID and skill ID. So these together are the basis of the index. Now a very important point to make is that the order matters. So this index is built first on employee ID. And then on skill ID. So if you want to imagine what the data look like. In the index. So imagine this is our employee ID. And this is our skill ID. All right. There we go. So we might have employee ID. This is what the index would look like. Like one, one, one, two, two, three, four, four, five, five. All right. And then our skill IDs would be next. This might be skill ID. I don't know three. It's filling some random skill IDs out here. Four, seven, let's see. This person can have skill number eight. Sorry, that's got to be bigger. I want to nine. One, three, and five. So it might be organized like this. Right. It's like a primary sort on this column. And then a secondary sort on this column. So within employee one, my skills are ordered numerically. In this case, an ascending order. Right. Three, four, seven. Here's employee two. Within employee two, they're sorted by skill ID. Within employee number four, they're sorted by skill ID. Within employee five, they're sorted by skill ID. So this is how the index is also constructed, which means if employee ID key column in the index, then I can use that index to quickly locate employees by their employee ID, but I cannot use it to quickly locate records by skill ID. Right. So it's interesting. So when we take a look at this index then, and we see that it has two index key columns, employee ID and skill ID, we need to know that employee ID is the primary sort. And skill ID is the secondary sort. So as terms of like speed, if I'm trying to use this to find something quickly, I will be able to find employee IDs quickly, but I will not be able to find skill IDs quickly. I could reverse that. As you can see, there are options over here on the right where I could change it. So like skill ID could be moved up. And then skill ID would become the primary sort, followed by employee ID. So yeah, let's see how we can create ourselves a non clustered index. Up until this point, we have everything that's just been clustered indexes, but let's say we want to create a non clustered index so that we can search for something else quickly. So let's say in our world, I would like to be able to sort or search for skills quickly by the name of the skill, which as we can see here is a skill name column. So what I can do is just make a new index. So I'll right click as I often do say new index. And you'll see we don't even have an option to make it clustered index because one already exists. So we're going to make a non clustered index. And then we get our new index dialog box. And you can see that selected table is here. We have to specify a name for our index. It'll suggest one, but I usually like to preface mine with ID X, just indicate that it's an index and then whatever the name of the column is. Maybe this is like skill name. That way I can visually tell what it is note that you can mark whether or not it's going to be unique. In this case, I can probably assume that it's unique because it's unlikely that I will have two skills with the same name. So I can assume that it's unique and by selecting that, it's a constraint. Right, the database will yell at me if I try to add a new row to this skill table where the skill name is identical to some other name because it won't be able to build the index. But then what I'm going to do is add myself a nice index key column. So this is the column that will be used as the basis for building this index. And in this case, I want it to be the skill name since my goal is to be able to quickly locate any skill by its name. So I just click on add and you see I can select from the available columns in this case, it's skill name. And you can see by default, it's going to use an ascending sort, but we can change that if we want to. It's really no need to change that here. Which we're told the data type, the size, whether it's identity column, well, these are all just inherited properties from the underlying attribute. And I could be satisfied with that. So if I click on OK, it's going to build my index. And then you can see we have our skill name index over here. And we're told in parentheses, it is unique and non clustered. I'll just open this back up to illustrate one final point. When our index exists like this, if we want to, for example, to be able to find the skill IDs associated with a particular skill, the way that it would work right now is that the database, we would give it some input as a skill name. So I don't know, maybe a public speaking might be a skill. And with this index that we just created, the database would be able to very, very quickly locate the record in the index for the public speaking skill. At that point, it would need to follow a pointer. To the underlying table, the skill table at which time it would be able to identify the skill ID that is associated with this skill name. So as this index is currently designed, that's what would happen. However, it doesn't have to work that way. As I mentioned a few moments ago, we can have additional included columns, which you can see here, that we can add to the index. To help the database do its work as necessary. So I could add out here. The skill ID and it's going to take up a little extra space. But now when I build this index. So hey, you're going to have to recreate the index. That's fine. Now that index can be used to quickly locate any skill just based on the name of the skill. And at that point, the database would also have the ID of that skill, which it could use, for example, to join that record to some other table. So we have that primary key value. So as an example, in this database. Let's take a look here. So here's our database design. And you can see we have department ID down here in the department table. That's a primary key. So we know that there's already going to be a clustered index built on this column. So we also have department ID up here in the employee table, which is serving as a foreign key. And by default, it's not going to have an index. So if we wanted to be able to quickly join records in the department table to records in the employee table, then we would need to build an index on this end of the relationship. So we can quickly find department IDs in the department table. But if we want to quickly be able to identify some rows in the employee table for any given department ID, let's say like department number three and want all the employees who work in department number three. Then I would have to build a new non clustered index on this department ID for in key, which, of course, I could do just by going into the employee table. And the creating a new non clustered index. Where we would put in the department ID as the index key column. And give that a. Some sort of reasonable name. So then we would have that one there. And at this point, I could very, very quickly join together my employee and department tables, since there would be indexes on both ends of that relationship. Even if they would alwaysipp the whole Mis