In this day and age, MAX(ID)


  • Trolleybus Mechanic

     Sigh.

    new_id = DBUtility.ExecuteScalar("SELECT MAX(id) FROM sometable")

    if new_id is Nothing
        new_id = 1
    end if

    DBUtility.ExecuteNonQuery("INSERT INTO sometable (id, name) VALUES (" & new_id & ", 'Some Value');");

    Yes, the version of SQL Server we're using supports Identity fields. No, the code wasn't written 10 years ago. Yes, I'm going home for the evening rather than fixing this.



  • @Lorne Kates said:

     Sigh.

    new_id = DBUtility.ExecuteScalar("SELECT MAX(id) FROM sometable")

    if new_id is Nothing
        new_id = 1
    end if

    DBUtility.ExecuteNonQuery("INSERT INTO sometable (id, name) VALUES (" & new_id & ", 'Some Value');");

    Yes, the version of SQL Server we're using supports Identity fields. No, the code wasn't written 10 years ago. Yes, I'm going home for the evening rather than fixing this.

    Sleep tight. The new version of SQL Server supports sequences... Could be good news, but somehow I've got a feeling that a lot of people will find a way to create even more WTFs with those.



  • Yes, I know someone else who does that. He used "uid" (as in: 'unique id') as a name for a column with neither something like 'auto_increment' nor 'unique' as attributes. However, he did include an 'id' column with both of said attributes.
    Now guess which of the two columns was used as a reference for a foreign key from another table?

    His genius method: Fetch the latest ID (which has to be be the highest one, right?), increment it by one and then use it for a subsequent insert.

    No, he did not lock the table in the meantime, neither optimistically nor pessimistically.

    But it gets better: This database contains the data for all the university's dormitories - and the dorms are only allowed to see the parts of the table which "belong" to them (through a special view). So, what happens when you combine the "get latest id" method with the "get only part of the table" scheme?

    Right.

    This comes from a man who, when asked why he was unable to import the data from the old database, answered: "The data was too normalized!"



  • new_id = DBUtility.ExecuteScalar("SELECT MAX(id) FROM sometable")

    if new_id is Nothing
        new_id = 1
    end if

    DBUtility.ExecuteNonQuery("INSERT INTO sometable (id, name) VALUES (" & new_id & ", 'Some Value');");

    Maybe I'm missing something, but wouldn't this insert a whole bunch of 1s in the id column?

     



  • @Mason Wheeler said:

    Maybe I'm missing something, but wouldn't this insert a whole bunch of 1s in the id column?

    I assumed he got rid of a "new_id += 1" while anonymizing. But, yes, you're right.



  • I was about to try and somehow justify this... but no. I'll admit - the application I currently work on has a separate table to keep track of all the identity fields. Why? Because there is so much embedded SQL in the app itself (shudders) that in order to make it database independent, something had to be done. The syntax was different enough between Oracle and SQL server that they went that way.


  • Trolleybus Mechanic

    @blakeyrat said:

    @Mason Wheeler said:
    Maybe I'm missing something, but wouldn't this insert a whole bunch of 1s in the id column?

    I assumed he got rid of a "new_id += 1" while anonymizing. But, yes, you're right.

     

    Yup. I was blinded by the pain.



  • On the plus side, you now have proof that your coworker is incompetant.

    Seriously, generating unique IDs for primary keys is something that was sorted out forty years go. Any programmer who hasn't leant it shouldn't be allowed to touch a production database.



  • Just make the ID column a BLOB. Then nobody can MAX()+1 it ever again ...



  • @Joel B said:

    the application I currently work on has a separate table to keep track of all the identity fields. Why? Because there is so much embedded SQL in the app itself (shudders) that in order to make it database independent, something had to be done. The syntax was different enough between Oracle and SQL server that they went that way.

    If you don't/can't use a RDBMS-specific technology (like identities or sequences), why don't you use GUIDs? Creating and managing a custom pool of IDs is error-prone and complex. It's like trying to manage concurrency at the application-level... Always a red flag.



    Besides the fact that they are unique, GUIDs can bring a positive performance side-effect. Configure a lower fill factor in your tables, and use the GUID column as a clustered index; because of the random nature of GUIDs the inserts will spread over the entire table instead of creating a hotspot like identity fields. Warning: If you don't set a lower fill factor then doing this will lead to continuous table reorganization - this is what happened when Microsoft gave Windows 7 for free, each download had a unique identifier and because of a high fill factor, the server struggled with continuous table reorg and the download site went down.


Log in to reply