In this post, I am going to write about one of the most common mistakes of 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 the database system and more data travel across the network.
- When you are using JOIN between two or more tables, and in that query, you use “SELECT * “, now guess about two columns with the same name. It will return more column than you needed and will create a binding problem.
- If you are using “SELECT * “, you may get all columns in arbitrary sequence, and if specified the column name, it will return 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 *”, you will face performance issue because this is not advisable to apply an index on all columns of the table.
- When you are using “SELECT *”, you require to create documentation for those columns which are returning and using by an application otherwise, this will create confusion.
- When you are using “SELECT * ” and in the future, someone adds one new column with TEXT data type, now you can imagine performance and other errors.
- If you require selecting all columns, also I would suggest providing a full list of columns because putting “SELECT *” needs fetching the name of the columns from stored metadata or system information and it will impact the 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 which is not possible with “SELECT *”.
Why you go with “SELECT *” ?
- The Lazy developers can only go with “SELECT *”.
- If you had special requirements and created a dynamic environment when add or delete column automatically handle by application code. In this particular case, you don’t require to change application and database code, and this will automatically affect on a production environment. In this case, you can use “SELECT *”.
- I didn’t find any other reason to use “SELECT *”.