SELECT TableName.Col1 VS SELECT Col1

Question

This might be a weird question but didnt know how to research on it. When doing the following query:

  SELECT Foo.col1, Foo.col2, Foo.col3
  FROM Foo
  INNER JOIN Bar ON Foo.ID = Bar.BID

I tend to use TableName.Column instead of just col1, col2, col3

Is there any performance difference? Is it faster to specify Table name for each column?

My guess would be that yes it is faster since it would take some time to lookup the column name and and differentiate it.

If anyone knows a link where I could read up on this I would be grateful. I did not even know how to title this question better since not sure how to search on it.

Answer 1

First of all: This should not matter. The time to look up the columns is such a miniscule fraction of the total processing time of a typical query, that this might be the wrong spot to look for additional performance.

Second: Tablename.Colname is faster than Colname only, as it eliminates the need to search the referenced tables (and table-like structures like views and subqueries) for a fitting column. Again: The difference is inside the statistical noise.

Third: Using Tablename.Colname is a good idea, but for other reasons: If you use Colname only, and one of the tables in your query gets a new column with the same name, you end up with the oh-so-well-known "ambiguous column name" error. Typical candidates for such a columns often are "comment", "lastchanged", and friends. If you qualify your col references, this maintainability problem simply disappears - your query will work as allways, ignoring the new fields.