Home > SQL Challenge > Another reason why we should not write SELECT *

Another reason why we should not write SELECT *

There are so many warnings from experienced SQL developers about not to write SELECT * in your query.

I recently got an email from our employee incentive payout team about a SQL view that behaved strangely. They’ve noticed that a date time field is showing employee login ids.

The SQL view is simply as this:

CREATE VIEW V_myView
AS

SELECT * FROM T_myTable

It turned out that the base table T_myTable got some modifications. New columns were added, and existing columns got switched around.

This caused the SQL view to behave weird.

To solve the problem, we can simply ALTER the view:

ALTER VIEW V_myView
AS

SELECT * FROM T_myTable

Or, a better solution is to get rid of the SELECT *, and spell out the column names explicitly. Future modification of the base table will not cause any problems in the SQL view.

ALTER VIEW V_myView
AS

SELECT  col1, col2, …. FROM  T_myTable

Categories: SQL Challenge
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: