I know I’ve covered quite a bit of the CREATE INDEX statement, but there is even more to learn. Today I want to cover ASC, DESC, and the WHERE clause for indexes.

ASC | DESC

By default, indexes are created in ascending (ASC) order. This is fine most of the time. You’re going to be looking for information in order. Think about looking for people’s names, you normally look in alphabetical order, A through Z. But every once in a while you’re going to be looking for data where this isn’t the best solution.

That’s when you can switch things up and order your index in descending (DESC) order. Imagine you have a table where you are writing a custom log. Every entry has a date stamp. If you always looked up data looking for the last (or most recent) time a certain even happened, wouldn’t it be faster if your index on that date stamp were in reverse chronological order?

It’s really as simple as

CREATE INDEX ix_tableName__columnName
ON tableName (columnName ASC | DESC)

Keep this in mind. It’s rarely used, but if you can pull this one out of nowhere… it will be your advantage!

WHERE <filter_predicate>

If you only wanted to index a subset of your table, let’s say the last 90 days of your logging table, you can do that using the WHERE clause.

You could have many reasons for wanting to do this. You want to save space in your index. Your logging table keeps several years of data, but you only regularly check the last 90 days. Seems like a waste of space to index anything older than 90 days. Of course, you realize you’ll need to run a recreate statement on a schedule to keep up with what is included and what isn’t.

Other reasons could include narrowing the focus of your searches, minimizing duplication, etc. This is another rarely used tool, but when you need it, it can really improve your performance!

That’s it for today, have any questions? Just let me know!