SQL 102-SELECT * Overhead

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?

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

2 comments

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

Leave a comment

Your email address will not be published. Required fields are marked *