Click here to Skip to main content
11,632,778 members (86,987 online)
Click here to Skip to main content

Tagged as

The Evil That is "Select *"

, 10 Aug 2011 CPOL 67.6K 9
Rate this:
Please Sign up or sign in to vote.
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)

Share

About the Author

John Simmons / outlaw programmer
Software Developer (Senior)
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.

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 2 Pin
pt140115-Jul-12 9:21
memberpt140115-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
membergiadich2-Aug-11 7:59 
GeneralRe: Yes, it's kind of implied that it is "select * where (some l... Pin
BobJanova2-Aug-11 6:57
memberBobJanova2-Aug-11 6:57 
GeneralReason for my vote of 3 John's later post indicates that SEL... Pin
dmjm-h29-Aug-11 5:22
memberdmjm-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
memberS 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
memberArmando 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
membermsmits2-Aug-11 2:26 
GeneralReason for my vote of 5 Everybody learns this the hard way Pin
Shahar Eldad1-Aug-11 18:56
memberShahar Eldad1-Aug-11 18:56 
GeneralReason for my vote of 5 I agree, except "cluster is 2 syllab... Pin
Walt Fair, Jr.1-Aug-11 18:48
subeditorWalt Fair, Jr.1-Aug-11 18:48 
GeneralReason for my vote of 4 Good read, interesting perspectives,... Pin
BrianBissell1-Aug-11 16:44
memberBrianBissell1-Aug-11 16:44 
GeneralAlso always list columns on an insert INSERT INTO mytab... Pin
djj5528-Jul-11 3:53
memberdjj5528-Jul-11 3:53 
General'Cluster' is two syllables. Select * is okay when you reall... Pin
BobJanova26-Jul-11 0:26
memberBobJanova26-Jul-11 0:26 
GeneralRe: Select * isn't okay if you only want the whole table schema.... Pin
giadich2-Aug-11 6:39
membergiadich2-Aug-11 6:39 
GeneralRe: I *know* "cluster" is two syllables. It's part of my endeari... Pin
John Simmons / outlaw programmer10-Aug-11 11:19
mvpJohn Simmons / outlaw programmer10-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
memberNagy Vilmos25-Jul-11 19:54 
GeneralReason for my vote of 5 Totally agree with John! Using SELEC... Pin
DrABELL25-Jul-11 17:15
memberDrABELL25-Jul-11 17:15 
GeneralJust to be Devil's advocate here - well, not quite - doing a... Pin
Chris Maunder25-Jul-11 15:19
adminChris Maunder25-Jul-11 15:19 
GeneralRe: Very wise. I think you really hit the underlying problem her... Pin
BrianBissell1-Aug-11 16:37
memberBrianBissell1-Aug-11 16:37 
GeneralRe: We didn't have the time to figure out exactly why the querie... Pin
John Simmons / outlaw programmer2-Aug-11 1:34
mvpJohn Simmons / outlaw programmer2-Aug-11 1:34 
GeneralThere is a script floating aroun that will search the procs ... Pin
Mycroft Holmes25-Jul-11 13:10
memberMycroft 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
memberSteve 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
memberSimon_Whale25-Jul-11 11:49 
GeneralReason for my vote of 5 Agree, causes much confusion and ext... Pin
Mika Wendelius25-Jul-11 10:52
mvpMika Wendelius25-Jul-11 10:52 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150728.1 | Last Updated 10 Aug 2011
Article Copyright 2011 by John Simmons / outlaw programmer
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid