Skip to content

SQL 102-SELECT * Overhead

2010 May 12
by Shannon Lowder

How many of you have heard me rant against using “SELECT * FROM tableName”? If you have I’m sure you’ve heard me mention the following reasons why SELECT * is evil.

  • the more data you ask for, the longer it takes to return that data
  • extra paging
  • table locking
  • hinders future attempts to create a covered index

Now with SQL Azure there’s one more reason.  SELECT * will cost you actual money.  Since you are paying for transfers in and out of your SQL Azure Database.  You can now actually measure the difference in what you’re paying for the service.  You can create a query, measure the number of bits you’ve transferred by using SELECT *, then compare that to the number of bits returned when you just SELECT the columns you actually need.  Find the Difference.  Then multiply that by the number of times that query will be run per month, and you have a total monthly transfer for that one query.

Once you have that number, you can look at the billing plan you chose.  Look at the amount you’re paying for that transfer.  You now have a dollar amount that SELECT * will cost you.  If you’re lucky the amount will be negligible.  But I know I’ve seen SELECT * in enough web pages and windows code to know it’s out there.  As you start moving from dedicated SQL Servers to SQL Azure instances, you’re going to feel this cost a bit more now.

If you start looking for those places now where you could minimize the amount of data being transferred, you can avoid this cost later, when you have completed your migration to the cloud.

How much are you paying, that you could save, simply by making this change?

2 Responses leave one →
  1. June 4, 2010

    My cousin recommended this blog and she was totally right keep up the fantastic work!

  2. jie jie permalink
    July 14, 2010

    My cousin recommended this blog and she was totally right keep up the fantastic work!

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS