Difference between select * & select
-
Hi Experts,
I would like to know the difference between
'select * from emp' & 'select eno,ename,salary from emp'.
Inputs are welcome !
Regards
Karthik
karthik
-
"SELECT *" will return all columns from the table, while the other query will only return the "eno", "ename", and "salary" columns, no matter how many columns the "emp" table has in it.
It's typically a better practice to use the latter method where possible, although you'll be hard-pressed to find even a veteran who doesn't use the former on occasion for ad hoc stuff, troubleshooting, etc.
-
Technically - the select * syntax is functionally similar to having TWO queries. (meaning the execution plan has to do two things instead of one). The first one has to query the syscolumns table to figure out what columns are in that user table, and the next is the perform the actual query with the column names specified.
In most cases though, the effort involved in the first step is so very low compared to the secondary query, it ultimately doesn't make a lot of difference performance-wise.
There are cases where it's rather ill-advised, because the select * will tend to make it easy to "screw up". For example:
1. Insert <table> select * from <othertable>
works fine until either table is modified (fields wise), and then let the odd errors begin....
2. a function returning a select * from <table> might not update its column definitions if you add something to that table (again - ddl-wise).
In most cases - it's better form (and better for you support-wise) to spend the extra time, and actually write the field names out. Of course - rules are meant to be ignored when you're in a hurry, so like David pointed out, you will see a LOT of select * thrown around in examples, in stuff built just to test, etc...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
And, selecting more columns than you need may also cause high speed INDEX SEEKs to change to mere INDEX SCANs, not to methion the additional network traffic that selecting more than need does.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
..which in turn means that there *IS* a difference performance-wise between being lazy with 'select *' or spelling out the actual columns called for...
/Kenneth
-
Well, yes - anything that pulls more data than is needed is going to run slower than something that pulls just what is needed (and if not slower to pull - slow to PUSH to the client, take up more resources, etc...).
That's the other aspect of sloppy coding that comes in - essentially doing things you don't need to do.
Good point.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
AND when doing "select *" you may be bringing more data than you intended if someone/somthind adds a column to the table(s) you are "select"ing from
Cheers,
* Noel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply