CompTIA DataSys+ (DS0-001)Database FundamentalsEasy
A software development team is building an application that frequently needs to retrieve user profile information based on a `UserID`. The `Users` table contains millions of records, and queries for `UserID` are becoming slow. Which SQL Data Definition Language (DDL) command should the team use to speed up these lookups?
- ACREATE VIEW
- BALTER TABLE ADD COLUMN
- CTRUNCATE TABLE
- DCREATE INDEX
Show answer & explanationAnswer & explanation
Correct answer: D. CREATE INDEX
The `CREATE INDEX` command is used to create an index on one or more columns of a table. Indexes significantly speed up data retrieval operations, especially on large tables, by providing faster lookup paths for specific values like `UserID`.
Why the other options are wrong
- A. CREATE VIEW creates a virtual table, which does not inherently improve query performance.
- B. ALTER TABLE ADD COLUMN modifies the table structure by adding a new column, not for performance optimization.
- C. TRUNCATE TABLE removes all rows from a table, which is a DDL command but not for speeding up lookups on existing data.
CREATE INDEX
A SQL DDL command used to create an index on a table, which helps retrieve data from the database more quickly.
- Improves the speed of data retrieval (SELECT statements).
- Can be created on single or multiple columns.
- Consumes disk space and can slow down data modification operations (INSERT, UPDATE, DELETE).
- Primary keys automatically have a unique index.
Memory trick: CREATE INDEX is the speed boost for your queries.