DS0-001 Practice Test 16 — 15 Questions

Question 1

When troubleshooting a performance bottleneck in a visualization, which action is most effective if the data source is a large SQL view?

Accepted Answer

Materializing the view. Materializing the view (creating a physical table with indexes) or optimizing the view's underlying query is the most effective way to resolve performance issues.

Answer

Changing the chart color

Answer

Increasing the number of filters

Answer

Using a different visualization software

Question 2

Which THREE of the following are benefits of database indexing?

Accepted Answer

Enforcement of uniqueness. Option A (Enforcement of uniqueness) is correct because a UNIQUE index (or a PRIMARY KEY constraint, which is backed by a unique index) forces the database engine to reject any duplicate values in the indexed column(s), guaranteeing data integrity. Option B (Faster data retrieval) is correct because an index provides a sorted, balanced-tree (B-tree) or hash structure that lets the query optimizer locate rows with index seeks instead of scanning the entire table, dramatically reducing I/O for SELECT and WHERE predicates. Option C (Optimization of JOIN operations) is correct because indexes on join columns allow the optimizer to perform efficient nested-loop, hash, or merge joins by probing the indexed key rather than scanning full tables. Option D (Faster write operations) is not a benefit: every INSERT, UPDATE, or DELETE must also maintain the index entries, adding overhead and slowing writes. Option E (Reduced storage requirements) is not a benefit: indexes consume additional disk space and memory, increasing rather than reducing storage needs.

Answer

Faster write operations

Answer

Reduced storage requirements

Question 3

An organization is deploying a database that requires a Recovery Time Objective (RTO) of less than one minute. Which backup and recovery strategy is most appropriate?

Accepted Answer

High-availability clustering. An RTO under one minute demands near-instantaneous failover with minimal or zero data loss, which high-availability clustering provides by keeping a synchronized standby node ready to take over automatically. Clustering solutions such as SQL Server Always On availability groups or Oracle RAC detect failure and redirect workloads within seconds, satisfying sub-minute recovery. Log shipping, tape, and daily incrementals all involve longer recovery windows.

Answer

Transaction log shipping to a standby server

Answer

Tape-based full backups

Answer

Daily incremental backups

Question 4

A DBA needs to automate the maintenance of database statistics in SQL Server. Which tool is the standard approach to manage this across all user databases?

Accepted Answer

SQL Server Agent Maintenance Plans. SQL Server Agent Maintenance Plans are the built-in, standard mechanism for scheduling recurring administrative tasks like updating statistics, rebuilding indexes, and running backups across all user databases. They integrate directly with SQL Server Agent jobs and provide a wizard-driven or T-SQL-driven way to target multiple databases in one plan. This is the canonical answer for automating statistics maintenance in SQL Server.

Answer

Active Directory Users and Computers

Answer

SQL Server Configuration Manager

Answer

Windows Task Scheduler

Question 5

Which THREE of the following are common data types in relational databases?

Accepted Answer

BOOLEAN. BOOLEAN (A) is a standard relational data type that stores TRUE/FALSE (or NULL) values, commonly used for flags and binary states. VARCHAR (D) is a variable-length character string type that stores alphanumeric text up to a defined maximum length, making it one of the most widely used column types. INTEGER (E) is a numeric data type storing whole numbers, typically used for IDs, counts, and other discrete values. PIVOT (B) is not a data type but a relational operation/query construct that rotates rows into columns, and DASHBOARD (C) is a reporting/visualization artifact rather than a column data type, so neither belongs in this list.

Answer

PIVOT

Answer

DASHBOARD

Question 6

Which THREE of the following are examples of descriptive statistics?

Accepted Answer

Standard deviation. Standard deviation (A) is a descriptive statistic because it quantifies the spread or dispersion of a dataset around its mean, summarizing variability without inferring beyond the observed data. The mean (B) is a measure of central tendency that summarizes a dataset by averaging all values, making it a classic descriptive statistic. The median (D) is also a measure of central tendency, representing the middle value of an ordered dataset and summarizing its central position descriptively. Machine learning (C) is not a descriptive statistic; it is a field of algorithms used for prediction or pattern recognition, often involving inferential or predictive modeling. Linear regression (E) is an inferential/predictive technique that models relationships between variables to estimate or forecast outcomes, not merely to summarize data.

Answer

Machine learning

Answer

Linear regression

Question 7

Which TWO of the following are examples of logical database design tasks?

Accepted Answer

Normalizing the data schema. Normalizing the data schema (C) is a logical design task because it organizes tables and relationships to eliminate redundancy and anomalies without regard to physical storage. Defining entity-relationship models (E) is also logical design, as ER modeling captures entities, attributes, and relationships at a conceptual/logical level before implementation. The other options are physical design or operational tasks: choosing a RAID level (A) and selecting a database file path (B) concern storage layout, while configuring the buffer pool size (D) tunes memory allocation for performance.

Answer

Choosing the RAID level for storage

Answer

Selecting the database file path

Answer

Configuring the database buffer pool size

Question 8

Which THREE of the following are benefits of implementing a standardized database schema migration process?

Accepted Answer

Version control for schema changes. Option A (Version control for schema changes) is correct because a standardized migration process stores each schema change as a versioned, ordered script (e.g., in Git alongside application code), so the database's evolution can be tracked, reviewed, and rolled back to a known revision. Option B (Audit trails for all schema modifications) is correct because migrations are applied through a controlled, logged mechanism that records who ran which migration, when, and in what order, providing a reliable change history for compliance and troubleshooting. Option E (Consistency of database structure across environments) is correct because the same migration scripts are replayed identically on development, test, staging, and production, eliminating schema drift between environments. Option C is incorrect because hardware startup time is a function of the OS, firmware, and services, not of how database schema changes are managed. Option D is incorrect because password conversion is an application-level authentication concern (e.g., rehashing credentials), not something a schema migration process performs automatically.

Answer

Faster hardware startup times

Answer

Automatic conversion of user passwords

Question 9

Which THREE of the following are necessary prerequisites for implementing database mirroring in a SQL Server environment?

Accepted Answer

Database must be in Full recovery model. Option A is correct because database mirroring requires the principal database to use the Full recovery model so that the transaction log can support the log stream sent to the mirror; Simple or Bulk-Logged recovery models are not supported for mirroring. Option B is correct because before mirroring can start, you must restore a full backup of the principal database on the mirror server WITH NORECOVERY, followed by any necessary transaction log backups WITH NORECOVERY, to bring the mirror database to a recovering state. Option E is correct because the SQL Server service accounts on both the principal and mirror instances must have the appropriate permissions to connect and communicate, typically via domain accounts or certificates with endpoints configured for database mirroring. Option C is not required — a domain controller is not necessary, and in fact running SQL Server on a domain controller has its own restrictions. Option D is not required — the mirror database stays in a restoring/recovering state (not read-only accessible) until a failover occurs, and read-only access is only available with a snapshot, not as a mirroring prerequisite.

Answer

The server must be a domain controller

Answer

Must be running in Read-only mode

Question 10

Which THREE database configuration settings help ensure data security and regulatory compliance?

Accepted Answer

Role-Based Access Control (RBAC). Role-Based Access Control (RBAC) is correct because it enforces least-privilege access by granting permissions to roles rather than individual users, which is a core requirement of security frameworks and regulatory mandates such as SOX, HIPAA, and PCI DSS. Transparent Data Encryption (TDE) is correct because it encrypts data at rest (data files, log files, and backups) using a database encryption key protected by a certificate or asymmetric key, satisfying compliance requirements for protecting stored sensitive data. Audit logging of DDL and DML operations is correct because it creates a tamper-evident trail of who changed what schema objects and data and when, which is explicitly required for regulatory audits and forensic investigation. Disabling transaction log backups is not correct because it breaks the log chain, eliminates point-in-time recovery, and increases data-loss risk, which harms rather than helps compliance. Automatic plan cache clearing is not correct because it is a performance-tuning behavior that has no bearing on access control, encryption, or auditability.

Answer

Disabling transaction log backups

Answer

Automatic plan cache clearing

Question 11

Which TWO of the following are common types of data visualizations used to represent trends over time?

Accepted Answer

Line Chart. A Line Chart (C) is correct because it plots data points connected by lines along a continuous time axis, making it the standard visualization for showing trends, patterns, and changes over time. An Area Chart (D) is also correct because it is essentially a line chart with the area beneath the line filled in, and it likewise uses a continuous time-based axis to represent trends over time (often emphasizing magnitude or cumulative volume). A Histogram (A) is not correct here because it displays the frequency distribution of continuous data across bins rather than a time series. A Scatter Plot (B) is not correct because it shows the relationship or correlation between two variables as individual points, not a trend over time. A Pie Chart (E) is not correct because it represents parts of a whole as proportional slices at a single point in time, not changes over time.

Answer

Histogram

Answer

Scatter Plot

Answer

Pie Chart

Question 12

Which THREE of the following are valid methods to improve database performance for read-heavy workloads?

Accepted Answer

Create read-only replicas. Option A (Create read-only replicas) is correct because read replicas offload SELECT traffic from the primary database, allowing reads to be distributed across multiple nodes and improving throughput for read-heavy workloads. Option B (Create covering indexes) is correct because a covering index contains all columns required by a query, letting the database satisfy the query entirely from the index without touching the base table, drastically reducing I/O for frequent read queries. Option E (Implement query caching) is correct because caching the results of frequently executed read queries avoids repeated database work and returns results faster, directly benefiting read-heavy patterns. Option C (Increase the number of write threads) does not belong because it targets write throughput, not read performance, and can even contend with reads. Option D (Enable synchronous replication) does not belong because synchronous replication waits for replicas to acknowledge writes, adding latency and reducing write performance rather than improving read-heavy workloads.

Answer

Increase the number of write threads

Answer

Enable synchronous replication

Question 13

Which TWO of the following are common administrative tasks in a Data Governance program?

Accepted Answer

Managing the data catalog metadata. Managing the data catalog metadata (B) is a core data governance administrative task because the catalog documents data assets, definitions, lineage, ownership, and stewardship information that governance relies on to make data discoverable and accountable. Conducting access rights certification (E) is also a common governance administrative task, as it periodically reviews and attests that users retain only the data access privileges appropriate to their roles, supporting least-privilege and compliance requirements. The other options fall outside data governance administration: updating the server operating system (A) is IT infrastructure/patch management, repairing broken network cables (C) is physical network maintenance, and optimizing database query performance (D) is database administration/tuning rather than governance of data policies, stewardship, or access accountability.

Answer

Updating the server operating system

Answer

Repairing broken network cables

Answer

Optimizing database query performance

Question 14

Which TWO tasks are critical before performing a major database schema upgrade?

Accepted Answer

Full database backup. Option A (Full database backup) is correct because a complete, restorable backup (e.g., a full backup plus any needed transaction log backups, or a snapshot) is the only reliable rollback path if the schema upgrade fails or corrupts data, so it must be taken immediately before the change. Option C (Migration script validation in a staging environment) is correct because rehearsing the DDL/DML migration scripts against a staging copy that mirrors production schema, data volume, and dependencies exposes syntax errors, lock contention, and constraint or index conflicts before touching production. Option B (Adding more memory) is not a prerequisite for a schema upgrade; memory sizing is a capacity-tuning activity and does not protect data or validate the migration. Option D (Deleting all stored procedures) is destructive and unnecessary, since procedures should be reviewed for dependency compatibility, not removed. Option E (Updating the OS kernel version) is unrelated to schema migration readiness and introduces additional change risk rather than mitigating it.

Answer

Adding more memory to the server

Answer

Deleting all stored procedures

Answer

Updating the OS kernel version

Question 15

Which THREE of the following are indicators of significant index fragmentation in a database?

Accepted Answer

Low page density. Low page density (A) is a direct indicator of significant index fragmentation because fragmentation leaves pages partially filled with free space, so the average page fullness (page density) drops well below 100%. High scan latency (B) is correct because fragmented indexes force the storage engine to read more pages and perform extra I/O during scans, measurably increasing the time to complete scan operations. Decreased query performance (C) is correct because fragmentation inflates the number of page reads for range scans and ordered lookups, so queries that rely on the index slow down noticeably. Increased memory usage (D) is not a reliable fragmentation indicator, since buffer pool consumption is driven by workload and caching behavior rather than index page ordering. Increased server uptime (E) is unrelated to fragmentation, as uptime measures elapsed time since the last restart and says nothing about index page layout or density.

Answer

Increased memory usage

Answer

Increased server uptime