select * vs select column

by

There are several reasons you should never (never ever) use SELECT * in production code:

  • since you’re not giving your database any hints as to what you want, it will first need to check the table’s definition in order to determine the columns on that table. That lookup will cost some time – not much in a single query – but it adds up over time

  • if you need only 2/3 of the columns, you’re selecting 1/3 too much data which needs to be retrieving from disk and sent across the network

  • if you start to rely on certain aspects of the data, e.g. the order of the columns returned, you could get a nasty surprise once the table is reorganized and new columns are added (or existing ones removed)

  • in SQL Server (not sure about other databases), if you need a subset of columns, there’s always a chance a non-clustered index might be covering that request (contain all columns needed). With a SELECT *, you’re giving up on that possibility right from the get-go. In this particular case, the data would be retrieved from the index pages (if those contain all the necessary columns) and thus disk I/O and memory overhead would be much less compared to doing a SELECT *.... query.

Yes, it takes a bit more typing initially (tools like SQL Prompt for SQL Server will even help you there) – but this is really one case where there’s a rule without any exception: do not ever use SELECT * in your production code. EVER.

More Related Contents:

  1. How does database indexing work? [closed]
  2. What is the best way to paginate results in SQL Server
  3. Best way to select random rows PostgreSQL
  4. Best way to test if a row exists in a MySQL table
  5. Condition within JOIN or WHERE
  6. INNER JOIN vs LEFT JOIN performance in SQL Server
  7. Equals(=) vs. LIKE
  8. Subqueries vs joins
  9. Optimise PostgreSQL for fast testing
  10. Does the order of where clauses matter in SQL?
  11. Fastest way to remove non-numeric characters from a VARCHAR in SQL Server
  12. SQL ‘like’ vs ‘=’ performance
  13. Counting DISTINCT over multiple columns
  14. How many rows in a database are TOO MANY?
  15. Is a JOIN faster than a WHERE?
  16. What’s the best to check if item exist or not: Select Count(ID)OR Exist(…)?
  17. Fastest way to determine if record exists
  18. SQL count(*) performance
  19. Why is a UDF so much slower than a subquery?
  20. SQL primary key: integer vs varchar
  21. SQL performance: WHERE vs WHERE(ROW_NUMBER)
  22. Subquery v/s inner join in sql server
  23. SQL Server SELECT LAST N Rows
  24. Improving performance of cluster index GUID primary key
  25. When using GETDATE() in many places, is it better to use a variable?
  26. Two single-column indexes vs one two-column index in MySQL?
  27. How do you interpret a query’s explain plan?
  28. IN vs OR of Oracle, which faster?
  29. Oracle , != , ^= operators
  30. sql query joins multiple tables – too slow (8 tables)

Leave a Comment