SQL Server: varbinary or int to store a bit mask?

Question

Is there any advantage of using int vs varbinary for storing bit masks in terms of performance or flexibility.

For my purposes, I will always be doing reads on these bit masks (no writes or updates).

Answer 1

You should definitely use an INT (if you need 32 flags) or BIGINT (for 64 flags). If you need more flags you could use BINARY (but you should probably also ask yourself why you need so many flags in your application).

Besides, if you use an integral type, you can use standard bitwise operators directly without converting a byte array to an integral type.

If you do need more flags and have to use BINARY you lose native support for bitwise operators and therefore easy support for checking flag values. I would probably move checking for flag values to a client application but if you're comfortable programming in T-SQL that's an option as well. If you're using C# you have a BitArray class with the necessary operations and in Java you have a BitSet class.