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

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.

---

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

---

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

quote:

---

Originally posted by rockmoose

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

Which is the opposite of what David said...

---

quote:

---

Originally posted by rockmoose

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

---

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

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]

---

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

---

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

---

quote:

---

Originally posted by jsmith8858
Unfortunately, it appears that neither of you fully understand what that "certain case" is,

---

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.

---

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):

---

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.

---

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.

---

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.

---

quote:

---

Any other legitimate reasons to avoid * ?

---