Temporary Indexes

Temporary placeholders can be valuable

Recently I was studying for the MCM exam and one of the things that came up in my research was the idea of temporary indexes. I had never used them before and was intrigued by the idea. I have dropped indexes before a large load and then recreated them, but these are indexes I planned to keep on the table indefinitely.

There are always processes that you run periodically on some schedule. These might be end of month, or end of year processes where you run a series of reports or queries that aren’t necessarily run at other times in your business cycle.

Since this was a new concept for me, I wanted to ask and see how many others might actually be using this. Maybe you’ll get an idea for how to speed up some of the periodic, but very important, processes you run. For this Friday:

Do you use temporary indexes?

By temporary, I mean indexes that you create for a process and then discard at the end of a process. These could be indexes that you use to supplement some type of data load, perhaps indexes used in staging tables. Or they could be indexes that are added to some OLTP tables for the processing of certain reports in a short period of time.

However you might implement them, let us know how you use them, and how they have improved performance for you.

Steve Jones

The Voice of the DBA Podcasts

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged , . Bookmark the permalink.