Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / SQL
Tip/Trick

The Evil That is "Select *"

Rate me:
Please Sign up or sign in to vote.
4.90/5 (36 votes)
10 Aug 2011CPOL2 min read 91.8K   9   24
Just say no to SELECT * in your SQL
There I was working on a web page, when I decided that I needed to add some new columns to an existing table. So I did. Forty-five minutes later, or system admin calls, and says a bunch of people are having problems wiht the web sight (and a related desktop app). A co-worker asked if anyone had added new columns to any of the tables, and I raised my hand.

It seems that SOMEONE had used SELECT * in one or more SQL queries into the affected table, and the act of adding a column completely hosed up the code as a result of my adding the new columns.

Nobody here really knows where the offending code is, or even if it might be in a stored procedure, but the fastest way out of the mess was to remove the columns from the existing table, and create a new table to hold them.

This is PRECISELY how spaghetti code is propagated throughout a project, along with apparently pointless tables in the database.

For the record, we don't know exactly why we're having problems because we don't have time to find out (because we wouldn't fix it if we did find out). We suspect someone wrote an INSERT or UPDATE statement that tries to do a SELECT * to move some column data around between tables. I can think of only one word to describe this, and the first syllable is "cluster".

The tip:

DO NOT USE SELECT * IN YOUR SQL QUERIES - FOR ANY REASON.

EDIT ================

For those of you who felt the need to tell me that the word "cluster" has two syllables - I already know that, and I have no idea where you people got the idea that I couldn't count. That particular statement is part of my endearing yet sardonic wit.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Paddedwall Software
United States United States
I've been paid as a programmer since 1982 with experience in Pascal, and C++ (both self-taught), and began writing Windows programs in 1991 using Visual C++ and MFC. In the 2nd half of 2007, I started writing C# Windows Forms and ASP.Net applications, and have since done WPF, Silverlight, WCF, web services, and Windows services.

My weakest point is that my moments of clarity are too brief to hold a meaningful conversation that requires more than 30 seconds to complete. Thankfully, grunts of agreement are all that is required to conduct most discussions without committing to any particular belief system.

Comments and Discussions

 
GeneralMy vote of 2 Pin
pt140115-Jul-12 9:21
pt140115-Jul-12 9:21 
GeneralRe: Yes, there is a way. I just can't recall that now... I have... Pin
giadich2-Aug-11 7:59
giadich2-Aug-11 7:59 
GeneralRe: Yes, it's kind of implied that it is "select * where (some l... Pin
BobJanova2-Aug-11 6:57
BobJanova2-Aug-11 6:57 
GeneralReason for my vote of 3 John's later post indicates that SEL... Pin
dmjm-h29-Aug-11 5:22
dmjm-h29-Aug-11 5:22 
GeneralReason for my vote of 5 I couldn’t agree with you more, and ... Pin
S Douglas25-Aug-11 13:31
professionalS Douglas25-Aug-11 13:31 
GeneralReason for my vote of 4 Not entirely true. E.g. select top 1... Pin
Armando de la Torre4-Aug-11 6:40
Armando de la Torre4-Aug-11 6:40 
GeneralThis article is right but doesn't explain why... There are a... Pin
msmits2-Aug-11 2:26
msmits2-Aug-11 2:26 
GeneralReason for my vote of 5 Everybody learns this the hard way Pin
Shahar Eldad1-Aug-11 18:56
Shahar Eldad1-Aug-11 18:56 
GeneralReason for my vote of 5 I agree, except "cluster is 2 syllab... Pin
Dr.Walt Fair, PE1-Aug-11 18:48
professionalDr.Walt Fair, PE1-Aug-11 18:48 
GeneralReason for my vote of 4 Good read, interesting perspectives,... Pin
BrianBissell1-Aug-11 16:44
BrianBissell1-Aug-11 16:44 
GeneralAlso always list columns on an insert INSERT INTO mytab... Pin
Corporal Agarn28-Jul-11 3:53
professionalCorporal Agarn28-Jul-11 3:53 
General'Cluster' is two syllables. Select * is okay when you reall... Pin
BobJanova26-Jul-11 0:26
BobJanova26-Jul-11 0:26 
GeneralRe: Select * isn't okay if you only want the whole table schema.... Pin
giadich2-Aug-11 6:39
giadich2-Aug-11 6:39 
GeneralRe: I *know* "cluster" is two syllables. It's part of my endeari... Pin
#realJSOP10-Aug-11 11:19
mve#realJSOP10-Aug-11 11:19 
GeneralReason for my vote of 5 It is the evil death* and should alw... Pin
Nagy Vilmos25-Jul-11 19:54
professionalNagy Vilmos25-Jul-11 19:54 
GeneralReason for my vote of 5 Totally agree with John! Using SELEC... Pin
DrABELL25-Jul-11 17:15
DrABELL25-Jul-11 17:15 
GeneralJust to be Devil's advocate here - well, not quite - doing a... Pin
Chris Maunder25-Jul-11 15:19
cofounderChris Maunder25-Jul-11 15:19 
GeneralRe: Very wise. I think you really hit the underlying problem her... Pin
BrianBissell1-Aug-11 16:37
BrianBissell1-Aug-11 16:37 
GeneralRe: We didn't have the time to figure out exactly why the querie... Pin
#realJSOP2-Aug-11 1:34
mve#realJSOP2-Aug-11 1:34 
GeneralThere is a script floating aroun that will search the procs ... Pin
Mycroft Holmes25-Jul-11 13:10
professionalMycroft Holmes25-Jul-11 13:10 
GeneralRe: This is the one I "borrowed" from the net. Searches SPs and... Pin
Steve Echols25-Jul-11 16:02
Steve Echols25-Jul-11 16:02 
GeneralReason for my vote of 5 So true so many debug sessions that... Pin
Simon_Whale25-Jul-11 11:49
Simon_Whale25-Jul-11 11:49 
GeneralReason for my vote of 5 Agree, causes much confusion and ext... Pin
Wendelius25-Jul-11 10:52
mentorWendelius25-Jul-11 10:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.