"SELECT * FROM..." VS "SELECT ID FROM..." Performance [duplicate]

Question

As someone who is newer to many things SQL as I don't use it much, I'm sure there is an answer to this question out there, but I don't know what to search for to find it, so I apologize.

Question: if I had a bunch of rows in a database with many columns but only need to get back the IDs which is faster or are they the same speed?

SELECT * FROM...

vs

SELECT ID FROM...

Answer 1

You asked about performance in particular vs. all the other reasons to avoid SELECT *: so it is performance to which I will limit my answer.

On my system, SQL Profiler initially indicated less CPU overhead for the ID-only query, but with the small # or rows involved, each query took the same amount of time.

I think really this was only due to the ID-only query being run first, though. On re-run (in opposite order), they took equally little CPU overhead.

Here is the view of things in SQL Profiler:

With extremely high column and row counts, extremely wide rows, there may be a perceptible difference in the database engine, but nothing glaring here.

Where you will really see the difference is in sending the result set back across the network! The ID-only result set will typically be much smaller of course - i.e. less to send back.

Answer 2

Never use * to return all columns in a table–it’s lazy. You should only extract the data you need. so-> select field from is more faster