Which T-SQL statement should be used to instruct a database management system to use an index instead of performing a full table scan?

Question

Hello everyone,

I am currently preparing for the DP-900: Microsoft Azure Data Fundamentals exam and would like to get an expert's clarification on a specific question.

The question is as follows:

Which T-SQL statement or clause should be used to instruct a database management system to use an index instead of performing a full table scan?

The common options are SELECT, WHERE, and JOIN.

My understanding is that the correct answer is the WHERE clause, because its primary function is to filter rows based on a condition. If that condition is on an indexed column, it allows the query optimizer to perform an efficient index seek.

Could you please confirm if this reasoning is correct, especially in the context of the fundamental concepts tested in the DP-900 exam?

Thank you for your help!

Answer 1

Let me first say this, I don't care for examinations for a single cent. I can answer real-world questions, but I am not trying to answer malformed exam questions.

The questions ask for a statement, and then you give the options SELECT, WHERE and JOIN. SELECT is a statement. WHERE and JOIN are clauses that can be used in SELECT, UPDATE and DELETE statements, as well as in CTEs. So that is a bad start.

And, no, there is no "statement" to the DBMS to force the use an index. But you can supply a hint. For instance, take this query:

SELECT * FROM tbl WHERE indexedcol > @somevalue

The index on indexedcol is a non-clustered index. Say that @somevalue is a parameter. At compile time, SQL Server does not know the run-time value, so it makes a blind guess of a 30% hit rate. With that hit rate, a table scan is a lot more efficient than an Index Seek. However, you may know that in practice, @somevalue will always be close to the max value of indexcol, and the hit rate may even be below 1%. In this case, you can modify the query to read:

SELECT * FROM tbl WITH (INDEX = index_onindexedcol) WHERE  > @somevalue

I should add that these sort of hints are something we should use only sparingly. There are some more options to resolve this situation, but I let stop here for today.