Can select * usage ever be justified?

by

I’m quite happy using * in audit triggers.

In that case it can actually prove a benefit because it will ensure that if additional columns are added to the base table it will raise an error so it cannot be forgotten to deal with this in the audit trigger and/or audit table structure.

(Like dotjoe) I am also happy using it in derived tables and column table expressions. Though I habitually do it the other way round.

WITH t
     AS (SELECT *,
                ROW_NUMBER() OVER (ORDER BY a) AS RN
         FROM   foo)
SELECT a,
       b,
       c,
       RN
FROM   t;

I’m mostly familiar with SQL Server and there at least the optimiser has no problem recognising that only columns a,b,c will be required and the use of * in the inner table expression does not cause any unnecessary overhead retrieving and discarding unneeded columns.

In principle SELECT * ought to be fine in a view as well as it is the final SELECT from the view where it ought to be avoided however in SQL Server this can cause problems as it stores column metadata for views which is not automatically updated when the underlying tables change and the use of * can lead to confusing and incorrect results unless sp_refreshview is run to update this metadata.

More Related Contents:

  1. Whats wrong in this sql query? It throws error [closed]
  2. How do I UPDATE from a SELECT in SQL Server?
  3. SQL update from one Table to another based on a ID match
  4. SQL join: selecting the last records in a one-to-many relationship
  5. COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better? [duplicate]
  6. SQL SELECT WHERE field contains words
  7. What is the difference between using a cross join and putting a comma between the two tables?
  8. Return a value if no record is found
  9. SQL WHERE ID IN (id1, id2, …, idn)
  10. How can I select from list of values in Oracle
  11. Are there any way to execute a query inside the string value (like eval) in PostgreSQL?
  12. Select TOP X (or bottom) percent for numeric values in MySQL
  13. Fastest way to determine if record exists
  14. How to kill a running SELECT statement
  15. How do I select all the columns from a table, plus additional columns like ROWNUM?
  16. DATEDIFF function in Oracle [duplicate]
  17. Best way to do nested case statement logic in SQL Server
  18. How to select multiple rows filled with constants?
  19. How to select only the first rows for each unique value of a column?
  20. How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?
  21. Why postgres returns unordered data in select query, after updation of row?
  22. SQL column reference “id” is ambiguous
  23. SQL SELECT from multiple tables
  24. How to select records without duplicate on just one field in SQL?
  25. how does SELECT TOP works when no order by is specified?
  26. Select distinct from multiple fields using sql
  27. SQL: Two select statements in one query
  28. Check if two “select”s are equivalent
  29. “SELECT *” vs “SELECT ” for performance
  30. Select rows where column value has changed

Leave a Comment