Vitox
Vitox

Reputation: 4424

Best way to store a "signed" TINYINT on MS SQL Server?

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.

Upvotes: 0

Views: 2109

Answers (1)

Dennis Mitchell
Dennis Mitchell

Reputation: 17

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

Upvotes: -2

Related Questions