Why should we avoid using * in SELECT statement?

Answer:

Avoid

SELECT * FROM TableName

Here * indicates all columns. When we write SELECT *, we are asking the query engine to send back all the columns which we may not need in our application. The way of unnecessary columns makes for more data inflow from the database server to the client which on the other hand slows access and increases the load on the client machines thereby causes more more time to travel across the network. Not only that, using SELECT * refrains the query from applying the covering index. Consider, that we have 5 columns in a table and we have applied the covering index for 3 columns.Now we added 2 more columns to the underlying table.This would cause the query optimizer to ignore the optimized covering index and will go for a Full Table scan.

Another reason could be that we have two tables where both of them having ID column as their primary key and we have performed a join operation between the tables like

Select *

                                From table1

                                Join table2  ON table1.ID = table2.ID

In such as case, it will be difficult for the data consumer to understand which ID column pertains to whom.