A Tiny Trauma

  • Comments posted to this topic are about the item A Tiny Trauma

  • Very appropriately titled 🙂  I use tinyints in some specific cases, and had not realized they are not allowed to go negative.  I will certainly take that into consideration moving forward before assigning that data type to a column in the future.  Thanks for sharing!

  • Would the overhead have been too much to go with case when ResendLimit>ResendCount then ResendLimit – ResendCount else 0 end as RetriesLeft,
    My own tendency is to go with minimal rewriting, but that doesn't always translate into efficient code.


    Puto me cogitare, ergo puto me esse.
    I think that I think, therefore I think that I am.

  • Good article.  I'm curious though.  Should the number ever be negative in such a case?  I'm thinking that the TINYINT actually caught an anomaly for you.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks srienstr.
    That was the main option I considered (along with casting within the subtraction).
    I eventually decided against it - less on the overhead grounds - more because I or someone else may do some similar query in the future but not use that view.  
    Since there were less than a million rows, and no referential integrity issues etc, changing types seemed to be the cleanest.  Both work.
    In hindsight, I think correcting the data types reduces the 'Technical Debt' slightly over 'bodging' the calculation, so I am happy with my approach.

  • Thanks Jeff.

    Wow, nearly 1,000,000 points. I am in awe.  Just as well SQLServerCentral didn't use even a smallint for that (and they should watch out if it is numeric(6,0)...).

    I had originally completely overlooked the possibility of someone changing the ResendCount AFTER the resends had been done - making the derived count of resends available negative.  Anomaly, well, not really, perfectly reasonable real-world behaviour, settable through the user interface.  In fact, after this I reviewed the counts and reduced quite a few (after ensuring it would be happy with negative retries), so you are slightly correct in as much as it brought it to my attention and improved things overall.  But I'll certainly be more careful with TinyInt in future.

  • The tinyint is not your problem. Sure, after changing it to smallint, you will not receive an aritmetic overflow exception, but your data will be invalid (negative). So you only hide the problem instead of to solve it (and depending on the number of rows in your table, you are wasting disk space (3 byte per row)

    God is real, unless declared integer.

  • I probably would have done a re-cast in the view.

    CAST(ResendLimit AS SMALLINT) - ResendCount as RetriesLeft

    But it depends on if we want to retain invalid data or prevent it from ever being inserted/updated in the first place. If ResendCount should never be more than ResendCount, we don't even want bad data in the table, then consider leaving the column definitions as is but add a check constraint on the table to prevent that update scenario from ever occurring. 

    , CONSTRAINT CC_ResendCount CHECK (ResendCount <= ResendLimit)

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

  • thomas_franz - Wednesday, February 6, 2019 6:30 AM

    The tinyint is not your problem. Sure, after changing it to smallint, you will not receive an aritmetic overflow exception, but your data will be invalid (negative). So you only hide the problem instead of to solve it (and depending on the number of rows in your table, you are wasting disk space (3 byte per row)

    Actually, it would be wasting 1 byte per row.

    TINYINT 1 byte
    SMALLINT 2 bytes
    INT 4 bytes

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

  • Hi Eric and Thomas

    It's a matter of timing.

    In the application as it stands, the ResendLimit says the max times the relevant communication can be resent - if instances were to be resent now.
    It is perfectly plausible for this to be set to say 5 initially, then someone decides it should really be 2 or 0 for whatever reason.
    Future resends will adhere to the new limit, but old communications may have been sent more than the current limit.
    Arguably, the limit and the count should both be on the individual log records, so we'd be comparing the limit to the count in force at the time the record was generated, but that is overkill (and besides, we may want it to conform to the current limit whilst the resends are happening).
    If the value of ResendLimit - ResendCount is negative, this is telling us the communication was sent more times than the current limit, but is not an error as such.

    I think I can live with a byte extra per row, cost is a fraction of a cent (or a second of my time).

  • If space were an issue there's always row\page compression which would give you variable length storage on integers under the covers:

    https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/row-compression-implementation

    We tend to avoid tinyint for similar reasons to the article and then use compression to get the space back (admittedly in a data warehouse where we'll gladly trade a bit of time compressing data up front for a faster read speed).

  • BarneyL - Wednesday, February 6, 2019 7:27 AM

    If space were an issue there's always row\page compression which would give you variable length storage on integers under the covers:

    https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/row-compression-implementation

    We tend to avoid tinyint for similar reasons to the article and then use compression to get the space back (admittedly in a data warehouse where we'll gladly trade a bit of time compressing data up front for a faster read speed).

    Be careful with compression.  Along with "It Depends", there is little that is "free" in SQL Server.  While compression works wonders for disk and memory space and a marked decrease in I/O and there's only a minor increase in CPU for queries, there are other things to consider, like the duration and intensity of doing index rebuilds and the related page splits from out of order INSERTs and "ExpAnsive" UPDATEs.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I suspect that this is partly down to unofficial standards in software languages. Integer types are usually capable of holding negative numbers whereas word, dword and so on types do not support negative mumbers.

    As far as I know, TINYINT is not a part of any SQL standard and has been added by Microsoft (and MySQL) and with no defined standard the definition of this naturally varies between -128...+127 or 0..255

  • n.ryan - Wednesday, February 13, 2019 6:09 AM

    I suspect that this is partly down to unofficial standards in software languages. Integer types are usually capable of holding negative numbers whereas word, dword and so on types do not support negative mumbers.

    As far as I know, TINYINT is not a part of any SQL standard and has been added by Microsoft (and MySQL) and with no defined standard the definition of this naturally varies between -128...+127 or 0..255

    Rather than measurements and counts, I think that TINYINT was intended as a more storage efficient datatype for low cardinality numeric columns like: GenderCode, StatusID, or AgeGroup. My own table designs tend to contain a generous amount of TINYINT, CHAR(1), and DATE columns, because I understand that the limitations of the data types still meet the use case requirements, and there is no point is wasting storage space and memory.

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

  • Eric M Russell - Wednesday, February 13, 2019 7:35 AM

    Rather than measurements and counts, I think that TINYINT was intended as a more storage efficient datatype for low cardinality numeric columns like: GenderCode, StatusID, or AgeGroup. My own table designs tend to contain a generous amount of TINYINT, CHAR(1), and DATE columns, because I understand that the limitations of the data types still meet the use case requirements, and there is no point is wasting storage space and memory.

    That's a good point. I work on the same principles of minimising the data type to the requirements of the data. If later the column data type needs to be changed this can always be done, although I'm still waiting on MS to implement column data domains (introduced in SQL-92 IIRC)...

Viewing 15 posts - 1 through 15 (of 16 total)

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