SELECT * Everywhere
Using SELECT * by default fetches columns the code never uses, wastes I/O, defeats covering indexes, and couples consumers to schema shape. Name the columns you need and project into DTOs for stable, efficient queries.
SELECT * retrieves every column of every matching row. It is fine for ad-hoc exploration, but using it as the default in application code and views is an anti-pattern. The query asks for more data than the code uses and binds itself to the exact shape of the table.
Why It Happens
It is the shortest thing to type and it always returns whatever exists. Early in a project, when a query reads most columns anyway, the difference seems negligible. ORMs that hydrate full entities encourage it. Copy-pasted examples and quick prototypes carry it into production, where it calcifies.
Why It Hurts
The database reads and ships columns the application discards, wasting I/O, memory, and network bandwidth, especially when wide text or blob columns ride along unnecessarily. It defeats covering indexes: an index that contains the two columns you need cannot satisfy a query that demands all twenty, forcing a table lookup. It couples consumers to schema shape, so adding, removing, or reordering a column can silently break code that reads results by ordinal position or that maps columns by count. In views and INSERT ... SELECT * statements, a schema change can fail or corrupt data unexpectedly.
Warning Signs
SELECT *appears throughout application code rather than only in throwaway scripts.- Result sets carry columns the surrounding code never references.
- Code reads columns by numeric index, making it fragile to reordering.
- Adding a column to a table breaks unrelated queries or views.
Better Alternatives
Name the columns you need. Explicit projection documents intent, shrinks payloads, and unlocks covering indexes that can answer a query from the index alone. For application boundaries, project into DTOs or typed records that list exactly the fields consumed. Where an ORM hydrates entities, use partial selects or projections for read-heavy paths. Views should enumerate columns so downstream consumers have a stable contract.
How to Refactor Out of It
Start with the hottest and widest queries, where the payoff is largest. Replace SELECT * with an explicit column list matching what the consumer uses, and check whether a covering index now applies. Add a lint rule or code-review check that flags SELECT * outside of approved exploratory contexts. For views and stored procedures, expand the star into named columns to stabilize the interface. Measure: reduced bytes returned and improved index usage are the signals that the change paid off.