SELECT * FROM TABLE in SQL Query Is a Bad Idea? Why? - 7 Reasons Revealed!

March 16, 2020  

Why using SELECT * FROM TABLE in SQL Query Is a Bad Idea? - 7 Reasons!

I have read many articles on the internet where people suggest that using SELECT * in SQL query is a bad practice and you should always avoid that. Instead, you should always use an explicit list of columns. That's a good suggestion and one of the SQL best practices I teach to junior developers, but many of them don't explain the reason behind it. 

Unless you explain some reasons why one should not use SELECT * in queries, it's difficult to convince many SQL developers, many of whom have started learning SQL by doing SELECT * from EMP in the Oracle database.

In this article, I will try to bridge that gap by giving some practical reasons for why using SELECT * in Query is a bad idea.

Here are a couple of reasons that make sense on why you shouldn't use SELECT * from a table in your SQL query. 

1. Unnecessary I/O (Input Output)

By using SELECT *, you can be returning unnecessary data that will just be ignored, but fetching that data is not free of cost. This results in some wasteful IO cycles at the database end since you will be reading all of that data off the pages when perhaps you could have read the data from index pages.

This can make your query a little bit slow as well. If you don't know how your query executes, how the query engine processes your query in which order clauses are executed, etc., I suggest you read a good book like SQL Performance Explained by Markus Winand or “The Complete SQL BootCamp” course on Udemy to learn more.

2. Increased Network Traffic

SELECT * obviously returns more data than required to the client, which, in turn, will use more network bandwidth. This increase in network bandwidth also means that data will take a longer time to reach the client application, which could be your own machine if you are running your query on a query editor like SQL Server Management Studio, Toad, or SQL Developer Tool for Oracle, or your Java application server.

3. More Application Memory

Due to this increase in data, your application may require more memory just to hold unnecessary data that it will not be using but coming from Microsoft SQL Server.

4. Dependency on Order of Columns on Result Set

When you use the SELECT * query in your application and have any dependency on order of column, which you should not, the ordering of the result set will change if you add a new column or change the order of columns.

5. Breaks Views While Adding New Columns to a Table

When you use SELECT * in views, then you create subtle bugs if a new column has been added and the old one is removed from the table. Why? Because your view will not break but start returning an incorrect result.

To avoid that, you should always use WITHSCHEMABINDING with views in SQL Server Database. This will also prevent you from using SELECT * in views.

6. Conflicts in a JOIN Query 

When you use SELECT * in JOIN query, you can introduce complications when multiple tables have columns with the same name e.g. status, active, name, etc.

On a straight query, this might be fine, but when you try to order by one of these columns or use the query in a CTE or derived table, you will need to make some adjustments.

7. Copying Data from One Table to Another

When you use the SELECT * into INSERT, SELECT statement, which is a common way to copy data from one table to another, you could potentially copy incorrect data into the incorrect column if the order of the column is not the same between both tables.

Some programmers think that using SELECT * vs. SELECT 1 in your EXISTS clause is faster because the query parser had to do extra work to validate the static value.

That might have been true long ago, but nowadays, the parser has become smart enough to know that within an EXISTS clause, the SELECT list is completely irrelevant.

Conclusion:

That's all about why you should not use SELECT * in SQL query anymore. It's always better to use the explicit column list in the SELECT query than a * (star) wildcard. It not only improves the performance but also makes your code more explicit. It also helps you create maintainable code, which will not break when you add new columns to your table, especially if you have views that refer to the original table.

Source: Database Zone