Questions and answers for exam Preparation

MCQs

Total Questions : 11 | Page 1 of 2 pages

Question 1. Point out the correct statement :

Avoid less row count SQL statements that can cause a table lock
A blocking lock occurs when one lock causes another process to wait in a holding queue
Lock is a done by database when any connection access a different piece of data concurrently
None of the mentioned

Discuss Question

Answer: Option B. -> A blocking lock occurs when one lock causes another process to wait in a holding queue

If blocking locks are common (rather than infrequent), there is probably some kind of design or query implementation problem.

Question 2. To remove archival compression and restore the data to columnstore compression :

Use ALTER TABLE
Use ALTER COLUMN
Use ALTER DATABASE
All of the mentioned

Discuss Question

Answer: Option A. -> Use ALTER TABLE

use ALTER TABLE (Transact-SQL) or ALTER INDEX (Transact-SQL) with the REBUILD option and DATA COMPRESSION = COLUMNSTORE.

Question 3. Point out the correct statement :

When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the source filegroup
The Database Engine first scans the data pages of the base table to retrieve key values and builds an index leaf row for each data row
When SORT_IN_TEMPDB is set to ON, the default, the sort runs are stored in the destination filegroup.
None of the mentioned

Discuss Question

Answer: Option B. -> The Database Engine first scans the data pages of the base table to retrieve key values and builds an index leaf row for each data row

When the internal sort buffers have been filled with leaf index entries, the entries are sorted and written to disk as an intermediate sort run.

Question 4. ______________ allow concurrent transactions to read (SELECT) a resource.

Update locks
Shared locks
Exclusive Locks
All of the mentioned

Discuss Question

Answer: Option B. -> Shared locks

Shared (S) locks on a resource are released as soon as the data has been read.

Question 5. Point out the correct statement :

On x86-based systems, creating a table or index with more than 1000 partitions is possible
Compressing large tables or indexes can have manageability and performance benefit
Partitioning slow tables or indexes can have the following manageability and performance benefit
None of the mentioned

Discuss Question

Answer: Option A. -> On x86-based systems, creating a table or index with more than 1000 partitions is possible

Partioning feature is not supported on x86 system.

Question 6. To produce execution plan output by using most Showplan Transact-SQL SET options, users must have

SHOW SHOWPLAN
SHOW PLAN
SHOWPLAN
None of the mentioned

Discuss Question

Answer: Option C. -> SHOWPLAN

The SHOWPLAN permission on the databases that contain objects referred to in the Transact-SQL statement, such as views, stored procedures, or user-defined functions.

Question 7. Point out the correct statement :

JDBC use parameter markers
Parameter markers are question marks (?) that replace a constant in an SQL statement
When forced parametrization is tried but fails, simple parametrization is still subsequently tried
All of the mentioned

Discuss Question

Answer: Option B. -> Parameter markers are question marks (?) that replace a constant in an SQL statement

Parameter markers are bound to a variable

Question 8. ____________ rolls back a user-specified transaction to the beginning of the transaction.