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

Solution 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.

Solution 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!