Download MICROSOFT CERTIFIED: SQL SERVER DATABASE ADMINISTRATOR EXAMINATION QUESTIONS and more Exams Microsoft Access Skills in PDF only on Docsity!
MICROSOFT CERTIFIED: SQL SERVER
DATABASE ADMINISTRATOR
EXAMINATION QUESTIONS AND
CORRECT ANSWERS (VERIFIED
ANSWERS) PLUS RATIONALES 2026 Q&A
| INSTANT DOWNLOAD PDF
What is the primary purpose of SQL Server Agent? A) Manage database files B) Schedule and automate jobs C) Encrypt database data D) Monitor disk usage Answer: B) Schedule and automate jobs Rationale: SQL Server Agent is a Windows service used to schedule and execute jobs such as backups, maintenance plans, and ETL tasks. It is essential for automation of routine administrative operations in SQL Server environments. 2 Which recovery model fully supports point-in-time recovery? A) Simple B) Bulk-logged
C) Full D) Read-only Answer: C) Full Rationale: The Full recovery model logs all transactions and retains log records until they are backed up. This enables point-in-time recovery, making it ideal for critical production databases requiring minimal data loss. 3 What does the DBCC CHECKDB command do? A) Checks user permissions B) Validates database integrity C) Optimizes query plans D) Rebuilds indexes Answer: B) Validates database integrity Rationale: DBCC CHECKDB checks the physical and logical integrity of all objects in a database, detecting corruption in tables, indexes, and system structures. It is a key maintenance command. 4 Which index type is best for improving query performance on large tables? A) Heap index B) Clustered index C) XML index D) Filtered index Answer: B) Clustered index Rationale: A clustered index sorts and stores data rows based on key values, improving range query performance and reducing I/O for large datasets.
Answer: C) Store temporary objects Rationale: tempdb is used for temporary tables, table variables, work tables, and sorting operations during query execution. 8 Which command is used to create a database backup? A) BACKUP DATABASE B) RESTORE DATABASE C) CHECKPOINT D) SHRINK DATABASE Answer: A) BACKUP DATABASE Rationale: BACKUP DATABASE creates a full or partial backup of a SQL Server database for recovery purposes. 9 What is the purpose of a foreign key? A) Enforce uniqueness in a table B) Link two tables together C) Encrypt data D) Improve indexing speed Answer: B) Link two tables together Rationale: A foreign key enforces referential integrity by linking a column in one table to a primary key in another table. 10 Which SQL Server component manages memory allocation?
A) SQL Server Browser B) Buffer Manager C) Query Optimizer D) SQL Agent Answer: B) Buffer Manager Rationale: The Buffer Manager controls how SQL Server uses memory to cache data pages, improving performance by reducing disk I/O. 11 What is a deadlock? A) Server crash B) Infinite loop in query C) Two processes waiting on each other D) Backup failure Answer: C) Two processes waiting on each other Rationale: A deadlock occurs when two or more transactions block each other by holding locks that the other needs, resulting in a cycle of dependency. 12 Which tool is used to monitor real-time performance in SQL Server? A) SQL Profiler B) Activity Monitor C) DBCC CHECKDB D) SSIS Answer: B) Activity Monitor Rationale: Activity Monitor provides real-time monitoring of SQL Server processes, resource usage, and active queries.
Answer: B) Splitting tables for performance Rationale: Table partitioning divides large tables into smaller, manageable pieces, improving query performance and maintenance efficiency. 16 Which command removes duplicate rows? A) DELETE B) DISTINCT C) TRUNCATE D) DROP Answer: B) DISTINCT Rationale: DISTINCT filters out duplicate rows in query results, returning only unique values. 17 What does the CHECK constraint enforce? A) Data encryption B) Data type validation rules C) Referential integrity D) Backup integrity Answer: B) Data type validation rules Rationale: CHECK constraints ensure that column values meet specific conditions, such as range or format requirements. 18 Which system database stores login information?
A) tempdb B) model C) msdb D) master Answer: D) master Rationale: The master database contains system-level information including logins, configuration settings, and metadata. 19 What is the purpose of normalization? A) Improve security B) Reduce data redundancy C) Increase storage usage D) Speed backups Answer: B) Reduce data redundancy Rationale: Normalization organizes data into related tables to minimize redundancy and improve data integrity. 20 Which join returns only matching rows? A) LEFT JOIN B) RIGHT JOIN C) FULL JOIN D) INNER JOIN Answer: D) INNER JOIN Rationale: INNER JOIN returns only rows where matching values exist in both joined tables.
Answer: B) Chooses execution plans Rationale: The Query Optimizer evaluates multiple execution strategies and selects the most efficient plan for query execution. 24 Which feature improves disaster recovery across geographic locations? A) Replication B) Log shipping C) Always On Availability Groups D) Indexing Answer: C) Always On Availability Groups Rationale: This feature provides high availability across multiple geographic regions with automatic failover capabilities. 25 What is the purpose of a view? A) Store physical data B) Store backups C) Provide virtual table representation D) Encrypt columns Answer: C) Provide virtual table representation Rationale: A view is a virtual table based on a query that simplifies data access and enhances security. 26 What does TRUNCATE TABLE do?
A) Deletes specific rows B) Deletes all rows quickly C) Deletes table structure D) Logs all deletions Answer: B) Deletes all rows quickly Rationale: TRUNCATE removes all rows without logging individual row deletions, making it faster than DELETE. 27 Which SQL Server component handles query parsing? A) Storage Engine B) Query Processor C) Buffer Pool D) SQL Agent Answer: B) Query Processor Rationale: The Query Processor parses, compiles, and optimizes SQL queries before execution. 28 What is a transaction? A) Backup operation B) Single logical unit of work C) Index creation D) Table constraint Answer: B) Single logical unit of work Rationale: A transaction ensures ACID properties, grouping operations into a single unit that succeeds or fails entirely.
Answer: B) Separate structure pointing to data Rationale: Non-clustered indexes store pointers to actual data rows, improving lookup efficiency without altering physical order. 32 What is the purpose of cascading delete? A) Encrypt records B) Automatically delete related rows C) Create indexes D) Compress tables Answer: B) Automatically delete related rows Rationale: Cascading delete ensures referential integrity by deleting dependent records when a parent record is removed. 33 Which command modifies table structure? A) ALTER TABLE B) UPDATE TABLE C) CHANGE TABLE D) MODIFY DATABASE Answer: A) ALTER TABLE Rationale: ALTER TABLE is used to add, modify, or remove columns and constraints. 34 What is a stored procedure?
A) Backup script B) Precompiled SQL code C) Temporary table D) Index type Answer: B) Precompiled SQL code Rationale: Stored procedures are precompiled SQL statements that improve performance and reuse. 35 What does replication do? A) Encrypts data B) Copies data between servers C) Deletes logs D) Compresses indexes Answer: B) Copies data between servers Rationale: Replication distributes data across multiple servers for scalability and availability. 36 What is a primary key? A) Allows duplicates B) Uniquely identifies rows C) Encrypts data D) Stores logs Answer: B) Uniquely identifies rows Rationale: A primary key ensures each row in a table is uniquely identifiable.
and batch jobs. It allows DBAs to run recurring tasks without manual intervention, improving efficiency and reliability of operations. 40.Which isolation level prevents dirty reads but allows non-repeatable reads? A. Serializable B. Repeatable Read C. Read Committed D. Read Uncommitted Correct Answer: C Rationale: Read Committed is the default isolation level in SQL Server. It ensures that a transaction only reads committed data, preventing dirty reads. However, data can change between reads within the same transaction, allowing non-repeatable reads. 41.What does DBCC CHECKDB do? A. Rebuild indexes B. Check database integrity C. Backup database D. Optimize queries Correct Answer: B Rationale: DBCC CHECKDB is a critical maintenance command that checks logical and physical integrity of all objects in the database. It detects corruption and consistency issues across tables, indexes, and system metadata. 42.Which feature allows automatic failover in SQL Server Always On? A. Log Shipping B. Database Mirroring C. Availability Groups D. Replication
Correct Answer: C Rationale: Always On Availability Groups provide high availability and disaster recovery with automatic failover between replicas, ensuring minimal downtime and data loss in case of failure. 43.What is a clustered index? A. A separate index file B. A logical pointer only C. An index that defines physical order of data D. A temporary index Correct Answer: C Rationale: A clustered index determines the physical storage order of data rows in a table. Each table can have only one clustered index because data rows can be stored in only one order. 44.Which recovery model supports point-in-time recovery? A. Simple B. Bulk-Logged C. Full D. Read-Only Correct Answer: C Rationale: The Full recovery model logs all transactions and allows transaction log backups, enabling point-in-time recovery in case of failure or data loss. 45.What is a deadlock in SQL Server? A. A slow query B. A memory overflow C. Two processes waiting on each other D. A backup failure
Correct Answer: B Rationale: Replication is used to copy and distribute data from one database to another, ensuring data availability and supporting distributed applications and reporting systems. 49.What does the TempDB database store? A. User data only B. System logs C. Temporary objects and intermediate results D. Backup files Correct Answer: C Rationale: TempDB is a system database used for temporary tables, table variables, work tables, and intermediate query results during sorting or joining operations. 50.Which command is used to rebuild an index? A. ALTER INDEX REBUILD B. UPDATE INDEX C. REINDEX TABLE D. FIX INDEX Correct Answer: A Rationale: ALTER INDEX REBUILD recreates the entire index, removing fragmentation and improving performance by reorganizing data pages efficiently. 51.What is log shipping primarily used for? A. Real-time query optimization B. High availability disaster recovery C. Index rebuilding D. Memory tuning
Correct Answer: B Rationale: Log shipping involves sending transaction log backups from a primary server to a secondary server to maintain a standby copy for disaster recovery purposes. 52.What is the main function of a view in SQL Server? A. Store physical data B. Improve disk speed C. Provide a virtual table based on a query D. Encrypt database files Correct Answer: C Rationale: A view is a virtual table that represents the result of a stored query, simplifying complex queries and improving security by restricting direct access to data. 53.What does normalization primarily reduce? A. Data encryption B. Data redundancy C. Query speed D. Storage size only Correct Answer: B Rationale: Normalization organizes database structure to reduce redundancy and improve data integrity by dividing large tables into smaller, related tables. 54.What is the purpose of the CHECK constraint? A. Encrypt data B. Enforce domain rules on column values C. Create indexes D. Backup tables
