|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Bitwise operations on BigIntIt appears that one cannot perform bitwise operations on a bigint data
type. First, is this correct? If so, is there a funtion that can split a bigint into two integers (high and lo). I have a table that uses two bigint columns to store up to 63 On/Off flags for each column. I need to be able to locate all rows with a particular flag set. Everything works smoothly as long as I am examining any of the low 32 bits. Once I get into the high 32 bits, nothing works. Any help will be much appreciated!!! Bill Hi
Bitwise operators can only be used on int, smallint, or tinyint data. You should seriously re-consider your data model if you are doing bitwise operations to find data. SQL Server is a relational DB and normalization is the way to do this. Regards -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ Show quote "Bill Mullen" wrote: > It appears that one cannot perform bitwise operations on a bigint data > type. First, is this correct? > > If so, is there a funtion that can split a bigint into two integers > (high and lo). > > I have a table that uses two bigint columns to store up to 63 On/Off > flags for each column. I need to be able to locate all rows with a > particular flag set. Everything works smoothly as long as I am > examining any of the low 32 bits. Once I get into the high 32 bits, > nothing works. > > Any help will be much appreciated!!! > > Bill > Mike,
Thanks. Do you have any suggestions concerning the data model? I understand that you do not have access to the model, but given this limited information, can you make a suggestion? I have a player table that stores various data about the player from thier application. The application currently list 45 special interest categories and ask the player to select all that apply. The business rule also states that there will never be more than 60 interest categories and there are rules defined for the time when they hit 60. Thanks again. Bill On Fri, 8 Jul 2005 02:27:01 -0700, "Mike Epprecht (SQL MVP)" <m***@epprecht.net> wrote: Show quote >Hi > >Bitwise operators can only be used on int, smallint, or tinyint data. > >You should seriously re-consider your data model if you are doing bitwise >operations to find data. SQL Server is a relational DB and normalization is >the way to do this. > >Regards >-------------------------------- >Mike Epprecht, Microsoft SQL Server MVP >Zurich, Switzerland > >MVP Program: http://www.microsoft.com/mvp > >Blog: http://www.msmvps.com/epprecht/ > > > >"Bill Mullen" wrote: > >> It appears that one cannot perform bitwise operations on a bigint data >> type. First, is this correct? >> >> If so, is there a funtion that can split a bigint into two integers >> (high and lo). >> >> I have a table that uses two bigint columns to store up to 63 On/Off >> flags for each column. I need to be able to locate all rows with a >> particular flag set. Everything works smoothly as long as I am >> examining any of the low 32 bits. Once I get into the high 32 bits, >> nothing works. >> >> Any help will be much appreciated!!! >> >> Bill >> Implement it as an additional table that joins playes to their
interests. Like this: CREATE TABLE PlayerInterests (player_id INTEGER NOT NULL REFERENCES player_id, interest_id INTEGER NOT NULL REFERENCES Interests (interest_id), PRIMARY KEY (player_id, interest_id)) Interests are therefore represented by rows rather than bits, which makes it much easier to query. This is relational design 101 so I recommend you invest in some books on DB design. It's difficult to cover all the pre-requisites in an online forum. -- David Portas SQL Server MVP -- Storing the 60 responses as bits in an integer would be the most storage
efficient method. However, it appears that this application will not be storing a lot of data. I mean, how many players are there total, perhaps a few hundred? Show quote "Bill Mullen" <bill@no_such_address.com> wrote in message news:pbjsc11q7clq7m7cb34c883c2t6khusdbj@4ax.com... > Mike, > > Thanks. Do you have any suggestions concerning the data model? I > understand that you do not have access to the model, but given this > limited information, can you make a suggestion? > > I have a player table that stores various data about the player from > thier application. The application currently list 45 special interest > categories and ask the player to select all that apply. The business > rule also states that there will never be more than 60 interest > categories and there are rules defined for the time when they hit 60. > > Thanks again. > Bill > > > On Fri, 8 Jul 2005 02:27:01 -0700, "Mike Epprecht (SQL MVP)" > <m***@epprecht.net> wrote: > > >Hi > > > >Bitwise operators can only be used on int, smallint, or tinyint data. > > > >You should seriously re-consider your data model if you are doing bitwise > >operations to find data. SQL Server is a relational DB and normalization is > >the way to do this. > > > >Regards > >-------------------------------- > >Mike Epprecht, Microsoft SQL Server MVP > >Zurich, Switzerland > > > >MVP Program: http://www.microsoft.com/mvp > > > >Blog: http://www.msmvps.com/epprecht/ > > > > > > > >"Bill Mullen" wrote: > > > >> It appears that one cannot perform bitwise operations on a bigint data > >> type. First, is this correct? > >> > >> If so, is there a funtion that can split a bigint into two integers > >> (high and lo). > >> > >> I have a table that uses two bigint columns to store up to 63 On/Off > >> flags for each column. I need to be able to locate all rows with a > >> particular flag set. Everything works smoothly as long as I am > >> examining any of the low 32 bits. Once I get into the high 32 bits, > >> nothing works. > >> > >> Any help will be much appreciated!!! > >> > >> Bill > >> > Currently, a little over 300K
Show quote >I mean, how many players are there total, perhaps a >few hundred? Not a big table then, as JT guessed. But regardless of size it's
important to have an efficient database design. You can't efficiently query a bitmapped column. Did you try my suggestion? -- David Portas SQL Server MVP -- We are examing the work effort to determine exactly what will need to
be done (both to the database design and the underlying application). We are currently looking at around 96 hours. Understanding that this is not very effcient for performing queries, the only time this will be performed is to generate mailing lists for marketing purposes - maybe once or twice a week. If we can reduce the options to 31 or fewer would you still suggest the rework? I appreciate your insight and help. On 8 Jul 2005 07:31:43 -0700, "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote: Show quote >Not a big table then, as JT guessed. But regardless of size it's >important to have an efficient database design. You can't efficiently >query a bitmapped column. Did you try my suggestion? > >-- >David Portas >SQL Server MVP If level of effort is an issue, then perhaps store the bits in 2 ints
instead of 1 bigint? If you decide to go the route of implementing a foreign key table to store the question responses, then you could by with about 6 bytes per record. PlayerID int, QuestionID tinyint, Response bit Show quote "Bill Mullen" <bill@no_such_address.com> wrote in message news:5fdtc1terfh0u2qk5kts779nhemnfjj9ru@4ax.com... > We are examing the work effort to determine exactly what will need to > be done (both to the database design and the underlying application). > We are currently looking at around 96 hours. > > Understanding that this is not very effcient for performing queries, > the only time this will be performed is to generate mailing lists for > marketing purposes - maybe once or twice a week. If we can reduce the > options to 31 or fewer would you still suggest the rework? > > I appreciate your insight and help. > > > On 8 Jul 2005 07:31:43 -0700, "David Portas" > <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote: > > >Not a big table then, as JT guessed. But regardless of size it's > >important to have an efficient database design. You can't efficiently > >query a bitmapped column. Did you try my suggestion? > > > >-- > >David Portas > >SQL Server MVP > > If we can reduce the Absolutely. And the fact that you are considering compromising your business > options to 31 or fewer would you still suggest the rework? requirements in that way demonstrates one reason why your bitmap design decision was wrong in the first place. -- David Portas SQL Server MVP -- > It appears that one cannot perform bitwise operations on a bigint data Why would you want to? Bitmapped flags are a lousy way to store data in> type. First, is this correct? SQL Server. -- David Portas SQL Server MVP -- David,
Thank you, but can you not offer some suggestions to solve the problem? - Bill On 8 Jul 2005 02:56:47 -0700, "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote: Show quote >> It appears that one cannot perform bitwise operations on a bigint data >> type. First, is this correct? > >Why would you want to? Bitmapped flags are a lousy way to store data in >SQL Server. > >-- >David Portas >SQL Server MVP > Why would you want to? Bitmapped flags are a lousy way to store data in In *ANY* system. back in '94 I had to crunch on that stuff in wimpy Access > SQL Server. 2.0 on data coming from a COBOL program on an HP 3000. I can still hear Butthead in the back of my mind saying, "That sucked worse than anything has ever sucked before." -- Peace & happy computing, Mike Labosh, MCSD "Working here is like living inside a Salvador Dali painting." -- Me. Yeah, ME! [Oh fer cryin out loud] > I have a table that uses two bigint columns to store up to 63 On/Off I hate when they do that.> flags for each column. I need to be able to locate all rows with a > particular flag set. Everything works smoothly as long as I am > examining any of the low 32 bits. Once I get into the high 32 bits, > nothing works. If you have a .NET guy lurking around, the .NET framework has a BitArray class that I think he can use. You get the values from the database into a DataTable, and then you can use a BitArray object by feeding it your numbers, and then examine it as an array of Booleans. [this will wrap] http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemcollectionsbitarrayclasstopic.asp -- Peace & happy computing, Mike Labosh, MCSD "Working here is like living inside a Salvador Dali painting." -- Me. Yeah, ME! [Oh fer cryin out loud] Bill,
What did you try? The bitwise operators in T-SQL work with bigint. For example: declare @a bigint declare @b bigint set @a = 123456789123 set @b = 321654987654 select @a | @b, @a & @b, ~@a /* Result: 408000502663 37111274114 -123456789124 */ Steve Kass Drew University Bill Mullen wrote: Show quote >It appears that one cannot perform bitwise operations on a bigint data >type. First, is this correct? > >If so, is there a funtion that can split a bigint into two integers >(high and lo). > >I have a table that uses two bigint columns to store up to 63 On/Off >flags for each column. I need to be able to locate all rows with a >particular flag set. Everything works smoothly as long as I am >examining any of the low 32 bits. Once I get into the high 32 bits, >nothing works. > >Any help will be much appreciated!!! > >Bill > > All - As Steve showed, SQL Server CAN perform bitwise operations on
BigInts. Our code was type casting (accidentally) the bigint to check to an integer type and therefore was returning the wrong results. We are still looking into changes for a future release, but we will continue down the current path as it now works. Just for grins, I ran a test that calculates the total number of each bit set for the 300+K rows of data. It took less than one second to run the report, and I get results back almost instantly when selecting rows with a specific bit set. I appreciate all the help and information. - Bill On Fri, 08 Jul 2005 04:17:15 -0500, Bill Mullen <bill@no_such_address.com> wrote: Show quote >It appears that one cannot perform bitwise operations on a bigint data >type. First, is this correct? > >If so, is there a funtion that can split a bigint into two integers >(high and lo). > >I have a table that uses two bigint columns to store up to 63 On/Off >flags for each column. I need to be able to locate all rows with a >particular flag set. Everything works smoothly as long as I am >examining any of the low 32 bits. Once I get into the high 32 bits, >nothing works. > >Any help will be much appreciated!!! > >Bill > We are still looking into changes for a future release, but we will Yep, social security "works," as does my 1985 lawnmower and my AppleIIc. > continue down the current path as it now works. That doesn't mean I like or trust them.  |
|||||||||||||||||||||||
Other interesting topics