Preventing usage of "SELECT *..."

Question

Preventing usage of "SELECT *..."

Viewing 6 posts - 136 through 140 (of 140 total)

You must be logged in to reply to this topic. Login to reply

Geoff-577403 SSC Journeyman Points: 78 More actions · Answer 1

Ninja's_RGR'us (2/13/2011)
Geoff-577403 (2/13/2011)
This can be done a lot easier with SQL 2008 Policy Management Feature.
:smooooth:
How?

Isn't it interesting how the order of two words can change the whole meaning of a question .....

"Can this be done a lot easier with SQL 2008 Policy Management Feature"

- Not to worry, I don't think it can.

:blush:

ajolson1964 SSC Veteran Points: 224 More actions · Answer 2

So as a young developer myslef. What are the pitfalls of using the Select * ?

TomThomson SSC Guru Points: 104773 More actions · Answer 3

ajolson1964 (2/15/2011)
So as a young developer myslef. What are the pitfalls of using the Select * ?

There is one real pitfall and one maybe pitfall:

1) the real pitfall: if a lot of the columns aren't needed, you are passing a lot of data around; if this is going between a server and a client over a limited bandwidth network that can be a pain.

2) the maybe pitfall: if some app developer has written code that breaks if it gets more columns than it expects, using select * means that schema changes will break the app if an extra column has been added to the view or table that the app uses. My view is that in almost every case an app developer who writes such code is incompetent, and if he can do things like that he's probably going to do other stupid and dangerous things too, so maybe this isn't a pitfall but a good detector of bad app programming.

Select * should NEVER be used in an app, because of 1 above.

It should ALWAYS be used in a stored procedure or view whose function is to deliver ALL the available data about some object, since if it isn't used that SP or view has to be rewritten when the schema changes; if it is used the SQL Server optimiser will automatically recompile an SP next time it is used (no development action required) and a view has to be recompiled (which doesn't happen automatically, unfortunately) but does not have to be rewritten.

Tom

ajolson1964 SSC Veteran Points: 224 More actions · Answer 4

Thanks.

When I posted the question I had not read pages 10 - 14 that somewhat covered the reasons for not using it. Your answer got to the heart of the matter.

Really goes to knowing the data, # columns ,etc as well.

I do see the use of it when in preproduction but as a matter of routine use in production , views yada yada yada,yea not a great idea.

thanks again for the response

Manie Verster SSCertifiable Points: 7152 More actions · Answer 5

cphite (2/14/2011)
Interesting solution, but honestly I think a far better solution is to train your developers to avoid doing things like SELECT * in production code, along with having a good code review process. Personally, I find SELECT * to be extremely useful when starting to build a query, especially when a database has less than desirable table and column names.

I fully agree with you. When starting to create a query I always use select * BUT I never use * in an aggregate function like count(*) etc. To me it is totally wrong to do that, I rather use a column in my table.

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

sebastien.delestre Grasshopper Points: 11 More actions · Answer 6

Hello,

With SQL Server 2008 (at least) there is a problem

DENY SELECT ON OBJECT::MA_TABLE(DUMMY_COLUMN) TO User1;

work like a charm (return an error) when I try to make a "SELECT *"

but in a stored procedure I can do a "select *"

CREATE PROCEDURE [dbo].[myProc]

AS

BEGIN

SELECT * FROM MA_TABLE;

END

Why ? I need to get an error when I execute a stored procedure which contains a "SELECT *"

Regards