Boolean Field in Visual Foxpro and MS SQL Server

In Visual Foxpro 9 (VFP) and it’s lower versions, there exists a logical field with which a programmer can use to have some kind of a digital flip-type switch that we regularly use in queries or in a control, like a checkbox, for example. If one can imagine how a light switch works, the idea behind is the same with that of a logical field. The apparent difference emerging from that point being, is that the usual ON and OFF status that we regularly see on a common household light switch is omitted, the logical field in VFP uses a boolean value of either .T. or .F. instead, with “.T.” representing the value for TRUE and “.F.” for FALSE.

So it’s kind of like when your dad asks you if you have left the garage lights on overnight, you may probably hear, “Is it TRUE that you have left the lights on last night?”

Anyway, Creating a logical field in a VFP cursor is as simple as this.

Create Cursor curTest (field1 l)

Select curTest

MODIFY STRUCTURE

But in MS SQL Server, with versions like 2008 or 2012, there is no Boolean or Logical field type. This is probably the reason why I see many software developers turning their trust to characters and integer types just to work around this inadequacy. 

For instance, a programmer can use several of the INT (integers) type field just to represent a value of 0 or 1. Because 0 and 1 can both respectively represent the ON and OFF nature of a basic switch. The idea makes sense until you add another value, like “2” then it loses the foremost characteristic of being a “flip-type” switch. 

The Workarounds in SQL Server

I’ve seen programmers use the int type, which has a range of -2^31 (-2,147,483,648) to  2^31-1 (2,147,483,647) and can take up storage of 4 Bytes in the table. Others use the smallint type, of which range is shorter than the int type but takes up 2 Bytes of storage space. There is also the tinyint that has a range of only 0 to 255 and takes up one Byte of storage space.

One thing to consider when you are feeling a bit determined to use these INT types for a simple switch is that each column of int created in a table will take up the storage space that I mentioned above. For example, if you have two columns of a tinyint field, each column will respectively take up one Byte of storage in a table.

At the other end of the spectrum, there are also those who use a character field to represent a switch in a similar fashion. These programmers, like I do in the early stages of my programming career, have liked the idea of using CHAR(1) type field to mimic the purpose of a logical field. The reason being is that it’s a straightforward solution and it’s easy to read. All that one needs to do is update the char field with a string having a value of “T” or an “F” character. 

The downside of using a char(1) field, however, is that it can take up other characters as well like “A” for instance, aside from the “T” or “F” characters. Also, each char(1) type column’s value in a table will take up 1 Byte of storage. Therefore, if you have two of this column in your table, the values will take up 2 Bytes and so on.

What I Think Is the Right Way to Do It

The best thing to do, in my humble opinion, is to use a bit type field in MS SQL Server. This type accepts integer data type like a bit value of 1 or 0, and it can have a NULL value as well.  

In this example, I’ve created a temporary table with a bit type field. I then inserted a 1 value.

If (Select name from sys.tables where name like '%#testTable%') is not null

      Begin

            drop table #testTable;

            create table #testTable (field1 bit);

     

            insert into #testTable (field1) values (1);

           

            select field1 from #testTable;     

      End

The advantage of the bit type is that it can automatically convert the string values TRUE and FALSE to bit values.  The “TRUE” becomes 1 and “FALSE” becomes 0.

If (Select name from sys.tables where name like '%#testTable%') is not null

      Begin

            drop table #testTable;

            create table #testTable (field1 bit);

     

            insert into #testTable (field1) values ('TRUE');

           

            select field1 from #testTable;     

      End

Sending Updates from Visual Foxpro

Luckily, MS SQL Server will convert the boolean value sent from Visual Foxpro by way of SQL-Pass Through or (SPT) connection.  

For this example, I have a table that I use for keeping LEAVE PAYS. It has a bit type column named isLeapYear. Let’s see what’s in it.

Notice that all other values in the isLeapYear column has zero values except for what’s in the fourth row, which has a bit value of 1.

Now, let’s try and change that into 0 (zero) by sending an UPDATE from Visual Foxpro with this command.

=SQLExec(oHandle.nHandle,'Update MyDatabase.dbo.MyTAble Set isLeapYear = .F.')

Now, we can see that all of the values in the isLeapYear column became zeros. 

Showing the Results with a Checkbox Control in a Grid

Let’s change the value of the fifth column, in the first row to 1. Then we’ll pull the records from MS SQL Server to a Visual Foxpro Grid that has a checkbox column.

=SQLExec(oHandle.nHandle,'Update MyDatabase.dbo.MyTAble Set isLeapYear = .F. where lpID = 9')

 So, this is how it should look like now. 

After we pulled the records from SQL Server, this is what a grid with a checkbox control would look like. Notice that the checkbox is automatically ticked because the underlying value in that column is .T. while the rest have .F. values. I will discuss how I created this checkbox in a grid  on a new post once I get a free time again.

Final Thoughts

Visual Foxpro is somehow lucky to have found its Logical values automatically converted by MS SQL Server to bit values. 

But another point worth considering for using a bit type field is that the SQL Server Database Engine optimizes storage of bit columns.  For example, if a table has less than 8 bit columns, all of the columns are stored as 1 byte. Having more than 8 bit columns but less than 16 will be stored as 2 bytes and so on.

As opposed to using an int type that always stores 1 byte or more per column, this is a great way to conserve server storage space if one is too concern about data normalization and storage conservation. 

Have a nice day!