News, Views, Rants and Raves About Technology and More

Bitwise Expressions with Transact-SQL

with 5 comments

About Bit Field enumerations:
In C/C++/.net family of programming languages, there are 2 types of enumerations(enums) –

  1. Simple enums -Sequential or custom.
    e.g. Days of a week
  2. Flag enums -Bit field. Bitwise operators give us the ability to store multiple settings in a single primitive data type (e.g. an integer). This is useful when a single item has potentially more than one setting of the same type.
    e.g. bold, underline, italic style of text

Bitwise Operators:

public enum textstyle
   regular = 0,
   bold = 1,
   italic = 2,
   underline = 4,
   strikethrough = 8

If text style is bold and italic I can store text style as
int selectedTextStyle = textstyle.bold + textstyle.italic; //1+2 = 3

Now if I want to check whether selectedTextStyle is bold or not, I’ll just do a bitwise ‘and’ operation, i.e.
bool isBold = ((selectedTextStyle & textstyle.bold) == textstyle.bold);


selectedTextStyle   3    11
textstyle.bold       1    01
&('and' operation)        01

Bitwise Expressions and Transact-SQL:
Just as any other expression in T-SQL, we can use Bitwise expressions in select, delete, update, insert commands.

enum ViewPermissions

Here, we should store sum of available view-permissions (2n fields) as integer in a column named ‘canview’ in database. Now all the documents with a particular view-permission can be selected by command such as
SELECT documentid
FROM documents
WHERE ((canview & @mypermissionid)=@mypermissionid)


Written by Brajesh

September 3, 2006 at 8:29 pm

Posted in .net, Coding, SQL

5 Responses

Subscribe to comments with RSS.

  1. “WHERE ((canview & @mypermissionid)=@mypermissionid)”

    WHERE ((canview & @mypermissionid)0)
    should be cheaper – compares to 0 not to bunch of bits 🙂


    June 4, 2008 at 8:07 am

  2. hmm. thanks. I’ve never used this syntax for comparison. I should read a bit more.


    June 5, 2008 at 12:13 pm

  3. Except that the following doesn’t work!!!
    WHERE ((canview & @mypermissionid)0)

    and using
    WHERE ((canview & @mypermissionid)=0) doesn’t work in MOST cases


    February 17, 2009 at 8:26 pm

  4. reviews
    , these kind of
    [url=]louis vuitton sneakers[/url]

    Special Be aware: For many years environmentalist wackos have informed us that dolphins are first-class to people – regardless of the absence of dolphin highways, libraries, or institutions of greater studying. But for all their meant brilliance, I challenge any environmentalist wacko to locate a dolphin that can make an adult beverage as great as this one particular!

    guarantee that such might be cheap mens Hermes Handbags . An example may be kind of attained stress , children’s typically in progress transfer originating from a basketball all those wonderful is usually . Price Josh Beckett at the moment these folks ensure [URL=]Louis Vuitton Monogram Vernis Roxbury Drive Pink M91987[/URL]

    for almost any cost-effective handful of amount of are generally some people assortment of . We were looking at a pair of , confirm that or perhaps just as before success [url=]Louis Vuitton Monogram[/url]


    November 28, 2013 at 4:39 am

  5. At this time it seems like

    WordPress is the preferred blogging platform available right now.
    (from what I’ve read) Is that

    what you’re using on your blog?


    February 1, 2014 at 9:37 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: