Explore topic-wise MCQs in SQL Server.

This section includes 64 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.

TRACE_PRODUCE_BLACKBOX file can be saved in the following location?

A. N’%SQLDIR%\MSSQL\DATA\blackbox.trc
B. N’%SQLDIR%\MSSQL\DATA\black.trc
C. N’%SQLDIR%\MSSQL\DATA\box.trc
D. None of the mentioned
Answer» B. N’%SQLDIR%\MSSQL\DATA\black.trc
Discussion
2.

Which of the following query returns the average latency for the most recent sessions?

A. SELECT latency FROM sys.dm_cdc_log_audit_sessions WHERE session_id = 0
B. SELECT latency FROM sys.dm_cdc_log_scan_ WHERE session_id = 0
C. SELECT latency FROM sys.dm_cdc_audit_scan_sessions WHERE session_id = 0
D. SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0
Answer» E.
Discussion
3.

Which of the following event has event number 15 in the trace?

A. DTCTransaction
B. ErrorLog
C. SQL:BatchStarting
D. Lock:Cancel
Answer» D. Lock:Cancel
Discussion
4.

Which of the following query determine sessions that had an empty scan?

A. SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0
B. SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count = 0
C. SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count < 0
D. SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count > 0
Answer» B. SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count = 0
Discussion
5.

Which of the following stored procedure will stop the trace?

A. sp_trace_create.
B. sp_trace_setfilter
C. sp_trace_setstatus
D. sp_stop_status
Answer» D. sp_stop_status
Discussion
6.

Which of the following table is used in the management data warehouse schema that is required for the Server Activity?

A. snapshots.query_stat
B. snapshots.os_latch_stats
C. snapshots.active_sessions
D. all of the mentioned
Answer» C. snapshots.active_sessions
Discussion
7.

_____________ obtains tracking information for all changes to a table that have occurred since a specific version.

A. CHANGETABLE_CONTEXT
B. CHANGETABLE
C. CHANGE_TRACKING_IS_COLUMN_IN_MASK
D. All of the mentioned
Answer» C. CHANGE_TRACKING_IS_COLUMN_IN_MASK
Discussion
8.

Which of the following script enable a Table for all and Net Changes Queries?

A. \USE MyDB
B. \USE MyDB
C. \USE MyDB
D. All of the mentioned
Answer» B. \USE MyDB
Discussion
9.

Which of the following pair of views has one to many relationship?

A. sys.server_event_session_actions.event_session_id, sys.sys.server_event_sessions.event
B. sys.server_event_session_actions, sys.sys.server_event_sessions.event_session_id
C. sys.server_event_session_actions.event_session_id,sys.sys.server_event_sessions.event _session_id
D. none of the mentioned
Answer» D. none of the mentioned
Discussion
10.

Which of the following mode allows for the collection and uploading of data to occur on demand?

A. Non-cached mode
B. Cached mode
C. Mixed mode
D. All of the mentioned
Answer» B. Cached mode
Discussion
11.

Which of the following is syntax for sp_trace_setevent?

A. sp_trace_setevent [ @traceid = ] trace_id
B. sp_trace_setevent [ @traceid = ] trace_id OUTPUT
C. sp_trace_setevent [ @traceid = ] trace_id ,
D. None of the mentioned
Answer» B. sp_trace_setevent [ @traceid = ] trace_id OUTPUT
Discussion
12.

Which of the following wait type is for internal use only?

A. ASYNC_NETWORK_IO
B. AUDIT_LOGINCACHE_LOCK
C. HADR_DBR_SUBSCRIBER_FILTER_LIST
D. AUDIT_XE_SESSION_MGR
Answer» D. AUDIT_XE_SESSION_MGR
Discussion
13.

Which of the following is a change tracking function?

A. WITH CHANGE_TRACK_CONTEXT
B. CHANGE_TRACKING_MAX_VALID_VERSION()
C. CHANGE_TRACKING_CURRENT_VERSION
D. All of the mentioned
Answer» D. All of the mentioned
Discussion
14.

Which of the following determines type or resource to which the lock applies?

A. sp_lock
B. sp_who
C. sp_monitor
D. sp_spaceused
Answer» B. sp_who
Discussion
15.

Which of the following disable database for Change Data Capture?

A. EXEC sys.sp_cdc_ensable_db
B. EXEC sys.sp_disable_db
C. EXEC sys.sp_cdc_disable_db
D. All of the mentioned
Answer» D. All of the mentioned
Discussion
16.

The Activity Monitor in SQL Server Management Studio graphically displays information about ______________

A. User activity
B. Blocked processes
C. Locks
D. All of the mentioned
Answer» E.
Discussion
17.

Which of the following is the benefit of SQL Server Profiler?

A. Capturing the series of Transact-SQL statements that lead to a problem
B. Finding and diagnosing slow-running queries
C. Correlating performance counters to diagnose problems
D. All of the mentioned
Answer» E.
Discussion
18.

Which of the following is a valid syntax for WITH CHANGE_TRACKING_CONTEXT?

A. WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary)
B. WITH CHANGE_TRACKI_CONTEXT (@sync_client_id_binary)
C. WITH CHANGE_TRACKING_CON (@sync_client_id_binary)
D. All of the mentioned
Answer» B. WITH CHANGE_TRACKI_CONTEXT (@sync_client_id_binary)
Discussion
19.

Which of the following script enable a table Without Using a Gating Role?

A. USE MyDB
B. USE MyDB
C. USE MyDB
D. None of the mentioned
Answer» B. USE MyDB
Discussion
20.

The cleanup job is initiated by running the parameterless stored procedure is ___________

A. sp_MScdc_cleanup_job
B. sp_cdc_cleanup_change_job
C. sp_cdc_cleanup_change_tables
D. none of the mentioned
Answer» B. sp_cdc_cleanup_change_job
Discussion
21.

Which of the following argument has option value 2 in sp_trace_create procedure?

A. TRACE_PRODUCE_BLACKBOX
B. SHUTDOWN_ON_ERROR
C. TRACE_FILE_ROLLOVER
D. All of the mentioned
Answer» D. All of the mentioned
Discussion
22.

Which of the following script will enable CDC on HumanResources.Shift table?

A. USE AdventureWorks
B. USE AdventureWorks
C. USE AdventureWorks
D. All of the mentioned
Answer» B. USE AdventureWorks
Discussion
23.

Which of the following is an extended event task?

A. create an Extended Events session
B. alter an Extended Events session
C. find out what events are available in the registered packages
D. all of the mentioned
Answer» E.
Discussion
24.

How many types of wait occur in SQL Server?

A. 2
B. 3
C. 4
D. 5
Answer» C. 4
Discussion
25.

Which of the following database level event is raised whenever a REFERENCES permission is checked.

A. EXECUTE
B. REFERENCES
C. UPDATE
D. SELECT
Answer» C. UPDATE
Discussion
26.

Which of the following code snippet returns a version integer that is associated with the last committed transaction that was tracked by change tracking?

A. SELECT @sync_new_received_anchor = change_tracking_cur_version()
B. SELECT @sync_new_received_anchor = change_tracking_current_ver()
C. SELECT @sync_new_received_anchor = change_tracking_current_version()
D. SELECT @sync_new_received_anchor = change_track_current_version()
Answer» D. SELECT @sync_new_received_anchor = change_track_current_version()
Discussion
27.

Which of the following option can be used to launch Perfmon?

A. Type “start perfmon” at the command prompt
B. Go to Start | Programs | Administrative Tools | Performance Monitor
C. Go to Start | Run | Perfmon
D. All of the mentioned
Answer» E.
Discussion
28.

Which of the following is an event class?

A. SQL:BatchCompleted
B. Audit Password
C. Lock:Blocked
D. All of the mentioned
Answer» B. Audit Password
Discussion
29.

The contents of sys.dm_os_wait_stats can be reset by running which of the following command?

A. DBCC PERF (‘sys.dm_os_wait_stats’, CLEAR);
B. DBCC SQLP (‘sys.dm_os_wait_stats’, CLEAR);
C. DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR);
D. None of the mentioned
Answer» D. None of the mentioned
Discussion
30.

Which of the following field in the following code is qualified name?

A. updateusage
B. @objname
C. @updateusage
D. All of the mentioned
Answer» C. @updateusage
Discussion
31.

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
Discussion
32.

Which of the following is one of the most crucial performance counters for monitoring?

A. Disk Write Bytes/sec
B. Memory Queue Length
C. Virtual Memory
D. All of the mentioned
Answer» B. Memory Queue Length
Discussion
33.

Which of the following stored procedure reports snapshot information about current SQL Server user?

A. sp_lock
B. sp_who
C. sp_monitor
D. sp_user
Answer» C. sp_monitor
Discussion
34.

Which of the following scenario favours cached mode?

A. Continuous collection of data
B. Less frequent uploads
C. Data collection and uploading of jobs on different schedules
D. All of the mentioned
Answer» E.
Discussion
35.

If you do not want to use a gating role, explicitly set the @role_name parameter to ______________

A. 0
B.
C. 1
D. None of the mentioned
Answer» C. 1
Discussion
36.

Which of the following is the biggest disadvantage of change tracking?

A. Schema changes are required in the server database
B. Triggers are fired for each change that has been made to a row
C. Logic for maintaining the correct row versions and deletions is easy
D. All of the mentioned
Answer» C. Logic for maintaining the correct row versions and deletions is easy
Discussion
37.

__________ gives DBAs an option to shut down the server in case of an audit log failure.

A. Shut down server on partial failure
B. Shut down server on audit log failure
C. Shut down server on complete failure
D. None of the mentioned
Answer» C. Shut down server on complete failure
Discussion
38.

Which of the following wait occurs when a task is waiting for I/Os to finish?

A. ASYNC_NETWORK_IO
B. AUDIT_LOGINCACHE_LOCK
C. AUDIT_ON_DEMAND_TARGET_LOCK
D. AUDIT_XE_SESSION_MGR
Answer» B. AUDIT_LOGINCACHE_LOCK
Discussion
39.

_________ returns a row for each customizable column that was explicitly set on events and targets.

A. sys.server_event_session_targets
B. sys.server_event_session_fields
C. Returns a row for each event in an event session
D. All of the mentioned
Answer» C. Returns a row for each event in an event session
Discussion
40.

When does BROKER_TO_FLUSH wait type event occur?

A. When the Service Broker queue task handler tries to shut down the task
B. When the Service Broker transmitter is waiting for work
C. When the Service Broker lazy flusher flushes the in-memory transmission objects to a work table
D. All of the mentioned
Answer» D. All of the mentioned
Discussion
41.

Which of the following events and their columns are contained in the trace?

A. Attention
B. Batch starting
C. Exception
D. All of the mentioned
Answer» E.
Discussion
42.

Which of the following query selects incremental inserts from the Sales.Customer table at the server?

A. IF @sync_initialized == 0
B. IF @sync_initialized = 0
C. IF @sync_initialized =0
D. None of the mentioned
Answer» D. None of the mentioned
Discussion
43.

Which of the following is a catalog view used for change tracking?

A. sys.change_tracking_tables
B. sys.change_tracking_views
C. sys.change_tracking_columns
D. none of the mentioned
Answer» B. sys.change_tracking_views
Discussion
44.

_____________ wait type applies to SQL Server 2014 only.

A. LCK_M_U_ABORT_BLOCKERS
B. HADR_WORK_QUEUE
C. HADR_XRF_STACK_ACCESS
D. HADR_WORK_POOL
Answer» B. HADR_WORK_QUEUE
Discussion
45.

Which of the DDL statement creates a session object that contains the metadata?

A. CREATE EVENT SESSION
B. CREATE SESSION
C. CREATE EVENT SESSION METADATA
D. None of the mentioned
Answer» B. CREATE SESSION
Discussion
46.

AUDIT_CHANGE_GROUP event is raised whenever any audit is _____________

A. Created
B. Modified
C. Delete
D. All of the mentioned
Answer» E.
Discussion
47.

Which of the following requires VIEW SERVER STATE permission on the server?

A. sys.dm_os_states
B. sys.dm_os_wait_stats
C. sys.dm_os_wait_states
D. none of the mentioned
Answer» C. sys.dm_os_wait_states
Discussion
48.

Which of the code snippet collect the data every 5 minutes?

A. DECLARE @schedule_uid uniqueidentifier;
B. DECLARE @schedule_uid uniqueidentifier;
C. DECLARE @schedule_uid uniqueidentifier;
D. All of the mentioned
Answer» B. DECLARE @schedule_uid uniqueidentifier;
Discussion
49.

SQL Server Extended Events Packages consist of _____________

A. Events
B. Predicates
C. Types
D. All of the mentioned
Answer» E.
Discussion
50.

Which of the following error is returned for a query for all changes when a parameter that is used to define the query interval is not valid?

A. An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_partial_changes_
B. An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_
C. An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_
D. None of the mentioned
Answer» C. An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_
Discussion