Non-clustered indexes are a very important component in SQL server. We’ve discussed them briefly in a previous chapter. They basically act like an index in the back of a book. They give you a quick shortcut to find data that’s organized in an alternative way. Today, we’ll learn how they work and how SQL utilizes them. This will help us understand where to put them when designing a database.
How Non-Clustered Indexes Work
Let’s take a look at how SQL uses non-clustered indexes. As we already saw in the image above, SQL has it’s own little shortcut table down below that tells it what colors are what age range. If we ask them system the following SQL, the index is not used:
SELECT * FROM Students WHERE StudentID > 3
In this case, the clustered index, or physical storage pattern is more important. SQL knows that the data is in pages A and B and leaves B, C, and D. SQL uses the clustered index to figure out the best way to bring back these results.
However, now consider this SQL statement:
SELECT * FROM STUDENTS WHERE AGE > 22
SQL is smart enough to know the physical storage has nothing to do with age. Therefore, it’s not going to use the clustered index in this scenario. However, it’s perfect for using our non-clustered index on age! SQL quickly puts together that it can throw away all the A rows, since they are under 21. It can grab all the C and D rows without even looking at them, since they are over 22. Lastly, it just has to look at the B range and see which ones have a value of 23. That’s it!
As you can see, an index in the right place can save SQL server lots of time! In the next section, we’ll learn how to decide when to use a non-clustered index. These decisions can drastically impact the performance of your database. It’s worth time learning to do it right.
When To Use Non-clustered Indexes
When deciding what fields need indexes, you need to think about how your data will be accessed. As you saw above, the non-clustered index came into play when someone requested the indexed field in their WHERE clause. Think about each field in your table. Which ones are likely to be used in WHERE clauses? If your system does not allow ad hoc queries but only runs reports, you should already know what kinds of parameters people will search on. A good starting point is to put indexes on any field that is regularly used in WHERE clauses.
Creating A Non-Clustered Index
CREATE NONCLUSTERED INDEX [index name] ON [table name] ( [existing column] )
CREATE NONCLUSTERED INDEX (ClassID_Index) ON Class (ClassID)
Creating a non-clustered index is almost exactly like creating the clustered except that we can have as many as we want. However, too many indexes can actually slow SQL down as it will have to constantly update the indexes whenever rows change in the system. Keep indexes to the most-utilized columns only.
Making Sure Indexes Are Used
When it comes to making sure SQL is utilizing indexes, the WHERE clause is very important. We’ll look at some of these concepts further along, but it’s good to consider them now when you start writing queries that use your indexes.
- Use BETWEEN Clause Instead of WHERE with AND when using dates and numbers.
- Avoid converting columns to another data type in the WHERE clause.
- Filter results with indexed columns before moving them to temp tables.
Now you should be well equipped to create indexes that properly take advantage of everything SQL can do to help you get your data back as efficiently as possible. Now we’re ready to move on to our next topic, the stored procedure. This is one of the most important features of SQL server and will help you build a powerful system that encapsulates everything we’ve learned so far.
blog comments powered by