arguements against " select * "

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

SQL Server 2000 Forums

SQL Server Development (2000)

arguements against " select * "

Author

Topic

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-04-21 : 10:29:35

Hi all,

Can you give me some more ammunition/reasoning around this topic ?

My points against "select *" are :

1> It can select unnecessary data, resulting in unnecessary data transferred to the client;
2> Lazy

The rebuttal's for the specific instance are that they are :copying" the data from one table to another exact structural match, hence they will always want all tables, and that if the query fails due to structure differences, this is a good thing since it raises this error, and that the less the developer needs to type, the less chance there is for errors, and that the "select *" is more manageable, understandable and maintainable.

Am I wrong ... I have always *known* that select * is bad, but I can't seem to give persuasive arguements on the topic ? Am I wrong ?

Thanx

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-21 : 11:08:49

I know of at least 1 (and I'm sure there are many) threads where this has been discussed...

Just can't find them...

1. Effeciency: Not only only returning only the daya you need, but the better use of indexes
2. Table structure change, you isolate the code from breaking

INSERT INTO myTable99(Col1, Col2, Col3,ect)
SELECT * FROM myTable00 WHERE

Change myTable00...booooooooom

3. I like lazy, but not at the cost of exposure to database problems

4. Actually, in a coorelated query, SELECT * is actually optimized

SELECT Col1, Col2, Col3 FROM myTable99 a
WHERE EXISTS (SELECT * FROM myTable00 b WHERE a.id = b.id)

5. I guess the biggest thing from a calling application perspective is that people say that it doesn't matter because the record set will dynamically "know" the new column...but the code still won't...still more overhead...

6. I get a chuckle when Access developers say DELETE * FROM mytable99

7. Think also about many table joins like 4 or 5...ouch

...Try the search...I know one's out there...

Brett

8-)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-21 : 11:15:31

Just as I read this message I received an email from our development dba.
She gives us regular sql tips and todays one by pure coincidence happened to be on select * usage.

This is what she said:

--****************************************************************

SQL Server TIP: SELECT * Usage

Its very difficult to get out of this habit, but believe me this is very essential. Please DONOT use this syntax. Always qualify the full list of columns. Using all columns increases network traffic, requires more buffers and processing, and could prove error prone if the table or view definition changes.

--******************************************************************************************

Thanks Nikki,
I know she sometimes visits this site.

Duane.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-04-21 : 11:51:04

Thanks Brett and Ditch,

I did try the search - timed out a few times, and didn't get any hits I could work from (one of the searches returned 209 PAGES of topics :-)

I am trying get a conclusive list here so I can really make them understand why I am against it ... thanks for the inputs so far, and if anyone finds that topic, please post a link here

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-21 : 12:16:32

I did mention that you SHOULD use SELECT *

In a coorelated query, or an existance check as well...

IF EXISTS (SELECT * FROM myTable99 WHERE somepredicate)

Got that from Nigel...

There's a link out there as to why...

Mostly because M$ engineered it that way (to be optimized)...I think

Brett

8-)

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-04-21 : 13:18:45

Sorry Brett ,

Yes, I am sure Select * is useful in places, but I think there is probably a limited set, like you example of correlated queries. In those cases, it should be documented as a deviation from the norm, for the accepted reasons - there is almost always a deviation from any standard, given the right circumstances.

BTW - I never use :
If exists (select * from mytable WHERE somepredicate)
I prefer
If exists (select 1 from mytable WHERE somepredicate)

Is it better ? I will check when I am back at work and have a SQL Server to check on :-)

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-21 : 14:33:46

Hey Wanderer. I too have had a problem with the 'select *' factor. check it out here: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34242[/url]

In short, I have a table which has relationships with several other tables, and I needed to return at least 2 columns in each other table (about 15 others). In total I counted to have at least 169 columns returned in that particular query in which I Only needed data of about 50 columns (wow was I suprised to see that number) and also found that I did not get ALL the data back, it ran well on QA, and on my dev server at home, but not on my prod server even tho it was an exact duplicate. I upsized an access db to sql, and yes the select * worked with access, and having 230+ queries in access I didn't make any changes to easily and quickly convert many queries into sprocs (since many where not imported.) I am now going through each and everone of these sprocs (what a pain) to both change this select * deal as well as optimize the queries to better work with sql. Even tho I was going to optimize these queries later, I find i cannot continue without removing this select * from many of my sprocs and am VERY GLAD to find this out now, and not later. I'm not exaclty sure if the problem was because I needed 50 columns and was returning 169 and there was a problem in between with ado or sql or whatever, but this is just my 2-cents on the deal. I have re-toought myself to start returning ONLY the columns I need, to get away from future erros like this.

One thing I DO hate about this is:

"What if I change the table structure like add 2, 3, or 5 columns to the table? Then I'd have to go in EACH sproc and edit them!!!"

I hated doing that in access, which was why I went with 'select *' in access to start with. But this experience has changed my mind. Also, if you find good documentation on this else where or other posts, please post the url here, I am still interested in this topic.

Thanks.

- RoLY roLLs

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-21 : 17:01:35

I've been looking...I still can't find that thread OR the damn link that discusses that M$ optimized SELECT * for those special cases...

I believe it doesn't move any data around in those cases since they are existance checks...

Brett

8-)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-21 : 23:15:09

Another reason to not use SELECT * is because of it's use of the system tables. Put about 5 SELECT * arguments into a stored procedure (be a little creative and use one in derived table, on is select statement, one as a column, etc) Run a profiler.

You will see you are constantly accessing the system tables to figure out what these columns are. Also pull up your recompiles. See if you like the results. (grin)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-04-22 : 02:26:56

hmm - thanks Derrick - select * using system tables to determine colum list was not one that jumped to mind.

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-22 : 09:43:13

quote:
Originally posted by derrickleggett

Another reason to not use SELECT * is because of it's use of the system tables. Put about 5 SELECT * arguments into a stored procedure (be a little creative and use one in derived table, on is select statement, one as a column, etc) Run a profiler.

You will see you are constantly accessing the system tables to figure out what these columns are. Also pull up your recompiles. See if you like the results. (grin)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Another good one....I gotta make a list...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-22 : 11:02:43

Heres the list

Any contributions more than welcome...

http://weblogs.sqlteam.com/brettk/

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-04-24 : 12:38:11

quote:
Originally posted by X002548

Any contributions more than welcome...

You've covered it in "Isolate code from Table object changes", but I'd just like to add an anecdote; I was called in to help a client who's web application performance was falling like a stone.

Turned out their inhouse developers had used "SELECT *" *everywhere* - and I mean **everywhere** (how many *'s am I allowed in this thread?!)

Anyways, they had decided to add a stack of TEXT columns to loads of tables so that the call centre could record notes, chit chat with customer, instructions to despatch ... you get the idea.

And of course all those SELECT * queries were now pulling the TEXT columns, even where the App wasn't using them. And the call centre loved the TEXT columns. And they were putting in tons of text. And the developers had to find & fix THOUSANDS - I mean **THOUSANDS** of occurences of SELECT *.

Deffo got to break that habit early. Of course I had to get them to use SProcs instead of dynamic SQL from the App too. One happy customer with a great-performing app.

Development cost to change the code? Scary!

Kristen

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-24 : 14:42:17

quote:
Originally posted by Kristen

Deffo got to break that habit early. Of course I had to get them to use SProcs instead of dynamic SQL from the App too. One happy customer with a great-performing app.

Thank GOD I started early

quote:
Originally posted by Kristen

Development cost to change the code? Scary!

I bet! I'm still trying to find my 230+ sprocs and it ain't easy.

- RoLY roLLs

Kristen
Test

22859 Posts

Posted - 2004-04-25 : 01:22:54

quote:
Originally posted by RoLYroLLs

I bet! I'm still trying to find my 230+ sprocs and it ain't easy.

- RoLY roLLs

Sounds to me, with that many to do, that you might benefit from a bit of tools-engineering to give yourself a leg-up.

Dunno if it would help, as an idea, but I use an SProc that returns "useful lists" of tables. I've given it a really short name, so its easy to type in QA. It takes the name of the table as a parameter (using wildcards you only have to give up some unique portion of the name; it takes column names as a clue too - which are often lying around for cut&paste).

e.g.
USE PUBS
MyShortSprocName 'authors'

You can get all this from the object browser, but it seems that its never formatted correctly for what I want to do ... there again it may just be me doing "Make Work"!

My SProc queries the Extended Properties on the columns - adding a comment with the descriptive name of the column (assuming you add those in Entprise Manager's Table Design tool)

It then gives me the following lists:

All columns on one line (useful for INSERT (col1, col2...)

SELECT style statement with one column per line - useful to then just delete / comment out the ones not required; each line has comment using descriptive name of column

All columns prefixed with '@' and their datatype - e.g. for using in a DECLARE for local variables

All JOINs, based on FOREIGN KEY stuff in the DB.

Running this in QA, using Text output mode, I then just cut & paste the stuff I want into the query.

The sort of stuff it outputs looks like:


------------ ---------------------------------------------------------------------
Column list: au_id, au_lname, au_fname, phone, address, city, state, zip, contract
      Table: dbo.authors
         PK: au_id

SELECT (syscolumns)                                                 
-------------------------------
SELECT
	au_id,			-- [Description would be here]
	au_lname,		-- 
	au_fname,		-- 
	phone,			-- 
	address,		-- 
	city,			-- 
	state,			-- 
	zip,			-- 
	contract 		-- 
FROM	dbo.authors

Kristen

Kristen
Test

22859 Posts

Posted - 2004-04-25 : 01:58:22

quote:
Originally posted by RoLYroLLs

I bet! I'm still trying to find my 230+ sprocs and it ain't easy.

- RoLY roLLs

Forgot to mention: whilst you are trawling through all your SProcs on a Tidy Up mission there may be some other things you should clean up.

I'm sure I don't know what they all are, but I would be interested in what other people think would be useful.

Make sure you have "dbo." in front of every use of every table name (supposedly makes it run faster because SQL doesn't waste time trying to, fail to, find a [MyLoginName].MyTable first) - assuming you don't use some other [OwnerName]

Do NOT prefix your SProc names with "SP_" 'coz SQL goes and looks for it in MASTER first, wasting more time.

We also do the following:

Put

CREATE PROCEDURE ...
/* WITH ENCRYPTION */
AS

in the definition so we can, globally, remove the comment when running the script on the Prod Server so the SProcs are encrypted (reduces hack-attack risk, and stops people doing RightClick edits!). Encryption has been cracked (not SQL2K yet AFAIK, but only a matter of time I expect, so I don't regard it as secure, but.)


SET NOCOUNT ON
SET XACT_ABORT ON

Prevents spurious resultsets with row counts; causes whole SP to abort if there is a syntax error etc, which reduces the amount of error checking we do, line-by-line, in the SProc itself. (Child SProcs that are EXEC'd, and fail, still need to be error-checked after the call in the parent SProc)


SET NOCOUNT OFF
RETURN @intRetVal	-- Return error number, 0=No error

Make sure all SProcs return a value. We use "@intRetVal" in all our SProcs as the variable that contains the exit value.

After each statement (but only where appropriate) we do


SELECT	@intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
IF @intErrNo <> 0 OR @intRowCount <> 1 Change as appropriate!
BEGIN
	SELECT	@intRetVal = -1,
		@strErrMsg = @strErrMsg + 'Update FOO failed.  '
END

Each SProc is in a separate source code file, and we top&tail:

[Note: start with a blank line]
PRINT 'Create procedure MySpName'
GO
EXEC dbo.xxx_SP_LogScriptRun 'MySpName', '20040424'
GO
IF EXISTS (SELECT * FROM sysobjects ...
	DROP PROCEDURE dbo.MySpName
GO

[SProc defined here]

SET NOCOUNT OFF
RETURN @intRetVal	-- Return error number, 0=No error
/* TEST RIG

EXEC MySpName MyTestParameters ...123

SELECT TOP 10 * FROM MyTable ORDER BY UpdateDate DESC

 */
--================== MySpName ==================--
GO
IF exists (SELECT * FROM sysobjects ... 
GRANT EXECUTE ON dbo.MySpName TO MyRole
GO
PRINT 'Create procedure MySpName DONE'
GO
[another blank line here]

This does the following jobs:

Log the EXEC of the SProc to some local table. The '20040424' is a "version" number. We use today's date and only change it when we get to a "release" point. When it all goes pear-shaped we compare the Script Run Log on the PROD and DEV servers to find out what we forgot

We include a "TEST RIG" in a comment at the bottom of the SProc. This is then available using RIGHT CLICK in EM/QA - handy if on client's site and don't have all testing tools to hand. Also keeps the test logic together with SProc so it "evolves" with the SProc.

We also put our GRANT EXECUTE permissions in this source file.

To "deploy" we concatenate all appropriate SProcs files into a single, monster, script file (hence the blank line top&bottom to stop first/last line becoming one!) and then run that. We try to output then, using a query on sysdepends, in tree-order. However I don't think it is that important, and the script can just be run a second time if there are interdependencies (unlike "Generate SQL", which DROPs everything first then CREATES, this DROP then CREATE on a one-by-one basis works better for this job)

The PRINT statements, top and bottom, provide a progress-report when running the big concatenated script.

The "--================== MySpName ==================--" comment just before the GO which ends the SProc definition is for the benefit of any GENERATE SQL script that you might need to do in the future - it provides a nice clear marker of where the separation is between each SProc.

What a lot of wibble I produce!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources