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.
| 151. |
Which of the following field in cdc.lsn_time_mapping has varbinary data type ? |
| A. | tran_begin_time |
| B. | tran_id |
| C. | start_lsn |
| D. | All of the mentioned |
| Answer» C. start_lsn | |
| 152. |
______ 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 | |
| 153. |
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. | |
| 154. |
The cleanup job is initiated by running the parameterless stored procedure : |
| 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 | |
| 155. |
The commit time of each transaction with an associated entry in a database change table is available in the table ______ |
| A. | cdc.log_time_mapping |
| B. | cdc.lsn_time_mapping |
| C. | cdc.lsn_time_audit |
| D. | All of the mentioned |
| Answer» C. cdc.lsn_time_audit | |
| 156. |
Upper bound on expected throughput of the capture job is calculated using the following computation: |
| A. | (maxtrans * maxscans) / number of seconds between scans |
| B. | (maxtrans * maxscans) * number of seconds between scans |
| C. | (maxtrans * maxscans) – number of seconds between scans |
| D. | None of the mentioned |
| Answer» B. (maxtrans * maxscans) * number of seconds between scans | |
| 157. |
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 | |
| 158. |
Which of the following is 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 | |
| 159. |
Which of the following feature distinguishes change data capture from change tracking ? |
| A. | Historical data |
| B. | DML |
| C. | DML type |
| D. | Whether column was changed |
| Answer» B. DML | |
| 160. |
__________ 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 | |
| 161. |
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() | |
| 162. |
Which of the following format output events in an Event Tracing for Windows ? |
| A. | event_file |
| B. | ring_buffer |
| C. | histogram |
| D. | etw_classic_sync_target |
| Answer» E. | |
| 163. |
______ 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_targets |
| 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 | |
| 164. |
___ can be applied to the metadata or to an active session and the metadata. |
| A. | DROP EVENT SESSION |
| B. | ALTER EVENT SESSION,STATE=STOP |
| C. | ALTER EVENT SESSION,STATE=START |
| D. | All of the mentioned |
| Answer» E. | |
| 165. |
__________ 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 | |
| 166. |
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 | |
| 167. |
_____ waits occur when a worker requests access to a resource that is not available because the resource is being used by some other worker or is not yet available. |
| A. | Resource |
| B. | Queue |
| C. | Lock |
| D. | External |
| Answer» B. Queue | |
| 168. |
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 | |
| 169. |
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 | |
| 170. |
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. | |
| 171. |
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 | |
| 172. |
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 | |
| 173. |
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. | |
| 174. |
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 | |
| 175. |
_______ reports the total processor time with respect to the available capacity of the server. |
| A. | Disk Read Bytes/sec |
| B. | Avg.Disk Queue Length |
| C. | Processor time |
| D. | Response time |
| Answer» D. Response time | |
| 176. |
Which of the following database level event si raised whenever a REFERENCES permission is checked. |
| A. | EXECUTE |
| B. | REFERENCES |
| C. | UPDATE |
| D. | SELECT |
| Answer» C. UPDATE | |
| 177. |
DDL Trigger can capture the information about the EVENT that fired it by using __________ function. |
| A. | EVENTDATA() |
| B. | EVENTDATA() |
| C. | EVENTS() |
| D. | None of the mentioned |
| Answer» B. EVENTDATA() | |
| 178. |
You can use the handy event name of __________ to make sure your trigger covers all DDL events. |
| A. | DDL_DATABASE_EVENTS |
| B. | DDL_DATABASE_LEVEL_EVENTS |
| C. | DDL_DATABASE_LEVEL |
| D. | All of the mentioned |
| Answer» C. DDL_DATABASE_LEVEL | |
| 179. |
____ could potentially lead to large audit records. |
| A. | DATABASE_OBJECT_ACCESS_GROUP |
| B. | DATABASE_MIRRORING_LOGIN_GROUP |
| C. | DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP |
| D. | DATABASE_OBJECT_PERMISSION_CHANGE_GROUP |
| Answer» B. DATABASE_MIRRORING_LOGIN_GROUP | |
| 180. |
Which of the following statement is used to enable auditing from SQL Server ? |
| A. | auditpol /set /subcategory:”application generated” /success:enable /failure:enable |
| B. | polaudit /set /subcategory:”application generated” /success:enable /failure:enable |
| C. | auditpolenable /set /subcategory:”application generated” /success:enable /failure:enable |
| D. | auditenable /set /subcategory:”application generated” /success:enable /failure:enable |
| Answer» B. polaudit /set /subcategory:”application generated” /success:enable /failure:enable | |
| 181. |
Which of the following views log from a file that is named \\serverName\Audit\HIPPA_AUDIT.sqlaudit ? |
| A. | SELECT * FROM sys.fn_audit_file (‘\\serverName\Audit\HIPPA_AUDIT.sqlaudit’, default,default); |
| B. | SELECT * FROM sys.fn_get_audit_file (‘\\serverName\Audit\HIPPA_AUDIT.sqlaudit’, default,default); |
| C. | SELECT * FROM sys.fn_get_audit(‘\\serverName\Audit\HIPPA_AUDIT.sqlaudit’, default,default); |
| D. | None of the mentioned |
| Answer» C. SELECT * FROM sys.fn_get_audit(‘\\serverName\Audit\HIPPA_AUDIT.sqlaudit’, default,default); | |
| 182. |
Grant ________ permission on the stored procedures to database roles for implementing call level security. |
| A. | EXECUTE |
| B. | EXEC |
| C. | READ |
| D. | WRITE |
| Answer» B. EXEC | |
| 183. |
Which of the following will not return user name of current session? |
| A. | CUURENT_USER() |
| B. | USER_NAME() |
| C. | CUSER_NAME() |
| D. | None of the mentioned |
| Answer» E. | |
| 184. |
Return type of CertName field in vwVisibleLabels is: |
| A. | XML |
| B. | INT |
| C. | NVARCHAR(256) |
| D. | VARCHAR(256) |
| Answer» D. VARCHAR(256) | |
| 185. |
Which of the following functions decrypts data by using a symmetric key? |
| A. | DECRYPTBYSYMKEY |
| B. | DECRYPTBYKEYAUTOASYMKEY |
| C. | DECRYPTBYASYMKEY |
| D. | DECRYPTBYKEYAUTOCERT |
| Answer» C. DECRYPTBYASYMKEY | |
| 186. |
__________ encryption was introduced in SQL Server 2005 and is available in all editions of SQL Server. |
| A. | Column-level Encryption |
| B. | Transparent Data Encryption |
| C. | BitLocker |
| D. | None of the mentioned |
| Answer» B. Transparent Data Encryption | |
| 187. |
Like data compression, TDE database encryption is performed at the ______ level. |
| A. | Page |
| B. | Domain |
| C. | Column |
| D. | Row |
| Answer» B. Domain | |
| 188. |
Which of the following catalog view is used to monitor TDE? |
| A. | sys.certificates |
| B. | sys.certificate |
| C. | DMVs |
| D. | All of the mentioned |
| Answer» B. sys.certificate | |
| 189. |
To add or remove server role membership use : |
| A. | ALTER ROLE |
| B. | ALTER ANY LOGIN |
| C. | ALTER SERVER ROLE |
| D. | None of the mentioned |
| Answer» D. None of the mentioned | |
| 190. |
Any SQL Server authentication login can see ________ login name. |
| A. | Own and sa |
| B. | sa |
| C. | Own |
| D. | None of the mentioned |
| Answer» B. sa | |
| 191. |
Which of the following returns a row for each security principal in a SQL Server database ? |
| A. | sys.database_role_members |
| B. | sys.sql_logins |
| C. | sys.database_principals |
| D. | None of the mentioned |
| Answer» D. None of the mentioned | |
| 192. |
The schema securable scope contains the following securables: |
| A. | Contract |
| B. | Asymmetric key |
| C. | Schema |
| D. | Object |
| Answer» E. | |
| 193. |
The Windows password policies enforced for SQL Server logins depends upon _____________________ API. |
| A. | NetValidate |
| B. | NetValidatePasswordPolicy |
| C. | NetValidatePolicy |
| D. | ValidatePasswordPolicy |
| Answer» C. NetValidatePolicy | |
| 194. |
________ returns a list of the permissions effectively granted to the principal on a securable. |
| A. | HAS_PERMS_BY_NAME |
| B. | sys.fn_my_permissions |
| C. | sys.fn_permissions |
| D. | All of the mentioned |
| Answer» C. sys.fn_permissions | |
| 195. |
Which of the following is the biggest disadvantage of SQL Server Authentication mode ? |
| A. | SQL Server Authentication cannot use Kerberos security protocol |
| B. | Allows SQL Server to support Web-based applications where users create their own identities |
| C. | Allows SQL Server to support older applications and applications provided by third parties that require SQL Server Authentication |
| D. | None of the mentioned |
| Answer» B. Allows SQL Server to support Web-based applications where users create their own identities | |
| 196. |
______ optional password policies are available for SQL Server logins. |
| A. | 2 |
| B. | 3 |
| C. | 4 |
| D. | All of the mentioned |
| Answer» C. 4 | |
| 197. |
By using Windows Authentication, Windows groups can be created at the _________ level. |
| A. | Login |
| B. | Domain |
| C. | User |
| D. | None of the mentioned |
| Answer» C. User | |
| 198. |
Which of the following authentication modes is more secure ? |
| A. | SQL Server Authentication |
| B. | Windows Authentication |
| C. | Mixed Mode |
| D. | All of the mentioned |
| Answer» C. Mixed Mode | |
| 199. |
An exciting new feature in SQL Server 2014 is the support for the deployment of a Failover Cluster Instance (FCI) with : |
| A. | Cluster Shared Volumes (CSV) |
| B. | In memory database |
| C. | Column oriented database |
| D. | All of the mentioned |
| Answer» B. In memory database | |
| 200. |
A Windows Failover Cluster can support up to _______ nodes. |
| A. | 12 |
| B. | 14 |
| C. | 16 |
| D. | 18 |
| Answer» D. 18 | |