This tip applies to both Microsoft SQL Server and to Oracle, and probably several other databases.

When you use the asterisk (aka star) in a select statement it requires all columns of data from the result set to be accessed.

Why not SELECT *?

If you are querying a small table with 3 or 4 columns, then the following two statements won’t be very different:

SELECT * FROM …

Or

SELECT column1, column2, column3, column4 FROM …

But when your query gets more complicated, and you then join in two more tables, which combined with the original table have a total of 40 columns, then all 40 columns are returned in your record set.

SELECT * FROM Table1, Table2, Table3 WHERE …   

will return all 40 columns

SELECT column1, column2, column3, column4
FROM Table1, Table2, Table3 WHERE …   

will only return 4 columns.

Recently I was optimizing a query using the SELECT * syntax which returned about 25 columns, one of which was a VARBINARY(MAX) column.  This query was taking about 7 seconds to run.  I then substituted the 6 column names that I needed for the select *, and with no other changes, that same query ran in about .1 seconds.

Additionally assume that you had the following query:

SELECT column1, column2, column3, column4 FROM Table1

And you had an index that contained column1, column2, column3, and column4.  This would be called a covered index, and when your query was run, it would never hit any of the data pages, and instead pull all of your results from the index.  This can much quicker than having to access the data pages.

So my recommendation is to never use the SELECT * syntax, specify what you want in your result sets.  Even if your table is small, specify what you want, because over time your table may grow to have more columns.

Why not COUNT(*)

Instead of COUNT(*), you should use COUNT(1).   Either way, you will get the same results. 

The COUNT(*) counts the number of rows in your result set by bringing all of the columns into memory, but using COUNT(1) doesn't require any of these columns to come into memory.  This also applies when your query includes a GROUP BY with COUNT(1).

My recommendation is to NEVER use COUNT(*), and instead use COUNT(1).  They will both give you the same number of rows in your result set, but there is far less work required to get the count when using COUNT(1), and COUNT(*) is just wasteful.