Do not use “Select *”

How many times have you seen code where a “select *” is being used?  Chances are that if you have been in the industry for some time, you must have seen code that has a “select *” in it (either application code or DB code).  It is not a good choice for a variety of reasons –

Reason #1: Performance:

Consider these 2 queries:

Query 1: select * from tableA where colx = 5

vs say using

Query 2: Select Col1, Col2 from tableA where colx = 5

If there was a covered index on col5, col1 and col2, then in the case of the second query, it can just retrieve all the data from the index itself while the select * query will need to go to the data pages as well.  Since the intention is only to return Col1 and Col2, specifically mention which columns you need.

In addition to this performance issue, the un-necessary data transfer is another performance issue.  Just get the data that you need…nothing more, nothing less.

Reason #2: Plain simple code maintenance issue:

Say you have inherited a stored procedure which defines a table variable or a temp table (SQL Server lingo) and then does an “insert into #temp select * from…”.  If you add a new column that changes that then makes that select * to return an extra column, then this insert statement will fail.  In this case also, be explicit – mention which columns you are going to be inserting and where those columns are coming from.

Reason # 3: When using it in an EXISTS/NOT EXISTS condition:

Read Conor Cunningham’s post here.  You will see that when using an EXISTS/NOT EXISTS  condition, there is an advantage to use a “select 1” vs using a “select *”.  The reasons are well explained in Connor’s post.

Reason # 4: Column Dependency in Oracle 11g:

In Oracle 11g, the dependency mechanism has been extended down to the column level…so, what does that mean in relation to this post?  This means that if you are using explicit column names in your code, then the chances of the code getting invalidated and recompiled is less.

So, run through your code as a sanity check and see where all you have a “select *” in the code and then work on fixing it.