When Is Using `SELECT *` in SQL Queries Acceptable?

Can the Use of SELECT * Ever Be Justified?

Selecting all columns (SELECT *) in SQL queries has long been considered a bad practice for various performance and security reasons. However, there are exceptions where its use can be justified.

One such scenario is in audit triggers. By using SELECT * in an audit trigger, any changes to the underlying table structure will trigger an error, alerting developers to update the audit trigger or table accordingly. This helps ensure that audit trails remain complete and accurate.

Another acceptable use case for SELECT is in derived tables and column table expressions. For example, including SELECT in a nested table can allow for efficient retrieval of necessary columns without negatively impacting performance. However, it should be noted that this requires the optimizer to correctly identify the required columns, which may not always be the case in all databases.

While SELECT can also be used in views, caution should be exercised as it can lead to metadata issues in SQL Server. Specifically, the lack of automatic metadata updates when the underlying tables change can lead to inaccurate results. Therefore, if SELECT is used in a view, it is crucial to manually update the metadata using sp_refreshview to prevent potential errors.

It's important to emphasize that the use of SELECT should still be carefully considered and avoided in production code unless necessary. By understanding the exceptions outlined above, developers can make informed decisions regarding when SELECT can be justifiably employed.

The above is the detailed content of When Is Using `SELECT *` in SQL Queries Acceptable?. For more information, please follow other related articles on the PHP Chinese website!