Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 152, comments - 2306, trackbacks - 64

My Links

SQLTeam.com Links

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL
<< Becoming a Better Developer, plus a SQL Crime Drama! | Home | More on GROUP BY; Examining SUM(Distinct) >>

SELECT * FROM TABLE -- except for these columns

A occasional question seen in the forums, which was just recently asked today, is:

"I know I can use

SELECT *
FROM table

to get all of the columns from a table, but is there a way to write

SELECT *  minus columnX
FROM Table

to get all of the columns except for certain ones?"

Now, my goal isn't to debate whether not "SELECT *" is bad or good or should be used or not.  The fact of the matter is, people use it all the time because it is quicker and shorter than typing out all of the column names. People are lazy, right?  (myself included.)  And these lazy people often would like to return all of the columns from a table except for one or two without typing them all out.  So, why isn't this concept allowed? Would it make any sense?

Here's my usual response:

First, the answer is no, SQL does not support that.  You must specify what you want.

Second, if you ask me "why not? It would be great!", let me ask you this in return:

"If SQL did support that syntax, and you executed

SELECT * minus ColumnX
FROM Table

but columnX did not exist in that table, what should happen?"

I think it's kind of an interesting thing to think about.    Should an error occur?  Or should it just happily return the results, since we didn't want ColumnX anyway?  Maybe it should issue a warning? I really don't know ... Would we all ever be able to agree on a definitive, logical way to handle this?

Here's another way to think about it:  What if there were a command called "DontExec" that simply didn't execute the stored procedure specified.  What should happen if you call DontExec on a stored procedure that doesn't exist? 

That sounds kind of silly, I know, but it is the same basic thing to consider as if you had a "* minus Column" option in a SELECT statement.  Shorter to type? Sure.  Does it make any sense? Not really.

By the way -- the third part of my response is usually this:  the easiest solution is to simply use SSMS, QA, or EM to assist you in building your column list by using the scripting features or the query builder if you don't want to type it all out. 

(UPDATE: As Denis points out in the comments, it's actually even easier.  Now you really have no excuse to use *. )

Print | posted on Thursday, July 26, 2007 3:36 PM

Feedback

# re: SELECT * FROM TABLE -- except for these columns

IMO, it should throw an error
7/26/2007 5:06 PM | Mladen

# re: SELECT * FROM TABLE -- except for these columns

Jeff, no need to script, just drag the columns folder into the query window, I wote about this trick almost 2 years ago
works in QA and SSMS

http://sqlservercode.blogspot.com/2005/09/query-analyzer-trick.html
7/26/2007 9:01 PM | Denis the SQL Menace

# re: SELECT * FROM TABLE -- except for these columns

Nice! Thanks, Denis!
7/26/2007 9:40 PM | Jeff

# re: SELECT * FROM TABLE -- except for these columns

i thought everone knew about that... :))
7/27/2007 9:27 AM | Mladen

# re: SELECT * FROM TABLE -- except for these columns

Mmmmm since that was new, how about this one?

http://sqlservercode.blogspot.com/2007/01/increase-your-productivity-with-query.html

no one knew this one at my job

7/27/2007 9:55 AM | Denis the SQL Menace

# re: SELECT * FROM TABLE -- except for these columns

Frankly, the only time someone should be selecting * is for some adhoc data discovery; in which case, the inclusion of some extraneous columns shouldn't matter.

Production level code should explicitly list the columns to be returned. The principal reason for this is to insure against schema changes. When a field is added to a table, select * will return it -- sometimes with unexpected results.

With the myriad of IDEs available for database developers, including the entire list of columns should not be a burden
7/27/2007 9:57 AM | Bob

# re: SELECT * FROM TABLE -- except for these columns

Thanks, Bob, I think we all agree with that. The point of the post wasn't to debate the merits of SELECT * from a table, as I tried to indicate.
7/27/2007 10:30 AM | Jeff

# re: SELECT * FROM TABLE -- except for these columns

If you run a query like that often you should create a view that gives you just what you want. Otherwise specify what you want when you type the query. You can even create sql files with the queries that you use often. But I don't see a compelling reason for the except clause.
7/27/2007 11:20 AM | Brian

# re: SELECT * FROM TABLE -- except for these columns

I use "Denis the SQL Menace"'s process to avoid the "SELECT *" : in Mgmt Studio (same applies to Query Analyzer) highlight the table you're doing the SELECT on, press CTLR+D (to put the results in grid-mode), then press ALT+F1 (to get the table metadata) then highlight the column_name column in the 2nd table & you can paste it into your query window & do the grunt work of replacing newlines with ", " to build the column list.

I also remap CTRL+1 keystroke to sp_helptext so I can quickly view the contents of views/functions/sprocs w/out having to navigate the object explorer tree.
7/27/2007 2:27 PM | jeffro

# re: SELECT * FROM TABLE -- except for these columns

IT IS VERY GOOD
7/28/2007 11:04 AM | vijay

# re: SELECT * FROM TABLE -- except for these columns

Good Post !
But , **SELECT * FROM TABLE** will leads to performance issue. be sure to use the column name to fetch the information from the DB.
8/15/2007 6:44 AM | Anand Muthu

# re: SELECT * FROM TABLE -- except for these columns

i have doubt.. how to selest a particular table and that particular table how to selest a particular data..
12/21/2007 2:13 PM | chandra ganapathi

# re: SELECT * FROM TABLE -- except for these columns

is there's a way to select a table using a variable? I trie SELECT * FROM $var
7/17/2008 9:48 PM | Web designer

# re: SELECT * FROM TABLE -- except for these columns

I don't think the question is misguided at all. Incorporating "except" logic into the SELECT statement would dramatically improve readability.

Suppose I have 26 columns, each named after a letter in the alphabet. I want all but one of them. This could be important if, say, one columns is freeform text that takes up a lot of space.

I could type:
select a,b,c,d,e,f,g,h,i,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z
But which field did I leave out? Not obvious, is it?
Wouldn't it be clearer o say this?
select * except j
Readable code is much easier to maintain.

I appreciate the suggestion of using a tool to autogenerate the query, but such tools aren't available in every environment, and won't produce code as readable as "select * except j".
10/10/2008 5:03 PM | Paul

# re: SELECT * FROM TABLE -- except for these columns

Well, your reason isn't a strong reason


"If SQL did support that syntax, and you executed

SELECT * minus ColumnX
FROM Table

but columnX did not exist in that table, what should happen?"

Error (Since if you select an non-existing column, it pops an error)


Denis' comment is good, but for simplicity of language,
I still personally support to have syntax like select *, -ColumnAIdontWant from blah)
10/20/2008 7:36 AM | Acta

# re: SELECT * FROM TABLE -- except for these columns

I agree with others that your argument for not supporting this is weak.
1/8/2009 12:28 AM | Josh

# re: SELECT * FROM TABLE -- except for these columns

IMO, it should not throw an exception. It's analogous to C# or Java ArrayList. If you have a list, and you ask, remove(x), but x is not in the list, it does nothing. Your only goal is that x not be in the list. If it wasn't in the list to begin with, then throwing an exception is just annoying because now you first have to check, if (list.contains(x)) list.remove(x), which is just dumb.

C# kind of screwed up Dictionary<U,T> this way, that if you say Object y = dict[x], but x is not in the dictionary, it will throw an exception, so then you end up using dict.TryGetValue(x)... lame. But Hashtable ht[x] returns null like a good boy.

In the projects my team codes, we do a lot of stuff with business objects, and have API abstraction layers that return business objects. So, we use "select *" most of the time because most of the data stored in the tables were from user input, and this data has to be in the business object.

re: Syntax
I think "minus" is fine, as long as you could do:
select *, minus(COL_X, COL_Y) from table ...
just as long as you don't have to type minus infront of every column. Or maybe even:
select *, -(COL_X, COL_Y) from table ...



1/31/2009 3:38 PM | rkippen

# re: SELECT * FROM TABLE -- except for these columns

rkippen -- close, but not quite. you are forgetting something. in your examples, x is data, it is not code! x is not an object in your application, or a table or column in your database, it is simply a piece of data. That is the key difference!
2/2/2009 3:12 PM | Jeff S

# re: SELECT * FROM TABLE -- except for these columns

You could possibly create a subselection of column names from an information_schema query. I've done that before.
3/16/2009 3:48 PM | Mark

# re: SELECT * FROM TABLE -- except for these columns

Well, the most annoying part about not being able to do this is in INSTEAD OF triggers.
Sure we don't want the SELECT * in the usual code, but in an INSTEAD OF trigger MSSQL forces you to recreate your original order since it totally lacks BEFORE triggers.

So it's VERY annoying to have to update that trigger every time the table needs updating. It would be extremely handy to just be able to say
INSERT INTO x
SELECT * FROM inserted MINUS (keyColumn)

After all, keyColumn that's autoID will prevent that from working if it's included, forcing you to fully list every field, which has to be maintained for something that ya really should have a before trigger for.
4/21/2009 10:37 AM | Kevin

Post Comment

Title  
Name  
Email
Url
Comment   
Please add 5 and 3 and type the answer here:

Powered by:

Copyright © Jeff Smith