How can I force a query to not use a index on a given table?

Question

I'm currently doing some testing to determine the performance implications of including an index on a given column in SQL Server 2005.

The test data set I'm using has approximately ~72 million rows (about 6 GB of data). In order to actually test the performance of the index I need to be able to compare the performance with and without the index there.

That's all well and fine, but creating an index in the first place is not a cheap operation. If I want to test the table without the index, I need to, at the very least, disable the index. To test with the index I need to re-enable it which takes quite a long time.

Is there any way that I can force SQL Server 2005 to ignore a given index when it's executing a query? I don't want to have to disable the index just to test a query since it takes such a long time to disable the index.

Answer 1

SELECT * FROM MyTable WITH (INDEX(0)) WHERE MyIndexedColumn = 0 

Query would normally use the index on MyIndexedColumn, but due to the table hint, it will instead tablescan.

---

SELECT * FROM MyTable WITH (INDEX(IndexName)) WHERE MyIndexedColumn = 0 

Query would normally use the index on MyIndexedColumn, but due to the table hint, it will instead use the index named IndexName.

Answer 2

I'm working with all different kinds of DBs and can never remember the specific hint when I need it. Therefore I'm using a pure SQL approach that (currently) works with all the DBs that cross my way.

The idea is just to make it impossible for the DB to use the specific index by obfuscating the respective expression in the SQL. E.g. when a where clause makes the database believe it's best resolved using an index, but it isn't.

SELECT * FROM MyTable WHERE MyIndexedColumn + 0 = 0 

Similarly, you can add an empty string to a string value. Current optimizers do no resolve such expressions can cannot use an index on (MyIndexedColumn).

This is actually an anti-pattern I described in my book. Here are some on page about math in SQL

It's definitively good enough for ad-hoc testing. In production code, hints are more expressive of course!