Back to basics

  • Comments posted to this topic are about the item Back to basics

  • Great question on data types. Thanks.

  • Good question. But i never feel to use "Bitwise AND" (&) in real working scenario. It will good, if anyone come with some real working example. 🙂

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Nice question, thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • What if i define @S as BIGINT

    Can you give me a real time scenario as in this operator would be useful. i am just a bit curious as i have never used used it.

  • Danny Ocean (7/1/2013)


    Good question. But i never feel to use "Bitwise AND" (&) in real working scenario. It will good, if anyone come with some real working example. 🙂

    It provides a neat way of sending multiple options as a single integer parameter.

    For example, let's say you have a table called "Locations" with 2 columns, LocationID and Location, containing the following values:

    LocationID Location

    1 London

    2 Dublin

    4 Paris

    8 Berlin

    16 New York

    etc...

    Then you could select any combination of Location values as follows:

    declare @Selection int;

    set @Selection=10; -- (this will select Dublin and Paris, because Dublin's LocationID plus Paris's LocationID equals 10)

    select

    Location

    from

    Locations

    where

    LocationId & @Selection>0;

  • martin.whitton (7/2/2013)


    Danny Ocean (7/1/2013)


    Good question. But i never feel to use "Bitwise AND" (&) in real working scenario. It will good, if anyone come with some real working example. 🙂

    It provides a neat way of sending multiple options as a single integer parameter.

    For example, let's say you have a table called "Locations" with 2 columns, LocationID and Location, containing the following values:

    LocationID Location

    1 London

    2 Dublin

    4 Paris

    8 Berlin

    16 New York

    etc...

    Then you could select any combination of Location values as follows:

    declare @Selection int;

    set @Selection=10; -- (this will select Dublin and Paris, because Dublin's LocationID plus Paris's LocationID equals 10)

    select

    Location

    from

    Locations

    where

    LocationId & @Selection>0;

    Thanks martin 🙂

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • martin.whitton (7/2/2013)


    It provides a neat way of sending multiple options as a single integer parameter.

    Nice example Martin 🙂

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Danny Ocean (7/1/2013)


    Good question. But i never feel to use "Bitwise AND" (&) in real working scenario. It will good, if anyone come with some real working example. 🙂

    Hi, another example where BIT comparison is useful, msdb..sysschedules keeps the freq_interval in bitwise value 🙂

    use [msdb]

    go

    create table #DaysOfWeekBitWise(

    [bitValue] [tinyint] NOT NULL PRIMARY KEY,

    [name] [varchar](10) NULL,

    )

    go

    insert into #DaysOfWeekBitWise ([bitValue], [name])

    values (1, N'Sunday')

    , (2, N'Monday')

    , (4, N'Tuesday')

    , (8, N'Wednesday')

    , (16, N'Thursday')

    , (32, N'Friday')

    , (64, N'Saturday')

    go

    select j.name

    , case when j.enabled = 1 then 'Yes' else 'No' end as enabled

    , jsch.next_run_date

    , jsch.next_run_time

    --, jst.*

    , s.freq_interval

    , ISNULL( STUFF( (SELECT N', ' + name FROM #DaysOfWeekBitWise AS B WHERE B.bitValue & s.freq_interval = B.bitValue FOR XML PATH('') ), 1, 2, '' ), 'None' ) AS backup_schedule

    from msdb.dbo.sysjobs as j

    left join msdb.dbo.sysjobschedules as jsch

    on jsch.job_id = j.job_id

    left join msdb.dbo.sysschedules as s

    on s.schedule_id = jsch.schedule_id

    order by j.name

    go

    drop table #DaysOfWeekBitWise

    go

    Cheers

  • Patibandla (7/2/2013)


    What if i define @S as BIGINT

    Can you give me a real time scenario as in this operator would be useful. i am just a bit curious as i have never used used it.

    The last time I saw it used in a database was similar to the city option example given. In this instance, it was used to determine permissions across multiple databases by comparing the database 'lock' (say '01001001') to the user 'key' (say '11011001'). If the bitwise AND came back the same as the database lock, then you had access to the database.

    I would note the field is semantically overloaded. But, that is a different discussion.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • raulggonzalez (7/2/2013)


    Danny Ocean (7/1/2013)


    Good question. But i never feel to use "Bitwise AND" (&) in real working scenario. It will good, if anyone come with some real working example. 🙂

    Hi, another example where BIT comparison is useful, msdb..sysschedules keeps the freq_interval in bitwise value 🙂

    use [msdb]

    go

    create table #DaysOfWeekBitWise(

    [bitValue] [tinyint] NOT NULL PRIMARY KEY,

    [name] [varchar](10) NULL,

    )

    go

    insert into #DaysOfWeekBitWise ([bitValue], [name])

    values (1, N'Sunday')

    , (2, N'Monday')

    , (4, N'Tuesday')

    , (8, N'Wednesday')

    , (16, N'Thursday')

    , (32, N'Friday')

    , (64, N'Saturday')

    go

    select j.name

    , case when j.enabled = 1 then 'Yes' else 'No' end as enabled

    , jsch.next_run_date

    , jsch.next_run_time

    --, jst.*

    , s.freq_interval

    , ISNULL( STUFF( (SELECT N', ' + name FROM #DaysOfWeekBitWise AS B WHERE B.bitValue & s.freq_interval = B.bitValue FOR XML PATH('') ), 1, 2, '' ), 'None' ) AS backup_schedule

    from msdb.dbo.sysjobs as j

    left join msdb.dbo.sysjobschedules as jsch

    on jsch.job_id = j.job_id

    left join msdb.dbo.sysschedules as s

    on s.schedule_id = jsch.schedule_id

    order by j.name

    go

    drop table #DaysOfWeekBitWise

    go

    Cheers

    Thanks raulggonzalez 🙂

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Good question, genuinely back to basics, but as your int value was 32767 wouldn't 32512 have been a better distractor than 65280?

    Tom

  • Patibandla (7/2/2013)


    What if i define @S as BIGINT

    According to BOL, [Bitwise Operations (Transact-SQL) bitwise operations don't support bigint; in practise (in SQL 2012 anyway) you can mix and match bit, tinyint, smallint, int, and bigint freely with bitwise AND, bitwise OR, and bitwise XOR. I suspect that this is an error in BOL and bigint actually is supported, but it would be risky to use bigint with bitwise operators for production without confirmation from Microsoft that this is the case. There's a community comment on the page that saying that bigint works, but MS don't appear to police those at all.

    Tom

  • Thanks for the question!



    Everything is awesome!

  • martin.whitton (7/2/2013)


    Danny Ocean (7/1/2013)


    Good question. But i never feel to use "Bitwise AND" (&) in real working scenario. It will good, if anyone come with some real working example. 🙂

    It provides a neat way of sending multiple options as a single integer parameter.

    For example, let's say you have a table called "Locations" with 2 columns, LocationID and Location, containing the following values:

    LocationID Location

    1 London

    2 Dublin

    4 Paris

    8 Berlin

    16 New York

    etc...

    Then you could select any combination of Location values as follows:

    declare @Selection int;

    set @Selection=10; -- (this will select Dublin and Paris, because Dublin's LocationID plus Paris's LocationID equals 10)

    select

    Location

    from

    Locations

    where

    LocationId & @Selection>0;

    I'm confused. Doesn't 2 (Dublin) and 8 (Berlin) equal 10 not 2 (Dublin) and 4 (Paris)? What am I missing?

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply