This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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 will 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 * “, you are selecting more columns from the database, and your application might not use some of this column. This will create extra cost and load on the database system, and unnecessary data travels across the network.
- When you are using JOIN between two or more tables, and in that query, you use “SELECT * “, guess about two columns with the same name. It will return more columns 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, you have to specify all the 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 the 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, you can imagine performance and other errors.
- If you need to select 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 which 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 have special requirements and create 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 be done in a production environment. In this case, you can use “SELECT *”.
- I didn’t find any other reason to use “SELECT *”.
