Press "Enter" to skip to content

When SELECT * Doesn’t

Chad Callihan protects the reputation of SELECT *:

There are plenty of scenarios where using SELECT * can be an issue. Using SELECT * with EXISTS isn’t one of them.

When using EXISTS and SELECT * together, SQL Server is smart enough to realize what you’re doing and knows you don’t care about what’s in the SELECT.

Read on for an example. I’ve trained myself (been trained?) still to use SELECT 1. The reason is, I know SELECT * works exactly the same way but the benefit of using SELECT 1 is that doing this consistently allows you to do a search for SELECT * in your code base to find actual perpetrators (people writing queries expecting to return the entire result set and which may be susceptible to performance problems or future maintainability problems). Using SELECT 1 in the EXISTS clause means you get fewer false positives in that search as a result.

That said, Joe Celko chimes in to provide some of the history behind SELECT * as the convention for references in the EXISTS clause.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.