SQL Server - 50 MCQs (Entry to Mid-
Level)
Q1: Which SQL Server database stores system-level objects like logins and configuration
settings?
A) msdb
B) master
C) model
D) tempdb
Correct Answer: B
Explanation: The master database holds logins, configuration, and metadata for the SQL
Server instance.
Q2: Which SQL Server system database is recreated every time SQL Server restarts?
A) msdb
B) model
C) tempdb
D) resource
Correct Answer: C
Explanation: tempdb is a temporary workspace recreated on every restart.
Q3: What is the maximum number of clustered indexes allowed per SQL Server table?
A) 1
B) 2
C) Unlimited
D) Depends on columns
Correct Answer: A
Explanation: Only one clustered index is allowed per table since it defines physical row
order.
Q4: Which recovery model does not support transaction log backups?
A) Full
B) Simple
C) Bulk-logged
D) All support backups
Correct Answer: B
Explanation: In Simple recovery, logs are truncated automatically, so transaction log
backups are not possible.
Q5: Which SQL Server feature supports automatic failover and readable secondary replicas?
A) Log Shipping
B) Database Mirroring
C) Always On Availability Groups
D) Replication
Correct Answer: C
Explanation: Always On Availability Groups allow automatic failover and multiple
secondaries.
Q6: Which command checks database integrity in SQL Server?
A) DBCC CHECKTABLE
B) DBCC CHECKALLOC
C) DBCC CHECKDB
D) DBCC CHECKFILE
Correct Answer: C
Explanation: DBCC CHECKDB checks the logical and physical integrity of all objects in a
database.
Q7: Which system database stores SQL Server Agent jobs and backup history?
A) msdb
B) master
C) model
D) resource
Correct Answer: A
Explanation: msdb contains SQL Agent jobs, backup/restore history, and alerts.
Q8: What happens if the transaction log becomes full?
A) SQL Server auto-shrinks log
B) New transactions halt
C) SQL Server truncates log
D) SQL Server drops log
Correct Answer: B
Explanation: Transactions cannot proceed until log space is freed or expanded.
Q9: Which DMV shows currently running SQL Server requests?
A) sys.dm_exec_sessions
B) sys.dm_exec_requests
C) sys.dm_exec_connections
D) sys.dm_tran_locks
Correct Answer: B
Explanation: sys.dm_exec_requests shows details about active queries and waits.
Q10: Which SQL Server tool graphically displays estimated and actual execution plans?
A) Activity Monitor
B) SQL Profiler
C) Query Store
D) SSMS Execution Plan Viewer
Correct Answer: D
Explanation: SSMS allows graphical execution plan viewing for optimization.
Q11: Which SQL Server edition is free but limited in features and size?
A) Standard
B) Enterprise
C) Developer
D) Express
Correct Answer: D
Explanation: SQL Server Express is free with size/feature limits.
Q12: Which type of join returns all rows from the left table, even if no matches exist in the
right?
A) INNER JOIN
B) FULL OUTER JOIN
C) LEFT JOIN
D) CROSS JOIN
Correct Answer: C
Explanation: LEFT JOIN returns all left-side rows and matches from the right.
Q13: Which SQL Server feature captures a consistent view of the database without locking?
A) Read Committed Snapshot Isolation
B) Serializable
C) Repeatable Read
D) Read Uncommitted
Correct Answer: A
Explanation: RCSI uses row versioning to reduce locking and blocking.
Q14: Which SQL Server feature enables point-in-time recovery?
A) Full recovery model
B) Simple recovery model
C) AlwaysOn
D) Log Shipping
Correct Answer: A
Explanation: Full recovery supports point-in-time restores using log backups.
Q15: Which tool replaces SQL Profiler for lightweight monitoring?
A) DMVs
B) Extended Events
C) Activity Monitor
D) Trace
Correct Answer: B
Explanation: Extended Events provide efficient, customizable monitoring.
Q16: Which isolation level provides the highest consistency but most blocking?
A) Read Committed
B) Repeatable Read
C) Serializable
D) Snapshot
Correct Answer: C
Explanation: Serializable prevents phantom reads and ensures full consistency but can
block more.
Q17: Which SQL Server feature provides schema and data movement to another server?
A) Replication
B) Backup
C) AlwaysOn
D) Log Shipping
Correct Answer: A
Explanation: Replication transfers schema and data between servers in near real time.
Q18: Which SQL Server command is used to create a new login?
A) CREATE USER
B) CREATE LOGIN
C) CREATE ACCOUNT
D) ALTER LOGIN
Correct Answer: B
Explanation: CREATE LOGIN creates a server-level login; CREATE USER creates a database-
level user.
Q19: Which SQL Server security feature enforces row-level access policies?
A) Transparent Data Encryption
B) Always Encrypted
C) Row-Level Security
D) Dynamic Data Masking
Correct Answer: C
Explanation: Row-Level Security restricts access based on predicates per user.
Q20: Which type of index is best for high-cardinality columns used in WHERE filters?
A) Clustered Index
B) Non-Clustered Index
C) Filtered Index
D) Columnstore Index
Correct Answer: B
Explanation: Non-clustered indexes speed up searches on selective columns.
Q21: Which SQL Server feature minimizes storage and improves analytics performance?
A) Non-clustered Index
B) Columnstore Index
C) Heap
D) Filtered Index
Correct Answer: B
Explanation: Columnstore indexes compress data and boost query performance in analytics
workloads.
Q22: Which system stored procedure lists all user databases?
A) sp_helpdb
B) sp_databases
C) sp_helpfile
D) sp_who2
Correct Answer: B
Explanation: sp_databases returns a list of databases on the instance.
Q23: Which SQL Server feature encrypts data at rest in data files and backups?
A) Always Encrypted
B) Dynamic Data Masking
C) Transparent Data Encryption
D) Row-Level Security
Correct Answer: C
Explanation: TDE encrypts the database files and backups automatically.
Q24: Which SQL Server agent job category handles backup jobs?
A) Database Maintenance
B) Replication
C) Data Collector
D) Integration Services
Correct Answer: A
Explanation: Database Maintenance category includes backup and integrity checks.
Q25: Which DMV shows index usage statistics?
A) sys.dm_db_index_operational_stats
B) sys.dm_db_index_usage_stats
C) sys.dm_exec_query_stats
D) sys.dm_exec_cached_plans
Correct Answer: B
Explanation: sys.dm_db_index_usage_stats tracks seeks, scans, lookups, and updates.
Q26: Which SQL Server isolation level allows dirty reads?
A) Read Committed
B) Read Uncommitted
C) Serializable
D) Repeatable Read
Correct Answer: B
Explanation: Read Uncommitted allows reading uncommitted/dirty data.
Q27: Which SQL Server backup type captures only changes since the last full backup?
A) Full Backup
B) Differential Backup
C) Transaction Log Backup
D) Copy-Only Backup
Correct Answer: B
Explanation: Differential backups contain only changes since the last full backup.
Q28: Which SQL Server tool can be used to monitor locks and blocking?
A) Activity Monitor
B) sys.dm_tran_locks
C) SQL Profiler
D) Query Store
Correct Answer: B
Explanation: sys.dm_tran_locks shows lock information at runtime.
Q29: Which SQL Server recovery model generates the least log records for bulk operations?
A) Full
B) Simple
C) Bulk-logged
D) Minimal
Correct Answer: C
Explanation: Bulk-logged reduces logging overhead for bulk inserts and index operations.
Q30: Which feature allows restoring individual database pages?
A) Piecemal restore
B) Page-level restore
C) Point-in-time restore
D) Partial restore
Correct Answer: B
Explanation: Page-level restore lets you restore individual corrupted pages.
Q31: Which SQL Server service is required for SQL Agent jobs?
A) SQL Server Browser
B) SQL Agent Service
C) SQL Writer
D) SQL Profiler
Correct Answer: B
Explanation: SQL Agent Service must be running for jobs and schedules.
Q32: Which SQL Server index type includes non-key columns to make it covering?
A) Unique Index
B) Composite Index
C) Included Column Index
D) Filtered Index
Correct Answer: C
Explanation: Included columns extend non-clustered indexes for covering queries.
Q33: Which DMV provides details about expensive queries?
A) sys.dm_exec_requests
B) sys.dm_exec_query_stats
C) sys.dm_tran_locks
D) sys.dm_exec_connections
Correct Answer: B
Explanation: sys.dm_exec_query_stats shows aggregated execution stats like CPU, reads,
duration.
Q34: Which backup type does not affect the backup sequence chain?
A) Full
B) Copy-Only
C) Differential
D) Transaction Log
Correct Answer: B
Explanation: Copy-Only backups are independent and don’t disturb the sequence.
Q35: Which SQL Server feature allows execution of jobs across multiple servers?
A) Linked Servers
B) SQL Agent Multiserver Jobs
C) Replication
D) AlwaysOn
Correct Answer: B
Explanation: Multiserver jobs run across servers managed from a master server.
Q36: Which SQL Server object can be partitioned?
A) Indexes
B) Tables
C) Both Tables and Indexes
D) Filegroups only
Correct Answer: C
Explanation: Both tables and indexes can be partitioned across filegroups.
Q37: Which DMV provides details about active connections?
A) sys.dm_exec_sessions
B) sys.dm_exec_requests
C) sys.dm_exec_connections
D) sys.dm_exec_query_plan
Correct Answer: C
Explanation: sys.dm_exec_connections shows details about each active connection.
Q38: Which SQL Server database is used as a template when creating new databases?
A) msdb
B) model
C) master
D) resource
Correct Answer: B
Explanation: The model database is copied when creating new databases.
Q39: Which SQL Server feature ensures encryption during client-server communication?
A) Always Encrypted
B) TLS/SSL
C) Transparent Data Encryption
D) Dynamic Data Masking
Correct Answer: B
Explanation: TLS/SSL encrypts traffic between SQL Server and clients.
Q40: Which SQL Server isolation level eliminates non-repeatable reads but allows phantom
reads?
A) Read Committed
B) Repeatable Read
C) Serializable
D) Snapshot
Correct Answer: B
Explanation: Repeatable Read prevents non-repeatable reads but phantom reads are still
possible.
Q41: Which command displays the execution plan without running the query?
A) SET SHOWPLAN_ALL
B) SET STATISTICS TIME
C) SET STATISTICS IO
D) DBCC SHOWPLAN
Correct Answer: A
Explanation: SET SHOWPLAN_ALL shows estimated plans without executing queries.
Q42: Which SQL Server feature can create encrypted columns that even DBAs cannot read?
A) Transparent Data Encryption
B) Always Encrypted
C) Dynamic Data Masking
D) Row-Level Security
Correct Answer: B
Explanation: Always Encrypted keeps encryption keys client-side so DBAs cannot decrypt
values.
Q43: Which SQL Server feature supports offloading backups to secondary replicas?
A) Log Shipping
B) Replication
C) Always On Availability Groups
D) Mirroring
Correct Answer: C
Explanation: AlwaysOn Availability Groups allow backups from secondary replicas.
Q44: Which DMV returns current wait statistics?
A) sys.dm_exec_requests
B) sys.dm_os_wait_stats
C) sys.dm_tran_locks
D) sys.dm_exec_query_plan
Correct Answer: B
Explanation: sys.dm_os_wait_stats shows wait times for diagnosing bottlenecks.
Q45: Which SQL Server feature masks sensitive data for non-privileged users?
A) Always Encrypted
B) Dynamic Data Masking
C) Row-Level Security
D) TDE
Correct Answer: B
Explanation: Dynamic Data Masking hides sensitive data for certain users without
encryption.
Q46: Which SQL Server job step type runs packages for ETL?
A) Replication
B) SSIS Package
C) Transact-SQL Script
D) ActiveX Script
Correct Answer: B
Explanation: SQL Server Integration Services (SSIS) packages handle ETL workloads.
Q47: Which SQL Server feature keeps track of schema changes?
A) CDC
B) Change Tracking
C) DDL Triggers
D) Replication
Correct Answer: C
Explanation: DDL triggers fire on schema changes such as CREATE, ALTER, DROP.
Q48: Which SQL Server feature allows incremental statistics updates?
A) AUTO_CREATE_STATISTICS
B) AUTO_UPDATE_STATISTICS
C) AUTO_UPDATE_STATISTICS_INCREMENTAL
D) Filtered Stats
Correct Answer: C
Explanation: Incremental stats updates improve performance for partitioned tables.
Q49: Which SQL Server feature lets you isolate tempdb workloads?
A) TempDB filegroups
B) Multiple TempDB files
C) Partitioned TempDB
D) Resource Governor
Correct Answer: D
Explanation: Resource Governor can control workload impact on TempDB.
