Hi I am creating database for rating the items in my list but I want to ask what this if I have five stars rating how can I do it in database "rating" column should I use Int with check constraint or user define type ?

5 Answers 5


This is an example of using a check constraint for your ratings table

Ratings decimal(2,1)
CONSTRAINT chk_Ratings CHECK (Ratings >= 0 AND Ratings <= 5)

You better use tinyint. It's int from 0 to 255. It's also takes only 1 byte to store value. It's enough for your 5-star rate.

If you need store values like 4.5 stars you may store stars like this:

1 star =10

2.5 stars=25

and so on

And, of course, you need check constraint on this field.

tinyint - using storage 1 byte

numeric(1)/decimal - using storage 5 bytes

  • I like this solution. Another option is to store from 0 to 10 where 0 is 0 stars, 1 is 0.5 stars, 2 is 1 star, ..., and 10 is 5 stars, but this makes it more clear that it's a 0 to 5 scale (or 1 to 5 if it's the "if I could give it a zero rating I would" rather than a "if I could give it a negative rating I would" scale) Jan 10, 2019 at 6:50

Here is the test of Data Types:

--Create A test Table

,rate_Varchar varchar(4)
,rate_float FLOAT(1)
,rate_real REAL
,rate_numeric NUMERIC(2,1)
,rate_decimal DECIMAL(2,1)

--Insert Some Sample Rows

INSERT INTO [dbo].[DTTest]

--Column Size

EXEC sp_help [DTTest]

So you should use float to save Decimal Ratings(4.75 or 4.5) etc.


You'll probably want to use numeric or decimal so that you can have 4.5 stars.


In my view it depends on the rating strategy your application provides, I mean if your application provides ratings only like 1,2,3,4,5 stars not any decimals, you can obviously go with tinyint, and if your application will accept the ratings like 1.5, 2.2, 4.5..... and in that case you can probably go with float / decimals...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.