sql - Does the number of columns returned affect the speed of a query?

Question

If I have two queries

SELECT Id, Forename, Surname
FROM Person
WHERE PersonName Like(‘%frank%’)

And

SELECT *
FROM Person
WHERE PersonName Like(‘%frank%’)

Which query will run faster? Is the where clause / table joining the biggest factor, or the number of columns returned?

I’m asking because I’m building a series of objects that map to database tables. Typically each object will have at least these three methods:

Select – Selects everything

List – Selects enough that is can be used to populate a dropdown list

Search – Selects everything that is viewable in the results, typically about 6 or so columns.

If each query returned exactly the same set of columns the maintenance and testing of the code should be more trivial. The database is unlikely to get above 50,000 rows in any given table, so if the difference in performance is small then I’ll take the saving in development time. If the performance is going to drop through the floor, then I’ll work at in from a different angle.

So, for ease of development, is SELECT * sensible, or is it naive?

Answer 1

You better avoid SELECT *

• It leads to confusion when you change the table layout.
• It selects unneeded columns, and your data packets get larger.
• The columns can get duplicate names, which is also not good for some applications
• If all the columns you need are covered by an index, SELECT columns will only use this index, while SELECT * will need to visit the table records to get the values you don't need. Also bad for performance.