Adding a clustered index

- You can find out what indexes are operating on your DataTables, by expanding the "Indexes" folder below the table name. For instance, I'm going to take a look at the "Invoices" table here. Let's go ahead and expand it open, and we'll find the folder called "Indexes." If I expand that, you'll see that we already have a clustered index here based off of the "Invoices" table, because it's the primary key for that table. Since all of tables already have a primary key, you'll find that all of them also already have a clustered index already in place. If you want to find out information about the index, simply double-click on it. That'll open up the "Index Properties" window here, and we can go through the different pages that we have available, to see all of the different properties of the index.

For instance, if I go here to the "Fragmentation" page by clicking here, we'll see that the average row size is currently set to 23. This is the number of records that are grouped into the lowest level of the B-Tree, which is referred to as the "leaf level", which we can see down here on the very bottom. In the case of the "Invoices" table, we can see that the leaf level includes an average of 23 records per leaf. Let's go ahead and say "okay" to this to close out the "Index Properties" window. And that will return us back to our Object Explorer. Now, if you wanted to add a new index on a table, simply come over here to the "Indexes" folder, right-click, and say "New Index".

Notice that "Clustered Index" is grayed out. That's because we can only have one "Clustered Index" per table, and we already have one, so I can't add another. I can add a "Non-Clustered Index" and we'll talk about what that is in the next movie. Now, if you want to add a "Clustered Index" on a new table using SQL, DDL Commands, we can do that by starting up a new query window. So let's go ahead and exit out of that, and I'll come up here to the "New Query" button, and we'll start creating a new table here that's just going to be an example. I'll say "CREATE TABLE". And the table name is just going to be "ClusteredExample".

I'm going to add in a couple of fields. The first one is going to be "ExampleID", and it's going to be an "int" data type. And the next one is going to be, "FirstName" and it's going to be a "char[50]" data type. Let's go ahead and execute this code that will create a new table here in my database. If I right-click on "Tables" and say "Refresh", we should be able to find it here in the "ClusteredExample" table right there. Okay, so now I've got a table I can create a new "Clustered Index" on. Because it doesn't have a primary key established, there is no "Clustered Index" in place right now.

So, in order to create a new "Clustered Index", we'll do the "CREATE" statement, and this time we're going to specify what we're creating is a "Clustered Index". The name of the index typically starts with the "IX_" prefix. And then we have the name of the table that we're going to apply the "Clustered Index" to, followed by an "_" and then the name of the field that the "Clustered Index" is going to be applied on. In this case, the "ExampleID" field. So, that's the name of the index that we're going to create.

Then we get to specify where we're creating it. So, I'll type in "ON" and then the "ClusteredExample", which is the name of the table. And then in parentheses, the name of the field, which is, "ExampleID". We'll go ahead and finish that with the closing parenthesis and the semicolon. And now when I execute it, I should get the message down here that the Command was completed successfully. And if I take a look at this "ClusteredExample" table, and come down here into "Indexes", we'll see the new "Clustered Index" has been added to this table. Let's go ahead and close the SQL window here, and I'm not going to save any changes.

And we can go ahead and also delete this table now. Just right-click on it and choose "Delete" from the menu. Go ahead and say "OK", and that table will be deleted from the database. So that's how you would create a "Clustered Index" on a table that didn't already have one. In reality though, all of your DataTables should have a primary key. In which case, the creation of "Clustered Index" will happen automatically. So there really should be little to no reason why you would need to create one on your own.