SQL Server Performance

why not to use select * from table

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by dashpatil, Mar 22, 2006.

  1. dashpatil New Member

    Just i want the differenc between the select * from tablex and select column1,column2 from tablex

    dashpatil
  2. Madhivanan Moderator

    Select * will select data from all the columns. If you want specified columns, then you can specify it. But there wont be any performance issue when using * and all_Columns

    Madhivanan

    Failing to plan is Planning to fail
  3. FrankKalis Moderator

  4. Madhivanan Moderator

    >>SELECT * causes much unnecessary traffic over the network.

    I dont understand

    What happens when you explicitely list all the columns instead of *?

    Madhivanan

    Failing to plan is Planning to fail
  5. FrankKalis Moderator

  6. mmarovic Active Member

    quote:Originally posted by Madhivanan

    >>SELECT * causes much unnecessary traffic over the network.

    I dont understand

    What happens when you explicitely list all the columns instead of *?

    Madhivanan

    Failing to plan is Planning to fail
    1. The chance is for specific functionality you won't need to return all columns from the table. If you need just a few, there is a chance index covering all columns from the query. Even if not, you save network traffic that way.
    2. Even if you need to return all columns from the table what happens when column is dropped from the table or new column is added in case of "select *" query? How would client application handle changed recordset structure?
  7. druer New Member

    The other difference is shear speed. If you only need Column1, Column2 in the results and have say Column3 in the WHERE clause, the system would be able to completely resolve the query by using an index on Column3, Column1, Column2. Meaning it wouldn't have to figure out which rows to use from an index, and then go do a bookmark lookup of the resulting values to get the results to return. If there are several different queries for different values that all end up hitting the source table, that is a lot of contention on the same table, so it is slower individually, plus the additional time for contention and blocking just makes it worse.


    Hope it helps,
    Dalton

    Blessings aren't so much a matter of recieving them as they are a matter of recognizing what you have received.

Share This Page