We need three modes to display the status of users in the database!
We can do it in two ways
Null: Not checked
0: Not accepted
0: Not checked
2: Not accepted
Which data type is better when we need to represent three values?
It’s highly unlikely that there is a material performance difference here.
So the choice is between
create table AppUser ( Id int identity primary key, Name nvarchar(200) not null, UserAccepted bit null )
create table UserStatus ( Id tinyint primary key, Description varchar(200) ) create table AppUser ( Id int identity primary key, Name nvarchar(200) not null, UserStatus tinyint not null references UserStatus default 0 )
And while one might be preferable in your particular application, they are both reasonable designs.
I would go with TINYINT for these reasons:
- You might come up with other modes – for example, “verifying”
- Bit can’t be aggregated – that usually comes up with PIVOT function, where you have to aggregate – you first need to cast to int family type.
- Single bit column doesn’t actually provide storage savings – it still takes a byte (the first bit in a byte) – other columns would provide a storage saving