Results 1 to 5 of 5

Thread: & operator

  1. #1
    Join Date
    Nov 2003
    Posts
    19

    Smile Unanswered: & operator

    I have seen & operator is being used in some queries for comparison.. like the following:

    select count(*) from sysdatabases
    where (status2 &16 = 16 )
    or (status2 &32 = 32 )
    or (status &32 = 32 )
    or (status &256 = 256 )
    go

    Is there a reason why we cannot use status2 = 16 instead of status2 &16 = 16 ??

    By the way, what does the above query return?

    Thanks
    Bhargava

    Thanks
    Bhargava

  2. #2
    Join Date
    May 2002
    Posts
    39
    It is obvious that the status and status2 columns of sysdatabases are being interpreted bitwise. Using &16, &32 and &256 serve as masks, to check whether the specific bit that we are interested in, is set or not.

    You cannot check directly with the value, cos there might be other bits of the field set and this will result in different overall values.

    Let me know if you follow that are need more information in checking bits of a field and I can explain in more detail.

    Not entirely sure what each of these bits represent. (Not really an admin.)
    ...but here's what I found in sybooks (12.5)

    (Focus on the specific masks that are in your query and see the explanation for that particular bit)

    For status column
    ------------------------
    Table 54-3: status control bits in the sysdatabases table
    Decimal
    Hex
    Status
    4
    0x04
    select into/bulkcopy; can be set by user
    8
    0x08
    trunc log on chkpt; can be set by user
    16
    0x10
    no chkpt on recovery; can be set by user
    32
    0x20
    Database created with for load option, or crashed while loading database, instructs recovery not to proceed
    256
    0x100
    Database suspect; not recovered; cannot be opened or used; can be dropped only with dbcc dbrepair
    512
    0x200
    ddl in tran; can be set by user
    1024
    0x400
    read only; can be set by user
    2048
    0x800
    dbo use only; can be set by user
    4096
    0x1000
    single user; can be set by user
    8192
    0x2000
    allow nulls by default; can be set by user


    For status2 column
    -----------------------------
    Decimal
    Hex
    Status
    1
    0x0001
    abort tran on log full; can be set by user
    2
    0x0002
    no free space acctg; can be set by user
    4
    0x0004
    auto identity; can be set by user
    8
    0x0008
    identity in nonunique index; can be set by user
    16
    0x0010
    Database is offline
    32
    0x0020
    Database is offline until recovery completes
    64
    0x0040
    Database is being recovered (internal use)
    128
    0x0080
    Database has suspect pages
    512
    0x0200
    Database is in the process of being upgraded
    1024
    0x0400
    Database brought online for standby access
    -32768
    0xFFFF8000
    Database has some portion of the log which is not on a log-only device


    Hope this help!

  3. #3
    Join Date
    Nov 2003
    Posts
    19
    I would appreciate if you could explain more on bit checking. I still dont understand the difference between (status2 = 16) and (status2 &16 = 16). I think, if other bits are set, then (status2 = 16) would be false ... and this would the same case with (status2 &16 = 16).
    Am I missing something here?

    Thanks
    Bhargava

  4. #4
    Join Date
    May 2002
    Posts
    39
    Ok will explain

    Consider a single byte as follows..

    00010001 (This in decimal form is 17) -- say A

    Now the mask applied is 16 which in binary is 00010000 -- say B

    Now (A == B) is FALSE, quite clearly

    However, ((A & B) == B) is TRUE because..
    using the bitwise AND

    A & B = 16 (which is what the value of the mask B is)
    Remember A & B is a bitwise AND operation.

    so quite literally this may be represented as below

    00010001 -- A
    & 00010000 -- B
    --------------
    00010000 -- (result is nothing but B, the mask)

    Here are the boolean rules for AND

    0 & 0 = 0
    0 & 1 = 0
    1 & 0 = 0
    1 & 1 = 1

    These rules must applied to each matching bit of A and B.

    In case you are curious, another way to check bitwise if a specific bit is set or not is simply to use the bitwise AND with a mask, and if the bit is set the result will be non-zero. If not set the result will always be ZERO

    So in our example we can as well check if ((A & B) != 0)

    Ok??

  5. #5
    Join Date
    Nov 2003
    Posts
    19
    Thank you so much for your nice explanation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •