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
 Old Forums
 CLOSED - SQL Server 2005/Yukon
 SELECT * not allowed in Yukon?

Author  Topic 

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-11-13 : 20:17:15
My boss ran the Best Practices Analyzer on one of our development servers and one of the things he traced for was SELECT *. It found a few instances (legacy code that we don't have time right now to change), but the strange thing was what it said. It said that SELECT * would not be permitted in stored procedures or UDFs in future versions. Now that's great, but starting at which version?

Rob, Nigel, Jasper, Vyas, do you know if Yukon has done this?

Tara

robvolk
Most Valuable Yak

15732 Posts
Posted - 2003-11-13 : 21:02:06
So far the beta version allows SELECT * in a sproc and UDF. I'd take exception to a "best practices" analyzer or compiler that disallowed that though. Might as well remove the SELECT * syntax entirely, what would be the point of disallowing it in sproc's and UDF's but allowing it in views and general queries?
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts
Posted - 2003-11-14 : 12:00:47
It's interesting that something scanning for best practices would tell you about something that would be completely forbidden in the future. However, at PASS they did show a little bit of what I will call auto-scripting of stored procedures. They demo'd some features that would build your sproc parameters based on the table definition (like for a simple INSERT sproc). This looked more like what someone would see who builds stored procedures in Enterprise Manager, and nobody here would do a thing like that, would they?

I also heard rumblings that they would detect a data type change in a table and automatically update stored procedure parameter definitions, etc. I didn't see this actually demo'd, and again, I'm sure this is only for those who are making their changes via the Enterprise Manager aspect of Workbench. I could rant on this topic for a long time, but let me summarize as this: 1) SCRIPT IT! and 2) I don't want the computer making changes in other objects for me automatically (at least not without my prior consent on each individual change).

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]
Go to Top of Page

chadmat
The Chadinator

1974 Posts
Posted - 2003-11-14 : 13:19:15
I doubt Yukon will forbid this, however I hope it does happen soon. Usually they talk about forbiding something for a few releases before actually taking it out (To give you time to clean up existing code).

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-11-14 : 13:21:23
Whenever it does happen, it will be a dream come true.

Tara
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts
Posted - 2003-11-14 : 18:44:17
I'd love to see it. It would upset SO many people

Mark, if you want to see how to do cool code generation now, check out <broken record>Codesmith</broken record> from here http://www.ericjsmith.net/codesmith/

Maybe I should write an article about it, this tool saves me SO much time.


Damian
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-12-02 : 15:25:43
It'd be awesome...but do you know how much stuff would begin to blow up?



Brett

8-)
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts
Posted - 2003-12-02 : 16:00:36
stuff blows up now because people use select *
Go to Top of Page

Billpl
Yak Posting Veteran

71 Posts
Posted - 2004-06-03 : 21:53:12
ya know, the more I think about this subject the more I start to disagree.

For starters. When I developing I always use *. That way I can focus on the business object and pick and chose fields as needed. When I'm done I will simply determine which fields are needed and adjust the SP. Plus when I'm in SQL Analyzer (which is always open when I work) I'd hate to have to type in the field names. That would waste countless hours.

Second. What would be the logic in having it removed from SP's & UDF's but not general queries and Views. Wouldn't it be the other way around? The procedure level is where the best decisions can be made about the output data. The guy that's more likely to burn down the server is the arrant client who *'s out some 45 field monster table just to grab a few fields, not the low level guy who's maintaining tightly controlled systems that are prone to RAD business changes, etc.

One thought in the opposite direction. Expanding * functionality. How about something like this "Select a.*( -Field1, -Field6,.. )..." where you could leave off fields instead.

just a thought...
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts
Posted - 2004-06-03 : 22:32:57
As much as I hate to say it.. this is not a good idea. Actually I think they have it the wrong way around..as Bill said.. It should be prohibited from views!

By all means, tell me I suck when the "analyser" finds its use but please, leave it to me!

Bill, the REMOVE keyword is used in only one product I know and the language is not SQL..
FYI, you're wish would be written as "SELECT Employee REMOVE { Field1, field6 }".. There is no need for a "*" simply because it has a real projection operator.

"SELECT Employee" returns all columns, projection is achieved with the "OVER" keyword..

[url]http://docs.alphora.com/D4LGProjectandRemove.html[/url]

DavidM

"Always pre-heat the oven"
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2004-06-04 : 07:29:09
if exists (select * from ...)

select * is really useful to get the column names when you are looking at a system from query analyser.
Would be nice to prevent it in SPs, views, ufd's etc except against temp tables but you would also want to prevent it being used from applications - don't see how you could do that and stil allow it from query analyser.

An app to build SPs based on table defintions and updating when the table structure changes has to be one of the most useless things I've heard of.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2004-06-05 : 16:29:42
:) Well, at least you have an opinion Nigel.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-06-06 : 07:22:10
quote:
Originally posted by nr

select * is really useful to get the column names when you are looking at a system from query analyser.

I have a little SProc that does that - gives me a comma separated list (e.g. for INSERT), each column name on a separate line with a trailing comma (e.g. for SELECT), repeated using friendly name (e.g. SELECT [Customer name] = CustName, ...), DECLARE statement, JOINs for all FKeys and the PKs as a comma separately list (useful for ORDER BY).

I just cut&paste the bit from the results into whatever I'm building (I appreciate that this is mostly available from Object Browser, but there were some style things I wanted of my own).
quote:
An app to build SPs based on table defintions and updating when the table structure changes has to be one of the most useless things I've heard of.

We do that! We have standard SProcs for GET / SAVE and DELETE. These are mechanically generated with defined areas where we add Business Rules etc. When we change the schema of the table we re-run them and compare&merge the "New" and "Actual" versions.

(FWIW our SAVE SProcs are "UpSert" - they INSERT if not exists, otherwise UPDATE. They have another of other abilities (basically to cater for data conversion or parameters originating from Web based FORMs)

Kristen
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2004-06-06 : 20:44:53
>> I have a little SProc that does that
you mean
select name + ' ,' from syscolumns where id = object_id('tbl) order by colid

I always just code it when I need it.

I meant getting the col name to paste into a query.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-06-07 : 05:29:16
quote:
Originally posted by nr

>> I have a little SProc that does that
you mean
select name + ' ,' from syscolumns where id = object_id('tbl) order by colid

Not quite, it does that but in a variety of different flavours so I can cut & paste whichever chunk I need
quote:
I meant getting the col name to paste into a query.

Yup, I do do that - SELECT TOP 10 * FROM MyTable - seems so much quicker to me than clicking around the Object Browser, but its horses for courses.

I'll bung some sample output from my SProc below, using Northwind [Order Detail] as an example. I've had to contrieve the JOINs 'coz my SProc relies on some intermediate data in tables which I haven't got set up in Northwind, so I did find&replace on some of the names to make the example, but you'll get the idea no doubt!.

One slight benefit is that I can pass a partial LIKE table name to the SProc, or even a column name, which saves a bit of typing - it provides a list of suitable EXEC commands if the table name is ambiguous.

Kristen

------------ -------------------------------------------------
Column list: OrderID, ProductID, UnitPrice, Quantity, Discount
      Table: dbo.[order details]
         PK: OrderID, ProductID

SELECT (syscolumns)
--------------------------------------------------------
	SELECT
		OrderID,		-- Order Number
		ProductID,		-- Product Number
		UnitPrice,		-- Unit Price
		Quantity,		-- Quantity
		Discount 		-- Discount
	FROM	dbo.[order details]
	ORDER BY OrderID, ProductID

SELECT (sysproperties)
----------------------------------------------------------------------------
	SELECT
		[Order Number] = OrderID,
		[Product Number] = ProductID,
		[Unit Price] = UnitPrice,
		[Quantity] = Quantity,
		[Discount] = Discount
	FROM	dbo.[order details]
	ORDER BY OrderID, ProductID

DECLARE
----------------------------------------------------------------------------
DECLARE	@OrderID		[int],	-- Order Number
	@ProductID		[int],	-- Product Number
	@UnitPrice		[money],	-- Unit Price
	@Quantity		[smallint],	-- Quantity
	@Discount		[real],	-- Discount

PK Columns
---------------------------------------------------------
OrderID,
ProductID,


JOIN Description                              Parent Column  Child table
---- ---------------------------------------- -------------- ---------------
J    Order ID:Order Header                    OrderID        [Orders]                                 

	SELECT TOP 10 * 
	FROM	[order details] OD
		LEFT OUTER JOIN dbo.[Orders] OI
			  ON OI.OrderID = OD.OrderID

JOIN Description                              Parent Column  Child table
---- ---------------------------------------- -------------- ---------------
J    Product Code:Product                     ProductID      [Products]          
			 
	SELECT TOP 10 * 
	FROM	[order details] OI
		LEFT OUTER JOIN dbo.[Products] P
			  ON P.ProductID = OI.ProductID

JOIN Description                              Parent Column  Child table
---- ---------------------------------------- -------------- ---------------
D    Order ID:Order Item                      OrderID        [order details]       

	SELECT TOP 10 * 
	FROM	[Orders] OI
		LEFT OUTER JOIN dbo.[order details] OD
			  ON OD.OrderID = OI.OrderID


			 
-- Use this query to produce a column list with a count of the number of rows (where used)
------------------------------------------------------------------------------------------
SELECT '	OrderID,', '--', 
	(select SUM(CASE WHEN OrderID IS NULL THEN 0 ELSE 1 END) from [order details])
UNION ALL SELECT '	ProductID,', '--', 
	(select SUM(CASE WHEN ProductID IS NULL THEN 0 ELSE 1 END) from [order details])
UNION ALL SELECT '	UnitPrice,', '--', 
	(select SUM(CASE WHEN UnitPrice IS NULL THEN 0 ELSE 1 END) from [order details])
UNION ALL SELECT '	Quantity,', '--', 
	(select SUM(CASE WHEN Quantity IS NULL THEN 0 ELSE 1 END) from [order details])
UNION ALL SELECT '	Discount,', '--', 
	(select SUM(CASE WHEN Discount IS NULL THEN 0 ELSE 1 END) from [order details])

-- Use this query to produce a column list with Max Length of the data in that column
-------------------------------------------------------------------------------------
SELECT '	OrderID,', (select MAX(DATALENGTH(OrderID)) from [order details])
UNION ALL SELECT '	ProductID,', (select MAX(DATALENGTH(ProductID)) from [order details])
UNION ALL SELECT '	UnitPrice,', (select MAX(DATALENGTH(UnitPrice)) from [order details])
UNION ALL SELECT '	Quantity,', (select MAX(DATALENGTH(Quantity)) from [order details])
UNION ALL SELECT '	Discount,', (select MAX(DATALENGTH(Discount)) from [order details])
Go to Top of Page

aiken
Aged Yak Warrior

525 Posts
Posted - 2004-09-20 : 18:05:53
I'll resurrect this one.

First, for getting column names, what's wrong with "sp_help tablename"? It's easier to type, and it shows you the datatypes as well.

Second, for "if exists", why not "if exists (select 1 from tablename where...)"?

Me, I'm happy about this change.

Cheers
-b
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-09-21 : 07:21:26
quote:
Originally posted by aiken
Second, for "if exists", why not "if exists (select 1 from tablename where...)"?


this is acctualy the only exception when (select * ...) should be used. has to do with indexes. sql server chooses the best index to execute this and so it's faster.
i read it on this very forum

Go with the flow & have fun! Else fight the flow
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2004-09-21 : 07:42:11
where?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-09-21 : 07:56:54
as soon as the search page doesn't time out on me i'll try to find it.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-09-21 : 08:19:48
found it:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22834

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-09-21 : 13:59:44
"what's wrong with "sp_help tablename"?"

It doesn't present the columns in a style suitable for use in SELECT or INSERT etc. which is what I wrote my little SProc to do instead.

I have since added the ability to create a SELECT list suitable for INSERT statement and XML in/output:

SELECT
	sop_ordh_ID		= 9999999,	-- Order ID [Identity]  [PK] 
	sop_ordh_Date		= 'ddmmyyyy',	-- Date
	sop_ordh_cust_Code	= 'XXX30',	-- Customer Code
	sop_ordh_TotalPrice	= $99999.99,	-- Total Price
...
FROM	dbo.DNG_SOP_ORDH_OrderHeader
ORDER BY sop_ordh_ID

where the required type of the field is indicated (mainly used for importing from "weird" sources, hence need to constrain input to appropriate type and length (my SProc is aware of the superset-datatypes we use, hence 'XXX30' implies max 30 alphanumerics, in a forced Capitals field)

and

SELECT
	[Tag] = 1,
	[Parent] = NULL,
	[DNG_SOP_ORDH_OrderHeader!1!sop_ordh_ID!element]	= 9999999,	-- Order ID [Identity]  [PK] 
	[DNG_SOP_ORDH_OrderHeader!1!sop_ordh_Date!element]	= 'ddmmyyyy',	-- Date
	[DNG_SOP_ORDH_OrderHeader!1!sop_ordh_cust_Code!element]	= 'XXX30',	-- Customer Code
	[DNG_SOP_ORDH_OrderHeader!1!sop_ordh_TotalPrice!element] = $99999.99,	-- Total Price
...


Kristen
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site