Reputation: 4424
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:
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).
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?
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
?
Upvotes: 0
Views: 2109
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