DS0-001 Practice Test 14 — 15 Questions

Question 1

Which SQL statement is used to remove a table entirely from the database?

Accepted Answer

DROP TABLE. The DROP TABLE statement removes the definition and all data within the table permanently.

Answer

DELETE TABLE

Answer

REMOVE TABLE

Answer

TRUNCATE TABLE

Question 2

Which TWO of the following are DDL (Data Definition Language) commands?

Accepted Answer

DROP. CREATE and DROP are DDL commands used to define or modify database structures.

Answer

INSERT

Answer

SELECT

Answer

UPDATE

Question 3

During a database deployment, the administrator needs to ensure that audit logs are captured for all privileged user actions. Which component should be configured?

Accepted Answer

Database Audit Specification. A SQL Server Audit or similar database-level auditing feature allows the tracking of specific actions (like ALTER or DROP) performed by privileged accounts.

Answer

Row-Level Security

Answer

Data Masking

Answer

Query Store

Question 4

You are performing a backup for a database that is in a 24/7 environment. Which backup type should you use if you want to avoid interfering with the existing backup sequence of full and differential backups?

Accepted Answer

Copy-only backup. A 'Copy-only' backup creates a copy of the database or log without affecting the differential or log backup sequence.

Answer

Full backup

Answer

Differential backup

Answer

Transaction log backup

Question 5

An application requires an automatic timestamp for every row inserted into an 'Audit' table. Which feature should the developer implement?

Accepted Answer

Trigger. A trigger is a database object that automatically executes code when a specific event (like INSERT) occurs on a table.

Answer

Index

Answer

Constraint

Answer

View

Question 6

To prevent unauthorized access to specific columns in a 'Salary' table, which object should be created for the reporting team?

Accepted Answer

View. A view can be used to expose only specific columns to a user or group, effectively hiding sensitive data like salaries.

Answer

Index

Answer

Sequence

Answer

Stored Procedure

Question 7

A database is experiencing high lock contention on a specific table during batch update processes. What is the most effective way to resolve this issue without changing the application code?

Accepted Answer

Enabling snapshot isolation. Implementing snapshot isolation (or read-committed snapshot isolation) allows readers to access the versioned data without blocking writers, significantly reducing contention.

Answer

Switching to an asynchronous commit model

Answer

Increasing the memory allocation for the buffer pool

Answer

Dropping all non-clustered indexes

Question 8

Which TWO settings should be configured in a database deployment to improve performance for heavy read-intensive reporting workloads?

Accepted Answer

Columnstore indexes. Read-only replicas and proper indexing strategies are key to offloading work from the primary write instance.

Answer

Increasing the number of transaction logs

Answer

Dropping primary key constraints

Answer

Asynchronous commit mode

Question 9

A DBA is troubleshooting a slow query. The execution plan indicates an 'Implicit Conversion' is occurring, which prevents the use of an index seek. What is the most likely cause?

Accepted Answer

Data type mismatch between column and parameter. An implicit conversion happens when the data type of a column does not match the data type of the constant or parameter being compared to it, forcing the engine to convert every row.

Answer

The database collation is incorrect

Answer

The table is not partitioned

Answer

The statistics are outdated

Question 10

Which TWO of the following are common symptoms of an undersized transaction log file?

Accepted Answer

Transaction log full errors. Frequent auto-growth events and failed transactions (due to log full errors) are the primary indicators of an undersized log.

Answer

Memory fragmentation

Answer

Excessive CPU usage

Answer

Slow index reading

Question 11

A DBA is configuring an Always On Availability Group in SQL Server. To ensure that the secondary node can be used for read-only reporting tasks, which setting must be correctly configured in the Availability Group replica settings?

Accepted Answer

Readable Secondary. The 'Readable Secondary' property must be set to 'Read-intent only' or 'Yes' to allow the secondary node to accept connections for reporting tasks.

Answer

Automatic Failover

Answer

Synchronous Commit

Answer

Primary Role

Question 12

When migrating data between two different relational databases, which DDL-related issue is most likely to cause failure if ignored?

Accepted Answer

Incompatible data types. Data types can vary significantly between RDBMS vendors, leading to truncation or conversion errors if not mapped correctly during migration.

Answer

Table naming conventions

Answer

Storage engine choice

Answer

Primary key naming

Question 13

When planning database capacity, you calculate the projected growth of a table based on transaction volume and row size. What does 'database bloat' refer to in this context?

Accepted Answer

The accumulation of unused space in data pages. Database bloat is the accumulation of unused space within data files, often caused by frequent updates and deletes that leave empty pages that aren't automatically reclaimed.

Answer

The rapid growth of transaction logs

Answer

The degradation of query performance over time

Answer

The increase in the number of concurrent connections

Question 14

A database is configured with an 'Asynchronous Commit' replication mode. What is the potential risk during a primary server failure?

Accepted Answer

Potential data loss. Asynchronous replication acknowledges transactions on the primary before they reach the secondary, meaning some committed data might not have been replicated, leading to data loss upon failover.

Answer

Increased latency on the primary server

Answer

Deadlocks on the primary

Answer

Inability to perform reads on the secondary

Question 15

During a disaster recovery test, the DBA realizes the database restoration takes too long. Which action is the most effective way to improve the restore speed of a large database?

Accepted Answer

Use striped backups. Using the 'WITH MOVE' and 'FILE=' options is standard, but using multiple backup files ('striped backups') allows for parallel reading and writing during the restore process, significantly increasing speed.

Answer

Disable antivirus

Answer

Run in single-user mode

Answer

Increase the recovery interval