DA0-002 Data Concepts and Environments Practice Question
Exhibit
CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, OrderDate DATE, TotalAmount DECIMAL(10,2), INDEX idx_cust (CustomerID), INDEX idx_date (OrderDate) );
Refer to the exhibit. A database administrator notices that queries filtering on both CustomerID and OrderDate are slow. Which single change would most likely improve performance for such queries?
⚠ Common exam trap
The trap is choosing partitioning by OrderDate because it sounds like a performance fix — candidates overlook that the query filters on CustomerID first, and partitioning on the wrong column does not help and may hurt.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Add a composite index on (CustomerID, OrderDate)
A composite index on (CustomerID, OrderDate) allows the database to satisfy queries that filter on both columns using a single index seek, avoiding a full table scan or multiple index lookups. The column order matters: CustomerID first supports equality filtering, and OrderDate second supports range or sort operations within each customer. This is the most direct performance improvement for the described query pattern.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Partition the table by OrderDate
Why it's wrong here
Partitioning by OrderDate prunes on the date predicate alone, yet queries filtering on CustomerID still scan every partition. A composite index leading with CustomerID then OrderDate serves both predicates. Date partitioning suits time-range reporting or retention purges, not equality lookups on another column.
- ✗
Convert TotalAmount to VARCHAR
Why it's wrong here
Storing TotalAmount as VARCHAR forces implicit conversion during numeric comparisons and aggregations, preventing index seeks and inflating storage. Character types suit identifiers and text; monetary values belong in DECIMAL. The slow queries filter on CustomerID and OrderDate, which this change does not touch.
- ✓
Add a composite index on (CustomerID, OrderDate)
Why this is correct
A composite index on (CustomerID, OrderDate) matches the query's equality filter followed by its range or sort column, letting the engine seek directly to the relevant CustomerID entries already ordered by OrderDate. This avoids scanning and sorting, unlike separate single-column indexes.
- ✗
Remove the primary key constraint
Why it's wrong here
Dropping the primary key removes uniqueness enforcement and the clustered index that supports lookups, degrading rather than aiding filtering. Primary keys exist to guarantee row identity and enable efficient seeks; removing one is considered only when bulk-loading into a staging table where constraints are reapplied later.
About these practice questions
One of 1,004 original DA0-002 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official CompTIA exam blueprint
This DA0-002 practice question is part of Courseiva's free CompTIA certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DA0-002 exam.