CompTIA DataSys+ (DS0-001)Database FundamentalsMedium
A database developer is writing a complex SQL query that involves joining five different tables and performing several aggregations. This query is executed frequently by multiple reporting tools. To improve performance and reusability, the developer wants to encapsulate this query logic and make it callable with different parameters. Which database object should be used?
- AIndex
- BStored Procedure
- CView
- DMaterialized View
Show answer & explanationAnswer & explanation
Correct answer: B. Stored Procedure
A stored procedure allows encapsulating complex query logic, accepting parameters, and executing a series of SQL statements. This makes it ideal for improving performance (due to pre-compilation), reusability, and security for frequently executed, parameterized queries.
Why the other options are wrong
- A. An index speeds up data retrieval but doesn't encapsulate query logic or accept parameters.
- C. A view simplifies complex queries but doesn't accept parameters or encapsulate procedural logic.
- D. A materialized view pre-computes and stores query results for faster retrieval but doesn't accept parameters or encapsulate logic.
Stored Procedure
A pre-compiled collection of one or more SQL statements or other procedural logic stored in the database.
- Accepts input parameters and returns output parameters/result sets.
- Enhances performance, reusability, and security.
- Can contain control-flow statements (IF, WHILE, etc.).
Memory trick: Stored Procedures 'Store' 'Procedures' for complex, callable logic.