SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Tiny Trauma


A Tiny Trauma

Author
Message
mhtanner
mhtanner
SSC-Addicted
SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)

Group: General Forum Members
Points: 465 Visits: 266
Comments posted to this topic are about the item A Tiny Trauma
jennym
jennym
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 Visits: 80
Very appropriately titled Smile 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!
srienstr
srienstr
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1915 Visits: 584
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)SSC Guru (936K reputation)

Group: General Forum Members
Points: 936045 Visits: 49079
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mhtanner
mhtanner
SSC-Addicted
SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)

Group: General Forum Members
Points: 465 Visits: 266
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.
mhtanner
mhtanner
SSC-Addicted
SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)

Group: General Forum Members
Points: 465 Visits: 266
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.
thomas_franz
thomas_franz
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3266 Visits: 495
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)
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117275 Visits: 15446
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)


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117275 Visits: 15446
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



"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
mhtanner
mhtanner
SSC-Addicted
SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)

Group: General Forum Members
Points: 465 Visits: 266
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).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search