Home > SQL, SQL Server, SQL Server 2008, SQL Server 2008 R2 > SELECT * Vs SELECT Column Name in SQL Server Query

SELECT * Vs SELECT Column Name in SQL Server Query

People always keep saying do not use SELECT * exercise SELECT <column names> instead. But no one put more light on this and this becoming myth in SQL Server and other database management systems. Here are couple of reasons why should we avoid SELECT * and go with SELECT <column names>

  • In SELECT * we are not providing any column hints to SQL Engine as which columns we are looking for, in background SQL Engine will check the table’s metadata to generate output columns list. This additional lookup is not mentioned in actual execution plan but will add additional overheads to query if not cached.’

SELECT * Execution Plan

SELECT *

SELECT <column names> Execution PlanSELECT <column names>

  • The data would be retrieve from the index pages if SQL query contains all the necessary columns and thus disk I/O and memory overhead would be much less compared to doing a SELECT *.
  • In SELECT <column names> query, there’s always a possibility of a non-clustered index might be serving SQL Engine’s request. While we do not have that possibility in SELECT *.
  • If someone changed existing metadata of table object and winded up with adding image/text data type columns  will be additional overhead to all SELECT * operations.

Hope this helps to you guys.

Thanks & Regards,

Sandip Shinde

About these ads
  1. July 31, 2013 at 1:44 pm | #1

    Does your blog have a contact page? I’m having trouble locating it but, I’d like
    to send you an e-mail. I’ve got some recommendations for your blog you might be interested in hearing. Either way, great blog and I look forward to seeing it improve over time.

  2. November 7, 2013 at 11:10 am | #3

    I am having a question here…

    Lets say a table having only 3 columns…(col1, col2, col3)
    and which one of following you will suggest with respect to performance.

    select * from Table1
    Or
    select col1,col2,col3 from Table1

    Will this have any impact?

    • November 7, 2013 at 10:52 pm | #4

      Srihari, please read my article carefully. Select * will create impact on performance as indexes will not take places.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: