• (unregistered)

    Goodie, first poster YEAH.  Just another case of Upper Management knowing nothing about the technology they are incharge of.

  • (cs) in reply to

    [8]Scripts may change me
    But they dont sort columns[8]

  • (unregistered)

    You would be amazed at the performance benefits of alphabetizing.....   It's a new Oracle feature.   ;-)

  • (cs)

    [8][8] dumb dumb dumb!

    dumb dumb dumb! [8][8][8]

  • (unregistered)
    1. What virtue is there in alphabetizing the columns? Do you have so many columns in your tables that you can't find them? That's kindof a red flag.
    2. If this is the policy, why not make the devs write change scripts that re-order the columns? You could use Red-Gate SQL Compare to do the hard parts for you. This way, the changes are repeatable. What happens when your DBA screws up the process in production? I make that kind of mistake all the damn time.

    [email protected]

  • (cs)

    >>If this is the policy, why not make the devs write change scripts that re-order the columns?

    Exactly. Even Enterprise Manager will generate the scripts for you that do this, you don't even need a 3rd party product.

    (Of course, this doesn't excuse the "policy" ... that's ridiculous)

  • (cs)

    I believe you can reorder the columns in Enterprise Manager.  Just drag and drop.

    And if this was really that big of an issue, you could theoretically use a script to alphabetize the columns through syscolumns manipulating the colorder column.

  • (unregistered)

    One day, all of our primary/foreign keys and unique constraints disappeared from a group of tables in our production environment. This secretly wreaked havoc on our system for a few days until we noticed it, because some parts of our system relied on the SQL server failing inserts/updates that violated those constraints.

    It is suspected that this 'feature' of Enterprise Manager, used by someone (probably an exec/VP) who should not have had access to the production database, is the reason why it happened.

  • (unregistered)

    I experienced a similar "hardware WTF" from an anal retentive sysadmin. One of our RAID 5 drives failed, no problem since that's what RAID 5 is all about. All you have to do is swap out the bad drive and it will rebuild.

    But not this guy. The failed drive was in the middle of the cage, and he got the bright idea to <FONT color=#ff1493>move down all the other drives</FONT> so the new drive would be at the end of the cage. Mayhem ensued of course since that totally hosed the drive array. For extra WTF credit, the most recent backup tape had <FONT color=#ff1493>already been sent to offsite storage</FONT> and would have taken a day to retrieve, so we had to restore from an older backup to prevent significant downtime.

  • (unregistered) in reply to

    Bizarre, especially when you consider that the only time anyone would know what order the columns are in is if the execs are being allowed to 'select * from ...' which is a very dangerous thing all by it self.  All the programmers, IT staff and such should be getting what they need by column name anyway at which point specifying they are column order explicitly... even if its the same as the definition order.

    <FONT style="BACKGROUND-COLOR: #efefef">I've found putting the database in 3rd Normal keeps most of the execs out of the actual tables.  You then given them some nice views to play with.  In this situation you could then update the tables as require and either not tell the bosses about the new columns that control some goofy new feature they arn't interested in.  Or you also supply an new view definition with the new columns in their proper alphabetical place.</FONT>

  • (unregistered) in reply to
    :

    <font style="background-color: rgb(239, 239, 239);">Or you also supply an new view definition with the new columns in their proper alphabetical place.</font>



    I wish everything in life could have views.

    "No boss, I did it the way you asked -- using only goto statements.  Just check out the source code through the view called "boss_view.java"
  • (unregistered) in reply to
    [image]  wrote:

    <FONT style="BACKGROUND-COLOR: rgb(239,239,239)">Or you also supply an new view definition with the new columns in their proper alphabetical place.</FONT>



    I wish everything in life could have views.

    "No boss, I did it the way you asked -- using only goto statements.  Just check out the source code through the view called "boss_view.java"
    Author: Anonymous
    Me Too [:)]
  • (cs) in reply to Phil Scott

    Phil Scott:
    I believe you can reorder the columns in Enterprise Manager.  Just drag and drop.

    And if this was really that big of an issue, you could theoretically use a script to alphabetize the columns through syscolumns manipulating the colorder column.

    The result of "dragging and dropping" in EM is the script that Alex describes in his initial post.

    As for the second point, why don't you try that and let us know how it goes? [8-|]

    Good point from someone else about the Views -- all report writers and any other "ad-hoc" access to the DB should be done through views anyway.  That would be the perfect way to alphabetize the columns to keep the "higher-ups" happy.

  • (unregistered)

    in MySQL, the ALTER TABLE command can specify where to put the column. after the column definition, you can put "FIRST" or "AFTER other_column"

  • (cs) in reply to
    :

    <FONT style="BACKGROUND-COLOR: #efefef">I've found putting the database in 3rd Normal keeps most of the execs out of the actual tables.  You then given them some nice views to play with.  In this situation you could then update the tables as require and either not tell the bosses about the new columns that control some goofy new feature they arn't interested in.  Or you also supply an new view definition with the new columns in their proper alphabetical place.</FONT>

    This is exactly what we do. We have a CEO that likes to micro manage the database sometimes so we gave him a copy of Golden, some scripts, and access to views only for his db login.

    Everyone's happy.

  • (unregistered) in reply to
    :
    [image]  wrote:

    <FONT style="BACKGROUND-COLOR: rgb(239,239,239)">Or you also supply an new view definition with the new columns in their proper alphabetical place.</FONT>



    I wish everything in life could have views.

    "No boss, I did it the way you asked -- using only goto statements.  Just check out the source code through the view called "boss_view.java"

    You know...[^o)] this could be done with a 'specialized' CVS just spawn off a boss tree you call 'Final' or something important like that and then just deploy off the 'Development' branch.

  • (cs)

    I heard this story second hand, but from 2 reliable sources. My former manager had read an article saying that for performance reasons, Sybase stored procedures should all fit on a page. Do you see where this is going???

    So she had a programmer rewrite all the stored procs. He used a really small font, but still had to break them up into multiple procs. When he was all done, the system was slower, so the manager had him put them back the way they were. He quit shortly after.

  • (cs) in reply to Rick
    Rick:
    So she had a programmer rewrite all the stored procs. He used a really small font, but still had to break them up into multiple procs....


    Well there's the problem...everyone knows your font size has to be a power of 2 for the best performance....;)
  • (unregistered)

    I can just see the day where some other dumb user (as it is probably possible at that places) uses a query like "select * from blah" and then and then reads the column by order rather than by name.  They'd have fun tracking down that problem.

  • (cs)

    He should just convince his boss that in real relational databases the column ordering is actually not guaranteed and cannot be assumed. Possibly run scripts on random evenings to re-order columns randomly. And that therefore specifying alphabetic ordering is impossible.

    Or you could just move all the data to XML.

  • (cs) in reply to EWilson
    EWilson:

    Or you could just move all the data to XML.



    Just imagine how much work it would be to use a text editor to manually alphabetize each XML record.

    Yes, you can use XSL or a script, but text editors are so much wtf-friendly. (And XSL doesn't guarantee an 'obvious' order in all cases anyway.)
  • (unregistered)

    Or you could just use JavaScript. [:P]

  • (cs) in reply to

    :
    Or you could just use JavaScript. Stick out tongue

    Preferably by having a stored procedure generate the Javasript.

  • (unregistered)

    Luckily, I was able to talk my DBA into giving me complete control over at least my development db schemes. So I can drop tables / create tables at will. [<:o)]

    Naturally, I provide him with alter table scripts for production, which he then rewrites to his gusto (mostly reformating them to his OS390 standards) before execution... [:^)]

    This just reminds of a few EBCDIC/ASCII hassles, but that's a completely different story which I won't go into here [:'(]

    -- Daniel

  • (cs)

    Perhaps someone should familiarize the MGT with a little thing called an 'index'...

  • (cs) in reply to Schol-R-LEA

    Please ignore that last post. I can only blame temporary stupidity for that statement.

  • (unregistered)

    Now that would be a REAL wtf... reading the table, sorting the data, dropping the table, and reinserting to make sure the data was alphabetized too :D

    I think that would be wtf-overload... not even a college student would try that one!

  • (unregistered) in reply to TheDan666
    TheDan666:

    [image]  wrote:
    Or you could just use JavaScript. Stick out tongue

    Preferably by having a stored procedure generate the Javasript.

  • (unregistered)

    OT: why is it that this site loads like crap? everytime i stumble on some .asp shit i get annoyed by slow downloads, incomplete(!) downloads, layout fuckups. i have to reload at least once in a while to make it viewable.

    stupid asp/windows not so good?

  • (unregistered)

    An even worse problem from this practice is that it will create problems where other people have used the poor practice of "INSERT INTO SELECT *" on the assumption that the columns will match up.

    Imagine that all of a sudden your column order changes break, and all the wrong data ends up in the wrong fields!

  • (cs)

    So do any of you bitches have the spec for this system?

    obWTF: Is is possible that so many people have never heard of trolls before and also cannot detect sarcasm, or is it just that the only persons who post indignant responses to obvious jokes and/or trolls are the small group of people Not Getting It while everyone else does?

  • (unregistered)
    Alex Papadimoulis:
    To get around this, Enterprise Manager does the following:

    • Creates a temp table based on the original table
    • select * from original table into temp table
    • drops original table
    • recreates original table with new column order
    • select * from temp into new
    • drops temp table


    why does Enterprise Manager 2 times create a new table and copy all the data? it can be done easier:

    • rename the original table to temp
    • create a new table with original name and new column order
    • select *  from temp into new table (with original name)
    • drop temp table
    of course, the problem with foreign keys remains ...
  • (unregistered)

    Gaaaarh. Just lost (another) few thousands of brain cells reading this post! [:|]

    Knowing that it may be a WTF to do so in general, what would happen if table columns are referenced via index number (like in C# : dr.GetString(2))?

    A procedure like this (resorting columns) would definitely f**k up some of my (production) code...

  • (unregistered) in reply to

    ... table columns ... referenced via index number....

    Indifferent

  • (unregistered) in reply to

    <FONT size=2> </FONT>

    Gaaaarh. Just lost (another) few thousands of brain cells reading this post! Indifferent

    Knowing that it may be a WTF to do so in general, what would happen if table columns are referenced via index number (like in C# : dr.GetString(2))?

    A procedure like this (resorting columns) would definitely f**k up some of my (production) code...

    You have production code that <relies on the order of columns>, now that is a WTF
  • (cs)
    Alex Papadimoulis:
    The WTF appears when one learns that the DBA never actually runs the scripts the dev team sends! The DBA is under orders from on high to read the script and manually add / modify the columns via Enterprise Manager! Why you ask? So that the new columns can be inserted into the table in alphabetical order rather than at the end...
    Somebody find out the DBA's salary and calculate the cost of modifying table schemas.....
  • (unregistered)

    There must be more to this "alphabetized columns" thing. How do they ensure the primary key fields are first in the list? Or are they?

  • (unregistered) in reply to icelava
    icelava:
    [image] Alex Papadimoulis wrote:
    The WTF appears when one learns that the DBA never actually runs the scripts the dev team sends! The DBA is under orders from on high to read the script and manually add / modify the columns via Enterprise Manager! Why you ask? So that the new columns can be inserted into the table in alphabetical order rather than at the end...
    Somebody find out the DBA's salary and calculate the cost of modifying table schemas.....
  • (unregistered)

    At my employer, all customer applications must use DB2 for some reason. The only problem is, most internal apps use MS-SQL or Oracle.

    The solution?

    Dump whatever data you need in hourly/daily/weekly batch jobs into text files, and import into DB2. Policy sucks.

  • (cs)

    Just please someone tell me why they have to be alphabetized.

    For bonus points, can someone post a written policy with this in it??

  • (unregistered) in reply to

    Cunning naming strategies and a big thesaurus...

  • (cs)

    I believe this is a hold-over from Sybase- which Microsoft bought in order to create Sql Server.  Not sure the benefits of ordering columns-  except for human readability.

  • (cs) in reply to
    :
    <FONT size=2> </FONT>

    Gaaaarh. Just lost (another) few thousands of brain cells reading this post! Indifferent

    Knowing that it may be a WTF to do so in general, what would happen if table columns are referenced via index number (like in C# : dr.GetString(2))?

    A procedure like this (resorting columns) would definitely f**k up some of my (production) code...

    You have production code that <relies on the order of columns>, now that is a WTF
  • (unregistered)

    Thats absolutely stupid. Who cares what order the columns are in, thats what SQL is for.
    If I was on the dev team at that company I would go postal.

  • (cs)

    Knowing that it may be a WTF to do so in general, what would happen if table columns are referenced via index number (like in C# : dr.GetString(2))?

    If you are doing a SELECT * FROM TABLE, then using the field by index, you deserve your fate.

    If you are doing SELECT Id, Name, Company FROM TABLE, and using the index, you code will be fine as long as you don't change the query.  It doesn't matter what order they are in in the database, you will receive them in the order you ask for.

  • (cs)

    With wonderful, beautiful .NET, even if you specify column names in your select statement you will sometimes get random column reordering unless you use <FONT size=2>GetOrdinal("columnName") to retreive the column index.</FONT>

  • (unregistered)

    All of the above criticisms still stand. As people have said – column order in a table is irrelevant and you’re “lucky” at best to get the columns back in a SELECT * in the same order you’ve created the table. As a side effect of this note the SQL Server is under no contract to *physically* place your attributes on disk in the same “order” as specified in the CREATE TABLE statement.

    <o:p></o:p>For example, Sybase ASE has a “bit” datatype (I can’t recall if SQL Server has it, too) which will coalesce all the bit columns in the table into a single column on-disk (sort of) regardless of the order in the create table statement.

    So yes, that policy is stupid, horrible, double-plus ungood, etc.

  • (cs) in reply to

    :
    Cunning naming strategies and a big thesaurus...

    Indeed:

    • AAA_CustomerID
    • AAB_FirstName
    • AAC_LastName
    • ...
  • (cs) in reply to EWilson

    Tinoh, re: " obWTF: Is is possible that so many people have never heard of trolls before and also cannot detect sarcasm, or is it just that the only persons who post indignant responses to obvious jokes and/or trolls are the small group of people Not Getting It while everyone else does?"

    As was well put in another thread, but relating to this, sarcasm, with the lack of any emoticons or other markup indicating sarcasm, is difficult to detect in that there are plenty of people who would post things seriously that one might consider sarcastic due to how incredibly stupid it sounds.  How are you to know which is sarcastic and which is stupid?  Do you possess some magic ability that few of us are gifted with?  I am sure sad to be in the "small" group of Not Getting It, I'm usually in the Other Group.

    Regarding trolls, they are also difficult to detect, though somewhat easier than detecting sarcasm.



  • (unregistered)

    So they don't have a DBA at all, just a file clerk apparently.   Otherwise, why would "Developers often need to add columns to database tables"...

Leave a comment on “Database Ch-Ch-Ch-Ch-Changes”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article