Yes/No Fields in SQL Server

Mike Wolfe -

"Upsizing" back-end data storage from ACE (.mdb/.accdb files) to SQL Server carries many benefits: improved concurrency, better security, more robust backup options, performance enhancements, etc.  But there are some pitfalls you need to be aware of.

One example is "Yes/No" fields.  Yes/No fields in Jet/ACE are stored in 1-byte BIT fields.  Note that the BIT keyword has multiple aliases in Access SQL, including BOOLEAN, LOGICAL, LOGICAL1, and YESNO.

These BIT fields can hold one of two values: -1 for True and 0 for False.  They cannot contain NULL values, as this classic article from Allen Browne makes clear: Why I stopped using Yes/No fields.

"JET is quite happy working with Nulls in Number fields. Since Access uses 0 for False, and -1 for True, use a Number field in place of the Yes/No field, and JET will process these queries correctly."
- Allen Browne (emphasis in original)

Upsizing Yes/No fields to SQL Server

Most upsizing wizards will convert a Jet/ACE BIT (aka YESNO) field to a SQL Server BIT field.  That simplistic approach can lead to problems.

"Equivalent" is in the eye of the beholder

In this list of equivalent data types, the SQL Server BIT data type is listed as equivalent to the Access SQL YESNO data type.  There is one critical difference, though. SQL Server BIT fields return 1 for True, whereas Jet/ACE BIT fields return -1 for True.  

For this reason, you risk introducing problems in your Access application if you simply convert your Access YESNO fields to SQL Server BIT fields.  For me, this was a problem when I relied on these fields for sorting purposes, such as showing all active records at the top of a lookup form.

Requirements for an equivalent SQL Server field

To match the behavior of a Jet/ACE YESNO field, a SQL Server field needs to meet the following criteria:

  1. Only two values: 0 for False and -1 for True
  2. Cannot be Null
  3. Have a default value (either False or True)

T-SQL to create such a field

When adding a YESNO-equivalent field to a table in SQL Server, I use the following T-SQL:

ALTER TABLE dbo.Customer ADD
 IsActive smallint NOT NULL  
  CONSTRAINT df_Customer_IsActive DEFAULT -1 
  CONSTRAINT ck_Customer_IsActive_YesNo CHECK (IsActive In (0, -1))

I use the smallint type because it is the smallest data type in terms of storage size that supports both 0 and -1.

Since I want this to mimic the behavior of the Access YESNO type, I make it a required field via the NOT NULL keywords.

I then set the default to True in the third line, though you could just as easily set the default to False by switching out the -1 for a 0.

Finally, I restrict the possible values to 0 or -1 via the Check Constraint in line four.  This helps avoid unexpected behavior when applying bitwise logical operations in VBA to true values other than -1.

A note about constraint names

Naming constraints in SQL Server is optional.  I always name mine, though, because I script all of my database changes.  By naming my constraints, it makes it easier (and more readable) to drop them in later scripts as my database schema evolves.

Caveats

Obviously, this approach requires more storage space than using the SQL Server bit data type.  Every Yes/No field defined as a smallint will take up 16 bits of space, while SQL Server bit fields may use as little as a single bit of storage space.  In most cases, the added simplicity is a good tradeoff.

Additionally, if you drag the table field onto a form in design view, Access will create a text box instead of a check box.  The simple workaround is to create an unbound check box first, then assign the field as the ControlSource afterwards. The Allen Browne article I linked to earlier addresses this, too.

Photo from pexels.com