CODEWITHSUNDEEP
sqlsql-servert-sqltypes
p.campbell
p.campbell

Reputation: 100587

SQL: Datatype choice for values -1, 0 and 1

Consider a table whose job is to store a rating of an object. The column of interest here is the one called RATING. The valid range of values to store is:

The first thought was to store as a tinyint smallint. This would store only one byte two bytes per row. Given the tinyint's range of 0 to 255 smallint's range of -32768 to 32767, would it be suitable here? Is there another datatype more suitable? The space taken is trivial for the expected size of the table & database. The user interface will be responsible for ensuring that those three values will be chosen by way of an enum.

Question: can you suggest a smaller storage size or any clever setups to store one of these three values without sacrificing any ease-of-understanding?

Other considerations:

Upvotes: 1

Views: 2516

Answers (5)

fpcmm
fpcmm

Reputation: 41

I'd say, if possible, you should use bit or tinyint. Check out this table from sqlshack:

enter image description here

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294317

If space really really is an issue, two bits:

, Sign bit not null
, StorageVALUE bit not null
, VALUE AS CASE WHEN Sign = 0 THEN -StorageValue ELSE StorageValue END

Upvotes: 3

gbn
gbn

Reputation: 432311

When you sum it, it would be better to keep the data in one column in a usable format

SELECT SUM(Rating) FROM MYTable ...

So, as per other answers, smallint is the one you need. Otherwise, any contrived solution just makes it more difficult to use and validate.

Of course, you should define a CHECK CONSTRAINT to ensure you only have -1, 0 and 1 allowed

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332631

can you suggest a smaller storage size or any clever setups to store one of these three values without sacrificing any ease-of-understanding?

As you mention, finding the smallest data type is moot when you can buy terabyte drives for $100.

  1. An additional table, called RATING_CODE, with two columns:

    • RATING_CODE, pk
    • DESCRIPTION

  2. Change your current table's rating column to be rating_code, and setup a foreign key relationship to the RATING_CODE table.

Now it's easier to understand what each rating value means, because there's a description to go along with it. It's future proof, in case you want to add ratings like going up to 5 or 10.

Upvotes: 3

Philip Kelley
Philip Kelley

Reputation: 40319

smallint is the (ahem) smallest integer datatype that can accurately track -1, 0, and 1. If space is not an issue, and you have said that it isn't, then use smallint. Anything else would be overly clever and would require far more implementation work than is necessary.

Upvotes: 5

Related Questions