Exam DP-900 topic 1 question 100 discussion

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

As far as I know, the SELECT statement allows you to force using a specific index by adding WITH (INDEX(IndexName)) after FROM. In my opinion, A is the correct answer.

The question talks about "statement" not "clause" (WHERE is a clause). The SELECT statement allows using WITH to specify an index and force its use. In my opinion, "A" is the correct answer.

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

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?

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

option A

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;

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

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

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.

FWIW, GPT says "B"

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

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.

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;

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.