Fun with data types

Are protected underwriting terms applicable?  (Never mind what it means).  The answer is either Yes or No – they either are or they aren't.  No maybes possible, no special situations - so the perfect data type would be Boolean - a value of 1 means True, and 0 means False.  Named after 19^th century mathematician George Boole, who presumably worked out that the opposite of True was in fact False.  Isn't maths wonderful?

Unfortunately, in SQL Server, there is no Boolean data type.  This might be something of a setback.  Still, the nearest thing that can be used in place of Boolean data is the BIT data type, which is an integer data type that can accept a value of 1, 0 or NULL value only.  And it only uses up one bit so it is very efficient.  

So, problem solved, I hear you say?  Ho, ho, ho; that is hollow laughter echoing round the IT crowd’s squalid basement.   Because 1 is fine for Yes, but 0 won’t do for No.  Oh dear me no - the bulk upload software to which I want to transfer data expects either 1 or blank.  It could be changed of course, but that would mean months of development and testing. 

Hokay, how else can we do this?  Tinyint, Smallint and no doubt Int and Bigint all implicitly convert the desired blank into a 0.  So no joy in that direction. 

A fixed size character string?  So I tried it as char(1), and did a CASE statement to specify ‘1’ for Yes and “ for No.  But that changed ‘’ to ‘ ‘ in other words, a space or ASCII code, er, 32.  Not the same thing at all, as far as a computer is concerned.  I asked for a single character – I get a single character – a space is a character, it just happens to be an invisible character. 

The answer was to use a variable length character string – the varchar datatype is usually used for strings that can vary greatly in length.  Names, for example.  You don’t know who is going to order a widget next – could be Jim Lee, could be Felicity-Jayne Anstruther-Gough-Calthorpe.  Using a varchar allows the length to vary – from 0, which is what we want, to 1, so it’s now varchar(1), which looks bizarre but works!  There’s a length overhead to using a variable character field, but we can live with that. 

I still think it should be Boolean!