MCQOPTIONS
Saved Bookmarks
MCQOPTIONS
This section includes 424 Mcqs, each offering curated multiple-choice questions to sharpen your Structured Query Language (SQL) knowledge and support exam preparation. Choose a topic below to get started.
| 101. |
SQL Server 2008 provides enhanced partitioning information for __________ execution plans. |
| A. | compile-time |
| B. | run time |
| C. | both compile-time and run-time |
| D. | all of the mentioned |
| Answer» D. all of the mentioned | |
| 102. |
_____ is a database object that defines how the rows of a table or index are mapped to a set of partitions based on the values of certain column |
| A. | Partition function |
| B. | Partition scheme |
| C. | Partition column |
| D. | All of the mentioned |
| Answer» B. Partition scheme | |
| 103. |
Which of the following query estimates the size of the Production.WorkOrderRouting table if it is compressed by using ROW compression ? |
| A. | EXEC sp_estimate_data_compression_savings ‘Production’, ‘WorkOrderRouting’, NULL, NULL, ‘ROW’ ; |
| B. | EXEC sp_estimate_data_compression_savings ‘Production’, ‘WorkOrderRouting’, NULL, 1, ‘ROW’ ; |
| C. | EXEC sp_estimate_data_compression_savings ‘Production’, ‘WorkOrderRouting’, 1, NULL, ‘ROW’ ; |
| D. | EXEC sp_estimate_data_compression_savings ‘Production’, ‘WorkOrderRouting’, NULL, NULL, ‘COLUMN’ ; |
| Answer» B. EXEC sp_estimate_data_compression_savings ‘Production’, ‘WorkOrderRouting’, NULL, 1, ‘ROW’ ; | |
| 104. |
sp_estimate_data_compression_savings requires ______ permission on the table. |
| A. | UPDATE |
| B. | SELECT |
| C. | DELETE |
| D. | None of the mentioned |
| Answer» C. DELETE | |
| 105. |
________ backups are taken in bulk or full recovery models only. |
| A. | Log |
| B. | Active |
| C. | Passive |
| D. | Replica |
| Answer» B. Active | |
| 106. |
Internal checkpoint are generated in response to the following event : |
| A. | A database snapshot is deleted, whether explicitly or internally for DBCC CHECK |
| B. | Bringing a SQL Server failover cluster instance (FCI) offline |
| C. | Bringing a SQL Server failover cluster instance (FCI) online |
| D. | None of the mentioned |
| Answer» C. Bringing a SQL Server failover cluster instance (FCI) online | |
| 107. |
Which of the following checkpoint has ‘recovery interval’ less than zero ? |
| A. | Automatic |
| B. | Direct |
| C. | Indirect |
| D. | All of the mentioned |
| Answer» B. Direct | |
| 108. |
_____ logging is not supported for memory-optimized tables. |
| A. | Full |
| B. | Minimal |
| C. | Bulk |
| D. | None of the mentioned |
| Answer» C. Bulk | |
| 109. |
Which of the following best practice should be carried out concerning data types? |
| A. | Avoid using NOLOCK on SQL Server UPDATE and DELETE statements |
| B. | Capturing SQL Server Deadlock Information in XML Format |
| C. | Optimistic Locking in SQL Server using the ROWVERSION |
| D. | Processing Data Queues in SQL Server with READPAST and UPDLOCK |
| Answer» D. Processing Data Queues in SQL Server with READPAST and UPDLOCK | |
| 110. |
_______ is a deadlock between a statement that is reading and a statement that is performing some form of data modification. |
| A. | Reader |
| B. | Writer |
| C. | Reader-Writer |
| D. | All of the mentioned |
| Answer» D. All of the mentioned | |
| 111. |
____________ section lists all the resources that were involved in the deadlock. |
| A. | Processes |
| B. | Resources |
| C. | WAIT stats |
| D. | All of the mentioned |
| Answer» C. WAIT stats | |
| 112. |
An XML deadlock graph has ______ main sections. |
| A. | 2 |
| B. | 3 |
| C. | 4 |
| D. | 5 |
| Answer» B. 3 | |
| 113. |
Which of the following mode has exclusive range and exclusive resource lock ? |
| A. | RangeX_X |
| B. | RangeS_U |
| C. | RangeS_S |
| D. | RangeI_N |
| Answer» B. RangeS_U | |
| 114. |
Which of the following lock is incompatible with all lock modes ? |
| A. | Intent exclusive (IX) |
| B. | Schema stability |
| C. | Schema modification |
| D. | Bulk update |
| Answer» D. Bulk update | |
| 115. |
________ locks allow processes to bulk copy data concurrently into the same table. |
| A. | Bulk update |
| B. | Bulk import |
| C. | Bulk export |
| D. | Bulk copy |
| Answer» B. Bulk import | |
| 116. |
_____________ allow concurrent transactions to read (SELECT) a resource. |
| A. | Update locks |
| B. | Shared locks |
| C. | Exclusive Locks |
| D. | All of the mentioned |
| Answer» C. Exclusive Locks | |
| 117. |
Which of the following condition will ignore the request to enable delayed durability ? |
| A. | DELAYED_DURABILITY = FORCED |
| B. | DELAYED_DURABILITY = 0 |
| C. | DELAYED_DURABILITY = IGNORED |
| D. | DELAYED_DURABILITY = NULL |
| Answer» B. DELAYED_DURABILITY = 0 | |
| 118. |
ROLLBACK WORK always rolls back to the outermost BEGIN TRANSACTION statement for ___________ transactions. |
| A. | Simple |
| B. | Nested |
| C. | Distributed |
| D. | None of the mentioned |
| Answer» C. Distributed | |
| 119. |
What is the fundamental difference between COMMIT and COMMIT WORK ? |
| A. | COMMIT TRANSACTION accepts a user-defined transaction name |
| B. | COMMIT WORK accepts a user-defined transaction name |
| C. | COMMIT TRANSACTION accepts a user-defined function |
| D. | COMMIT WORK accepts a user-defined stored procedure |
| Answer» B. COMMIT WORK accepts a user-defined transaction name | |
| 120. |
A ROLLBACK TRANSACTION statement specifying a savepoint_name releases any locks that are acquired beyond the savepoint, with the exception of:. |
| A. | escalations |
| B. | containment |
| C. | contention |
| D. | None of the mentioned |
| Answer» B. containment | |
| 121. |
Implicit transactions are maintained by SQL Server for : |
| A. | SELECT |
| B. | DDL |
| C. | DML |
| D. | All of the mentioned |
| Answer» E. | |
| 122. |
_________ is the preferred statement for indicating errors in the transaction. |
| A. | SELECT |
| B. | |
| C. | RAISERROR |
| D. | All of the mentioned |
| Answer» D. All of the mentioned | |
| 123. |
If TRANCOUNT is 1, ________ makes all data modifications performed since the start of the transaction a permanent part of the database. |
| A. | COMMIT TRANSACTION |
| B. | SAVE TRANSACTION |
| C. | ROLLBACK TRANSACTION |
| D. | None of the mentioned |
| Answer» B. SAVE TRANSACTION | |
| 124. |
Which of the following argument is ignored by database engine in COMMIT TRANSACTION? |
| A. | @tran_name_variable |
| B. | @tran_name |
| C. | transaction_name |
| D. | All of the mentioned |
| Answer» B. @tran_name | |
| 125. |
______ permissions default to any valid user. |
| A. | ROLLBACK WORK |
| B. | ROLLBACK |
| C. | ROLLBACK |
| D. | None of the mentioned |
| Answer» B. ROLLBACK | |
| 126. |
In _______ transaction we include the DML statements that need to be execute as a unit. |
| A. | Explicit |
| B. | Implicit |
| C. | Distributed |
| D. | All of the mentioned |
| Answer» B. Implicit | |
| 127. |
__________ rolls back a user-specified transaction to the beginning of the transaction. |
| A. | ROLLBACK |
| B. | ROLLBACK WORK |
| C. | SAVE TRANSACTION |
| D. | COMMIT |
| Answer» C. SAVE TRANSACTION | |
| 128. |
______ marks the end of a successful implicit or explicit transaction. |
| A. | COMMIT TRANSACTION |
| B. | ROLLBACK TRANSACTION |
| C. | COMMIT WORK |
| D. | All of the mentioned |
| Answer» B. ROLLBACK TRANSACTION | |
| 129. |
_______ uniquely identifies a query plan for a batch that has executed and whose plan resides in the plan cache. |
| A. | pool_name |
| B. | pool_name |
| C. | sql_handle |
| D. | None of the mentioned |
| Answer» C. sql_handle | |
| 130. |
What is the syntax for DBCC FREEPROCCACHE with all the optional parameters in T-SQL? |
| A. | DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ]. |
| B. | DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]. |
| C. | DBCC FREEPROCCACHE [ ( { sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]. |
| D. | None of the mentioned |
| Answer» C. DBCC FREEPROCCACHE [ ( { sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]. | |
| 131. |
The conditions that invalidate a plan include the following : |
| A. | An explicit call to sp_compile |
| B. | Executing a stored procedure using the WITH COMPILE option |
| C. | Changes made to a table or view referenced by the query |
| D. | All of the mentioned |
| Answer» D. All of the mentioned | |
| 132. |
The maximum size for all caches is a function of the ________ size. |
| A. | RAM |
| B. | Buffer pool |
| C. | Hard disk |
| D. | None of the mentioned |
| Answer» C. Hard disk | |
| 133. |
Which of the following SQL Server SQL Statistics counter represents number of auto-parameterization attempts per second ? |
| A. | Batch Requests/sec |
| B. | Failed Auto-Params/sec |
| C. | Forced parameterizations/sec |
| D. | Auto-Param Attempts/sec |
| Answer» E. | |
| 134. |
Which of the following query will disable the index on Employee table ? |
| A. | ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode Employee DISABLE; |
| B. | ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources DISABLE; |
| C. | CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee DISABLE; |
| D. | None of the mentioned |
| Answer» B. ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources DISABLE; | |
| 135. |
The SORT_IN_TEMPDB option cannot be set for ________ statements. |
| A. | ALTER INDEX |
| B. | DROP INDEX |
| C. | CREATE INDEX |
| D. | All of the mentioned |
| Answer» C. CREATE INDEX | |
| 136. |
Which of the following index operation require additional disk space ? |
| A. | UPDATE INDEX |
| B. | DROP INDEX MOVE TO |
| C. | ALTER INDEX ADD CONSTRAINT |
| D. | All of the mentioned |
| Answer» C. ALTER INDEX ADD CONSTRAINT | |
| 137. |
The following index operations require no additional disk space : |
| A. | ALTER INDEX |
| B. | DROP INDEX |
| C. | CREATE INDEX |
| D. | All of the mentioned |
| Answer» E. | |
| 138. |
Which of the following query disables all the indexes on Employee table ? |
| A. | ALTER INDEX MUL ON HumanResources.Employee DISABLE; |
| B. | ALTER INDEX NEST ON HumanResources.Employee DISABLE; |
| C. | ALTER INDEX ALL ON HumanResources.Employee ENABLE; |
| D. | ALTER INDEX ALL ON HumanResources.Employee DISABLE; |
| Answer» E. | |
| 139. |
Which of the the following option is immediately applied to the index by using the SET clause in the ALTER INDEX statement ? |
| A. | ALLOW_PAGE_LOCKS |
| B. | ALLOW_COLUMN_LOCKS |
| C. | STATISTICS_RECOMPUTE |
| D. | None of the mentioned |
| Answer» B. ALLOW_COLUMN_LOCKS | |
| 140. |
Specifying the order in which key values are stored in an index is useful when queries referencing the table have : |
| A. | ORDER BY clauses |
| B. | SORT clauses |
| C. | WHERE clauses |
| D. | None of the mentioned |
| Answer» B. SORT clauses | |
| 141. |
Every table should have a ________ index defined on the column, or columns. |
| A. | CLUSTERED |
| B. | NON CLUSTERED |
| C. | FULL TEXT |
| D. | All of the mentioned |
| Answer» B. NON CLUSTERED | |
| 142. |
By default, indexes are stored in the ______ filegroup as the base table on which the index is created. |
| A. | same |
| B. | different |
| C. | may be same or different |
| D. | none of the mentioned |
| Answer» B. different | |
| 143. |
The way that a statement can be physically executed is called : |
| A. | query plan |
| B. | execution plan |
| C. | query execution plan |
| D. | all of the mentioned |
| Answer» E. | |
| 144. |
SET SHOWPLAN_TEXT is intended to return readable output for ________ utility. |
| A. | osql |
| B. | tsql |
| C. | plsql |
| D. | pgsql |
| Answer» B. tsql | |
| 145. |
________ displays the profile information for a statement. |
| A. | SET STATISTICS |
| B. | SET STATS PROFILE |
| C. | SET STATISTICS PROFILE |
| D. | All of the mentioned |
| Answer» D. All of the mentioned | |
| 146. |
What does collector_type_id stands for in the following code snippet ? core.sp_remove_collector_type [ @collector_type_uid = ] ‘collector_type_uid’ |
| A. | uniqueidentifier |
| B. | membership role |
| C. | directory |
| D. | none of the mentioned |
| Answer» B. membership role | |
| 147. |
_______ stores information about how the management data warehouse reports should group and aggregate performance counters. |
| A. | core.snapshots_internal |
| B. | core.supported_collector_types_internal |
| C. | core.wait_categories |
| D. | core.performance_counter_report_group_items |
| Answer» E. | |
| 148. |
______ introduces the Management Data Warehouse (MDW) to SQL Server Management Studio for streamlined performance troubleshooting. |
| A. | SQL Server 2005 |
| B. | SQL Server 2008 |
| C. | SQL Server 2012 |
| D. | SQL Server 2014 |
| Answer» C. SQL Server 2012 | |
| 149. |
Which of the following query returns the average throughput for the most recent sessions ? |
| A. | SELECT command_count*duration AS [Throughput] FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0 |
| B. | SELECT command_count/duration AS [Throughput] FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0 |
| C. | SELECT command_count/duration AS [Throughput] FROM sys.dm_cdc_log_scan_sesss WHERE session_id = 0 |
| D. | None of the mentioned |
| Answer» C. SELECT command_count/duration AS [Throughput] FROM sys.dm_cdc_log_scan_sesss WHERE session_id = 0 | |
| 150. |
sys.fn_cdc_map_time_to_lsn returns ________ value from the start_lsn column in the cdc.lsn_time_mapping system table for the specified time. |
| A. | LSN |
| B. | LNS |
| C. | SLN |
| D. | None of the mentioned |
| Answer» B. LNS | |