SQL Fool Adventures in SQL Tuning – a blog for the rest of us

1. 

   Colin Stasiuk
   February 4th, 2009 - 07:55

   Great article and examples… next time just remember “thinking blogs” should only be posted after noon LOL
2. 

   Margaret
   February 4th, 2009 - 12:20

   Well, any 5 year old would argue with you about broccoli being edible and balls being blue, but there you go — what does a 5 year old know?

   Great example of using this function and quite cute as well!

   ê¿ê
3. 

   Denis Gobo
   February 5th, 2009 - 17:36

   Michelle,nice post, see also my first post on sqlblog.com

   Speed Up Performance And Slash Your Table Size By 90% By Using Bitwise Logic
   http://sqlblog.com/blogs/denis_gobo/archive/2007/05/29/test.aspx
4. 

   Michelle Ufford
   February 6th, 2009 - 07:58

   Thanks, Denis, that’s great! The next task for me is to evaluate performance implications, and your article is giving me a jump start on that.
5. 

   Ranga
   April 16th, 2009 - 11:01

   Michelle,
   Just saw this website today…its great, like your style!

   I am kind of new to XML, couldn’t understand the & operator used in the below query: Can you please shed some light on this..

   WHERE a.attributes & b.attribute 0

   SELECT a.item
   , REPLACE( REPLACE( REPLACE((
   SELECT VALUE
   FROM #statusLookup AS b
   WHERE a.attributes & b.attribute 0
   ORDER BY b.VALUE FOR XML Raw)
   , ‘”/><row value=”‘, ‘, ‘), ”, ”)
   AS ‘attributes’
   FROM myTable a
   ORDER BY a.item;
6. 

   Michelle Ufford
   April 16th, 2009 - 11:42

   Hi Ranga,

   Thanks for the feedback! I’m glad you like the site.

   The & operator actually has nothing to do with XML; in the example above, XML was just used to help format the result set. The & operator is returning the bitwise value if the bitwise product contains it. To expand on the example above…

   CREATE TABLE #myTable
   (
         myWeekDay                 varchar(10)
       , bitwiseProduct            tinyint
       , bitwiseValue              tinyint
       , bitwiseOperatorResults    tinyint
   );
    
   INSERT INTO #myTable
   SELECT 'Sunday', 42, 1, 42 & 1 Union All
   SELECT 'Monday', 42, 2, 42 & 2 Union All
   SELECT 'Tuesday', 42, 4, 42 & 4 Union All
   SELECT 'Wednesday', 42, 8, 42 & 8 Union All
   SELECT 'Thursday', 42, 16, 42 & 16 Union All
   SELECT 'Friday', 42, 32, 42 & 32 Union All
   SELECT 'Saturday', 42, 64, 42 & 64;

   Now run these 2 queries to better examine what we’re doing with the WHERE a.attributes & b.attribute <> 0 syntax:

   SELECT * FROM #myTable;
    
   SELECT *
   FROM #myTable
   WHERE bitwiseOperatorResults <> 0;

   The first query shows you the results of passing each of the values into the bitwise product, with the use of the & operator. The second query shows you only those values that are contained in the bitwise product, which means they are “selected.”

   Does that help? If not, let me know and I’ll expand on it further.
7. 

   Ranga
   April 20th, 2009 - 11:46

   Thanks a lot for the explanation….
8. 

   Mike
   December 23rd, 2009 - 11:55

   This area of programming is becoming a lost art. Storing and retreiving data by using bit masks (as this article describes) is still a common use of bitwise operators, but there are so many other uses, namely data manipulation and calculation, that are becoming forgotton.
9. 

   Darren
   February 12th, 2010 - 06:55

   Here’s an interesting “bug” that I’ve found:

   DECLARE @bitvar BIT
   SET @bitvar = 6
   PRINT @bitvar & 1
   PRINT @bitvar & 2
   PRINT @bitvar & 4
   PRINT 6 & 1
   PRINT 6 & 2
   PRINT 6 & 4
10. 

    Dave
    February 16th, 2010 - 07:31

    You can also do the same trick without creating a lookup table variable, like this:

    [code]
    declare @tmp table
    (
    val int,
    color nvarchar(100)
    )

    insert into @tmp (val, color) VALUES (0, 'black')
    insert into @tmp (val, color) VALUES (1, 'red')
    insert into @tmp (val, color) VALUES (2, 'yellow')
    insert into @tmp (val, color) VALUES (4, 'blue')
    insert into @tmp (val, color) VALUES (8, 'white')

    declare @newval int
    set @newval = 6

    select
    *
    from
    @tmp t cross join
    (SELECT (@newval) as val) n
    where
    t.val & n.val 0
    [/code]
11. 

    compact treadmill
    March 4th, 2010 - 00:20

    Helpful article and examples, Thanks.
12. 

    Paulo Pedro
    August 1st, 2010 - 09:25

    DECLARE @bitvar BIT
    SET @bitvar = 6
    PRINT @bitvar & 1
    PRINT @bitvar & 2
    PRINT @bitvar & 4
    PRINT CAST(6 AS BIT) & 1
    PRINT CAST(6 AS BIT) & 2
    PRINT CAST(6 AS BIT) & 4

    This is one of many situations where we should cast values first.

    
13. 

    Nabarupam Deb
    March 8th, 2011 - 05:57

    What to do if the data is bigger than the BIGINT Range?
14. 

    Quotes
    April 29th, 2011 - 20:31

    Thanks for sharing this post.It is really worth reading.

Leave a comment