Explicitly Name Columns in SELECT Statements

SQL Server

Creating a basic query in SQL Server seems so very easy. How many times have you written a select statement like this example:

SELECT * FROM EmpTable;

When you use the asterisk (*) in your query it tells the SQL Server engine that you want to return all columns from your table that you have identified in the FROM clause. Even if you do want to return all the columns, it is best practice to name each of the columns you want to return in your query, like this example:

SELECT ColNum, EmpNum, EmpName, EmpHireDate, EmpTermDate FROM EmpTable;

When you use explicit column names in your SELECT statements, if offers certain advantages:

  • You save server bandwidth by only collecting the data from the columns you actually need
  • You save network bandwidth by only sending the data you need across the network from the database server to the client running your application
  • You application only had to process the specific columns your application needs
  • Even if the table schema changes, like someone adding a column to your table, you will get the specific columns you need in the order you requested them
  • If an index exists with the columns indicated in your query, the index might be used which will increase the speed of the query

This rule is also most powerful if the table you are attempting to query has several columns. If the target table has 30 columns, and you only need the first 3 columns, you might see significant speed improvements.

Advertisements

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