When Is Using 'SELECT *' in SQL Actually Justifiable?

The Merits of "SELECT *"

While "SELECT *" may often be discouraged in production code, there are indeed instances where it can be justifiable.

Audit Triggers

Audit triggers can benefit from using "SELECT ". When additional columns are added to the base table, using "SELECT " ensures that an error is raised, preventing the audit trigger and table structure from being overlooked.

Derived Tables

"SELECT " can be useful in derived tables and column table expressions. By using "," all columns are included, ensuring that if new columns are added to the base table, they will automatically be included in the derived table or CTE.

SQL Server Optimization

In SQL Server, the optimizer recognizes that only specific columns are needed in a query, even if "SELECT *" is used in a table expression. Therefore, there is no additional overhead in retrieving and discarding unnecessary columns.

Views (with Caution)

In principle, "SELECT " can be acceptable in views, as it is the final SELECT from the view where it should be avoided. However, caution is advised in SQL Server, as column metadata for views is not automatically updated when the underlying table changes. Using "" can lead to incorrect results unless the view is refreshed using sp_refreshview.

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