In this post, I am going to write about one of the most common mistake by Database Developers.
“SELECT * ” (SELECT all columns) to be good or bad in a Database System.
I discuss the pros and cons of “SELECT * ” and what should be our best practice with SELECT all columns.
Don’t go with ” SELECT * “:
- When you are using ” SELECT * ” at that time you are selecting more columns from the database and some of this column might not be used by your application.
This will create extra cost and load on database system and more data travel across the network.
- When you are using JOIN between two or more tables and in this query you use “SELECT * “, now imagine about two columns with the same name. This will return more column than you actually needed and will create a binding problem.
- If you are using “SELECT * “, then you may get all columns in arbitrary sequence and if you specified column name, then it will be returned in the specified order and you can also refer to this column by numerical index. If such a code exists in an application which requires all columns in a predefined order, then you have to specify all columns name in the correct order.
- When you are using “SELECT *” then you will face performance issue because this is not advisable to apply index on all columns of the table.
- When you are using “SELECT *” then you require to create documentation for which column is returning and using by an application otherwise this will create confusion.
- When you are using “SELECT * ” and in the future someone add one new column with TEXT data type, now you can imagine performance and other errors.
- If you really require to select all columns, then also I suggest you to provide a full list of columns because putting “SELECT *” is required to fetch the name of the columns from stored metadata or system information.This will impact to query performance.
- Now Imagine that you are using “SELECT *” in all your stored procedure and now you require to find one column name from the text of the stored procedure. This is not possible with “SELECT *”.
Why you go with “SELECT *” ?
- The Lazy developers can only go with “SELECT *”.
- If you have special requirements and created dynamic environment when add or delete column automatically handle by application code. In this special case you don’t require to change application and database code and this will automatically affect on production environment. In this case you can use “SELECT *”.
- I didn’t find any other reason to use “SELECT *”.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.