Why you should not use SELECT * in SQL Query

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 practice 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 Oracle database. In this article, I will try to bridge that gap by giving some practical reasons of why using SELECT * in Query is not a good idea.

1) Unnecessary IO
By using SELECT * you can be returning unnecessary data that will just be ignored but fetching that data is not free of cost. This result in some wasteful IO cycles at DB 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. See The Complete SQL BootCamp to learn more.

2) Increased network traffic
SELECT * return 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 SSMS 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 which it will not be using but coming from Microsoft SQL Server.

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

5) Breaking Views while adding new columns to 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. This will also prevent you from using SELECT * in views.

6) Conflict in JOIN Query 
When you use SELECT * in JOIN query, you can introduce complications when multiple tables have columns with 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.

Why you should not use SELECT * in SQL Query

7)  Copying data from one table to other
When you use 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 column is not same between two tables.

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

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

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 * wildcard. It not only improves the performance but also makes your code more explicit. It also helps you to create maintainable code, which will not break when you add new columns to your table especially if you have views which refer to the original table.

Other Programming Articles you may like to explore:
10 Things Java Developers Should Learn in 2018
20 Books Java Programmers Can Read in 2018
Kotlin or Java? Which is better for Android developers?
10 Everyday tools for Java Programmers
Python or Java? Which language is better to start with?
5 Courses to Learn Java 9 Better
10 highest paying technical jobs programmers can do
3 JVM Languages Java Developers should Learn in 2018
The Complete SQL BootCamp for Programmers

Thanks for reading this article so far. If you think that these points make sense then please share with your friends and colleagues. If you have any questions or feedback then please drop a comment.

No comments:

Post a Comment