A Tiny Trauma

Question

A Tiny Trauma

mhtanner

Mr or Mrs. 500

Points: 552
More actions
February 4, 2019 at 9:53 pm

#381345

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

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

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

jennym Old Hand Points: 376 More actions · Answer 1

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!

srienstr SSCrazy Points: 2435 More actions · Answer 2

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 SSC Guru Points: 1003853 More actions · Answer 3

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.

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

mhtanner Mr or Mrs. 500 Points: 552 More actions · Answer 4

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 Mr or Mrs. 500 Points: 552 More actions · Answer 5

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 Hall of Fame Points: 3874 More actions · Answer 6

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.

Eric M Russell SSC Guru Points: 125519 More actions · Answer 7

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

Eric M Russell SSC Guru Points: 125519 More actions · Answer 8

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

mhtanner Mr or Mrs. 500 Points: 552 More actions · Answer 9

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).

BarneyL Ten Centuries Points: 1044 More actions · Answer 10

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).

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 11

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.

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

Nick Ryan SSCrazy Points: 2237 More actions · Answer 12

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

Eric M Russell SSC Guru Points: 125519 More actions · Answer 13

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

Nick Ryan SSCrazy Points: 2237 More actions · Answer 14

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)...