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

Question

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:

• 1
• 0
• -1

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:

• storage space isn't a terribly large concern
• this value will be summed to get a total rating
• this may be a case of micro-optimization
• SQL Server 2008

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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