Reverberations

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:
e.g.

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);

Here,

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.
e.g.

enum ViewPermissions
{
   None=0;
   student=1;
   teacher=2;
   assistant=4;
}

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🙂

    seishin

    June 4, 2008 at 8:07 am

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

    Brajesh

    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

    JibberJabber

    February 17, 2009 at 8:26 pm

  4. reviews
    , these kind of
    warns
    [url=http://www.designertopten.com/men-louis-vuitton-shoes-outlet-14_44.html]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=http://www.designertopten.com/women/louis-vuitton-handbags/monogram-vernis/louis-vuitton-monogram-vernis-roxbury-drive-pink-m91987-clearance-1291.html]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=http://www.designertopten.com/louis-vuitton-handbags-epi-leather-outlet-1_2_34.html]Louis Vuitton Monogram[/url]

    marcelosilkwood540

    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?

    Doretha

    February 1, 2014 at 9:37 pm


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: