The Database Giveth and Taketh Away

  • Comments posted to this topic are about the item The Database Giveth and Taketh Away

  • Problem with automation is that it also causes people to be sloppy, as in "the system will fix it / alert me".

    I'm guessing you wrote that article in a program with spell checking and yet your last sentence probably isn't correct. I'm guessing "great" should be "greater". The word by it self is correct. But not in that context. So do i think those kind of errors will stop... not until we as humans are redundant. We are just replacing... "oups i did wrong" with "oups the system should have fixed that"... the "oups" part will still be there.

    "while also letting me know that setting a price to $150 might be wrong when all other prices are $1500 or great."


  • AI and automation strikes me as a great way to make mistakes faster. AI is great for recognising deviation from well-defined criteria, but the woolier the criteria get, the more work is required to make sure that the AI is properly tuned. Even then you're still going to get mistakes as you can't tune for everything, and how much work do you want to put into tuning an AI rather than just teaching people to be more careful and sanity check each other? A big part of an earlier role that I had to deal with bad data, was to teach the coders to properly catch the previous state of any data changed and write (& test) a rollback script for each change. That had the added benefit of making the coders better all round as it forced them to think much more carefully through their changes in general.

  • I believe the T-SQL language could benefit from assertions, perhaps declared using an new ASSERT clause. For example, the following query would abort with an error when the actual rowcount exceeded 1000.

    SELECT A, B, C
    FROM MyTable
    WHERE X = 10
    ASSERT (MAXROWS = 1000);

    Assertions could also be created on DML operations. For example the following UPDATE contains an obvious bug, and when executed would immediately abort and rollback with an error explaining that number of rows affected 1,488,721 exceeded the assertion.

    UPDATE Accounts
    SET Balance = 0, PostDate = GETDATE()
    WHERE @AccountNumber = @AccountNumber

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • That's a very interesting idea. I like it

  • This was removed by the editor as SPAM

  • Of course, most of us already code assertions (more or less) into our T-SQL, and this type of defensive coding may constitute half of the lines of code written for a stored procedure. But these non-declarative assertions require multiple lines of code, an explicit transaction block, logic to rollback the transaction, etc. and this programming itself can introduce bugs.

    Also, non-declarative assertions require that the SELECT or DML operations actually run to completion before the results can be validated. However, with declarative assertions, the query engine can determine at runtime if the statement will exceeded a threshold and then terminate the operation early without wasting time and unnecessary transaction logging.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 7 posts - 1 through 6 (of 6 total)

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