SQL Server Performance

SELECT * vs SELECT column list

Discussion in 'General DBA Questions' started by Zirco, Mar 30, 2005.

  1. Zirco New Member

    Hi,

    I recommend the dev team to use SELECT with an explicit list of columns. The developers agree that it is more efficient (we take the only columns you need) and more readable (we know what the SELECT bring back just by reading the stored proc).

    For the sake of technical discussion, a developer asked me if there is any other real advantage of the SELECT column list over SELECT *. Is there any difference between the two methods in terms of performance, compatibility?

    (We use SQL2K, C#, ADO.NET)

    Thanks in advance
  2. Luis Martin Moderator

  3. ranjitjain New Member

    there is a big performance issue between using SELECT * and SELECT column name.
    u can itself verify the time of execution by running both query on a table having more than 10 lac records and more than say 10 columns.
    if select * takes 1 min 10 sec to retrive all the records than select cloumn1 query will take hardly 10 secs to process.
    but it is recommended to use always where clause with SELECT query on a table with 1 gb records. if not possible then only use plain select query.
    u can read more on select query in this site in tips forums.
  4. dbu New Member

    Performance on a "covered" query is excellent. A "covered" query is one where the columns in the SELECT columnlist and the WHERE columnlist are present in an index on the table. By using a "covered" query the data pages do not have to be accessed.

    In SQL 2005 it is my understanding that we gain the ability to add columns to the index level (faster queries, less data page access) without the overhead pain.

    Dbu
  5. FrankKalis Moderator

    The less data you ask for, the faster it will be send over the network. This is nothing abstract or highly sophisticated, but simple and understandable. What more reasons do you need?

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
  6. chopeen Member

    Also remeber that the text, ntext, or image data type cannot be selected as DISTINCT.

    You may have a 'SELECT DISTINCT * FROM table1' query and it will work perfectly.

    One day somebody will add a text column to table1 and the above query will not execute anymore.

    I've seen this happen.

    --
    Marek Grzenkowicz

Share This Page