T-SQL Tuesday #151 – T-SQL Coding Standards

This month’s T-SQL Tuesday invitation comes from Mala Mahadevan who asks about T-SQL coding rules and if there are any exceptions to the rules. I had just recently been in a discussion about standards regarding SELECT * so that’s what we’re going to discuss in today’s post. To check out the full invitation, click the T-SQL Tuesday image below:

SELECT * Isn’t Always the Bad Guy

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.

Prove it

Let’s look at an example using the StackOverflow2013 database. We’ll look in the Users table for records where Location is Ohio.

We want to check how SQL Server handles each query so we’ll select the option in SSMS to make sure we get the Actual Execution Plan:

We’ll start with SELECT * and run the query below:

SELECT u.DisplayName
FROM Users u
WHERE Location = 'Ohio'
	AND EXISTS (
		SELECT *
		FROM Posts p
		WHERE  p.OwnerUserId = u.id
		)

How will SQL Server handle the SELECT *?

Even though our query specifies SELECT *, SQL Server used the nonclustered index on OwnerUserid along with the nonclustered index on Location. We can see this if we view the execution plan:

Now let’s get rid of the SELECT * and use SELECT 1 instead. Will we see any difference?

SELECT u.DisplayName
FROM Users u
WHERE Location = 'Ohio'
	AND EXISTS (
		SELECT 1
		FROM Posts p
		WHERE  p.OwnerUserId = u.id
		)

Looks the same to me. We can run the queries together and get a side-by-side look:

Or in other words…

Through Gritted Teeth

I’m still not a big fan of SELECT * here but I think I’m in the minority. And that’s perfectly ok. Since there’s not really a performance difference and it’s usage is based more on individual preference, it’s not a hill I’m willing to die on.

Thanks for reading!