What data type I should preferably use for storing flag fields in sql server?

Question

What data type I should preferably use for storing flag fields in sql server, bit(if I have only 0,1 and null), tinyint, smallint or int for flags which cannot be accommodated into bit? Does it effect my query performance?

Answer 1

Use Bit Datatype which is set to 1 if true or 0 ir False, much smaller and faster to query against Bit values Your column could be saved as 1 Byte if you have 8 or less Bit columns in your table and I think if you have more then 8 then SQL Server uses 2 Bytes Column , Which is A lot smaller than any other options you might have, tinyint, smallint and int all requires more space then a bit column and querying a bigger datatype means more time to query the data.
My Suggestion
My Suggestion would be to use Bit Column as it requires less space to store it and queries can perform much faster.

Answer 2

bit

its a boolean, so its only 1 or 0, and doesn't require a whole byte to store. Plus you can just make it nullable on the table design.

Answer 3

I got the answer for what I was looking, we should consider using smallest data type sufficient enough to store flag values required. This is with respect to the performance of database as storage size is not a big concern these days.

have a look at this link for more insights.

As per this link think about how resources other than disk space are used, think about buffer pool and storage bandwidth. At the extreme end, CPU cache and memory bus bandwidth, and also about how SQL Server works.