SELECT * in Programs

[RG]

As far as I can remember, this has been a "no no". But what would you answer
developers who say " We want the program to bomb when the table is out of
sync with program definiton"?

Thanks in advance

[Jeroen Mostert]

I'd say "if you really want to do that properly, retrieve the schema and
compare that to your desired baseline, instead of taking a performance hit
by forcing the server to select columns you don't need".

That, and "you're asshats -- you're forcing me to replace that table with a
view if I want to change its definition in a way that's not supposed to
impact your program, just because you've decided I shouldn't want to do that".

Developers should not want their programs to "bomb", they should want to
make them robust. That means not making overly broad assumptions. Only when
your *necessary* assumptions are violated should you take care to stop
execution before bad things happen. It's possible for a program to need a
table to have exactly a particular schema and no other to run correctly, but
it's not likely.

--
J.

[Sylvain Lafontaine]

Well, you don't explain how this program should *bomb* if the table get out
of sync with the program definition.

It's an error to say that any program will bomb if it get out of sync with
the table definitions and often, you end up instead with a moving target;
with the possibility that sometimes the program with bomb but in other
times, you might simply get a subtil bug that might require a long and hard
time to find (if it's ever found).

You might even end up with a bug that like Voldemort, will appear,
disappear, going dead and then resuscitate and repeats the cycle after that.

--
Sylvain Lafontaine, ing.
MVP - Access
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

"RG" <R...@discussions.microsoft.com> wrote in message
news:AE699777-2C0D-4A50...@microsoft.com...

[John Bell]

On Mon, 30 Aug 2010 10:33:03 -0700, RG <R...@discussions.microsoft.com>
wrote:

If they want to cause users discruption and heartache let them take
responsibility for it. What message are they going to give the users
will they know what to do... I think not. A program should not bomb
regarless of reason, it sounds like they are just being lazy. If you
need to do something to check the client do it when the program is
initialised.

BTW SELECT * is acceptable when checking existance i.e IF EXISTS (
SELECT * FROM ... WHERE .... ) as the columns do not have to be
checked.

John

[Erland Sommarskog]

That there is no guarantee that anything will bomb. Say that the table has
six bit columns at the end. You as the DBA decide that the sixth-to-last
column is useless, so you drop it. At the same time you add a new bit
column.

It is not very likely that something will bomb immediately after this.
If these bit columns are 0 90% of the time, it may even slip the tests.
But sooner or latter one bit will be in the wrong place, and then...

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[RG]

"Sylvain Lafontaine" wrote:

> .
>

[Gert-Jan Strik]

As mentioned by others: this "response" seems like a silly idea. Why
would you want to write fragile code?

Here are a few reasons why you shouldn't use SELECT * in production
code:
http://www.xs4all.nl/~gertjans/sql/dont-use-select-star-in-production-code.html

--
Gert-Jan

[--CELKO--]

1) Use
[NOT] EXISTS (SELECT * FROM .. WHERE..)

because this predicate deal with whole rows. There is an interesting
history about this in SQL FOR SMARTIES, but I will not go into it
here.

2) Mixed feelings, but I find I am writing it to expose computed
column names in a containing SELECT.

SELECT XX.*
FROM (SELECT a,b, ROW_NUMBER() OVER(..) AS x, ..
FROM Foobar, ..
WHERE ..) AS XX
WHERE XX.x = ...;

The outer SELECT is a shell that can see the XX.x column. It is a
bitch to reproduce the inner SELECT column list with the alias names
over and over as XX(a, b, x, ..)