What is the disadvantage of using select * from table name??
-
actually i know it is always better for good performance to specify the column name in sql query rather than issuing select * from table name. please tell me in detail all the reason that why we should not issue select * from table name. Thanks
tbhattacharjee
-
actually i know it is always better for good performance to specify the column name in sql query rather than issuing select * from table name. please tell me in detail all the reason that why we should not issue select * from table name. Thanks
tbhattacharjee
-
actually i know it is always better for good performance to specify the column name in sql query rather than issuing select * from table name. please tell me in detail all the reason that why we should not issue select * from table name. Thanks
tbhattacharjee
aside from the other response, if you have a database that gets updated or changed fairly often(like mine, always adding on new features), and their data is added to existing tables, depending on where the column is added, removed (old, unused, found a better way, etc..), or renamed(doesnt happen much) it could break your code, if you're expecting an int in col 3 and i add a column to the begining of the table it could change the datatype in column 3 and you're code then wouldnt read it correctly. however if you list out every column you want, in selects and inserts you wont have this problem unless a column is renamed(rare) or removed, and its easy enough to do a search through the code and a sql script file to find where its used if you're going to remove it.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
-
actually i know it is always better for good performance to specify the column name in sql query rather than issuing select * from table name. please tell me in detail all the reason that why we should not issue select * from table name. Thanks
tbhattacharjee
Example from the work force: We have a vendor supplied system that has too much data in it; we only want to keep 2 years worth of data online and archive anything over 2 years old. The archiving system used a statement similiar to: Insert into Archive_Table select * from Source_Table An in-house system was created to copy the data from production to archive, however, over time, the columns in the vendor supplied system chnaged: more were added, order changed etc. The archiving system failed because the destination table was not updated to match the source table and the insert statement was expected the SAME number of columns, order and type of columns in the destination table. Hope that helps. Tim
-
actually i know it is always better for good performance to specify the column name in sql query rather than issuing select * from table name. please tell me in detail all the reason that why we should not issue select * from table name. Thanks
tbhattacharjee
Because all that data has to come across the network to you, increasing network load needlessly. On the other hand, if you really want all the columns, then using * is OK.
-
actually i know it is always better for good performance to specify the column name in sql query rather than issuing select * from table name. please tell me in detail all the reason that why we should not issue select * from table name. Thanks
tbhattacharjee
If you explicitly specify the columns in a SELECT statement, they are guaranteed to be returned in the order you specify. Consequently, you may safely identify the columns as theReader(0), theReader(1), etc. rather than having to have the system look up the column names on every theReader("customerName") access. If, for whatever reason, the expected columns don't exist, it's probably better to have the problem caught in the SELECT statement than to throw an exception when the non-existent data is accessed. If your code is genuinely interested in getting all the columns that are or ever will be in the database, including any that may be added in future, then SELECT * is appropriate. This may be the case, for example, in a database viewer (though unless a naming convention is used to indicate fields that should be regarded as confidential, such an approach could be dangerous even there). If you're only interested in a few fields, even if the fields of interest are at present the only ones in the database, you should specify explicitly the fields of interest.