There are three reasons for not using SELECT * in embedded SQL programs:
- If you change the table structure i.e. adding a field, the program will have to be modified.
- Program can retrieve the columns which it might not use, leading an I/O overhead.
- The chance of an index only scan is lost.
In Embedded SQL programs, it’s generally not recommended to use SELECT *. There are several reasons behind this recommendation:
- Performance Concerns:
- Retrieving all columns when you only need a subset can lead to unnecessary data transfer between the database server and the application, impacting performance.
- It may result in more data being fetched than actually required, leading to increased network traffic and processing overhead.
- Code Maintainability:
- Explicitly specifying the columns you need makes your code more readable and maintainable. It clearly indicates which columns are being used in the program, making it easier for developers to understand and modify the code.
- Avoiding Unintended Consequences:
- If the structure of the database table changes, such as adding or removing columns, using
SELECT *can lead to unintended consequences in your application. Existing code may break if it relies on the assumption of a specific column order or if new columns are introduced.
- If the structure of the database table changes, such as adding or removing columns, using
- Avoiding Data Type Mismatches:
- Using
SELECT *might result in unexpected data type mismatches if the table structure changes, leading to runtime errors.
- Using
- Enhanced Query Optimization:
- When you explicitly specify the columns you need, the database optimizer can make better decisions about query execution plans, potentially improving performance.
To address these concerns, it’s considered good practice to explicitly list the columns you want to retrieve in your SELECT statements in Embedded SQL programs. This provides better control over your queries, improves performance, and enhances code maintainability.