36 Chambers – The Legendary Journeys: Execution to the max!

February 19, 2014

T-SQL Anti-Patterns: SELECT *

Filed under: Database Administration — Kevin Feasel @ 11:05 pm

This next anti-pattern is another pet peeve, though not as bad as ANSI-89 join syntax.  This one is using SELECT * in queries.  For ad hoc queries that you just run to gather diagnostics, SELECT * isn’t necessarily too bad.  As far as line-of-business work goes, however, it’s a bad idea for a couple of reasons.

The first reason is that SELECT * depends upon a specific column order.  Suppose I have a table which has ColumnA, ColumnB, and ColumnD, and I have a query which uses SELECT * to get the column information.  At this point, any structural change will silently break my expectations of that query.  I, as a developer, expect to get back columns ColumnA, ColumnB, and ColumnD, in that order.  But if somebody adds ColumnC in the middle, my SELECT * now returns four columns, and the layer which receives that result set could break, especially if it’s a relatively fragile layer like SSIS.

It’s not just column additions which break things, however.  Deleting a column or even changing column order could cause your query to fail.  But SSMS will never tell you about this error at design time because “SELECT *” is still valid syntax.  In contrast, suppose you had SELECT ColumnA, ColumnB, ColumnD as your clause.  In this case, I can do anything short of dropping one of those columns and the query would still work as expected.  Adding new columns does not alter the result set, nor does revising the table to change column order (for whatever reason you may want to do that).

The second reason this is a bad idea is that it typically returns too many columns.  If you want an optimal query, you want to get back only those columns which are necessary for the result set.  If I only need ColumnA, why pull back excess columns that my consuming layer would just ignore?  Instead, I can just get the one necessary column, resulting in less data passed across the wire between servers.  I can also take advantage of a covering index which includes ColumnA, whereas SELECT * makes covering indexes much less likely (or useful).

Unfortunately, a lot of Object-Relational Mapping solutions tend to want to pull back full table sets.  Then, when some developer is doing a table scan and sending a billion rows across the wire to his app server just to get the one column from one row that he needs, he complains about how slow the database is…

The easy fix for this problem is to name explicitly the columns you want.  This involves more upfront development effort, but honestly, at this point, there are tools which will automatically convert “SELECT *” into “SELECT [each column]” for you.  I use one called SQL Complete, and it does the job just fine.

About these ads

1 Comment »

  1. […] T-SQL anti-pattern is what I call zombie fields.  Like the SELECT * anti-pattern, this one shows laziness on the part of a developer—or, occasionally, a group of people […]

    Pingback by T-SQL Anti-Patterns: Zombie Fields | 36 Chambers - The Legendary Journeys: Execution to the max! — February 20, 2014 @ 10:05 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 94 other followers

%d bloggers like this: