MCQOPTIONS
Saved Bookmarks
MCQOPTIONS
This section includes 67 Mcqs, each offering curated multiple-choice questions to sharpen your SQL Server knowledge and support exam preparation. Choose a topic below to get started.
| 1. |
Implicit transactions are maintained by SQL Server for ___________ |
| A. | SELECT |
| B. | DDL |
| C. | DML |
| D. | All of the mentioned |
| Answer» E. | |
| 2. |
Which of the following scenario give rise to deadlock (There are 2 processes)? |
| A. | P1 & P2 trying TO GET TO 2 resources R1 & R2. |
| B. | P1 & P2 trying TO GET TO 2 resources R1 & R2. |
| C. | P1 & P2 trying TO GET TO 2 resources R1 & R2. |
| D. | All of the mentioned |
| Answer» B. P1 & P2 trying TO GET TO 2 resources R1 & R2. | |
| 3. |
Which of the following is valid syntax for SAVE TRANSACTION with all the optional parameters? |
| A. | SAVE { TRAN | TRANSACTION } |
| B. | SAVE { savepoint_name | @savepoint_variable } |
| C. | SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable } |
| D. | None of the mentioned |
| Answer» D. None of the mentioned | |
| 4. |
Which of the following query disables the resource governor? |
| A. | ALTER RESOURCE GOVERNOR ENABLE CONFIGURE; |
| B. | ALTER GOVERNOR DISABLE; |
| C. | ALTER RESOURCE GOVERNOR DISABLE; |
| D. | None of the mentioned |
| Answer» D. None of the mentioned | |
| 5. |
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 | |
| 6. |
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 | |
| 7. |
Which of the following code snippet demonstrates ALTER statement with STATISTICS_NORECOMPUTE in ONLINE state? |
| A. | ALTER INDEX ALL ON Production.Product |
| B. | ALTER INDEX ALL ON Production.Product |
| C. | ALTER INDEX ALL ON Production.Product |
| D. | All of the mentioned |
| Answer» B. ALTER INDEX ALL ON Production.Product | |
| 8. |
Which of the following guideline is considered during column design? |
| A. | Keep the length of the index key short for non clustered indexes |
| B. | An xml data type can only be a key column only in an XML index |
| C. | Examine column which have composite primary keys |
| D. | None of the mentioned |
| Answer» C. Examine column which have composite primary keys | |
| 9. |
Which of the following query can enable all indexes on Employee table? |
| A. | DBCC DBREINDEX (“Employee”, ” “); |
| B. | DBCC DBREINDEX (“EMP”, ” “); |
| C. | DBCC DBREINDEX (“HumanResources.Employee”, “ALL”); |
| D. | None of the mentioned |
| Answer» B. DBCC DBREINDEX (“EMP”, ” “); | |
| 10. |
_________ permissions default to any valid user. |
| A. | ROLLBACK WORK |
| B. | ROLLBACK |
| C. | COMMIT |
| D. | None of the mentioned |
| Answer» B. ROLLBACK | |
| 11. |
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 | |
| 12. |
___________ lets you affinitize a resource pool to one or more schedulers or NUMA nodes for greater isolation of CPU resources. |
| A. | MAX_IOPS_PER_VOLUME |
| B. | AFFINITY |
| C. | MAX_CPU_PERCENT |
| D. | MAX_MEMORY_PERCENT |
| Answer» C. MAX_CPU_PERCENT | |
| 13. |
Which of the following rebuilds the IX_Employee_OrganizationLevel_OrganizationNode index with a fill factor of 80 on the HumanResources.Employee table? |
| A. | ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources |
| B. | CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee |
| C. | ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee |
| D. | All of the mentioned |
| Answer» D. All of the mentioned | |
| 14. |
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 | |
| 15. |
The transaction log supports which of the following operations? |
| A. | Recovery of all incomplete transactions when SQL Server is started |
| B. | Recovery of individual transactions |
| C. | Supporting transactional replication |
| D. | All of the mentioned |
| Answer» C. Supporting transactional replication | |
| 16. |
ACTIVE_TRANSACTION has log_reuse_wait value is _________ |
| A. | 1 |
| B. | 2 |
| C. | 3 |
| D. | 4 |
| Answer» E. | |
| 17. |
Which of the log_reuse_wait_desc value has log_reuse_wait value 0? |
| A. | CHECKPOINT |
| B. | ACTIVE_TRANSACTION |
| C. | NOTHING |
| D. | All of the mentioned |
| Answer» D. All of the mentioned | |
| 18. |
Designing an nonaligned partitioned index can be useful in the following case? |
| A. | The base table has not been partitioned |
| B. | The index key is unique and it does not contain the partitioning column of the table |
| C. | You want the base table to participate in collocated joins with more tables using different join columns |
| D. | All of the mentioned |
| Answer» E. | |
| 19. |
Which of 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 | |
| 20. |
Which of the following partitioned attribute can appear in the following physical and logical operators? |
| A. | Index Seek |
| B. | Drop |
| C. | Alter |
| D. | None of the mentioned |
| Answer» B. Drop | |
| 21. |
To remove archival compression and restore the data to columnstore compression _____________ |
| A. | Use ALTER TABLE |
| B. | Use ALTER COLUMN |
| C. | Use ALTER DATABASE |
| D. | All of the mentioned |
| Answer» B. Use ALTER COLUMN | |
| 22. |
The data_compression and data_compression_desc columns include ___________ |
| A. | COLUMNSTOREARCHIVE |
| B. | COLUMNSTORE_ARCH |
| C. | COLUMNSTORE |
| D. | None of the mentioned |
| Answer» D. None of the mentioned | |
| 23. |
Which of the following compression type is supported by SQL Server 2014? |
| A. | Row |
| B. | Column |
| C. | Both row and column |
| D. | None of the mentioned |
| Answer» D. None of the mentioned | |
| 24. |
What is the purpose of schema name in the following code syntax? |
| A. | Name of the table |
| B. | Name of the database schema |
| C. | Name of the indexed view |
| D. | All of the mentioned |
| Answer» C. Name of the indexed view | |
| 25. |
Which of the following locking hint is similar to NOLOCK? |
| A. | PAGLOCK |
| B. | READCOMMITTED |
| C. | READUNCOMMITTED |
| D. | SERIALIZABLE |
| Answer» D. SERIALIZABLE | |
| 26. |
Which of the following query disables all the indexes on Employee table? |
| A. | ALTER INDEX MUL ON HumanResources.Employee |
| B. | ALTER INDEX NEST ON HumanResources.Employee |
| C. | ALTER INDEX ALL ON HumanResources.Employee |
| D. | ALTER INDEX ALL ON HumanResources.Employee |
| Answer» D. ALTER INDEX ALL ON HumanResources.Employee | |
| 27. |
__________ 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 | |
| 28. |
What is the syntax for COMMIT WORK statement in transaction? |
| A. | COMMIT [ WORK ] |
| B. | COMMIT WORK [ WORK ] |
| C. | COMMITWORK [ WORK ] |
| D. | None of the mentioned |
| Answer» C. COMMITWORK [ WORK ] | |
| 29. |
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 | |
| 30. |
Which of the following query will disable the index on Employee table? |
| A. | ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode Employee |
| B. | ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources |
| C. | CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee |
| D. | None of the mentioned |
| Answer» B. ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources | |
| 31. |
The conditions that invalidate a plan include which of 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 | |
| 32. |
Which of the following mode indicates the intention of the transaction to read all of the resources lower in the hierarchy? |
| A. | IS |
| B. | IX |
| C. | SIX |
| D. | All of the mentioned |
| Answer» D. All of the mentioned | |
| 33. |
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’ ; | |
| 34. |
Which of the following query cannot be parameterized according to the rules of forced parameterization? |
| A. | SELECT * FROM Person.Address |
| B. | SELECT * FROM Person.Address |
| C. | SELECT * FROM Person.Address |
| D. | All of the mentioned |
| Answer» B. SELECT * FROM Person.Address | |
| 35. |
____________ 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 | |
| 36. |
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 | |
| 37. |
Which of the following query is used to enable a disabled index using CREATE INDEX? |
| A. | CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee |
| B. | CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee |
| C. | CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee |
| D. | None of the mentioned |
| Answer» B. CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee | |
| 38. |
Which of the following resource can be locked in SQL Server? |
| A. | TID |
| B. | RID |
| C. | PID |
| D. | SID |
| Answer» C. PID | |
| 39. |
Resource Governor is a feature that enables you to specify limits on the amount of ____________ |
| A. | CPU |
| B. | Physical IO |
| C. | Memory |
| D. | None of the mentioned |
| Answer» E. | |
| 40. |
In _______ transaction we include the DML statements that need to be executed as a unit. |
| A. | Explicit |
| B. | Implicit |
| C. | Distributed |
| D. | All of the mentioned |
| Answer» B. Implicit | |
| 41. |
Which of the following code is used to enable compression on a table? |
| A. | EXEC sp_estimate_compression_savings 'Production', 'TransactionHistory', NULL, NULL, 'ROW' ; |
| B. | EXEC sp_estimate_compression_savings 'Production', 'TransactionHistory', NULL, NULL, 'ROW' ; |
| C. | EXEC sp_estimate_compression_savings 'Production', 'TransactionHistory', NULL, NULL, 'ROW' ; |
| D. | EXEC sp_estimate_compression 'Production', 'TransactionHistory', NULL, NULL, 'ROW' ; |
| Answer» B. EXEC sp_estimate_compression_savings 'Production', 'TransactionHistory', NULL, NULL, 'ROW' ; | |
| 42. |
Which of the following query enables the IX_Employee_OrganizationLevel_OrganizationNode index on the Employee table? |
| A. | DBCC DBREINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode); |
| B. | DBCC REINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode); |
| C. | DBCC DBINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode); |
| D. | All of the mentioned |
| Answer» B. DBCC REINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode); | |
| 43. |
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 | |
| 44. |
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 | |
| 45. |
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. | |
| 46. |
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 | |
| 47. |
Which of 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. | |
| 48. |
___________ 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 | |
| 49. |
__________ backups are taken in bulk or full recovery models only. |
| A. | Log |
| B. | Active |
| C. | Passive |
| D. | Replica |
| Answer» B. Active | |
| 50. |
_____________ 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 | |