September 25, 2003 at 9:17 am
Hey all,
I remember one of MS's internal training videos on SQL Svr 2000 programming talked about using EXISTS and the lady said using (SELECT * ...) is fine. She said a lot of people uses (SELECT 1 ...) but really there's no performance difference because SQL Server was only checking whether anything will be returned...
Does anyone know if this is true?
Even if I do a complicated join of multiple tables, I can still using (SELECT *) and performance wouldn't be hit?
September 25, 2003 at 9:39 am
This sound logical. Query optimizer 'knows' that you don't need the values.
How about joins themselves? Is there a difference in performance if you put more fields in the SELECT than you need :
e.g.
SELECT T1.Field1, T2.Field2
FROM (SELECT ID1, Field1, DummyField1 FROM Table1) T1
INNER JOIN
(SELECT ID2, Field2, DummyField2 FROM Table2) T2
ON T1.ID1 = T2.ID2
Is there any difference if you ommit the DummyFieldx?
September 25, 2003 at 11:22 pm
Good question, I have also wondered about this.
I've always tried to avoid the use of "Select *" in any database queries. It's better to explicitly define what columns are being returned.
This approach of avoiding "Select *" has also affected my use of EXISTS, as I tend to favour using (select 1). It's not a performance issue, as both methods generate the same query costs.
Cheers,
Kevin
September 26, 2003 at 3:21 am
Hi!
The "SELECT *" topic gets interesting when working on the client side. Eg. using ADO and doing a "SELECT *" rather than doing a "SELECT fld, fld, fld, ..." will cause ADO to issue 2 (in words "two"!) queries: (1) for getting the list of fields for the table and (2) for executing the statement.
So this could be an issue on the client side!
best regards,
chris.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply