When is Select * ok

Generally speaking SELECT * should be avoided. There are multiple reasons for this, including:

Requesting columns you do not need may force the optimizer to use more objects than needed or pull more data to memory than needed. Save some resources!
It is fragile from a development perspective. There are many cases where this could cause you an issue from procedure results being fed into a temp table, a new column addition causing a query to error with an ambiguous column reference error, or other more obscure errors

In our database projects we actually have code analysis enabled to error when SELECT * is used just for the reasons above.

However, Brad Shultz posted a blog about The Age-Old SELECT * vs. SELECT 1 Debate and it reminded me of something I saw presented once that I thought I would cover.

There is a situation where SELECT * does not really matter, and that is within an EXISTS statement. This is because SQL server ignores your select list when checking for existence of records.

You can test this yourself by running this little test.

If you try to run

You will get the dreadfully expected ….

However, let’s try that within an EXISTS statement

We can see that SQL Server did not even try to qualify the select list. If it had we would have gotten a divide by zero error.

Even though we have just illustrated that SQL does not evaluate the select list within an EXISTS check I still personally avoid using *, and I most definitely would not use 1/0. Just because it has worked all of this time Microsoft could change how it works in the future and select list does get evaluated. We do not want our code blowing up!

For example, someone at the SQL Server user group once told me that their existence checks were taking forever until they added a TOP 1 to them. While I could never confirm any difference in my testing where TOP 1 yielded any different plan or performance during an existence check they were solidly convinced that it made a difference.