Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    6

    Unanswered: Bitwise NOT Operation

    I was looking into some of the System stored procedures like sp_denylogin, sp_grantLogin etc. I found bit wise operations like

    update master.dbo.sysxlogins set xstatus = (xstatus & ~1) | 2, xdate2 = getdate()
    where name = @loginame and srvid IS NULL

    How does the bitwise NOT(~) works with different datatypes like INT, SMALLINT and TINYINT?

    Look at the following sample code and result. I just wanted to understand how it works with int and smallint


    declare @a int
    declare @b smallint
    declare @c tinyint

    select @a = 1, @b = 1, @c = 1

    select @a=~@a, @b= ~@b, @c= ~@c

    select @a, @b, @c

    Result
    ~~~~~
    -2 -2 254

    Thanks in advance
    Gnana

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Interesting question! In a former life, I worked on addressable converter interfaces, and much was done with bitmaps...so I played a bit also...

    Note the results if you execute the following select in your example:
    Code:
    select @a, @b, @c, cast(@a AS varbinary), cast(@b as varbinary), cast (@c as varbinary)
    Result
    ~~~~~
    -2 -2 254 0xFFFFFFFE 0xFFFE 0xFE

    Now you get to see the binary results...which still does not answer your question, but I betcha (as I head back to BOL) that the answer lies in there as the way the sign bit is interpreted in the larger two data types.

    Betcha that the tinyint data type does not have a sign bit.

    yep...that's it...from BOL:
    Quote Originally Posted by BOL
    tinyint

    Integer data from 0 through 255. Storage size is 1 byte.
    So the issue is not so much that the bitwise operation actually WORKS any differently...what you are seeing is just the PRESENTATION differences between data types (the final select assumes you want the data interpreted in it's numeric value, not the bit-representation of the data in the variable....so you get a negative number because you have set the sign bit on with the bitwise operation you used on the larger data types (that have sign bits).

    In other words, the bitwise operator works exactly the same on all three data types, it just LOOKS like it works differently when you select it back to see the results (without casting, that is).
    Last edited by TallCowboy0614; 09-06-06 at 18:53.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Nice explanation, Paul.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Yeah, thanks! I learned something too (something I usually try to avoid at all costs)...though we don't use much bit manipulation here (at least not out where everyone can see ).
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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