SQL Server Performance Forum – Threads Archive

Using Select * Vs Select ID, FName, LName…

I have read in one of the posts that use Select * is inefficient.. I was wondering what the experts’ opinion would be…[8D] You see, when we do need to select all columns, would it result in a performance gain if we’d list all the coloumns as against usig a SELECT * ? Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.

I think no at all. But, wait for development experts post.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

There would be no difference in performance if you select all columns anyway. However using select * from within an asp page, can result in the page breaking after adding a column to the table (in the same way as once you join two tables you should always prefix every column with the table’s name or alias) Cheers
Twan

You may be confusing selects like the following: if exists (select * from …….) with if exists (select 1 from …….) The latter one is marginally quicker as select 1 does not require the column names to be evaluated. Rik

There is a *flaw* in ADO, in that you should place binary columns at the last position in a SELECT statement.http://support.microsoft.com:80/support/kb/articles/Q175/2/39.ASP&NoWebContent=1 Apart from this, I think explicitely stating which columns to retrieve is good coding practice and mostly you don’t need all your columns at once, so that in such cases you’ll notice a performance simply by minimizing network traffic. Frank
http://www.insidesql.de
http://www.familienzirkus.de

How about: select count(*) from someTable AGAINST select count(ID) from someTable wouldn’t the second one be way faster than the first? Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.

I think latter statement would leave ot the cases where ID is null whereas count(*) won’t. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Pretty sure that if there is a unique index on a non-null column in the table, then there is no difference between the two statements, as SQL will use the unique index in both cases Cheers
Twan

COUNT(*) will count all rows including nulls
COUNT(field) will count all rows that are not null COUNT(field) when field is indexed will usually be faster than count(*)
COUNT(field) with clustered index might be slower than count(field) with a non-clustered index (less reads) as for exists statements if exists (select * from… )
if exists (select 1 from… )
if exists (select NULL from… ) I used to think the last 2 perform better but they all generate the same execution plan.

I have not been able to see any difference between count(*) and count(field) on my tables…? both use the same plan and each take eactly the same time to execute… In fact they use an index on a field that is not mentioned in the query…? it is almost as if when the column is not null, then both count(*) and count(field) will use the smallest index to get the rowcount…? Twan

you are right, Twan. As long as the column is not null they will use the same execution plan and the samllest index. maybe something had changed since the last time I’ve tested it, or maybe I’m just getting old! <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ />

I don’t think you’re getting old! <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> I also thought that count(field) was quicker, as I thought that if exists( select 1 was quicker. but both of these seem to have been resolved in SQL2k… Twan

Hey! No body mentioned about count(ID) not counting the rows which have value as NULL. This may be a critical factor on SELECT performance if the column under consideration is nullable. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Hi Gaurav, yeah you mentioned it yourself earlier on… and we put an assumption in that the column is declared as not null for the purpose of the rest of the discussion <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Cheers Twan

I think I am getting older for this… [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Gaurav Moderator Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

]]>