When Select * Doesn’t Matter

Pavlovian

There’s a strange response to some things in the SQL Server community that borders on religious fervor. I once worked with someone who chastised me for having SELECT * in some places in the Blitz scripts. It was odd and awkward.

Odd because this person was the most Senior DBA in the company, and awkward because they didn’t believe me that it didn’t matter in some cases.

People care about SELECT * for many valid reasons, but context is everything.

One For The Money

The first place it doesn’t matter is EXISTS. Take this index and this query:

The relevant part of the query plan looks like this:

What’s My Name

We do a seek into the index we created on the two columns in our WHERE clause. We didn’t have to go back to the clustered index for everything else in the table.

That’s easy enough to prove if we only run the subquery — we have to change it a little bit, but the plan tells us what we need.

This time we do need the clustered index:

Who We Be

You can even change it to something that would normally throw an error:

Two For Completeness

Another example is in derived tables, joins, and apply.

Take these two queries. The first one only selects columns in our nonclustered index (same as above).

The second one actually does a SELECT *.

The first query only touches our narrow nonclustered index:

Blackout

The second query does a key lookup, because we really do select everything.

Party Up

Trash Pile

I know, you’ve been well-conditioned to freak out about certain things. I’m here to help.

Not every SELECT * needs to be served a stake through the heart and beheading.

Thanks for reading!

5 thoughts on “When Select * Doesn’t Matter”

  1. Erik,

    Thanks for the edumacation! I are gettin’ smarter by the minute!

    Although you pointed out cases where SELECT * doesn’t matter, but I’m not sure if that means it’s ever a good idea.

    Is SELECT * actually advantageous in any of these (or other) examples? And if not, shouldn’t you avoid using it just to make it easier for your colleagues (or future you) to review your scripts without having to reevaluate whether or not it’s sacrilegious?

Leave a Reply

Your email address will not be published. Required fields are marked *