SQL pet peeve

[Commie Pinko Dirtbag]

NEVER rely on field order in a table!

This is EVIL:

INSERT INTO mytable VALUES(my_id, my_name, my_ssn)

This is GOOD:

INSERT INTO mytable (id, name, ssn) VALUES(my_id, my_name, my_ssn)

That was you CPD IT rant of the day. We now return to our scheduled programming

[LSK]

1. i ran into that a few weeks ago

Sometimes you can get away with it when making a backup of a table.

insert into backup_table values select * from original_table

[DS1]

2. HAH! Loser

When I make a new table I have a script that inserts every ASCII variation possible into the fields. That way when I want to single out a row of possible data, I run a Delete FROM where the row != to the data I want to keep. Then the stored procedure makes a NEW table with all the data in it, just with a 1 tacked onto the end of the table name.

It does wonders for performance.

[billyskank]

4. That is the stupidest thing I have ever heard

Well done! :thumbsup:

[DS1]

6. Thank you

:bounce:

[billyskank]

8. Actually, it's the second stupidest thing I've heard

The stupidest thing I ever heard actually happened, at my previous job. We commissioned this system from a Dutch firm (I think they must have been based next door to an Amsterdam headshop, or something) to allow us to dial and remotely read electricity meters.

Every meter has a number of meter channels, and some bright spark thought that it would be a major performance hack to have every set of meter channels in its own table! Yes, one table per meter. The database was queried using dynamic SQL.
:bounce:

It's the only database I've ever seen where you needed to put hints in to query the data dictionary. :o

Woe betide you if you tried to browse the schema using TOAD.....chug...chug...chug... x(

[DS1]

13. wow

But I maintain my idea is dumber - unless you have a dedicated transaction log server that can keep up with a few hundred billion or so rows of changes per user event :D

[UncleSepp]

3. SQL peeve of the day for me...

Writing pieces of a filter clause into a join:
...
from Table1
inner join Table2 on
Table1.Key = Table2.Key
and Table2.CodeForGodKnowsWhat = 42
...

Now imagine about thirty of those in a single stored proc, written several years ago, by a guy who wasn't a dev, and has now left to sail the world or ride yaks or anything else but support his code. Argh!

[no name no slogan]

5. SQL is for wussies

flat files, now THAT's where it's at

[DS1]

7. My ISAM would kick your txt files ass!

[billyskank]

9. Come now, break it up

Because as we all know, Real Programmers use Fortran.

http://www.pbm.com/~lindahl/real.programmers.html

[no name no slogan]

10. Fortran? FORTRAN?

Screw that! I just flip the switches on the front of the box in a pre-determined order and watch the LEDs light up.

I bet you even use punchcards, too, huh? Wuss. :silly:

[billyskank]

11. Well that is pretty hardcore

I do have to admit. :)

[no name no slogan]

12. Whew, I thought you were going to say "Luddite"

;)

[Ellen Forradalom]

14. It seems so obvious

Yet I've maintained such INSERT statements. I know about them because the rug was yanked out from under them after a little 'ALTER TABLE' statement and I had to go clean it up.