Exactly why is Choose * bad practice? Would it not mean less code to alter should you added a brand new column you desired?

I realize that Choose COUNT(*) is really a performance problem on some DBs, but let's say you actually wanted every column?

You will find really three major reasons:

  • Ineffectiveness in moving data towards the consumer. Whenever you Choose *, you are frequently locating more posts in the database than the application really must function. This will cause more data to maneuver in the database server towards the client, slowing down access and growing strain on your machines, in addition to taking additional time to visit over the network. This is also true when someone adds new posts to underlying tables that did not exist and were not needed once the original customers coded their data access.

  • Indexing issues. Think about a scenario where you need to tune a question to an advanced of performance. Should you use *, also it came back more posts than you really needed, the server would frequently need to perform more costly techniques to retrieve your computer data of computer otherwise might. For instance, you would not have the ability to create a catalog which simply covered the posts inside your Choose list, and even when you probably did (including all posts [shudder]), the following guy who came around and added a column towards the underlying table would make the optimizer to disregard your enhanced covering index, and you'd likely discover that the performance of the query would drop substantially for no readily no reason.

  • Binding Problems. Whenever you Choose *, you can retrieve two posts of the identical title from two different tables. This could frequently crash your computer data consumer. Make a query that joins two tables, each of which have a column known as "ID". Wouldso would someone know that was which? Choose * may also confuse sights (a minimum of in certain versions SQL Server) when underlying table structures change -- the vista isn't reconstructed, and also the data which returns could be nonsense. And also the worst some of it is you can be certain to title your posts anything you want, however the next guy who arrives may have not a way of understanding that he needs to be worried about adding a column that will collide together with your already-developed names.

But it is not every harmful to Choose *. I personally use it liberally of these use cases:

  • Ad-hoc queries. When attemping to debug something, especially off a narrow table I is probably not acquainted with, Choose * is frequently my closest friend. It will help me just see what's happening without needing to perform a ton of research in regards to what the actual column names are. This reaches be considered a bigger "plus" the more the column names get.

  • When * means "a row". Within the following use cases, Choose * is simply fine, and gossips it's a performance killer are simply urban stories who have had some validity a long time ago, try not to now:

    Choose COUNT(*) FROM table

    within this situation, * means "count the rows". Should you make use of a column title rather than * , it might count the rows where that column's value wasn't null. COUNT(*), in my experience, really drives home the notion that you are counting rows, and also you avoid strange edge-cases triggered by NULLs being removed out of your aggregates.

    Same goes using this type of query:

    Choose a.ID FROM TableA a
    WHERE Is available (
        Choose *
        FROM TableB b
        WHERE b.ID = a.B_ID)

    in a database with credibility, * means "a row". It does not appear you devote the subquery. Many people use b's ID within the Choose list, or they normally use the most important, but IMO individuals conventions are virtually nonsensical. That which you mean is "count the row", and that is what * signifies. Most query optimizers available are wise enough to understand this. (Though to tell the truth, I only know this to be real with SQL Server and Oracle.)