DP-900 Exam - Question 100

This is quite tricky WHERE is a clause but SELECT is the statement so answer A is correct

Not a trick question, just how much you understand database. Select * From Countries where CountryName = "USA" This is a full table scan because CountryName is not a key, the engine need to compare every row. Select * From Countries where CountryID = 33 This is not a full table scan, it become a index scan because CountryID is a key so the engine know it can skip to that ID Select * From Countries This is a full table scan but with no criteria, so it extract everything out

The answer should be "WHERE" because part of the question statement contains "use an index instead of performing a full table scan". Here is the "WHERE" statement directly jumps the related index table and fetches only the record match from the data.

The "WHERE" T-SQL statement should be used to instruct a database management system to use an index instead of performing a full table scan. When a query uses a WHERE clause to filter the results, the database management system can use an index to quickly locate the matching rows in the table. This is much faster than performing a full table scan, which would require the system to read every row in the table to find the matching rows. The "SELECT" and "JOIN" T-SQL statements by themselves do not instruct the database management system to use an index. The SELECT statement is used to retrieve data from one or more tables, and JOIN statement is used to combine data from multiple tables. But to make these statements efficient in terms of performance, the use of WHERE clause and indexes are important.

Statement => Instruction, so, must be WHERE

The correct answer is WHERE, as this is a search condition of the statement. So can be used to target one or more indexes. SELECT is wrong as this doesn’t influence what indexes may or may not be used.

Without the three possible answers the correct answer would be "use an index hint" which is part of the FROM clause. A SELECT alone doesn't meet the requirement. Maybe the answer is A, because it is the only "statement", the other two are just "clauses".

Answer should be "Table Hint" which is not in option

Do I need to explicitly specify? 1) No, no Need to specify explicitly. 2) DB engine should automatically select the index to use based on query execution plans it builds from @Tudor Constantin answer. 3) The optimiser will judge if the use of your index will make your query run faster, and if it is, it will use the index. from @niktrl answer https://stackoverflow.com/questions/6593765/how-to-use-index-in-select-statement Answer should be : A

Correct answer is B. Where -- https://devzone.advantagedatabase.com/dz/webhelp/Advantage9.1/how_indexes_are_used_by_the_where_clause.htm

I don't think any of these are correct. The closest is the Where clause. As for Select, I question what there is about it that would imply or force the use of an index.

it should be where

In T-SQL, you can use a query hint to instruct the database management system (DBMS) to use a specific index instead of performing a full table scan. The hint you would use is called WITH(INDEX(index_name)). Here's how you would use it: SELECT column1, column2, ... FROM table_name WITH(INDEX(index_name)) WHERE condition;

Confused question....

"Where" is not a statement, but a clause. Although the filter criteira is defined on "where", it is the "select" statement directs index usage.

Which T-SQL statement should be used to instruct a database management system to use an index instead of performing a full table scan? A. SELECT B. WHERE C. JOIN ChatGPT B. WHERE The T-SQL statement used to instruct a database management system to use an index instead of performing a full table scan is the WHERE clause in a query. By specifying appropriate conditions in the WHERE clause, you can guide the query optimizer to use available indexes to efficiently retrieve the desired data.

Tricky question, But A seems correct in this context

Correct Answer

The correct answer is SELECT

CORRECT A

it is FROM, but it is not present in the list. so none is correct. I am pretty sure you will get more than 3 options on the exam, FROM will be one of them. Example: SELECT * FROM mytable WITH (INDEX (ix_date)) WHERE field1 > 0

Dropped the exact question in chatgpt, this was the answer... In T-SQL, you can use the SELECT statement with a query hint to instruct the database management system (DBMS) to use a specific index instead of performing a full table scan. The query hint that accomplishes this is called INDEX or INDEX=<index_name> hint.

SELECT uses the index, correct answer SELECT is a statement WHERE not a statement, it's a clause to filter by column value SELECT * FROM table1 USE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3;

A. SELECT In the SELECT statement, you can specify which index to use for the query. This is typically done using query hints, like WITH (INDEX(index_name)), to suggest the use of a particular index. This approach is used when you have a specific understanding of the database's structure and the indexes available, and you want to guide the SQL Server query optimizer to use a specific index. The WHERE and JOIN clauses are used for filtering and combining data from different tables, respectively, and do not directly instruct the use of an index.

The key word here is STATEMENT. WHERE is a CLAUSE.

FWIW, GPT says "B"

from chatgpt : To instruct a database management system to use an index instead of performing a full table scan, you typically use a hint in the SELECT statement. Therefore, the correct choice from the provided options is: A. SELECT You would use the WITH(INDEX(index_name)) hint within the SELECT statement to specify the index to be used.

I agree with NIKnithiy on this one. SELECT is a statement, WHERE is a clause. The link in the answer is also nice as it lists the statements in question. https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16

Answer is option B:: WHERE clause tells a Select to 'streamline' the query execution, on the search criteria (the PREDICATE) specified in the "where" clause. To use an index in a query, if the index is defined on the column specified in the WHERE clause, the query makes use of the specified index:: CREATE TABLE person( id INT NOT NULL, first_name VARCHAR(15) NOT NULL, last_name VARCHAR(15) NOT NULL, age INT NOT NULL, PRIMARY KEY(id) ); /* Index on First Name */ CREATE INDEX person_fname ON person (first_name); /* This next query shows whether the query uses the intended index */ explain SELECT * FROM person WHERE first_name = "Donald" \G

option A

the correct answer is select. because it is a statement. where is a clause and not a statement

From CGPT : The correct answer is B. WHERE. In T-SQL, the WHERE clause is used to filter records. When an index is available on the columns specified in the WHERE clause, the database management system can use the index to quickly locate the rows that match the condition, avoiding a full table scan. Do you have any other questions about T-SQL or databases?

https://learn.microsoft.com/en-us/sql/t-sql/statements/statements?view=sql-server-ver16