SQL Server Certification Practice Test

1. Which isolation level prevents dirty reads but allows phantom reads?

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

2. What is the primary purpose of a clustered index in SQL Server?

Sort and store data rows based on the index key

Create a logical reference to data without physical reordering

Improve performance of JOIN operations only

Enforce uniqueness constraints exclusively

3. Which T-SQL statement is used to create a backup of a database?

RESTORE DATABASE

BACKUP DATABASE

CREATE BACKUP

COPY DATABASE

4. True or False: A non-clustered index can be created on a table without a clustered index.

True

False

5. What does ACID stand for in database transactions?

Atomicity, Consistency, Isolation, Durability

Accuracy, Control, Integrity, Distribution

Access, Commit, Index, Data

Authentication, Concurrency, Integration, Deployment

6. Which execution plan operator indicates a table scan?

Index Seek

Index Scan

Table Scan

Clustered Index Seek

7. True or False: Stored procedures in SQL Server are compiled and pre-optimized for faster execution.

True

False

8. What is the maximum size of a SQL Server database file?

1 GB

16 TB

1 TB

Unlimited

9. Which permission allows a user to execute a stored procedure?

EXECUTE

CONTROL

ALTER

CREATE

10. True or False: A primary key constraint automatically creates a clustered index.

True

False

11. What does the CHECKPOINT statement do in SQL Server?

Flushes dirty pages from the buffer cache to disk

Creates a backup of the transaction log

Pauses all database operations

Rolls back uncommitted transactions

12. Which recovery model allows point-in-time restore capability?

Simple

Bulk-logged

Full

Partial

13. True or False: Blocking occurs when one transaction holds locks that prevent another transaction from acquiring locks.

True

False

14. What is the primary benefit of using columnstore indexes?

Faster OLTP write operations

Improved compression and query performance for analytical workloads

Reduced disk space for all query types

Elimination of table locks

15. Which system view displays currently running queries in SQL Server?

Sys.tables

Sys.dm_exec_requests

Sys.indexes

Sys.objects

Microsoft SQL Server Certification Practice Test

1. Which isolation level prevents dirty reads but allows phantom reads?

2.

What first name or nickname would you like us to use?

2. What is the primary purpose of a clustered index in SQL Server?

3. Which T-SQL statement is used to create a backup of a database?

4. True or False: A non-clustered index can be created on a table without a clustered index.

5. What does ACID stand for in database transactions?

6. Which execution plan operator indicates a table scan?

7. True or False: Stored procedures in SQL Server are compiled and pre-optimized for faster execution.

8. What is the maximum size of a SQL Server database file?

9. Which permission allows a user to execute a stored procedure?

10. True or False: A primary key constraint automatically creates a clustered index.

11. What does the CHECKPOINT statement do in SQL Server?

12. Which recovery model allows point-in-time restore capability?

13. True or False: Blocking occurs when one transaction holds locks that prevent another transaction from acquiring locks.

14. What is the primary benefit of using columnstore indexes?

15. Which system view displays currently running queries in SQL Server?