Represent Is"Something" Data in SQL-Server [closed]

Question

Joe Celko (sql guru) says that we should not use proprietary data types and especially refrain from machine level things like bit or byte since SQL-Server uses a high level language. Basically that the principle of data modeling is data abstraction. So discerning the above recommendation for fields like "IsActive" etc., what would the correct choice be for the data type, one that is a very portable and one that is deciphered clearly by front end layers? Thanks!

Answer 1

In SQL Server, I would go for BIT data type as it matches the abstract requirements that you describe: it can have 2 values (which map to Yes and No by a widely used convention of Yes = 1 and No = 0). It can have an additional NULL value if desired.

If possible, using native data types has all the benefits of performance, clarity and understandability for others. Not to mention the principle of not overcomplicating things when you can keep them simple.

Answer 2

SQL Server doesn't have a Boolean data type so Boolean is out of the question. BIT is a numeric type that accepts the values 0 and 1 as well as null. I usually prefer a CHAR type with a CHECK constraint permitting values like "Y"/"N" or "T"/"F". CHAR at least lets you extend the set of values to more than just two if you want to.

BIT has the potential disadvantage that it's non-standard, not particularly user-friendly and not well understood even by SQL Server users. The semantics of BIT are very peculiar in SQL Server and even Microsoft's own products treat BIT in inconsistent ways.