How to Easily Toggle a Bit Column in the Database

Surely at some point you had a bit field in the database that indicated a yes or a no or some other binary flag. In this post, I’m going to give you three examples of how to toggle that bit.

While you do not necessarily deal with the database from an application, it is usually the case. If it’s not, you can skip this over. And your first option is, of course, get the value of the bit column first and update it based on that value. For the purposes of this article, I’m going to use C# along with Enterprise Library and assume that the column is not NULLable. Here is the code that you can write to toggle a bit:

var myFlag = (int)db.ExecuteScalar(db.GetSqlStringCommand("SELECT MyFlag FROM MyTable WHERE Id = " + pkId));
db.ExecuteNonQuery(db.GetSqlStringCommand("UPDATE MyTable SET MyFlag = " + (myFlag == 0 ? "1" : "0") + " WHERE Id = " + pkId));

This is a perfectly legitimate method of doing it. Once slight problem is that you have to make a trip from your application to the database to first retrieve the value, and then another trip to update it. Another trap here is that what if in between of these two operations the database was updated by another application and your flag changed? Well, you can certainly put these two executions in a transaction. What else can you do?

You can have just one query. Ever heard of CASEs in T-SQL? Here’s how:

db.ExecuteNonQuery(db.GetSqlStringCommand("UPDATE MyTable SET MyFlag = CASE WHEN MyFlag = 0 THEN 1 ELSE 0 END WHERE Id = " + pkId));

Now, a bit simplier and a single query. Can it be simpler? Why yes, of course.

Do you remember those classes where people were talking about logic operators and stuff and all? Well, comes handy here, then. Particularly, the XOR operator. As you know, XOR applied to two bits is defined as if either one of them is 1, the resulting value is 1, except when both of them are 1. Thus the X in the name that stands for exclusive. Now, think a little and run it down on a bit. 🙂 If you were to toggle a bit that is currently set to 0, what would you have as a second argument for the XOR? 0, of course, is out of the question, but 1 will do! Now, what if your bit is currently set to 1? If you use 0, it’ll stay 1, but if you use 1, the exclusivity of XOR will kick in and have it at 0! Can’t be simpler!

This now becomes an atomic query and it does not even matter whether you use it from an application or directly in a database or what else.
The final query is:

UPDATE MyTable SET MyBit = MyBit ^ 1

This is all there is to it.

This entry was posted in MSSQL and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *