BookReviews

Question 1

You create an index to support the WHERE clause of a query. However, SQL Server
does not use the index. What are the possible reasons? (Choose all that apply.)

• A - The arguments in the predicate are not searchable.
• B - SQLServer does not consider using an index to support the WHERE clause.
• C - The predicate is not selective enough.
• D - You are in the context of the tempdb database, and SQL Server does not use indexes in this database

Answer 1

Correct answers: A and C

• A - Correct: SQL Server does not use an index to support the WHERE clause if the arguments in the predicate are not searchable.
• B - Incorrect: SQL Server supports the WHERE clause with indexes.
• C - Correct: SQL Server might decide not to use an index to support the WHERE clause if the query is not selective enough.
• D - Incorrect: SQL Server considers using indexes in the context of the tempdb database just like in the context of any other database.

Question 2

^{What are the benefits of using a CTE over derived tables? (Choose all that apply.)}

^{A.CTEs are better performing than derived tables.}

^{B.CTEs don’t nest; the code is more modular, making it easier to follow the logic.}

^{C.Unlike with derived tables, you can refer to multiple instances of the same CTE name, avoiding repetition of code.}

^{D.Unlike derived tables, CTEs can be used by all statements in the session, and not just the statement defining them}

Answer 2

^{Correct Answers: B and C}

^{A.Incorrect: All types of table expressions are treated the same in terms of optimization— they get unnested.}

^{B.Correct: If you want to refer to one derived table from another, you need to nest them. With CTEs, you separate those by commas, so the code is more modular and easier to follow.}

^{C.Correct: Because the CTE name is defined before the outer query that uses it, the outer query is allowed to refer to multiple instances of the same CTE name.}

^{D.Incorrect: CTEs are visible only in the scope of the statement that defined them.}

Question 2

Which full-text search elements can you use to prevent indexing noisy words? (Choose all that apply.)

A. Stopwords

B. Thesaurus

C. Stemmer

D. Stoplists

Answer 2

Correct answers: A and D

A.correct: Stopwords include noisy words.

B.incorrect: Thesaurus is used for synonyms.

C.incorrect: Stemmer is used for generating inflectional forms of words.

D.correct: You group stopwords in stoplists

Question 2

When referring in the OUTPUT clause to columns from the inserted rows, when should you prefix the columns with the keyword inserted?

A. Always

B. Never

C. Only when the statement is UPDATE

D. Only when the statement is MERGE

Answer 2

Correct answer: A

A. correct: When referring to elements from inserted rows, you must always prefix the column with the keyword inserted.

B. incorrect: There are no cases where you can omit the keyword inserted—even if the statement is just an INSERT.

C. incorrect: It’s true that you need to prefix inserted elements in an UPDATE statement with the keyword inserted, but not just in an UPDATE statement.

D. incorrect: It’s true that you need to prefix inserted elements in a MERGE statement with the keyword inserted, but not just in a MERGE statement

Question 2

What is the advantage of using THROW in a CATCH block?
A. THROW in a CATCH block does not require parameters and so is easier to write.
B. THROW re-throws the original error so that the original error can be handled.
C. THROW causes an error severity of level 16 automatically.
D. The statement before a THROW requires a semicolon.

Answer 2

_{<strong>Correct Answer: B</strong><br></br><strong>A. incorrect</strong>: Although it is true that THROW does not take parameters in a CATCHblock, that is not necessarily an advantage.<br></br><strong>B. correct</strong>: The THROW statement in a CATCH block can re-throw an error andthereby allow you to report on an error in the TRY block without having to havestored any prior information. This makes it possible to do all error handling in the<br></br>CATCH block.<br></br><strong>C. incorrect:</strong> THROW always results in a severity level of 16, but that is not necessarily an advantage. RAISERROR is more flexible by allowing a range of severity levels.<br></br><strong>D. incorrect</strong>: Requiring a semicolon on the previous T-SQL statement is perhaps agood coding requirement, but it is not a benefit provided by the THROW command.}

Question 3

Which of the following are true about the SET QUOTED_IDENTIFIER statement? (Choose all that apply.)
A. When set to ON, QUOTED_IDENTIFIER allows you to use double quotation marks to delimit T-SQL identifiers such as table and column names.
B. When set to OFF, QUOTED_IDENTIFIER allows you to use double quotation marks to delimit T-SQL identifiers such as table and column names.
C. When set to ON, QUOTED_IDENTIFIER allows you to use double quotation marks to delimit strings.
D. When set to OFF, QUOTED_IDENTIFIER allows you to use double quotation marks to delimit strings

Answer 3

Correct answers: A and D
A. correct: When you set QUOTED_IDENTIFIER to ON, you can use double quotation marks to delimit T-SQL identifiers such as table and column names.
B. incorrect: When you set QUOTED_IDENTIFIER to OFF, you cannot use double quotation marks to delimit T-SQL identifiers such as table and column names.
C. incorrect: When you set QUOTED_IDENTIFIER to ON, you cannot use double quotation marks to delimit strings.
D. correct: When you set QUOTED_IDENTIFIER to OFF, you can use double quotation marks to delimit strings

Question 4

Which of the following strategies can help reduce blocking and deadlocking by reducing shared locks? (Choose all that apply.)

A. Add the READUNCOMMITTED table hint to queries

B. Use the READ COMMTTED SNAPSHOT option.

C.Use the REPEATABLE READ isolation level

D.Use the SNAPSHOT isolation level

Answer 4

correct answers: A, B, and D

A. correct: Adding a READUNCOMMITTED table hint causes no shared locks to be used by the statement.

B. correct: The READ COMMITTED SNAPSHOT option reads committed data from versions, not by acquiring shared locks.

C. incorrect: The REPEATABLE READ isolation level actually holds shared locks until the end of a transaction, and therefore can actually increase blocking and deadlocking.

D. correct: The SNAPSHOT isolation level also reduces shared locks by reading committed data from committed versions and not by using shared locks, so it also can reduce blocking and deadlocking.

Question 6

Which WHEN clauses are required in a MERGE statement at minimum?

A. At minimum, the WHEN MATCHED and WHEN NOT MATCHED clauses are required.

B. At minimum, only one clause is required, and it can be any of the WHEN clauses.

C. At minimum, the WHEN MATCHED clause is required.

D. At minimum, the WHEN NOT MATCHED clause is required.

Answer 6

Correct answer: B

A. incorrect: Only one clause is required at minimum.

B. correct: Only one clause is required at minimum, and it can be any of the WHEN clauses.

C. incorrect: There’s no specific WHEN clause that is required; instead, any one clause at minimum is required.

D. incorrect: There’s no specific WHEN clause that is required; instead, any one clause at minimum is required

Question 7

Which DMO gives you information about index usage?

• A.sys.dm_exec_query_stats
• B.sys.dm_exec_query_text
• C.sys.dm_db_index_usage_stats
• D.sys.indexes

Answer 7

Correct answer: C

• A. incorrect: The sys.dm_exec_query_stats DMO gives you statistics about queries, not indexes
• B. incorrect: The sys.dm_exec_query_text DMO gives you the text of the batches and queries
• C. correct: The sys.dm_db_index_usage_stats DMO gives you information about index usage
• D. incorrect: sys.indexes is a catalog view, not a DMO

Question 9

Which database do you have to install in order to enable the Semantic Search feature?

A. msdb

B. distribution

C. semanticsdb

D. tempdb

Answer 9

Correct answer: C

A.incorrect: The msdb database is installed by default and is used for SQL Server Agent.

B.incorrect: The distribution database is installed and used by replication.

C.correct: You need the semanticsdb database in order to enable semantic search.

D.incorrect: The tempdb database is installed by default and is used for all temporary objects

Question 10

How can you use output parameters in T-SQL stored procedures? (Choose all that apply.)
A. You can pass data into a procedure by using an output parameter, but you cannot receive information back from it.
B. You can pass data into a procedure by using an output parameter, and any change made to the parameter will be passed back to the calling routine.
C. You cannot pass data into a procedure by using an output parameter; it is only used for passing data back to the caller.
D. You cannot pass data into a procedure by using an output parameter, nor can you receive data back from a procedure from an output parameter.

Answer 10

Correct answer: b
A. incorrect: You can use an output parameter to receive information back from a stored procedure.
B. correct: You can both pass data into a stored procedure and retrieve information back from it, by using an output parameter.
C. incorrect: An output parameter is not used only for passing data back to the caller of the stored procedure. It is also used to pass data from the caller to a stored procedure.
D. incorrect: You can both pass data into a stored procedure and retrieve information back from it, by using an output parameter.

Question 11

Why is it important to prefer set-based solutions for querying tasks instead of iterative ones? (Choose all that apply.)

• A. Because set-based solutions are based on the relational model, which is the foundation of T-SQL
• B. Because set-based solutions always provide better performance than iterative solutions
• C. Because set-based solutions usually involve less code than iterative solutions
• D. Because set-based solutions enable you to rely on the order of data

Answer 11

Correct Answer: A and C

• A. Correct: Set-based solutions are based on principles from the relational model, and this model is the foundation of SQL (the standard language) and T-SQL (thedialect in SQL Server).
• B. Incorrect: Although it is not common, sometimes iterative solutions are faster than set-based ones
• C. Correct: Because set-based solutions are declarative and iterative solutions are imperative, set-based solutions tend to involve less code.
• D. incorrect: Set-based solutions cannot make any assumptions regarding the order of the data because sets are unordered

Question 13

What is the difference between the result of T1 CROSS APPLY T2 and T1 CROSS JOIN T2 (the right table expression isn’t correlated to the left)?

1. A.CROSS APPLY filters only rows where the values of columns with the same name are equal; CROSS JOIN just returns all combinations.
2. B.If T1 has rows and T2 doesn’t, CROSS APPLY returns an empty set and CROSS JOIN still returns the rows from T1.
3. C.If T1 has rows and T2 doesn’t, CROSS APPLY still returns the rows from T1 and CROSS join returns an empty set.
4. D.There is no difference.

Answer 13

Correct answer: D

• A. incorrect: Both return all combinations.
• B. incorrect: Both return an empty set.
• C. incorrect: Both return an empty set.
• D. correct: Both return the same result when there’s no correlation because CROSS APPLY applies all rows from T2 to each row from T1

Question 14

Which of the following is only possible when using the MERGE statement in regard to the OUTPUT clause?

A. Referring to columns from the source table

B. Referring to both the keywords deleted and inserted

C. Assigning aliases to output columns

D. Using composable DML

Answer 14

Correct answer: A

A. correct: Only in a MERGE statement’s OUTPUT clause can you refer to elements from the source table.

B. incorrect: This can be done in an UPDATE statement too.

C. incorrect: Aliasing of target columns in the OUTPUT clause is allowed in all statements.

D. incorrect: Composable DML supports all statements

Question 15

You are tasked with implementing a trigger. As part of the trigger’s code in specific conditions, you need to roll back the transaction. However, you need to copy the data from the inserted and deleted tables in the trigger into audit tables to keep track of what was supposed to be changed. How can you achieve this?

• A. Roll back the transaction, and then copy the data from the inserted and deleted tables into the audit tables
• B. Copy the data from the inserted and deleted tables into the audit tables and then roll back the transaction
• C. Copy the rows from the inserted and deleted tables into temporary tables, roll back the transaction, and then copy the data from the temporary tables into the audit tables
• D. Copy the rows from the inserted and deleted tables into table variables, roll back the transaction, and then copy the data from the table variables into the audit tables.

Answer 15

Correct Answer: D

• A. Incorrect: After you roll back the transaction in the trigger, the inserted and deleted tables are emptied
• B. Incorrect: The rollback causes the copying to the audit tables to be undone
• C.Incorrect: Changes against temporary tables are undone after you roll back a transaction
• D. Correct: Changes against table variables aren’t undone if you roll back a transaction, so this solution works correctly

Question 16

^{How do the inserted and deleted tables work with a DML statement in an AFTER trigger?}

_{<strong>A</strong><span>. For a DELETE statement, the inserted table contains new rows and the deleted table contains the deleted rows.</span>}

_{<strong>B</strong><span>. The inserted table only contains rows from the INSERT statement, and the deleted table contains only rows from the DELETE statement.</span>}

_{<strong>C</strong><span>. For an INSERT statement, the inserted table contains new rows and the deleted table is empty.</span>}

_{<strong>D</strong><span>. For an UPDATE statement, the inserted table is empty and the deleted table contains all the changed rows</span>}

Answer 16

_{Correct answer: <strong>C</strong><br></br> <strong>A</strong>. incorrect: In the case of a DELETE statement, there are no new or changed rows,so the inserted table is empty.<br></br> <strong>B</strong>. incorrect: The inserted and deleted tables also contain rows for the UPDATE statement, not just the INSERT and DELETE statements.<br></br> <strong>C</strong>. <strong>correct</strong>: An INSERT statement has all inserted rows in the inserted table but norows in the deleted table.<br></br> <strong>D</strong>. incorrect: For an UPDATE statement that updates rows in a table, the rows beingchanged will be in the inserted table with their new values, and in the deletedtable with their old values}

Question 17

Which of the following T-SQL statements automatically occur in the context of a transaction? (Choose all that apply.)

A. An ALTER TABLE command

B. A PRINT command

C. An UPDATE command D.

A SET command

Answer 17

correct answers: A and C

A.correct: An ALTER TABLE command is a DDL command that changes metadata and always executes as a transaction.

B.incorrect: A PRINT command does not change data, and therefore does not execute by itself in a transaction.

C.correct: An UPDATE statement changes data and executes as a transaction.

D.incorrect: A SET statement only affects session settings and does not change data, and therefore does not execute as a transaction.

Question 18

Which FOR XML options can you use to manually format the XML returned? (Choose all that apply.)

• A. FOR XML AUTO
• B. FOR XML EXPLICIT
• C. FOR XML RAW
• D. FOR XML PATH

Answer 18

Correct answers: B and D

• A. incorrect: FOR XML AUTO automatically formats the XML retuned
• B. correct: FOR XML EXPLICIT allows you to manually format the XML returned
• C. incorrect: FOR XML RAW automatically formats the XML retuned
• D. correct: FOR XML PATH allows you to manually format the XML returned

Question 19

You develop a Microsoft SQL Server 2012 server database that supports an application. The application contains a table that has the following definition:
CREATE TABLE Inventory

(ItemID int NOT NULL PRIMARY KEY, ItemsInStore int NOT NULL, ItemsInWarehouse int NOT NULL)

You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. The new column is expected to be queried heavily, and you need to be able to index the column. Which Transact-SQL statement should you use?

Answer 19

Answer: C

• A. ALTER TABLE Inventory All TotalItems AS Item3lnStore + ItemsInWarehouse
• B. ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED
• C. ALTER TABLE Inventory ADD TotalItems AS SUM (ItemsInStore, ItemsInWarehouse) PERSISTED
• D. ALTER TABLE Inventory All TotalItems AS SUM (ItemsInStore, ItemsInWarehouse)

Question 20

What is the restriction that grouped queries impose on your expressions?

• A. If the query is a grouped query, you must invoke an aggregate function.
• B. If the query has an aggregate function, it must have a GROUP BY clause.
• C. The elements in the GROUP BY clause must also be specified in the SELECT clause.
• D. If you refer to an element from the queried tables in the HAVING, SELECT, or ORDER BY clauses, it must either appear in the GROUP BY list or be contained by an
  aggregate function.

Answer 20

• A. incorrect: You can group rows without invoking an aggregate function.
• B. incorrect: A query can have an aggregate function without a GROUP BY clause. The grouping is implied - all rows make one group.
• C. incorrect: There’s no requirement for grouped elements to appear in the SELECT list, though it’s common to return the elements that you group by.
• D. correct: A grouped query returns only one row per group. For this reason, all expressions that appear in phases that are evaluated after the GROUP BY clause (HAVING, SELECT, and ORDER BY) must guarantee returning a single value per group. That’s where the restriction comes from

Question 21

What is the purpose of the GROUPING and GROUPING_ID functions? (Choose all thatapply.)

• A. You can use these functions in the GROUP BY clause to group data.
• B. You can use these functions to tell whether a NULL in the result represents a placeholder for an element that is not part of the grouping set or an original NULL from the table.
• C. You can use these functions to uniquely identify the grouping set that the result row is associated with.
• D. These functions can be used to sort data based on grouping set association—that is, first detail, and then aggregates

Answer 21

Correct answers: B, C, and D

• A. incorrect: These functions cannot be used in the GROUP BY clause.
• B. correct: When the functions return a 1 bit, a NULL is a placeholder; when they return a 0 bit, the NULL originates from the table.
• C. correct: Each grouping set can be identified with a unique combination of 1s and 0s returned by these functions.
• D. correct: These functions can be used for sorting because they return a 0 bit for a detail element and a 1 bit for an aggregated element. So if you want to see detail

first, sort by the result of the function in ascending order

Question 22

Which of the following are not allowed in the PIVOT operator’s specification? (Choose all that apply.)
A. Specifying a computation as input to the aggregate function
B. Specifying a computation as the spreading element
C. Specifying a subquery in the IN clause
D. Specifying multiple aggregate functions

Answer 22

Correct answers: A, B, C, and D

• *A. correct**: You cannot specify a computation as input to the aggregate function, rather just a name of a column from the input table.
• *B. correct**: You cannot specify a computation as the spreading element, rather just a name of a column from the input table.
• *C. correct**: You cannot specify a subquery in the IN clause, rather just a static list.
• *D. correct**: You cannot specify multiple aggregate functions, rather just one

Question 23

What do the RANK and DENSE_RANK functions compute?

• *A**. The RANK function returns the number of rows that have a lower ordering value (assuming ascending ordering) than the current; the DENSE_RANK function returns the number of distinct ordering values that are lower than the current.
• *B**. The RANK function returns one more than the number of rows that have a lower ordering value than the current; the DENSE_RANK function returns one more than the number of distinct ordering values that are lower than the current.
• *C**. The RANK function returns one less than the number of rows that have a lower ordering value than the current; the DENSE_RANK function returns one less than the number of distinct ordering values that are lower than the current.
• *D**. The two functions return the same result unless the ordering is unique

Answer 23

Correct answer: B
A. incorrect: These definitions are one less than the correct ones.
B. correct: These are the correct definitions.
C. incorrect: These definitions are two less than the correct ones.
D. incorrect: The opposite is true—the two functions return the same result when the ordering is unique