Home Forums SQL Server 2005 T-SQL (SS2K5) EXISTS Subqueries: SELECT 1 vs. SELECT * (Database Weekly 11.02.2008) RE: EXISTS Subqueries: SELECT 1 vs. SELECT * (Database Weekly 11.02.2008)

  • The advantage of "Select Col1, Col2...." (all columns explicitly named), vs "Select *", is more than just performance.

    First, if you just need a few columns, not all of them, it means you pull less data. That means less I/O, less RAM, less CPU, etc. This won't matter much in a small and/or low-traffic database, or one where you have a high bandwidth connection. It will matter more if you are pulling large amounts of data across a lower bandwidth network.

    For example, if you pull 2 Int columns, and 1 varchar(50) column, for 1-million records, you're looking at c. 58 Meg of data. If you don't need the varchar column, you instead pull 8 Meg of data. On a Gigabit connection on a server that's bored, you'll do just fine with the bigger pull, but across the Internet, you'll definitely notice it.

    The main advantage, however, is in future development on the database. If columns are added to the table, or renamed, an implicit select can be broken.

    For example, if you change the name of a column from "FName" to "FirstName", on a table, you can look in the system views for all procs, views, etc., that reference FName and update them accordingly, but it becomes much harder to identify procs, etc., that implicitly reference that column with a "*". Let's say you have five procs that use that column, and 175 that reference that table. If all 175 use "Select *", you have to go into each of them, figure out which ones actually do anything with that column, and then update them. If you use explicit selection, you go into the five that actually need updating, and fix those. The rest you can ignore. That means typing "Select *" (which is usually done to save a few seconds of typing column names), can cost you hours of work later on.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon