0

I need to store a value between -255 and 255 on SQL Server. I'm looking for the fastest way to SELECT these values on a large table (+5,000,000 rows) and every millisecond counts. Please suppose the structure, indexes and queries are already correctly designed.

I know I could use a SMALLINT that uses 2 bytes and is signed by implementation. But since my main goal is SELECT performance, I thought about using a TINYINT field for raw value, and a BIT field to sign the value as negative or positive. But my C low level background make me think that BIT uses 1 byte actually to store and/or process. After reading an answer here (TINYINT vs Nullable BIT performance in MS SQL Server) my suspicions appears to be correct.


So, to sum up and define better the "BEST" on the title, my questions are:

  1. Using a TINYINT and a BIT pair fields would use/process 2bytes or 1byte + 1bit when SELECTing? I'm not asking about storage since even on large table the difference would be negligible (please correct me if I'm wrong).

  2. If a BIT field uses 1byte when executing a SELECT process, there would be any performance gain between SELECTing a 2byte TINYINT and a BIT pair against a SMALLINT that already uses 2bytes and is signed by implementation?

  3. If YES, and there is actually a performance gain using the TINYINT and a BIT pair, it's possible to measure it to precisely evaluate if the gain on performance worth the cumbersome of using the TINYINT and a BIT pair, against the better design of using just a SMALLINT?


NOTE 1: Server is Microsoft SQL Server 2019 Datacenter
NOTE 2: The question is about SQL Server SELECTing those values. It won't be used in any aggregation, WHERE or any other operation on the SQL Server.
NOTE 3: The value won't be used as a INDEX's key. It'll be only INCLUDED on the appropriated index/indexes.
NOTE 4: The argument that I may need in the future support values outside of the -255 - 255 range is valid, but not on this scenario. On this scenario I'll NEVER need it. If there was this possibility, I already would use `SMALLINT` anyways, since would make sense to be prepared to support this values already.
NOTE 5: Already read this SO Question: ([Is BIT field faster than int field in SQL Server?][2]), but it only compares a single `BIT` with `INT` (4bytes), and the accepted answer doesn't provide any tangible argument.
Vitox
  • 3,852
  • 29
  • 30
  • 3
    The chances that someone else has run precisely the experiment that you yourself are actually contemplating are negligible. So try both, if you really want to, measure it, and proceed with confidence. Personally, I'd prefer a simple type *unless and until* there's a demonstrable performance deficiency - which would require that there be actual performance metrics to measure against. – Damien_The_Unbeliever Sep 30 '20 at 07:04
  • What makes you think forcing SQL Server to retrieve 2 columns instead of 1 will gain you anything performance wise? Even `INCLUDE` an addition column to the index comes at a cost. – Filburt Sep 30 '20 at 07:05
  • @Filburt I thought that one thing that could make difference is if `BIT` really uses just 1 bit, and the Query optimizer could save some considerable space when processing it. – Vitox Sep 30 '20 at 07:07
  • @Damien_The_Unbeliever I see your point. I'm hopping that someone a lot more experienced than I already made this experiments or have that particular knowledge. If not, than I would end up doing it my self, and answering my own question. But would be a lot faster if someone already had this experience/knowledge and would be able to share it with us. – Vitox Sep 30 '20 at 07:11
  • Just out of curiosity, what application uses signed 9-bit integers and why they don't range from -256 to 255? – AlwaysLearning Sep 30 '20 at 07:39
  • @AlwaysLearning good question and hard to explain! It's a pre-processed set of items. This items are late processed and grouped by a extremely specific and dynamic algorithm. The score of each group is the sum of each individual validated item of the set. Each item has a score up to `255`. But some of them are "against" that group, hence if validated, they penalize its final group score. Those "against" items have equivalent negative scores up to `-255`. – Vitox Sep 30 '20 at 07:49
  • @AlwaysLearning maybe an analogy would be: I have a lot of cars. Each car has a set of items that have positive accelerations (like engine, NOS) ranging up to 255, and some have negative accelerations (like break, friction, wind resistance). I than return every items set of every car, validate what items are installed on the car, and then sum up to calculate the fastest cars. The actual system is a lot more complex, but the idea is the same. Also, I could think on a lot of examples where a signed `TINYINT` or a `9-bit integer` field would be desirable. : ) – Vitox Sep 30 '20 at 07:59
  • 1
    I was thinking it might be audio, myself. There have been a number of non-byte sample sizes for audio over the years, particularly in telco networks. – AlwaysLearning Sep 30 '20 at 08:20
  • 1
    Just use a SMALLINT... Anything other than that will take up the same amount of space and give you nothing but headaches. Yes, a BIT column will use a BYTE if you only have one of them on the table. What makes a BIT column a "BIT" is the fact that a single BYTE can provide storage for up to 8 BIT columns. So, when you add the first BIT column you're paying for 8 (and you get the next 7 for free). – Jason A. Long Sep 30 '20 at 12:58
  • Use a SMALLINT. The bit field itself will requires more work for SQL to access (ie. read from the row, and then the logic to apply in the query) and space to save in the row. SMALLINT vs TINYINT *known worst-case size increase*: 1 byte (or potentially less if ROW compression is in play). SMALLINT vs TINYINT *known complexity increase*? None. – user2864740 Feb 26 '21 at 23:06
  • Great question! Great design! – Zhang Jun 15 '23 at 05:05

1 Answers1

-2

One byte (tinyint) will not store -255 to 255, only -128 to 127. You will have to use a smallint for -255 to 255.

Dennis Mitchell
  • 17
  • 3
  • 2
    Tiny int has range [0 to 255](https://learn.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver16) so the question is correct that either `smallint` or `tinyint` and a sign bit would work – Martin Smith May 17 '23 at 05:48