Using Select * from multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using Select * from multiple tables

Author

Topic

rosejr
Starting Member

12 Posts

Posted - 2007-11-08 : 11:13:52

Can someone explain to me why this would be considered "bad"? One thing that pops in my mind is that I really don't need all the columns from all these tables, only specific columns. Would this cause a performance issue when used in a stored proc for a transactional app?

SELECT *
FROM CASE_XREF CX, CASE_RENEWAL_XREF CRX, RENEWAL_BATCH RB, PROPOSAL P
WHERE RB.MKT_SEG = @MKT_SEG
AND RB.CORP_ENT_CD = 'oh'
AND RB.RENEWAL_DT = '01/01/2008'
AND CRX.TRIGGER_TYPE_CD = 'P'
AND RB.BATCH_ID = CRX.BATCH_ID
AND CRX.CASE_ID = CX.CASE_ID
AND CRX.REN_PROSPECT_ID = P.PROSPECT_ID
AND CRX.REN_PROP_NUM = P.PROP_NUM
AND P.PROP_STATUS <> 'C'
AND CX.ACCT_NBR = 123152

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-11-08 : 11:22:58

ummm, you've practically answered your own question. specifically, you are utilizing more i/o more memory and more network bandwidth when u select more data than u need.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 11:26:35

You are selecting ALL columns from ALL references tables.
Rewrite your query with the ANSI join style.

SELECT		* 
FROM		CASE_XREF AS cx
INNER JOIN	CASE_RENEWAL_XREF AS crx ON crx.CASE_ID = cx.CASE_ID
INNER JOIN	RENEWAL_BATCH AS rb ON rb.BATCH_ID = crx.BATCH_ID
INNER JOIN	PROPOSAL AS p ON p.PROSPECT_ID = crx.REN_PROSPECT_ID
			AND p.PROP_NUM = crx.REN_PROP_NUM
WHERE		rb.MKT_SEG = @MKT_SEG
		AND rb.CORP_ENT_CD = 'oh'
		AND rb.RENEWAL_DT = '01/01/2008'
		AND crx.TRIGGER_TYPE_CD = 'P'
		AND p.PROP_STATUS <> 'C'
		AND cx.ACCT_NBR = 123152

E 12°55'05.25"
N 56°04'39.16"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-11-08 : 11:48:02

indeed. you should replace * with an explicit list of only the columns you need to return.

elsasoft.org

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-08 : 13:50:51

If you are so inclined, read this

http://weblogs.sqlteam.com/brettk/archive/2004/04/22/1272.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-08 : 14:33:17

Also, some column names are returned duplicate times if they exist in more than one table (i.e., the columns you are joining on), which can cause problems at your client as well as if you need to do further SQL processing of these results.

i.e., if you run this:

select a from
(
select *
from
(select 1 as a) table1
cross join
(select 2 as a) table2
) x

you get:

Server: Msg 8156, Level 16, State 1, Line 1
The column 'a' was specified multiple times for 'x'.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources