SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Select * or declare all filds?
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

JimL
SQL Slinging Yak Ranger

USA
1534 Posts

Posted - 06/07/2005 :  13:04:12  Show Profile  Visit JimL's Homepage  Reply with Quote
Nice trick Rock!


Jim
Users <> Logic
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/07/2005 :  13:48:20  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
to me, this is absolutely perfectly acceptable:

select a.*, .. other calculations ...
from
 (some derived SQL) a

As long as (and this is they key point) the portions of the SELECT that ACCESS TABLES DIRECTLY contains the actual columns you need from those tables.

Any DBA that fights a developer on that one doesn't really know WHY they shouldn't use *, they are just blindly following advice or what they think is "best practices" without understanding it.

Let's review WHY it may or may not be a good idea to use SELECT * FROM SOME_TABLE (assuming that you want all of the columns returned, of course, which is a common reason AGAINST using * if you don't really need them all, which is legit):

1) for dynamic SQL (client side), the advantage of listing out columns in the SELECT is that it avoids column name lookups, and any table changes cause errors immediately at the SELECT statement, instead of introducing errors that may or may not occur elsewhere in your code as your process the recordset. This is a good thing.

2) also for client-side dynamic SQL, if the resulting columns are referenced by their ordinal position this ensures that the columns are in the proper order and it makes the code a lot clearer if you don't have the table schema in front of you.

3) for Stored Procs, Views or UDFs, of course, there is no performance disadvantage of using SELECT * since it is precompiled. But the potential issue is that if the underlying tables change, you get weird results! Even though your proc clearly says SELECT *, new columns in the table (added since the proc was last compiled) are missing! And changed columns can cause very weird results (or, hopefully, errors) that, on the surface, make no sense. A simple recompile or rebuild of the proc/view/UDF fixes this, of course. But to someone debugging things trying to figure out what went wrong, that can be pretty tough to figure out.

Any other legitimate reasons to avoid * ?

- Jeff

Edited by - jsmith8858 on 06/07/2005 13:59:02
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 06/07/2005 :  13:58:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by jsmith8858

Any DBA that fights a developer on that one doesn't really know WHY they shouldn't use *, they are just blindly following advice or what they think is "best practices" without understanding it.



I take offense to that. You shouldn't assume. A blanket rule , don't use SELECT * except in the case of EXISTS, is much easier than having to explain to a large group of developers in what special circumstances the rule can be ignored. Now if there was a developer here like you Jeff, then we could make exceptions, but it's very rare to find developers that have top notch SQL skills.

Tara
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/07/2005 :  14:04:31  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by tduggan

quote:
Originally posted by jsmith8858

Any DBA that fights a developer on that one doesn't really know WHY they shouldn't use *, they are just blindly following advice or what they think is "best practices" without understanding it.



I take offense to that. You shouldn't assume. A blanket rule , don't use SELECT * except in the case of EXISTS, is much easier than having to explain to a large group of developers in what special circumstances the rule can be ignored. Now if there was a developer here like you Jeff, then we could make exceptions, but it's very rare to find developers that have top notch SQL skills.

Tara



I didn't mean to offend you and I apologize; that comment wasn't really directed at you but rather to rules such as the one you mentioned which are often used in many corporations.

But ... I do sincerely get annoyed when people follow rules or "best practices" without understanding why, which occurs quite often. IMHO, it would be much better for your developers to be educated as to WHY they should and should not use *, as opposed to having them memorize a simple "never do it" rule. More work for you or them initially? Absolutely. Better off for you and them in the long run? Without a doubt! Of course, I do tend to have a different stance on things like this than many in the IT profession, I realize, where communication isn't always encouraged.

Just my $.02 ... as you know, I personally consider that understanding is much more valuable than memorizing.

- Jeff

Edited by - jsmith8858 on 06/07/2005 14:05:54
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 06/07/2005 :  14:12:37  Show Profile  Reply with Quote
select a.*, .. other calculations ...
from
 (some derived SQL) a


Which is the opposite of what David said...

Hands up everyone who has had to debug / alter code with "select *" all over the place. Nightmare.


rockmoose
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/07/2005 :  14:16:57  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by rockmoose

select a.*, .. other calculations ...
from
 (some derived SQL) a


Which is the opposite of what David said...



... and the same as what Michael said ...



- Jeff
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 06/07/2005 :  14:57:41  Show Profile  Reply with Quote
... which I did not read apparently ...

rockmoose
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/07/2005 :  15:28:20  Show Profile  Reply with Quote
quote:
Originally posted by rockmoose

Hands up everyone who has had to debug / alter code with "select *" all over the place. Nightmare.



[Hand raised]
So Jeff, you go from fully qualifying column names to SELECT * (in certain instances). I don't think your argument is worth the risk. And in any case, by listing the columns, it's sort of self documenting
[/Hand raised]

Eagle that should be a comma in front of your column so you don't have to type it.

Or you can use (And you can build this for all your basic DML..)


USE Northwind
GO

CREATE FUNCTION udf_BuildSELECT(
	@TABLE_NAME sysname
)
RETURNS varchar(8000)
AS
  BEGIN
	DECLARE @sql varchar(8000), @ORDINAL_POSITION int, @MAX_ORDINAL_POSITION int
	SELECT @sql = 'Table Does not Exists'
	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME = @TABLE_NAME)
	  BEGIN
		SELECT @sql = 'SELECT    ' + COLUMN_NAME + CHAR(13) + CHAR(10) 
		  FROM INFORMATION_SCHEMA.Columns
		 WHERE TABLE_NAME = @TABLE_NAME
		   AND ORDINAL_POSITION = 1

		SELECT @MAX_ORDINAL_POSITION = MAX(ORDINAL_POSITION), @ORDINAL_POSITION = MIN(ORDINAL_POSITION)
		  FROM INFORMATION_SCHEMA.Columns
		 WHERE TABLE_NAME = @TABLE_NAME
		   AND ORDINAL_POSITION > 1

		WHILE @ORDINAL_POSITION <= @MAX_ORDINAL_POSITION
		  BEGIN

			SELECT @sql = @sql + '        , ' + COLUMN_NAME + CHAR(13) + CHAR(10) 
			  FROM INFORMATION_SCHEMA.Columns
			 WHERE TABLE_NAME = @TABLE_NAME
			   AND ORDINAL_POSITION = @ORDINAL_POSITION

			SELECT @ORDINAL_POSITION = MIN(ORDINAL_POSITION)
			  FROM INFORMATION_SCHEMA.Columns
			 WHERE TABLE_NAME = @TABLE_NAME
			   AND ORDINAL_POSITION > @ORDINAL_POSITION
		  END

		SELECT @sql = @sql + '  FROM    '+ @TABLE_NAME
	  END

	RETURN @sql
  END
GO
	
SELECT dbo.udf_BuildSELECT('Orders')
GO

DROP FUNCTION udf_BuildSELECT
GO





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

Edited by - X002548 on 06/07/2005 15:32:29
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 06/07/2005 :  15:31:04  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by X002548


[Hand raised]
So Jeff, you go from fully qualifying column names to SELECT * (in certain instances). I don't think your argument is worth the risk. And in any case, by listing the columns, it's sort of self documenting
[/Hand raised]



Agreed.

Tara
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 06/07/2005 :  15:57:32  Show Profile  Reply with Quote
Thankyou Michael, Tara and Jeff..

Fixed!

I now understand why front end developers get lazy with the database code.. Because the front end is a theoryless, bug ridden nightmare which consumes all your time and turns you into into a loser.

How I pine for the days of looking at profiler for bad queries...

[Goes back to changing layout on a form for the 45 f%$&^ing time...]

DavidM

A front-end is something that tries to violate a back-end.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/07/2005 :  16:16:24  Show Profile  Reply with Quote
I wasn't really saying I thought that was was a good practice, just that I thought it was better.

I personally list them out, especially since I usually generate the column list with a script.

For other developers, I insist. I tend to agree with Tara, because I don't want to have an argument with every developer. I have heard people argue on and one about how much work it is, but I don't buy it. It isn't that much work, I do it myself, and I think it saves a lot of work in debugging.

In any case, they quickly find out that it's even more work to argue with me about this point, since I may decide to pull out the complete stored procedure standards, go over their code in detail, and reject it with about a hundred petty violations of standards.


quote:
Originally posted by jsmith8858

quote:
Originally posted by rockmoose

select a.*, .. other calculations ...
from
 (some derived SQL) a


Which is the opposite of what David said...



... and the same as what Michael said ...



- Jeff



CODO ERGO SUM
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/07/2005 :  16:48:30  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by tduggan

quote:
Originally posted by X002548


[Hand raised]
So Jeff, you go from fully qualifying column names to SELECT * (in certain instances). I don't think your argument is worth the risk. And in any case, by listing the columns, it's sort of self documenting
[/Hand raised]



Agreed.

Tara



Unfortunately, it appears that neither of you fully understand what that "certain case" is, nor the fact that it has absolutely no effect on anything, including self-documentation, other than making the code shorter and "offending" DBA's .

- Jeff
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 06/07/2005 :  17:11:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
We do understand it. Our point (or at least mine) is about making exceptions to the rules.

Tara
Go to Top of Page

raclede
Posting Yak Master

Philippines
180 Posts

Posted - 06/07/2005 :  23:25:50  Show Profile  Reply with Quote
I think the best practice is using the columns names instead of using * esp when creating enterprise applications or just a simple application, also when creating an application using C# for instance, it is advisable to get the value by its column name rather than using index numbers.

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/08/2005 :  10:02:11  Show Profile  Reply with Quote
quote:
Originally posted by jsmith8858
Unfortunately, it appears that neither of you fully understand what that "certain case" is,



Fair enough...let's examine that

quote:

As long as (and this is they key point) the portions of the SELECT that ACCESS TABLES DIRECTLY contains the actual columns you need from those tables.



OK, help me here. Why is this a key point?

quote:

Let's review WHY it may or may not be a good idea to use SELECT * FROM SOME_TABLE (assuming that you want all of the columns returned, of course, which is a common reason AGAINST using * if you don't really need them all, which is legit):



OK, Let's review

quote:

1) for dynamic SQL (client side), the advantage of listing out columns in the SELECT is that it avoids column name lookups, and any table changes cause errors immediately at the SELECT statement, instead of introducing errors that may or may not occur elsewhere in your code as your process the recordset. This is a good thing.



OK...but I wouldn't let you do any client side sql in any of my databases

quote:

2) also for client-side dynamic SQL, if the resulting columns are referenced by their ordinal position this ensures that the columns are in the proper order and it makes the code a lot clearer if you don't have the table schema in front of you.



hmmmm....dynamic sql

quote:

3) for Stored Procs, Views or UDFs, of course, there is no performance disadvantage of using SELECT * since it is precompiled. But the potential issue is that if the underlying tables change, you get weird results! Even though your proc clearly says SELECT *, new columns in the table (added since the proc was last compiled) are missing! And changed columns can cause very weird results (or, hopefully, errors) that, on the surface, make no sense. A simple recompile or rebuild of the proc/view/UDF fixes this, of course. But to someone debugging things trying to figure out what went wrong, that can be pretty tough to figure out.



OK, so it seems that you are making our point, and now I'm confused.

quote:

Any other legitimate reasons to avoid * ?



Sure

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


Jeff, help me out here. Am I misreading something? or did I miss a post?



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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/08/2005 :  10:04:13  Show Profile  Reply with Quote
EDIT: And whatever happened to the rumor that SQL Server was going to eliminate SELECT *?



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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/08/2005 :  10:34:54  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
>>OK, help me here. Why is this a key point?

That's the ENTIRE point, and entire issue of using *, and the only reason why you shouldn't -- when you select directly from database objects.

Again, look at the example:

Select a.*, ....
from
(select col1, col2, col3 from SomeTable) a


The key point, and the only time there can possibly be an issue of using *, is when you select from a database object. That does not occur in this situation. Go through the post again if you need to; what made you think I was advocating dynamic SQL or client-side SQL? I never said you should use it, I simply gave the reasons why you shouldn't use select * from a table.

The 3rd scenerio in which SELECT * can cause issues, which you apparently have agreed to, does NOT apply in the example I have given because in no case is a SELECT * being issued against a database object. All columns in the SQL statement are completely, fully defined by the SQL statement itself.

- Jeff
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/08/2005 :  10:38:14  Show Profile  Reply with Quote
Got it....still ain't worth it....especially if you get real silly with the dml....

And yes, I've posted solutions with the method you suggested. I just woudln't go to prod with it.

Thanks..

Damn hangover...



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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/08/2005 :  10:49:09  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
>>Got it....still ain't worth it....especially if you get real silly with
the dml....

Not worth it? What is the cost? If you get "really silly" with the DML, it is all the more reason to use it, to keep your SQL brief and focused so it is clear where you are introducing additional calculations or columns from other tablese versus bringing in the results of a derived table.

>>And yes, I've posted solutions with the method you suggested. I just woudln't go to prod with it.

That's my whole point; there is absolutely, positively NO reason not to "go to prod" with code like this -- not one -- other than a misunderstanding of why you should avoid * in the first place.

- Jeff
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/08/2005 :  11:02:29  Show Profile  Reply with Quote
Call me gun shy then...I've just looked at so much crap..I feel like Pavlov's dog when it comes to SELECT *

Your point is well taken though...I just won't do it...



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
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.33 seconds. Powered By: Snitz Forums 2000